3

I have a postgres database that uses sequences extensively to generate primary keys of tables. After a lot of usage of this database i.e. Adding/Update/Delete operation the columns that uses sequences for primary keys now have a lot holes/gaps in them and the sequence value itself is very high.

My question is: Are there any ways in which we can fix these gaps in Primary Keys? which should inturn bring down the max value of the number in that columns and then reset the sequence?

Note: A lot of these columns are also referenced by other columns as ForeignKeys.

Salman A. Kagzi
  • 3,833
  • 13
  • 45
  • 64
  • 3
    This is normal database operation - why do you feel the need to fill the gaps? Neatness? – Oded Jun 03 '11 at 07:38
  • 1
    Unless 'very high' means very close to the maximum value for the sequence (which is unlikely), you should not have to worry about those numbers. Any number is as good as every other number more or less. – schlenk Jun 03 '11 at 07:43
  • @Oded Neatness is not that big of a reason. The fact that there are huge add & delete operations performed on some of the tables, which have left quite big holes there and I am afraid that going further I might reach a point where this becomes a problem, i.e. there is not enough data but still i we run out of integer space. – Salman A. Kagzi Jun 03 '11 at 07:45
  • @Clodoaldo If I am able to fill in gaps, there wont be a need for bigint. The fact is, at any points there is never that much amount of data in those tables. It is only because of the large number of insert & delete operation that an Int column can run out and to over a long duration. I am still to calculate these duration. If there is something that can be used to fill in these holes by updating the IDs, it would only be used Quarterly or half yearly depending on the time that comes out of the calculation. – Salman A. Kagzi Jun 03 '11 at 17:08

3 Answers3

1

If you feel the need to fill gaps in auto-generated Posgresql sequence numbers, I have the feeling you need another field in your table, like some kind of "number" you increment programmatically, either in your code, or in a trigger.

plang
  • 5,446
  • 3
  • 25
  • 36
1

It is possible to solve this problem, but is expensive for the database to do (especially IO) and is guaranteed to reoccur. I would not worry about this problem. If you get close to 4B, upgrade your primary and foreign keys to BIGSERIAL and BIGINT. If you're getting close to 2^64... well... I'd be interested in hearing more about your application. :~]

Sean
  • 9,888
  • 4
  • 40
  • 43
0

Postgres allows you to update PKs, although a lot of people think it's bad practice. So you could lock the table, and UPDATE. (You can make an oldkey, newkey table all sorts of ways, e.g., window function.) All the FK relationships have to be marked to cascade. Then you can reset the currval of the id sequence.

Personally, I would just use a BIGSERIAL. If you have so many updates and deletes that you may run out even so, maybe there is some composite PK based on (say) a timestamp and id that would help you.

Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53