A reader on one of my Postgres posts asked a fair question: why put id in the pagination index at all? With clock_timestamp() instead of now() , it's hard to create two rows at exactly the same time.
So I measured it.
The setup
PostgreSQL 16, a 4-vCPU machine. The table is the one every app has:
CREATE TABLE concurrent ( id bigserial PRIMARY KEY , created_at timestamptz NOT NULL DEFAULT clock_timestamp ( ) , n int ) ;
Enter fullscreen mode
Exit fullscreen mode
Then I filled it two ways:
Concurrent inserts: pgbench with 16 clients, one INSERT per autocommit transaction, for 20 seconds (about 29,000 inserts a second).
One bulk insert: INSERT ... SELECT of 1,000,000 rows, like an import or a backfill.
How many timestamps collided
Table Rows Distinct created_at Rows sharing a timestamp
Concurrent inserts 583,817 572,945 10,872
Bulk insert 1,000,000 869,925 130,075
timestamptz stores microseconds.…