Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 3 · Lesson 16 · 5 min read

PostgreSQL Indexing Strategies for SQL Server DBAs

On this page
  1. No clustered index: heap tables in PostgreSQL
  2. INCLUDE, filtered and online indexes
  3. GIN, GiST, BRIN and hash indexes
  4. Indexes in DataGrip
  5. Check yourself

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 ServerPostgreSQL
Table storageUsually ordered by the clustered indexAlways a heap
Primary keyUsually the clustered indexAn ordinary unique B-tree index
Finding a row through an indexSeek, then Key Lookup into the clustered indexIndex Scan, then a fetch from the heap
Keeping rows physically sortedAutomaticNot 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.
  • CLUSTER isn't a clustered index. CLUSTER orders USING orders_order_date_idx rewrites 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 ServerPostgreSQL
Nonclustered indexCREATE INDEX (B-tree by default)
INCLUDE (cols)INCLUDE (cols), the same syntax
Filtered index WHERE ...Partial index WHERE ...
Index on a computed columnExpression index, ON t (lower(email))
ONLINE = ONCREATE INDEX CONCURRENTLY
Columnstore indexNo core equivalent. BRIN or an extension, see below.

Covering indexes work the way you'd expect:

SQL
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 orders

There'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:

SQL
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:

TypeUse it forSQL Server equivalent
GINjsonb containment, arrays, full-text searchFull-text indexes, JSON indexes on computed columns
GiSTRanges, geometry, "no overlapping bookings" rulesSpatial indexes, no overlap constraint
BRINHuge tables ordered by time or idPartly what partitioning or columnstore gives you
HashEquality only on long valuesHash indexes on memory-optimized tables

A few examples:

SQL
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.