Hard system-design scaling

What broke in the Postgres schema and insert path only at a million rows per second?

The 30 second answer

Things that are fine at small scale. A 32-bit ID would run out with billions of rows, so it became 64-bit. The foreign key from views to links halved throughput, so it was removed and deletes are cleaned up in code. The views table has no primary key, the visitor hash was shortened, and batched multi-row inserts capped near 700,000 rows a second until he switched to COPY.

The changes he lists for the views table.

  • IDs: a 32-bit integer tops out at 2,147,483,647, and the table was heading for billions of rows, so the ID type had to go. Postgres bigint is the 8-byte alternative.
  • No foreign key: views.url_id is a plain integer, not a foreign key. With the foreign key the speaker couldn't get past about 700,000 requests per second, and his code comment says it halved the rate to about half a million. The cost: deleting a link no longer cascades, so the app deletes its views itself.
  • No primary key on the views table.
  • A shorter visitor hash: the 64-hex-character hash was cut down (the new length isn't stated in the video).

INSERT versus COPY. The first janitor wrote each batch as a big multi-row INSERT in one transaction, and that capped out at around 700,000 rows per second. Switching to COPY ... FROM STDIN with CSV data let the janitor keep up. The PostgreSQL docs agree in general: COPY is almost always faster than INSERT for bulk loading, even when many inserts are batched into one transaction, and checking a foreign key row by row is slower than checking it in bulk.

What the database did at 1 million rows per second. About 1,000 writes per second and about 280 MB/s, with CPU to spare. The speaker estimates it could take about twice the load. The views table grew by about 8 GB per minute.

The tradeoff to say out loud. Dropping the foreign key and primary key trades integrity checks and an easy unique handle for write speed, and dedup on retry has to be designed separately. He also notes none of these problems appeared at smaller scale.

The general bulk-insert advice is in Multi-row INSERT or COPY instead of one row at a time.

Alternate interview scenarios

"Would you really drop the foreign key in production?"

For an append-only, high-volume analytics table, often yes, with orphan cleanup in the delete path and a periodic check. For core business tables, no. Name what you're giving up.

"What else would you consider for a table growing 8 GB a minute?"

Time-based partitioning, so old data can be detached or dropped as a whole partition instead of deleted row by row. That isn't covered in the video; it's the next question an interviewer will ask.

Sources (5)
  1. Same video, Battle-Testing Weer and Cpeak, Now in Rust (1:55:58)youtube.com
  2. PostgreSQL docs: Populating a Database (COPY, foreign keys)postgresql.org
  3. PostgreSQL docs: COPYpostgresql.org
  4. PostgreSQL docs: Numeric types (integer and bigint ranges)postgresql.org
  5. PostgreSQL docs: Table partitioningpostgresql.org