PostgreSQL Indexes Explained: B-Tree, Composite, Partial, and GIN

A PostgreSQL index helps the database find rows without scanning every table record. It can make reads faster, but it consumes storage and adds work to inserts, updates, and deletes.

#Start with real queries

List slow or frequent queries and inspect filters, joins, sort order, and pagination. Use EXPLAIN to inspect a plan and EXPLAIN ANALYZE carefully on representative data. A sequential scan is not automatically bad on a small table.

#B-tree and composite indexes

B-tree is the default choice for equality, ranges, joins, and ordered scans. Composite index order matters because leftmost columns determine which query shapes benefit. For a tenant-and-time query, an index beginning with the tenant column may be useful, but verify it with real data.

#Partial, unique, and GIN indexes

A partial index covers rows matching a predicate, such as active records. A unique constraint protects integrity under concurrency and creates supporting index behavior. GIN can help arrays and certain JSONB queries, but it may be larger and more expensive to update than a B-tree.

#Pagination and sorting

Offset pagination can become expensive as the offset grows. Keyset pagination uses a stable ordering and a tie-breaker so records do not move between pages unexpectedly. Indexes should match both the filter and order used by the query.

#Maintenance

Keep statistics current and monitor table and index growth. Do not create an index for every possible filter. Measure index usage, write cost, and storage before removing or adding one through a reviewed migration.

#Common mistakes

• Indexing every column independently.

• Ignoring composite column order.

• Testing only with tiny development data.

• Using a function that prevents a plain index from matching.

• Relying on an application check instead of a unique constraint.

#Frequently asked questions

#Why does PostgreSQL ignore my index?

The planner may estimate that a scan is cheaper, or query shape, statistics, data distribution, or type conversion may prevent effective use.

#Should foreign keys be indexed?

Often an index helps joins and parent updates, but verify the access patterns and existing indexes.

#Conclusion

PostgreSQL indexing is a measurement discipline. Start from real queries, choose the type and column order that match them, inspect plans with representative data, and account for write and storage costs.