An index in PostgreSQL is a secondary data structure maintained alongside a table that lets the query planner locate rows matching a predicate without scanning every row. The trade-off is universal: indexes accelerate reads (selections, joins, and orderings) at the cost of additional storage, slower writes (because every INSERT, UPDATE, or DELETE that touches an indexed column must update each relevant index), and extra work during VACUUM. PostgreSQL ships with a rich family of access methods: B-tree, Hash, GIN, GiST, SP-GiST, and BRIN, supplemented by extension-provided types such as Bloom, zombodb, rum, and pg_trgm's trigram operator classes.
B-tree is the default access method and the workhorse for most workloads. It maintains keys in sorted order on disk, which means it supports equality and range predicates, IS NULL and IS NOT NULL checks, and ORDER BY traversal in index order. The planner can also chain B-tree indexes through bitmap scans and use them as inputs to merge joins, making them remarkably versatile.
Writing performance is the constant reminder behind every indexing decision. Each indexed column adds write amplification: more WAL volume, more random I/O, and more dead tuples to clean up later. The art of indexing in PostgreSQL is choosing access methods and definitions that match real query patterns while keeping the write cost proportional to the benefit gained on reads.