Menu

A created_at-only cursor lost 587 rows, even with clock_timestamp() and microseconds
📰

A created_at-only cursor lost 587 rows, even with clock_timestamp() and microseconds

DEV Community: postgres·Muhammad Adnan·3 days ago
#x9Xwz3PJ
#devtopostgresql#devto#dev#created_at#rows
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.