PostgreSQL Indexing Strategies for SQL Server DBAs
On this page
No clustered index: heap tables in PostgreSQL
The first thing SQL Server DBAs look for is the clustered index. In PostgreSQL there isn't one.
Every PostgreSQL table is a heap: rows are stored roughly in the order they were written, not sorted by any key. Every index, including the primary key, is a separate structure that points into the heap, like a SQL Server nonclustered index on a heap table.
| SQL Server | PostgreSQL | |
|---|---|---|
| Table storage | Usually ordered by the clustered index | Always a heap |
| Primary key | Usually the clustered index | An ordinary unique B-tree index |
| Finding a row through an index | Seek, then Key Lookup into the clustered index | Index Scan, then a fetch from the heap |
| Keeping rows physically sorted | Automatic | Not maintained. CLUSTER sorts once. |
What it means in practice:
- Don't design around "the clustered key". Design indexes for your queries: what you filter, join and sort on.
- Random keys are less painful. A random GUID clustered key fragments a SQL Server table. In PostgreSQL the table isn't sorted by the key, although random UUIDs still scatter inserts across the primary key index.
uuidv7()(Day 5) avoids that. CLUSTERisn't a clustered index.CLUSTER orders USING orders_order_date_idxrewrites the table once in index order, under an exclusive lock. New rows aren't kept in that order. It helps read-mostly tables that are loaded once.
INCLUDE, filtered and online indexes
Most SQL Server index features have a direct counterpart:
| SQL Server | PostgreSQL |
|---|---|
| Nonclustered index | CREATE INDEX (B-tree by default) |
INCLUDE (cols) | INCLUDE (cols), the same syntax |
Filtered index WHERE ... | Partial index WHERE ... |
| Index on a computed column | Expression index, ON t (lower(email)) |
ONLINE = ON | CREATE INDEX CONCURRENTLY |
| Columnstore index | No core equivalent. BRIN or an extension, see below. |
Covering indexes work the way you'd expect:
CREATE INDEX orders_customer_idx ON orders (customer_id) INCLUDE (order_total);
EXPLAIN SELECT sum(order_total) FROM orders WHERE customer_id = 42;
-- Index Only Scan using orders_customer_idx on ordersThere's one difference. An index-only scan also needs the table to be recently vacuumed, because PostgreSQL checks a visibility map to know whether the heap must be visited (Day 18). On a busy table that autovacuum can't keep up with, the "covering" index still visits the heap.
Filtered indexes become partial indexes, and the query must include the same condition:
CREATE INDEX orders_open_idx ON orders (order_date) WHERE status = 'OPEN';On production tables, always build indexes with CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks writes for the whole build. CONCURRENTLY takes longer but lets writes continue, and it can't run inside a transaction block.
GIN, GiST, BRIN and hash indexes
B-tree covers most needs. Four more types handle cases where SQL Server needs separate features or has no answer:
| Type | Use it for | SQL Server equivalent |
|---|---|---|
| GIN | jsonb containment, arrays, full-text search | Full-text indexes, JSON indexes on computed columns |
| GiST | Ranges, geometry, "no overlapping bookings" rules | Spatial indexes, no overlap constraint |
| BRIN | Huge tables ordered by time or id | Partly what partitioning or columnstore gives you |
| Hash | Equality only on long values | Hash indexes on memory-optimized tables |
A few examples:
CREATE TABLE products (product_id int PRIMARY KEY, attributes jsonb NOT NULL);
CREATE INDEX products_attributes_idx ON products USING gin (attributes);
SELECT product_id FROM products WHERE attributes @> '{"color": "red"}';
CREATE TABLE bookings (booking_id int PRIMARY KEY, stay daterange NOT NULL);
CREATE INDEX bookings_stay_idx ON bookings USING gist (stay);
SELECT booking_id FROM bookings WHERE stay && daterange('2026-08-10', '2026-08-15');
CREATE TABLE events (event_id bigint PRIMARY KEY, created_at timestamptz NOT NULL);
CREATE INDEX events_created_brin ON events USING brin (created_at);GIN replaces SQL Server's computed-column trick for JSON. One index serves containment queries on any key. It also powers PostgreSQL's built-in full-text search, so there's no separate full-text engine to manage.
GiST handles overlap questions. With the btree_gist extension, it can enforce "no two bookings for the same room may overlap" as a constraint, which SQL Server can't express declaratively.
BRIN stores just the minimum and maximum value per block of pages. On an append-only table of 2 billion events, a BRIN index on created_at can be a few hundred kilobytes where a B-tree would be tens of gigabytes. It only works when values follow insertion order.
For analytics workloads that used columnstore indexes, core PostgreSQL has no columnar storage. Options include BRIN with partitioning (Day 19), materialized summary tables (Day 11), or columnar extensions and services. Benchmark before choosing.
Indexes in DataGrip
Expand a table in the Database Explorer and open Indexes to see each index's type and columns. Right-click a table and choose New, then Index, for a dialog that previews the CREATE INDEX statement. After adding an index, check with Explain Plan (Day 15) that queries really use it. The planner skips an index it thinks won't help.
Check yourself
Not for the table, which isn't stored in key order. Random UUIDs still spread inserts across the primary key index, so uuidv7() is the better default for new rows.
Whether the queries include the same condition, NOT is_deleted, or an equivalent the planner can prove matches. A partial index is only considered when the query's WHERE clause implies the index's predicate.
It helps read-mostly tables whose range queries benefit from physical order. It takes an exclusive lock while it rewrites the table, and later inserts aren't kept in order, so it must be repeated to stay useful.
Use CREATE INDEX CONCURRENTLY, the equivalent of SQL Server's ONLINE = ON. It takes longer and can't run inside a transaction block, and if it fails it leaves an invalid index behind that you must drop and retry. Check pg_index.indisvalid afterwards.
For word search, a GIN index on a tsvector column provides indexed full-text search with stemming and ranking. GIN also serves jsonb containment queries and array membership. The query syntax differs from CONTAINS, so search queries need rewriting during migration.