Postgres Serials Should be BIGINT (and How to...

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

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:

INT

Header: 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 = 32

BIGINT

Header: 24 bytes (Standard row overhead)
Data: 8 bytes (BIGINT)
Padding: 0 bytes (Already perfectly aligned to 8 bytes).
Total per Row: $24 + 8 + 0 = 32

You 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

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 table
We don’t want to take downtime
We need to preserve the current increments

Luckily 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 offline

Here 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 batches.
Changeover (Brief Downtime): Lock the table, complete the final backfill, drop old constraints, rename columns (id to id_old, id_new to id), and add a non-validated NOT NULL constraint.
Validate the NOT NULL constraint, promote the column to a Primary Key, and clean up.

Set Up the Test Environment

I’ll provide some sample code for doing a full INT to BIGINT changever. This will make more sense with a sample table that mimics a real-world scenario where the SERIAL primary key is the bottleneck. I’ve also added steps for a foreign key constraint because we see this frequently.

-- 1. Create the Parent Table (Standard SERIAL / INT)
CREATE TABLE user_events (
    id SERIAL PRIMARY KEY,
    data TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

-- 2. Create a Child Table (Foreign Key Dependency)
CREATE TABLE user_events_log (
    log_id SERIAL PRIMARY KEY,
    event_id INTEGER NOT NULL,
    log_message TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    CONSTRAINT fk_user_events
        FOREIGN KEY (event_id)
        REFERENCES user_events (id)
);

-- 3. Populate Initial Data (100k rows)
INSERT INTO user_events (data, created_at)
SELECT 'Historical Data', NOW() - (random() * (interval '90 days'))
FROM generate_series(1, 100000);

INSERT INTO user_events_log (event_id, log_message)
SELECT id, 'Log entry for event ' || id
FROM user_events;

-- We start inserting rows in the background to prove the migration is "Online".  (You may need to configure pg_cron in your environment for this to work.)
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'generate-events-traffic',
    '2 seconds', -- Runs every 2 seconds
    $$
    INSERT INTO user_events (data, created_at)
    SELECT 'Live Incoming Traffic', NOW()
    FROM generate_series(1, 1000);
    $$
);

Add the New BIGINT Columns

We add the column allowing NULLs. Later when we create the primary key index, NULLs will not be allowed. This is a quick metadata change even to a large table. It does take a short lock on the table, but only for a tiny blip because we’re creating a new column.

ALTER TABLE user_events ADD COLUMN id_new BIGINT;
ALTER TABLE user_events_log ADD COLUMN event_id_new BIGINT;

If you’re doing the full test, a trigger ensures any new rows inserted into this table will get their 'id_new' field populated automatically.

CREATE OR REPLACE FUNCTION sync_id_new()
RETURNS TRIGGER AS $$
BEGIN
    NEW.id_new := NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_id_new
BEFORE INSERT ON user_events
FOR EACH ROW
EXECUTE FUNCTION sync_id_new();

Backfill in batches

Now we can backfill the new column from the old one. We’ll do this in batches to avoid a massive transaction that could cause replication lag or I/O spikes. We write this as a PROCEDURE to allow us to specify the batch size and sleep time, also allowing it to COMMIT between batches.

CREATE OR REPLACE PROCEDURE backfill_id_new(batch_size INTEGER, sleep_time FLOAT)
AS $$
DECLARE
    rows_updated BIGINT := 0;
    max_id_to_process BIGINT;
BEGIN
    -- We define a "high water mark" so we don't chase the moving target forever.
    -- We know any rows higher than this value will not need to be backfilled.

    SELECT MAX(id) INTO max_id_to_process FROM user_events;

    LOOP
        WITH rows_to_update AS (
            SELECT id
            FROM user_events
            WHERE id_new IS NULL
            AND id <= max_id_to_process
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED
        )
        UPDATE user_events m
        SET id_new = r.id
        FROM rows_to_update r
        WHERE m.id = r.id;

        GET DIAGNOSTICS rows_updated = ROW_COUNT;

        COMMIT;

        EXIT WHEN rows_updated = 0;

        PERFORM pg_sleep(sleep_time);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Run Backfill on the main table
CALL backfill_id_new(1000, 0.5);

-- Backfill Child Table (We are using a simple update for our test script, in
-- practice you would use the same batching approach in prod)
UPDATE user_events_log SET event_id_new = event_id;

Batch vacuum

For a small test like this one, you don’t need to vacuum but as we’ve found with larger production moves, regularly running VACUUM is crucial during the backfill process to clean up dead rows created by the UPDATE statements. The old rows need to be cleaned up since we have new rows that have both INT and BIGINT and this cleanup prevents table bloat.

-- Run this command after every 5-10 backfill batches (e.g., every 500,000 rows)
VACUUM (ANALYZE, VERBOSE) user_events;
VACUUM (ANALYZE, VERBOSE) user_events_log;

You may need to play around with your batch size. Instead of using very large batch sizes (e.g., 4 million rows), stick to a smaller, efficient size (like 100,000 rows). The overall time for a smaller batch plus a vacuum proved to be more efficient than a single massive batch followed by a prolonged vacuum.

Create an index concurrently

We can now create the necessary unique index, which will eventually enforce the primary key constraint. Using CONCURRENTLY is the key to maintaining uptime.

-- 1. Ensure all backfilled rows are NOT NULL for the index creation
ALTER TABLE user_events
ALTER COLUMN id_new SET NOT NULL;

-- 2. Create the unique index CONCURRENTLY (non-locking DML)
CREATE UNIQUE INDEX CONCURRENTLY user_events_id_new_idx ON user_events (id_new);

Final catch-up and sequence configuration

Before the final swap, we perform a quick update on any rows inserted since the initial backfill and configure the sequence to start from the highest existing ID.

-- 1. Catch-up: Update any rows that were inserted during the batch backfill
-- This should be fast, as it only targets newly inserted rows (id_new IS NULL)
UPDATE user_events
SET id_new = id
WHERE id_new IS NULL;

-- 2. Get the new sequence ready to continue from the largest existing ID
-- SERIAL uses an underlying sequence. We rename it to use for IDENTITY
ALTER SEQUENCE user_events_id_seq RENAME TO user_events_id_identity_seq;

-- Set the sequence to the current max value of the old ID (plus a buffer, e.g., 1000)
SELECT setval('user_events_id_identity_seq', (SELECT MAX(id) FROM user_events) + 1000, false);

Updating foreign key columns on the child table

Any table that has a foreign key referencing the primary table's ID column must be updated to BIGINT before the main table's switchover is completed. 🫠

This process is simpler as these columns are not primary keys, but it still requires a process of adding a new BIGINT foreign key column, backfilling, and performing a quick rename switchover for each referencing table. The trick here is adding the constraint as NOT VALID and making it valid later.

-- 1. Enforce NOT NULL on Parent
ALTER TABLE user_events ALTER COLUMN id_new SET NOT NULL;

-- 2. Create Unique Index Concurrently, this prepares the future Primary Key without locking writes)
CREATE UNIQUE INDEX CONCURRENTLY user_events_id_new_idx ON user_events (id_new);

-- 3. Add Foreign Key Constraint to Child (NOT VALID)
ALTER TABLE user_events_log
    ADD CONSTRAINT fk_user_events_new
    FOREIGN KEY (event_id_new)
    REFERENCES user_events (id_new)
    NOT VALID;

-- 4. Validate FK (Scans table, but does not block parent updates)
ALTER TABLE user_events_log VALIDATE CONSTRAINT fk_user_events_new;

-- Done before the parent swap. Brief exclusive lock on child table only.

BEGIN;
    LOCK TABLE user_events_log IN ACCESS EXCLUSIVE MODE;

    -- Drop old FK and column
    ALTER TABLE user_events_log DROP CONSTRAINT fk_user_events;
    ALTER TABLE user_events_log DROP COLUMN event_id;

    -- Rename new column/constraint to match old names
    ALTER TABLE user_events_log RENAME COLUMN event_id_new TO event_id;
    ALTER TABLE user_events_log RENAME CONSTRAINT fk_user_events_new TO fk_user_events;
COMMIT;

The atomic swap (brief lock)

If you followed along for the sake of testing, stop your cron job SELECT cron.unschedule('generate-events-traffic');.

This is the final step, done inside a single transaction. It requires an exclusive lock, but since the index is already built, this step is purely metadata and should take milliseconds.

BEGIN;
    LOCK TABLE user_events IN ACCESS EXCLUSIVE MODE;

    -- Drop the Sync Trigger (We don't need it after the swap)
    DROP TRIGGER trg_sync_id_new ON user_events;
    DROP FUNCTION sync_id_new;

    -- Drop old PK constraint
    ALTER TABLE user_events DROP CONSTRAINT user_events_pkey;

    -- Make the new column active, drop old one
    ALTER TABLE user_events DROP COLUMN id;
    ALTER TABLE user_events RENAME COLUMN id_new TO id;

    -- Add IDENTITY (Creates a fresh sequence automatically)
    ALTER TABLE user_events
    ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

    -- Sync the new Sequence to the Data
    SELECT setval(pg_get_serial_sequence('user_events', 'id'), (SELECT MAX(id) FROM user_events));

    -- Re-add Primary Key (Using the pre-built index)
    -- Postgres will automatically rename the index 'user_events_id_new_idx' to 'user_events_pkey'
    ALTER TABLE user_events
    ADD CONSTRAINT user_events_pkey PRIMARY KEY USING INDEX user_events_id_new_idx;

COMMIT;

Conclusion

BIGINT is cheap! You might want to do a migration soon.

Migrating a sequencing column from INT to BIGINT is a complex database refactoring project, but by utilizing Postgres features like unique indexes, sequences, and the NOT VALID constraint trick, it can be executed with minimal application downtime.

Key Takeaways:

Do as much work as possible (adding new column, index, backfilling) while the application is online.
Test batch sizes and vacuum to get to a backfill process that is efficient
Update referencing foreign key columns to BIGINT before the main table switch.
Atomic switchover: Execute the column rename and constraint setup in a single, quick transaction.

As always, test the entire process on a non-production fork and ensure the plan works as expected before committing to production.

Latest Articles

Postgres Serials Should be BIGINT (and How to Migrate)