Re: 4 billion record limit?
От | Chris Bitmead |
---|---|
Тема | Re: 4 billion record limit? |
Дата | |
Msg-id | 397FB6B9.267A1A62@nimrod.itg.telecom.com.au обсуждение исходный текст |
Ответ на | 4 billion record limit? (Paul Caskey <paul@nmxs.com>) |
Список | pgsql-general |
The Versant ODBMS uses 48 bit oids, and if you do the math I think you'll find that should last you forever. (It uses an additional 16 bits to identify the database, but that's another story.). Any complex scheme to solve this seems like a waste of time. In a couple of years when you are likely to be running out, you'll probably be upgrading your computer to a 64bit one with a newer version of postgres, and then the problem will disappear. brad wrote: > > THe implications of the OIDs not wrapping are immense. We have some extremely > active databases that will easily reach this limit in two or three years. For > some applications, such as ecommerce, dumping then reinserting the rows is > not an option for large databases due to the 24 hours nature of their work. > > This is a much more complex problem than it would at first seem as the > "tripping up" over old records with low OIDs still presents a problem, yet if > the system is changed to try and manage a list of available OIDs, it will > then hit performance problems. > > Simply waiting for 64bit numbers is rather inelegant and also presumes usage > parameters for the database... remember Bill Gates saying that he couldn't > foresee any usage for more than 64MB of RAM? Besides which, PostgreSQL is the > best DB around... there's a high standard to maintain! > > Some initial ideas: > a) The system remains working the way that it does until a configurable > cutoff point (% of OIDs remaining), at which point the DBA can either switch > on some sort of OID "garbage collection" and take the associated performance > hit, or dump the data and reinsert it to pack the OIDs. > GARBAGE COLLECTION: > b) The system could wrap around, keeping an internal pointer of where it is > in the OID chain. It could scan the OIDs sequentially finding the first free > OID. It coudl then store that position as the new start point for the next > time an OID is needed. > c) An OID compression utility could be writen that doesn't require bringing > the DB down (but will obviously have a performance it). As running this > utilty would be a known impact, provision could be made, or the knock > budgeted for and accounted for while not bringing the entire DB to a halt. > d) OIDs themselves could form a list. The OID system as it stands now could > be the "default mode" but if the OIDs run out, then a new OID list is > started. This means the OID usage would then change to work along the same > lines that the Intel memory adressing works. Personally I hate it, but it > does work. > e) OIDs could be scrapped in favour of some other system. > > Well, that's my two pence worth. > > Brad > > Paul Caskey wrote: > > > Tom Lane wrote: > > > > > > Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes: > > > > Paul Caskey wrote: > > > >> 1. This implies a hard limit of 4 billion records on a server, right? > > > > > > > Basically, yes. > > > > > > It's only a hard limit if your application assumes OIDs are unique. > > > If you don't assume that, then I think it's not a big problem. > > > > > > It's possible (though obviously not especially likely) that you might > > > get OID collisions in the system tables after an OID-counter wraparound. > > > > This implies they do wrap around. So they are reused? Chris said no, but > > you're saying yes. > > > > (Maybe they wrap around "by accident", by adding one to MAXINT, which will > > give zero on an unsigned int, I believe. Will the system choke on zero? > > Has anyone tested this wraparound?) > > > > I will not have 4 billion records in one table or even one database. But > > on a large server with many databases, it is conceivable to have 4 billion > > records on one machine. With a lot of insert/delete activity, over a few > > years, it is certainly conceivable to have 4 billion inserts. If the oids > > don't wrap, I have a problem. I can ignore it for a long time, but it > > will loom, like Y2K. :-) > > > > Even if they do wrap, if I have some old records lying around with a low > > OIDs, they will trip me up. Like you said, these are "the outer limits", > > but I'm thinking ahead. > > > > Someone suggested in private that I pg_dump/restore all my data to > > "repack" the oids which start around 1700 on a fresh database. Thanks for > > that idea. Also thanks, Tom, for the sanity check Re: terabytes of data > > with 4 billion records. It's still possible, especially in coming years. > > It would be a big feather in PG's cap to "fully support" 64-bit platforms > > such as IRIX and Solaris (finally) and, coming soon to a theater near you, > > Linux on IA-64. > > > > -- > > Paul Caskey paul@nmxs.com 505-255-1999 > > New Mexico Software 5041 Indian School NE Albuquerque, NM 87110 > > --
В списке pgsql-general по дате отправления:
Следующее
От: Thomas LockhartДата:
Сообщение: Re: Some questions on user defined types and functions.