Hacker News Reader

Comment by PestoDiRucola | Hacker News Reader

One thing I don’t see being mentioned in this thread (I only skimmed the article, so I don’t know if it’s mentioned there) is that you can run out of numbers when using serial, as they have a max, so if you are planning to have a table which will have over 2147483647 rows, then you might look into other types to use as a unique identifier.

View on HN

You can use bigserial instead of serial, which goes up to 9223372036854775807.

If your DB supports unsigned integers, or starting sequences from -2,147,483,648, you can double the address range.

But if you are at all worried that you'll get within a couple of orders of magnitude of MAXINT32 in the lifetime of your application then you should immediately jump to 64-bit values. Doubling is often just noise and the cost of refactoring if you approach MAXINT32 much faster than expected is more or a problem than the extra storage cost of bigger keys.

PostgreSQL doesn't have unsigned integers.

Nor does MS SQL Server, though you can start IDENTITY from a maximally negative value. But any time I'd consider that I'd just jump up to an larger integer type instead.

Just use biginteger everywhere, still more efficient than even UUIDv4.

For anyone wondering, this is around 60 records per second for a year before you max out

> you can run out of numbers when using serial

Yes, that's why you use bigserial.

But the comment I replied to was mentioning serial though.

I think you can just make that dependent on the table's data. For a table of user posts use bigserial but for a list of companies, prices or names the serial will surely be sufficient.

Reply on news.ycombinator.com