Partitioning, extensions and advanced deployment

Declarative range and list partitioning, partition pruning, maintenance, extension management, PostGIS and pgvector, and upgrade strategies.

Declarative partitioning

create table event (
  id         bigint generated always as identity,
  occurred_at timestamptz not null,
  tenant_id  bigint not null,
  payload    jsonb not null,
  primary key (id, occurred_at)
) partition by range (occurred_at);

create table event_2026_09 partition of event
  for values from ('2026-09-01') to ('2026-10-01');
create table event_2026_10 partition of event
  for values from ('2026-10-01') to ('2026-11-01');

create index on event_2026_09 (tenant_id, occurred_at desc);

-- partition pruning shows in the plan: only matching partitions are scanned
explain (analyze) select * from event
where occurred_at >= '2026-09-10' and occurred_at < '2026-09-11';

-- detach keeps the data, drop removes it: both are fast
alter table event detach partition event_2026_09;
drop table event_2026_09;

• The partition key must be part of every unique constraint on the parent, which is why the primary key above includes occurred_at.
• Pruning only happens when the predicate is on the partition key and the planner can prove it at plan time; a function call on the column usually defeats it.
• Partitioning helps most when the access pattern is time-based and old data can be dropped in bulk - it is not a general performance fix for a badly indexed table.
• detach partition concurrently avoids the long lock, and a detached partition is a normal table you can archive or attach elsewhere.
• Automate partition creation. A missing partition means inserts fail outright, which is a self-inflicted outage at midnight on the first of the month.

Extensions worth knowing

-- pgvector: nearest neighbours without another service
create extension if not exists vector;
alter table document add column embedding vector(768);
create index document_embedding_idx on document
  using hnsw (embedding vector_cosine_ops);

select id, left(content, 80) as preview
from document
order by embedding <=> '[...]'::vector
limit 5;

Upgrading and operating

pg_upgrade --old-datadir /var/lib/postgresql/16/main \
           --new-datadir /var/lib/postgresql/17/main \
           --old-bindir /usr/lib/postgresql/16/bin \
           --new-bindir /usr/lib/postgresql/17/bin --check

# logical alternative: a clean replica on the new version, then a switch
# 1. create a logical replication slot on the old primary
# 2. subscribe from the new cluster
# 3. wait until lag is zero, then promote and repoint the application

• pg_upgrade is fast because it copies files rather than data, but it preserves bloat and requires the extensions to be available for the new version.
• The logical route allows near-zero downtime and a rollback path, at the cost of a full copy and the caveats of logical replication.
• Run analyze after an upgrade: statistics are rebuilt, and plans can change dramatically in the first hours.
• Read the release notes for behavioural changes, not just new features - some defaults and error conditions change between major versions.

FAQ

Related

Roles, permissions, schemas and row-level security Indexes: B-tree, GIN, GiST, partial and expression

Last refreshed 2026-09-18.