How to Make Your Backend Roar
summary
Builds a Django URL shortener and benchmarks PostgreSQL index strategies on a million rows: plain SELECT by key uses a B-tree index; switching to a covering index (INCLUDE) yields an index-only scan and cuts I/O. Finding zero-hit rows uses a partial index that shrinks index size from ~7 MB to ~88 KB and speeds queries. Reverse lookup on URL compares B-tree (47 MB, ~12 ms) vs hash index (32 MB, ~0.07 ms) showing hash can be both smaller and faster for mostly-unique text keys.
AI-written summary · about
chapters (22)
- 0:00Introduction
- 1:37What is URL Shortener
- 3:03Creating short URLs table
- 4:50Execution Plan explained
- 6:18First scenario: URL lookup
- 7:24How B-Tree index works
- 8:51Improving queries with Covering index
- 12:35Covering indexes benefits
- 12:59Second scenario: finding unused URLs
- 14:45What are performance
- 15:47Index size impact
- 16:52What is Partial index
- 17:15Creating Partial index
- 18:42Partial index use cases
- 19:29Third scenario: Reverse Lookup
- 20:59Hash indexes benefits
- 22:27How Hash index works
- 24:41Creating Hash index
- 25:46B-Tree vs Hash comparison
- 28:46Hash indexes pros and cons
- 30:36Hash indexes limitations
- 31:19Indexes summary and importance
related talks
37mRules of Indexing in PostgreSQL
37mRules of Indexing in PostgreSQL
46mA Deep Dive Into PostgreSQL Indexing
45mA Deep Dive into PostgreSQL Indexing
29mPostgres Index types and where to find them
53mAdvanced Indexing Techniques in PostgreSQL
46mPostgreSQL Beyond Django: Strategies to Get Max Performance with Álvaro Justen
29mHow to confirm that the index you added actually improves query performance