1

I have a Django application that uses PostgreSQL to analyze data from tweets. The data set increases by thousands of records with each request. I am using the database primarily as a cache, so I had planned to delete all records every 24 hours to permit new requests without needlessly increasing the size of the database.

Django uses the SERIAL type for storing the ids; a new item is given the next highest value of the last item that was created, rather than the first available number. I don't use the ids for anything outside of the ORM. My concern is that I will eventually run out of key space on my 32 bit VM. What does PostgreSQL when the next value is too large for SERIAL? Does it give error? Does it roll back to one?

Erwin Brandstetter
  • 146,376
  • 19
  • 347
  • 493
Sean W.
  • 125
  • 6
  • 1
    which version of postgres are you running? are you able to specify `bigserial` for a `bigint`? – swasheck Aug 05 '13 at 21:23
  • looks like you'd need to use a BigAutoField – swasheck Aug 05 '13 at 21:28
  • I'm not exactly sure how to change the field type in the Django models yet. Thanks for the help! – Sean W. Aug 05 '13 at 21:46
  • If (and only if) you _really_ delete the data, you can change the underlying sequence to `CYCLE` around. But using a bigserial is still a better idea. – dezso Aug 06 '13 at 09:39

1 Answers1

4

TLDR: If you're going to have more than 231 possible values change the column type to BIGSERIAL.

Explanation:

The SERIAL type is a signed 32-bit integer and is designed for values less than 231 (NOTE: that's 231, not 232 as they're signed integers). Here's the snippet from the PostgreSQL docs:

The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type names smallserial and serial2 also work the same way, except that they create a smallint column.

The behavior of how it handles overflowing past 231 isn't mentioned in the docs but it clearly states that you shouldn't use it if you expect to have values greater than that.

SERIAL columns are sequence numbers. The server keeps track of the previous value (the prior max) and each time a value is requested the value is incremented. This incrementation can happen regardless of whether the id is actually permanently saved. If the transaction that fetched the id is rolled back then the id will be "lost" and your sequence will have holes in it (eg. gaps of unused ids). There's nothing wrong with that and if you're simply using them as unique ids then having gaps should cause no issue.

Sequences are implemented like this to be efficient. The server only needs to keep track of one value (the prior max) to uniquely generating ids, it can cache them across multiple connections to speed up sequence generation, and by allowing gaps it never needs to transactionally lock the sequence object when it's generating sequences. This makes them very efficient and concurrent.

Just switch your database table to use BIGSERIAL.

dreamlax
  • 355
  • 2
  • 5
  • 13
sehrope
  • 968
  • 6
  • 9
  • 1
    If it overflows you'll get a `value too big for integer` error. It's trivial to test this; just `setval(...)` to maxint, then increment. And before anyone asks you really, really don't need to worry about running out of bigserial values. – Craig Ringer Aug 06 '13 at 04:24
  • "Just switch your database table to use BIGSERIAL". If the FK is not used anywhere can you just do this? `ALTER TABLE foo DROP COLUMN id;` `ALTER TABLE foo ADD COLUMN id bigserial NOT NULL PRIMARY KEY;` – kev Aug 29 '14 at 00:46