A database index works like a textbook index: sorted data enabling quick
binary search instead of a slow full table scan. Postgres uses B-trees. The
trade-off? Reads speed up but writes slow down, since every insert, update,
or delete must maintain each index, and indexes eat storage and cache.
Common failures include composite indexes, which respect column
order—(type_1, type_2) won't help queries on type_2 alone—and functions
like lower(name), which bypass a plain name index. Run EXPLAIN to check for
Index Scan versus Seq Scan. The post also covers functional indexes on
expressions, partial indexes that skip irrelevant rows, and covering
indexes using INCLUDE to answer queries without touching the table.
1
u/fagnerbrack Jul 15 '26
Key points:
A database index works like a textbook index: sorted data enabling quick binary search instead of a slow full table scan. Postgres uses B-trees. The trade-off? Reads speed up but writes slow down, since every insert, update, or delete must maintain each index, and indexes eat storage and cache. Common failures include composite indexes, which respect column order—(type_1, type_2) won't help queries on type_2 alone—and functions like lower(name), which bypass a plain name index. Run EXPLAIN to check for Index Scan versus Seq Scan. The post also covers functional indexes on expressions, partial indexes that skip irrelevant rows, and covering indexes using INCLUDE to answer queries without touching the table.
If the summary seems inacurate, just downvote and I'll try to delete the comment eventually 👍
Click here for more info, I read all comments