Postgres Serials Should be BIGINT (and How to... | Crunchy Data Blog

Elizabeth Christensen

Jan 20, 2026·11 min read

More by this author

Latest Articles<br>Postgres Serials Should be BIGINT (and How to Migrate)<br>Postgres 18 New Default for Data Checksums and How to Deal with Upgrades<br>PostGIS Performance: Simplification<br>Postgres Scan Types in EXPLAIN Plans<br>PostGIS Performance: Data Sampling

Production Postgres<br>Postgres Serials Should be BIGINT (and How to Migrate)

Elizabeth Christensen

Jan 20, 2026·11 min read·More by this author

Lots of us started with a Postgres database that incremented with an id SERIAL PRIMARY KEY. This was the Postgres standard for many years for data columns that auto incremented. The SERIAL is a shorthand for an integer data type that is automatically incremented. However as your data grows in size, SERIALs and INTs can run the risk of an integer overflow as they get closer to 2 Billion uses.We covered a lot of this in a blog post The Integer at the End of the Universe: Integer Overflow in Postgres a few years ago. Since that was published we’ve helped a number of customers with this problem and I wanted to refresh the ideas and include some troubleshooting steps that can be helpful. I also think that BIGINT is more cost effective than folks realize.SERIAL and BIGSERIAL are just shorthands and map directly to the INT and BIGINT data types. While something like CREATE TABLE user_events (id SERIAL PRIMARY KEY) would have been common in the past, the best practice now is BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY is recommended. SERIAL/ BIGSERIAL are not SQL standard and the GENERATED ALWAYS keyword prevents accidental inserts, guaranteeing the database manages the sequence instead of a manual or application based addition.INT - goes up to 2.1 Billion (2,147,483,647) and more if you do negative numbers. INT takes up 4 bytes per row column.BIGINT- goes up 9.22 quintillion (9,223,372,036,854,775,807) and needs a 8-bytes for storage.Serials vs UUID Before I continue talking about serials in Postgres, it is worth noting that Postgres also has robust UUID support, including v7 which was just released. If you decide to go with UUID, great. This makes a ton of sense for things that can be URLs or are across systems. However not all ids need to be UUIDs, so lots of folks still continue with a serialized / incremented integers.Cost difference between INT and BIGINT<br>Postgres does not pack data tightly like a text file. It writes data in aligned tuples /  rows, and standard 64-bit servers require data to line up on 8-byte boundaries. In many table layouts, INT and BIGINT consume the exact same amount of disk space. The "savings" of INT are often eaten by empty padding bytes.Think of this sample table:INTHeader: 24 bytes (Standard row overhead)Data: 4 bytes (INT)Padding: PostgreSQL adds 4 empty bytes to fill the gap so the next row starts on an 8-byte boundary.Total per Row: $24 + 4 + 4 = 32BIGINTHeader: 24 bytes (Standard row overhead)Data: 8 bytes (BIGINT)Padding: 0 bytes (Already perfectly aligned to 8 bytes).Total per Row: $24 + 8 + 0 = 32You pay $0.00 extra for using BIGINT.Even in the scenario where your specific column order does result in a true 4-byte increase per row for BIGINT, the costs are negligible. Let’s say you have 4 extra bytes per row for a billion rows, that’s just ~4 GB. On Crunchy Bridge that’s about .40 cents a month (similar on other modern clouds).Using BIGINT instead of INT for a database bound for production sequencing is probably the safer bet if you’re logging anything like timestamps, page hits, or things that will be incrementing to the millions or billions. Avoiding the man hours and cost to do an in-place data type change of this nature is worth it.Live data type change in Postgres - the atomic swap<br>Ok, let’s say I’ve convinced you to move to BIGINT now. Maybe you’re close to integer wraparound or maybe you’re small enough that you can do this now before it becomes a bigger headache.Changing a production data column type is always tricky business. The data type change needs to be done across millions and billions of rows in production, but:We can’t lock the tableWe don’t want to take downtimeWe need to preserve the current incrementsLuckily our support team helps folks often with these types of changes and with this blog I’ve collected notes and helpful tips over dozens of these projects for this blog post.The foundational strategy for this migration is to perform the bulk of the work asynchronously—while the application remains online—by creating a new BIGINT column, backfilling the data, and then performing a quick, single-transaction switchover. We like to call this changeover an atomic swap. Atomic swap is a specific technique used to switch a live table with a new version of itself without taking the application offlineHere is the high-level plan:Add a new BIGINT column, sequence, and a unique index. Backfill the old id values into the new column in...