PostgreSQL index access methods

PostgreSQL does not use the everyday MySQL model of selecting InnoDB or MyISAM for ordinary tables. Almost every table uses the core heap table access method. Separate index access methods and operator classes determine which queries an index supports.

A table access method is not a routine tuning switch

The PostgreSQL Table Access Method API lets extensions or custom builds implement table storage, but ordinary applications still default to heap:

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL
) USING heap;

USING heap is normally omitted. A new table access method enters the critical path for WAL, MVCC, VACUUM, backup, replication, extensions, and major upgrades. It is not a query hint that can be switched casually.

Inspect access methods exposed by an instance:

SELECT
  amname,
  CASE amtype
    WHEN 't' THEN 'table'
    WHEN 'i' THEN 'index'
    ELSE amtype::text
  END AS access_method_type
FROM pg_am
ORDER BY amtype, amname;

Core index access methods

PostgreSQL 18 core provides B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. bloom ships as a module but requires CREATE EXTENSION bloom. Use the official Index Types as the behavior boundary.

Common workload map

Equality / range / order / unique  → B-tree
JSONB contains / array member      → GIN
PostgreSQL full-text search        → GIN in most cases
Range / GIS / nearest neighbor     → GiST or a matching SP-GiST class
Huge, time-correlated append table → BRIN
Vector approximate-nearest-neighbor → pgvector HNSW / IVFFlat

HNSW and IVFFlat come from pgvector; they are not core PostgreSQL index methods. Test recall, filtering, memory, build time, WAL, replica lag, and extension upgrades independently.

Built-in full-text search includes parsers, dictionaries, ranking, highlighting, and GIN/GiST indexing. Built-in configurations do not solve tokenization for every language. Chinese, for example, normally needs an additional tokenizer/extension or application preprocessing; creating a GIN index alone does not prove search quality.

Match the query before creating an index

-- Ordinary filter and order
CREATE INDEX CONCURRENTLY orders_customer_time_idx
ON orders (customer_id, placed_at DESC);

-- JSONB containment: payload @> '{"status":"paid"}'
CREATE INDEX CONCURRENTLY events_payload_gin_idx
ON events USING gin (payload jsonb_path_ops);

-- Large table whose timestamps correlate with physical append order
CREATE INDEX CONCURRENTLY events_time_brin_idx
ON events USING brin (occurred_at);

jsonb_path_ops is focused on containment and jsonpath operators such as @>, @?, and @@; it does not support every operator available from the default jsonb_ops. Derive index DDL from real query shapes.

Record plans and size after creation:

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY pg_relation_size(indexrelid) DESC;

Then use EXPLAIN (ANALYZE, BUFFERS) to compare actual rows, heap blocks, rechecks, sorting, and write cost. See Indexes and EXPLAIN for the complete workflow.

Prefer native capability first

Native-first does not reject extensions. It avoids unnecessary binary, license, backup, and upgrade dependencies. See PostgreSQL extension selection for candidates.