Every team migrating from MySQL to PostgreSQL hits the same early question: what happens to all those AUTO_INCREMENT primary keys? The answer looks trivial in a schema diff and turns out to have real depth. I have operated both systems in production, and the id-generation machinery is one of the places where the two databases feel most similar on the surface and most different underneath.
The short version: MySQL attaches a counter to the table, PostgreSQL attaches a sequence object to the column, and both will leave gaps in your ids. The long version is worth knowing before you migrate, because the failure modes are different, the history is different, and the post-import cleanup step is the single most common way I have seen a PostgreSQL migration break on its first insert.
How AUTO_INCREMENT actually works
In InnoDB, AUTO_INCREMENT is a per-table counter. When a transaction needs a value, it takes it from the counter, and the counter never goes backwards even if the transaction rolls back. That is why gaps are normal: a rolled-back insert, a failed duplicate-key insert, or a bulk load that reserved a range all consume values that never land in the table.
The behavior of the counter under concurrency is controlled by innodb_autoinc_lock_mode. In MySQL 8.0 the default is interleaved mode (2), which is the most concurrent option and assumes row-based replication. Interleaved mode makes multi-row inserts fast, but it also means a single INSERT ... SELECT can produce non-consecutive ids. Teams coming from the old consecutive mode are sometimes surprised, but it is a reasonable trade and most applications never notice.
MySQL 8.0 also fixed one of the oldest and most famous InnoDB quirks. Before 8.0, the auto-increment counter lived only in memory. On restart, InnoDB re-initialized it with the equivalent of SELECT MAX(id) FROM the table. If you deleted the highest rows and then restarted the server, the counter went backwards and previously used ids could be handed out again. Anything that referenced those ids externally, from soft-deleted audit trails to cached URLs, could silently point at the wrong row. MySQL 8.0 made the counter durable by writing changes to the redo log, so the value now survives restarts and crashes. If you are on 8.x, this problem is history; if you are still operating 5.7 anywhere, it is worth knowing about.
How PostgreSQL sequences work
PostgreSQL takes the counter out of the table and makes it a first-class object: a sequence. A sequence has its own name, its own state, and its own behavior settings, including increment, min and max values, and a cache size. Identity columns and the older serial pseudo-type are both sugar over a sequence.
Like the InnoDB counter, sequences are non-transactional by design. nextval consumes a value whether or not the calling transaction commits, so rollbacks create gaps here too. This is the right trade: making sequences transactional would serialize every insert on the table, which is exactly what you do not want from an id generator. If a requirement genuinely says "no gaps ever," neither AUTO_INCREMENT nor a sequence is the right tool, and you need an explicitly locked counter table with the throughput cost that implies.
One PostgreSQL-specific detail worth knowing: for performance, sequence state is written to WAL in batches of 32 values, and each session can additionally pre-allocate values when CACHE is set above 1. After a crash, or when a cached session exits, unused pre-allocated values are simply skipped. The practical consequence is the same rule as MySQL: never treat ids as dense, ordered, or countable. Treat them as opaque unique values.
Prefer identity columns over serial
If your migration tooling or your habits produce serial columns, it is worth upgrading the pattern. serial is a shorthand that creates a sequence, sets the column default to nextval, and marks the sequence as owned by the column. It works, but the relationship is loose: a user can insert any value, permissions on the sequence must be managed separately, and dropping or copying the table has edge cases.
Identity columns, added in PostgreSQL 10 and the recommended choice on PostgreSQL 16 and 17, are the SQL-standard version of the same idea with tighter integration.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- GENERATED ALWAYS rejects manual ids unless you are explicit:
INSERT INTO orders (id, customer_id)
OVERRIDING SYSTEM VALUE
VALUES (1000000, 42);GENERATED ALWAYS protects you from application code accidentally writing its own ids, while OVERRIDING SYSTEM VALUE gives you a deliberate escape hatch for data loads. GENERATED BY DEFAULT behaves more like AUTO_INCREMENT, allowing explicit values without ceremony, and is often the pragmatic choice during a migration window. Note the bigint: coming from MySQL, where INT UNSIGNED reaches roughly 4.2 billion, remember that PostgreSQL has no unsigned types, so a signed 32-bit integer key has half the headroom you had before. Defaulting id columns to bigint sidesteps the entire int-exhaustion incident category.
Converting existing serial columns to identity is mechanical but fiddly: drop the column default, alter the column to add the identity property, and let PostgreSQL absorb or replace the sequence. Do it table by table with a script rather than by hand, and remember that ALTER TABLE takes locks, so schedule it like any other schema change. The patterns in our guide to ALTER TABLE locking apply directly.
The migration step everyone forgets: resetting sequences
Here is the classic first-week-on-PostgreSQL incident. You migrate data from MySQL, ids and all. The tables look perfect. Then the application inserts its first new row and gets a duplicate key violation on the primary key. The reason: the data load wrote explicit ids, but nothing advanced the sequences past them, so the first nextval returns 1.
The fix is to reset every sequence to the maximum existing value after the load. For identity and serial columns, pg_get_serial_sequence resolves the sequence name so you do not have to guess it:
SELECT setval(
pg_get_serial_sequence('orders', 'id'),
COALESCE((SELECT max(id) FROM orders), 1),
true
);
-- Verify current sequence state across the database:
SELECT schemaname, sequencename, last_value
FROM pg_sequences
ORDER BY schemaname, sequencename;Wrap the setval call in a loop over information_schema.columns for every identity column and run it as the final step of the import, every time, including in rehearsal runs. Migration rehearsals that skip this step pass all their read checks and then fail on writes, which is exactly the wrong time to find out.
Sequence caching and when to touch it
By default a PostgreSQL sequence has CACHE 1, meaning each nextval call touches shared sequence state. For nearly all workloads this is fine; sequences are extremely cheap. Under very high concurrent insert rates you can raise CACHE so each session pre-allocates a block of values, reducing contention at the cost of larger gaps and out-of-order ids across sessions.
My advice is the boring kind: do not tune this preemptively. Measure first. If insert throughput is actually limited by sequence contention it will be visible in wait events, and that is rare compared to the usual suspects of lock contention and index maintenance. If ids must be roughly time-ordered for your access pattern, keep the cache small and accept the shared state.
Behavioral differences that surprise MySQL engineers
- Ids survive TRUNCATE differently. MySQL's TRUNCATE resets AUTO_INCREMENT. PostgreSQL's TRUNCATE leaves sequences alone unless you say TRUNCATE ... RESTART IDENTITY.
- There is no ON DUPLICATE KEY UPDATE counter burn debate. PostgreSQL's INSERT ... ON CONFLICT consumes sequence values for conflicting rows just like MySQL's upsert consumes AUTO_INCREMENT values. Gaps again; still fine.
- LAST_INSERT_ID() has a better replacement. Instead of a separate call, PostgreSQL's RETURNING clause hands back the generated id, or an entire row, from the INSERT itself. This removes a round trip and a class of connection-pooling bugs.
- Per-table counters vs schema-level objects. Sequences can be shared, inspected, and altered independently of tables. That is occasionally useful and occasionally a foot-gun when a shared sequence gets dropped.
- No AUTO_INCREMENT offset tricks. Multi-primary MySQL setups sometimes use auto_increment_increment and auto_increment_offset to partition id space. The sequence equivalents are INCREMENT BY and START WITH, and they work per sequence rather than per server.
None of these are reasons to fear the migration. They are reasons to grep the codebase for LAST_INSERT_ID, TRUNCATE, and hand-written MAX(id)+1 logic before cutover rather than after. For a broader view of how the two systems compare beyond id generation, see the MonPG comparison pages and our PostgreSQL overview.
Monitoring the aftermath with MonPG
Once you land on PostgreSQL, id generation becomes an operational concern in two specific ways: sequence exhaustion on int4 columns that should have been bigint, and insert-path regressions after the migration when index and lock behavior differs from what the workload saw on InnoDB. Both are visible early if you are watching the database rather than waiting for the incident.
MonPG monitors PostgreSQL only, and that focus is the point: it keeps query history from pg_stat_statements, lock chains, wait events, autovacuum health, and connection pressure in one workflow, so a post-migration insert slowdown can be traced to the actual query family and wait profile instead of guessed at. If you are planning the cutover now, put database evidence in place before the first production write. The PostgreSQL monitoring guide covers the baseline worth having from day one.