Your primary key costs 3,000 disk reads a second

transcript

Your ID column might cost 3,000 disk reads a second. And nobody is reading old orders. These reads come from inserts.

A billion orders. One primary key index. Depth 4, whatever the key. Four page reads. With random UUID keys, that index is about 40 gigabytes.

A random key sends each insert to any of 5 million leaves. Assume 16 gigabytes of cache. It holds 40 percent. So 60 percent of inserts read disk first. A sequential key always lands on the right edge. 4 pages. 32 kilobytes. Always in memory.

Peak sale. 5,000 inserts a second. 60 percent of them miss the cache. That's 3,000 random disk reads a second. For one index.

Want UUIDs anyway? Use version 7, not version 4. Its first 48 bits are the time in milliseconds. So inserts go back to the right edge.

The depth never changed. The working set did. Choosing an ID is an index decision.

Full breakdown in the lecture.