PostgreSQL Advanced Features

Postgres has been quietly absorbing features for thirty years. In 2026 it's defensible to argue you should reach for non-Postgres datastores only when you have specific reasons, because Postgres can do most of what those datastores do — well enough that the operational simplicity of one system wins.

This page is the features that surprise people, ranked by how often they replace another tool.

JSONB

JSONB stores binary-encoded JSON with index support and a query language. Most "we need a document database" use cases are served by JSONB without leaving Postgres.

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload JSONB NOT NULL
);

-- Path queries
SELECT * FROM events WHERE payload @> '{"type": "click", "user_id": 42}';
SELECT * FROM events WHERE payload->>'session_id' = 'abc-123';

-- GIN index on the whole JSONB (large but flexible)
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- GIN index for containment queries only (smaller)
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- Functional index for a specific path
CREATE INDEX idx_events_user ON events ((payload->>'user_id'));

When to use:

When not to use:

See JsonbInPostgresql for depth.

LISTEN / NOTIFY

Lightweight pub-sub built into Postgres. Producers send notifications:

SELECT pg_notify('order_events', '{"id": 42, "status": "shipped"}');

Subscribers listen:

LISTEN order_events;
-- Connection now receives async notifications

Use cases:

Limits:

For "tell other instances something changed" within a single application, LISTEN/NOTIFY is dramatically simpler than running a message broker.

pgvector

Native vector search via the pgvector extension. Adds VECTOR(n) type, IVFFlat and HNSW indexes, and operators (<-> for L2, <#> for negative inner product, <=> for cosine).

CREATE EXTENSION vector;

CREATE TABLE chunks (
    id BIGSERIAL PRIMARY KEY,
    document_id BIGINT REFERENCES documents(id),
    content TEXT NOT NULL,
    embedding VECTOR(1536) NOT NULL
);

CREATE INDEX idx_chunks_embedding ON chunks
USING hnsw (embedding vector_cosine_ops);

-- Top-10 most similar
SELECT id, content, 1 - (embedding <=> \$1) AS similarity
FROM chunks
ORDER BY embedding <=>\$1
LIMIT 10;

For most teams in 2026, pgvector is the right vector database. Tens of millions of vectors, sub-100ms recall, no separate operational system. See VectorDatabases for the comparison.

Common Table Expressions (CTEs)

CTEs are SQL's structuring tool. Use them generously.

WITH active_users AS (
    SELECT id, email FROM users WHERE deleted_at IS NULL
),
recent_orders AS (
    SELECT user_id, total FROM orders 
    WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.email, COALESCE(SUM(o.total), 0) AS spent
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Recursive CTEs handle hierarchies (org charts, comment trees) and graph traversals:

WITH RECURSIVE descendants AS (
    SELECT id, parent_id, name FROM categories WHERE id = 5
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

Pre-PG12 CTEs were optimisation fences. PG12+ inlined them, so CTEs are now a structuring tool with no performance cost vs subqueries. Use them.

Window functions

SELECT 
    user_id,
    order_id,
    total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq,
    SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
    LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_total
FROM orders;

Solves "running totals," "rank within group," "time since last event," and similar queries that would otherwise require self-joins.

Range types

Native types for ranges (numeric, date, timestamp). With EXCLUDE constraints, you get conflict-free booking systems:

CREATE TABLE bookings (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT REFERENCES rooms(id),
    period TSTZRANGE NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

This is one constraint that prevents any two bookings of the same room from overlapping in time. The database does the conflict detection; you don't.

Partitioning

Native declarative partitioning since PG10. Partition by range, list, or hash.

CREATE TABLE events (
    id BIGINT,
    occurred_at TIMESTAMPTZ NOT NULL,
    -- ...
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_q1 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

Use cases:

pg_partman automates partition creation/dropping for time-series. Worth the install on any production time-series workload.

Logical replication

Publish/subscribe between Postgres instances at the row level (not just byte-level streaming):

-- On primary
CREATE PUBLICATION my_pub FOR TABLE orders, users;

-- On replica
CREATE SUBSCRIPTION my_sub 
CONNECTION 'host=primary user=replicator dbname=main' 
PUBLICATION my_pub;

FDW (Foreign Data Wrappers)

Query other databases as if they were tables. postgres_fdw for cross-Postgres; oracle_fdw, mysql_fdw, mongo_fdw, clickhouse_fdw for others.

Use case: pulling data from a legacy system into reports without ETL. Less common in 2026 (data warehouses subsume this), but still useful for migrations.

Trigger-based history

Combined with JSONB, simple history table:

CREATE TABLE orders_history (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    snapshot JSONB NOT NULL,
    op TEXT NOT NULL CHECK (op IN ('insert','update','delete')),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE FUNCTION log_orders_history() RETURNS TRIGGER AS $$BEGIN
    INSERT INTO orders_history (order_id, snapshot, op)
    VALUES (COALESCE(NEW.id, OLD.id), to_jsonb(COALESCE(NEW, OLD)), TG_OP::TEXT);
    RETURN COALESCE(NEW, OLD);
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_history_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_orders_history();

Cheap, comprehensive, queryable. Adequate for most audit/history use cases without needing temporal-table machinery.

Other tools worth knowing

Where to leave Postgres

Despite all of the above, some workloads still want a different store:

For most teams under "extreme" scale, the Postgres-everywhere posture saves operational cost. Adding a new datastore should require a specific reason.

Further reading