Postgresql
June 3, 2025
Common PostgreSQL Index Types
Postgres supports many index types, from the default b-tree indexes to more exotic types like hash, GIN, GiST, or BRIN. They are well documented in the PostgreSQL docs, but here’s a quick summary:
B-Tree indexes (default index type in PostgreSQL)
CREATE INDEX index_product_id ON products (product_id);
- Default index type in PostgreSQL.
- Supports <, <=, =, >=, >, BETWEEN, IN, IS NULL, IS NOT NULL.
- Organizes entries in ascending order.
Hash indexes
CREATE INDEX index_product_id ON products USING HASH (product_id);
- Ideal for equality checks, especially for integers.
- Doesn't support range queries or sorting.
Composite indexes
CREATE INDEX index_product_id_name ON products (product_id, product_name);
- Multicolumn index defined on multiple table columns.
- Optimize based on columns frequently used in WHERE clauses.
Partial indexes
CREATE INDEX index_product_id ON products(product_id) where product_available = ‘true’;
- Built over a subset of a table, defined by a conditional expression.
- Useful for filtering out frequently unqueried rows.
Covering indexes
CREATE INDEX index_product_id_name_status ON products (product_id, product_name) include (status);
- Allows index-only scans when the select list matches index columns.
- Additional columns are specified with the INCLUDE keyword.
Block Range index (BRIN)
CREATE INDEX brin_example_index ON logs USING BRIN(log_date);
- Designed for large, sorted tables like time-series data.
- Space-efficient, storing min and max values within logical blocks.
GiST and SP-GiST indexes
CREATE INDEX idx_gist ON my_table USING gist(my_column);
- GiST (Generalized Search Tree) supports indexing of complex data types such as geometric shapes and full-text documents.
- Allows for custom operators for querying, supporting advanced search functionalities like natural language processing.
- Uses "lossy" indexing strategies to summarize complex items, potentially leading to false positives that require detailed row checks.
CREATE INDEX idx_spgist ON my_table USING spgist(my_column);
- SP-GiST (Space Partitioned Generalized Search Tree) is ideal for data with natural partitioning, such as spatial and hierarchical data.
- Enables different branching factors at different nodes, allowing tailored tree structures that reduce search space.
- Particularly efficient for applications requiring complex spatial queries or managing large data hierarchies.