default for equality/range queries (

,

<

,

>

,

BETWEEN

,

ORDER BY

)

Composite

order matters—index used if equality on leftmost prefix (

WHERE a = ? AND b > ?

uses index on

(a,b)

, but

WHERE b = ?

does not). Put most selective/frequently filtered columns first.

Covering

:

CREATE INDEX ON tbl (id) INCLUDE (name, email)

- includes non-key columns for index-only scans without visiting table.

Partial

for hot subsets (

WHERE status = 'active'

→

CREATE INDEX ON tbl (user_id) WHERE status = 'active'

). Any query with

status = 'active'

can use this index.

Expression

for computed search keys (

CREATE INDEX ON tbl (LOWER(email))

). Expression must match exactly in WHERE clause:

WHERE LOWER(email) = 'user@example.com'

.

GIN

JSONB containment/existence, arrays (

@>

,

?

), full-text search (

@@

)

GiST

ranges, geometry, exclusion constraints

BRIN

very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after

CLUSTER

).

Partitioning

Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).

Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically

RANGE

common for time-series (

PARTITION BY RANGE (created_at)

). Create partitions:

CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')

.

TimescaleDB

automates time-based or ID-based partitioning with retention policies and compression.

LIST

for discrete values (

PARTITION BY LIST (region)

). Example:

FOR VALUES IN ('us-east', 'us-west')

.

HASH

for even distribution when no natural key (

PARTITION BY HASH (user_id)

). Creates N partitions with modulus.

Constraint exclusion

requires

CHECK

constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).

Prefer declarative partitioning or hypertables. Do NOT use table inheritance.

Limitations

no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.

Special Considerations

Update-Heavy Tables

Separate hot/cold columns

—put frequently updated columns in separate table to minimize bloat.

Use

fillfactor=90

to leave space for HOT updates that avoid index maintenance.

Avoid updating indexed columns

—prevents beneficial HOT updates.

Partition by update patterns

—separate frequently updated rows in a different partition from stable data.

Insert-Heavy Workloads

Minimize indexes

—only create what you query; every index slows inserts.

Use

COPY

or multi-row

INSERT

instead of single-row inserts.

UNLOGGED tables

for rebuildable staging data—much faster writes.

Defer index creation

for bulk loads—>drop index, load data, recreate indexes.

Partition by time/hash

to distribute load.

TimescaleDB

automates partitioning and compression of insert-heavy data.

Use a natural key for primary key

such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.

If you do need a surrogate key,

Prefer

BIGINT GENERATED ALWAYS AS IDENTITY

over

UUID

.

Upsert-Friendly Design

Requires UNIQUE index

on conflict target columns—

ON CONFLICT (col1, col2)

needs exact matching unique index (partial indexes don't work).

Use

EXCLUDED.column

to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.

DO NOTHING

faster

than

DO UPDATE

when no actual update needed.

Safe Schema Evolution

Transactional DDL

most DDL operations can run in transactions and be rolled back—

BEGIN; ALTER TABLE...; ROLLBACK;

for safe testing.

Concurrent index creation

:

CREATE INDEX CONCURRENTLY

avoids blocking writes but can't run in transactions.

Volatile defaults cause rewrites

adding

NOT NULL

columns with volatile defaults (e.g.,

now()

,

gen_random_uuid()

) rewrites entire table. Non-volatile defaults are fast.

Drop constraints before columns

:

ALTER TABLE DROP CONSTRAINT

then

DROP COLUMN

to avoid dependency issues.

Function signature changes

:

CREATE OR REPLACE

with different arguments creates overloads, not replacements. DROP old version if no overload desired.

Generated Columns

... GENERATED ALWAYS AS () STORED

for computed, indexable fields. PG18+ adds

VIRTUAL

columns (computed on read, not stored).

Extensions

pgcrypto

:

crypt()

for password hashing.

uuid-ossp

alternative UUID functions; prefer

pgcrypto

for new projects.

pg_trgm

fuzzy text search with

%

operator,

similarity()

function. Index with GIN for

LIKE '%pattern%'

acceleration.

citext

case-insensitive text type. Prefer expression indexes on

LOWER(col)

unless you need case-insensitive constraints.

btree_gin

/

btree_gist

enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).

hstore

key-value pairs; mostly superseded by JSONB but useful for simple string mappings.

timescaledb

essential for time-series—automated partitioning, retention, compression, continuous aggregates.

postgis

comprehensive geospatial support beyond basic geometric types—essential for location-based applications.

pgvector

vector similarity search for embeddings.

pgaudit

audit logging for all database activity.

JSONB Guidance

Prefer

JSONB

with

GIN

index.

Default:

CREATE INDEX ON tbl USING GIN (jsonb_col);

→ accelerates:

Containment

jsonb_col @> '{"k":"v"}'

Key existence

jsonb_col ? 'k'

,

any/all keys

?|

,

?&

Path containment

on nested docs

Disjunction

jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])

Heavy

@>

workloads: consider opclass

jsonb_path_ops

for smaller/faster containment-only indexes:

CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);

Trade-off

loses support for key existence ( ? , ?| , ?& ) queries—only supports containment ( @> ) Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression): ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED; CREATE INDEX ON tbl (price); Prefer queries like WHERE price BETWEEN 100 AND 500 (uses B-tree) over WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 without index. Arrays inside JSONB: use GIN + @> for containment (e.g., tags). Consider jsonb_path_ops if only doing containment. Keep core relations in tables; use JSONB for optional/variable attributes. Use constraints to limit allowed JSONB values in a column e.g. config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object') Examples Users CREATE TABLE users ( user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY , email TEXT NOT NULL UNIQUE , name TEXT NOT NULL , created_at TIMESTAMPTZ NOT NULL DEFAULT now ( ) ) ; CREATE UNIQUE INDEX ON users ( LOWER ( email ) ) ; CREATE INDEX ON users ( created_at ) ; Orders CREATE TABLE orders ( order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY , user_id BIGINT NOT NULL REFERENCES users ( user_id ) , status TEXT NOT NULL DEFAULT 'PENDING' CHECK ( status IN ( 'PENDING' , 'PAID' , 'CANCELED' ) ) , total NUMERIC ( 10 , 2 ) NOT NULL CHECK ( total

0 ) , created_at TIMESTAMPTZ NOT NULL DEFAULT now ( ) ) ; CREATE INDEX ON orders ( user_id ) ; CREATE INDEX ON orders ( created_at ) ; JSONB CREATE TABLE profiles ( user_id BIGINT PRIMARY KEY REFERENCES users ( user_id ) , attrs JSONB NOT NULL DEFAULT '{}' , theme TEXT GENERATED ALWAYS AS ( attrs -

'theme' ) STORED ) ; CREATE INDEX profiles_attrs_gin ON profiles USING GIN ( attrs ) ;