Databases3 min read
PostgreSQL indexing: which index, on which column, and when not to
Adding an index is easy. Knowing which one, in which column order, and which existing indexes to delete is the part that changes query times.
Database guides on PostgreSQL indexing and query planning, MongoDB schema design, Redis caching patterns, migrations and backups.
Adding an index is easy. Knowing which one, in which column order, and which existing indexes to delete is the part that changes query times.
Everyone runs EXPLAIN. Fewer people read the row estimates, which is where the actual answer usually is.
Every PostgreSQL connection is a process. Ten containers with a pool of twenty is two hundred processes, and your database was configured for one hundred.
MongoDB is schemaless in the same way a spreadsheet is: you still have a schema, it is just enforced by whoever wrote the last query.
Caching is easy until the cache expires. Everything interesting about Redis in production happens in the seconds after a popular key disappears.
The honest default for most products is PostgreSQL. Here is the specific set of conditions under which it is not.
The migration that takes your site down is almost never the complicated one. It is the ALTER TABLE that took a lock nobody expected.
You do not have backups. You have restores — and you only know whether you have those if you have done one this quarter.
Both are excellent. The differences that matter now are extensibility, data type strictness and operational culture, not raw speed.
Read Committed prevents dirty reads. It does not prevent two people spending the same balance — and that is the bug you will get.
No spam. Just the occasional case study and craft breakdown.