On Mon, 2006-12-04 at 17:53, Raymond O'Donnell wrote:
> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
> > When a row is deleted the serial number and oid are also deleted. The
> > sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php
Yeah. The requirement for no gaps is a real no starter for any kind of
large data set.
Imagine modelling all the people in the US. 350 million entries. No
big deal. I can create that in minutes.
You each one give a number. Again, no big deal. A couple of minutes.
For everyone that dies, you remove the name. No big deal. a couple
seconds to run a delete.
For every one that is born, you add it to the list, giving it the next
number. again, no big deal. My workstation could probably handle the
load.
Now, turn that on its head. Every time you delete someone, you have to
renumber the data set, and for everyone added you have to make sure
there are no gaps.
Suddenly, you've got a problem that could bring even big iron to its
knees. All because some buearocrat (sp) hadn't the imagination to think
of non-sequential numbering systems.
While there are occasional systems where it is reasonable to actually
have no sequential gaps, most of the time the only justification is "I
don't like them." If that is the case, you should rethink your design.
If you're stuck with them because of some idiotic rule from on high,
then at least abstract the numbers to some degree to improve performance
and keep you from having to update about half of an entire table several
times a minute.
