PostgreSQL Partitioning and JSONB: Production Query Design

How I use declarative partitioning and JSONB selectively, with pruning, expression/GIN indexes and relational constraints where they add value.

Why this matters in production

Partitioning and JSONB are both useful features that are easy to overuse. Neither substitutes for a clear access pattern and indexing strategy.

The database is not a passive persistence layer. It is a concurrency system, a cache hierarchy, a durability mechanism and often the most stateful dependency in the architecture. I therefore review database design together with API behavior, background jobs, failure recovery, deployment and observability.

A design that performs well on a development dataset can fail very differently under production cardinality. More rows or documents change selectivity, working-set size, lock duration, cache hit rate, replication lag and maintenance cost. My objective is predictable behavior rather than one impressive benchmark.

The decision model I use

1. Partition for pruning or operations

Time-based retention, very large tables and maintenance isolation are better reasons than simply saying the table is big.

In an architecture review I convert this into a measurable question: what workload assumption makes this choice correct, what signal would tell us that assumption is no longer true, and what is the operational response? That prevents a database feature from becoming a permanent design decision simply because it worked on the first release.

2. Keep the partition key in important query predicates

Partition pruning works when PostgreSQL can exclude irrelevant partitions.

In an architecture review I convert this into a measurable question: what workload assumption makes this choice correct, what signal would tell us that assumption is no longer true, and what is the operational response? That prevents a database feature from becoming a permanent design decision simply because it worked on the first release.

3. Use JSONB for variable attributes, not every column

Stable, constrained fields still benefit from typed relational columns.

In an architecture review I convert this into a measurable question: what workload assumption makes this choice correct, what signal would tell us that assumption is no longer true, and what is the operational response? That prevents a database feature from becoming a permanent design decision simply because it worked on the first release.

4. Index the JSON operators actually used

GIN and expression indexes should reflect concrete predicates.

In an architecture review I convert this into a measurable question: what workload assumption makes this choice correct, what signal would tell us that assumption is no longer true, and what is the operational response? That prevents a database feature from becoming a permanent design decision simply because it worked on the first release.

5. Keep partition count operationally reasonable

Too many partitions add planning and maintenance overhead.

In an architecture review I convert this into a measurable question: what workload assumption makes this choice correct, what signal would tell us that assumption is no longer true, and what is the operational response? That prevents a database feature from becoming a permanent design decision simply because it worked on the first release.

6. Make retention cheap

Dropping an old time partition can be much cleaner than deleting millions of rows individually.

In an architecture review I convert this into a measurable question: what workload assumption makes this choice correct, what signal would tell us that assumption is no longer true, and what is the operational response? That prevents a database feature from becoming a permanent design decision simply because it worked on the first release.

Reference implementation

CREATE TABLE telemetry (
  tenant_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL,
  attributes jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE INDEX idx_telemetry_error_code
ON telemetry ((attributes->>'errorCode'));

SELECT tenant_id, occurred_at
FROM telemetry
WHERE occurred_at >= now() - interval '1 day'
  AND attributes->>'errorCode' = 'E42';

The example is deliberately focused on the decision rather than framework boilerplate. In production I also capture the query or command frequency, expected cardinality, latency target and failure behavior so the database choice can be tested against an explicit workload.

Data modeling and ownership

I want every table, collection or document family to have a clear application owner. Shared read access may be appropriate, but shared write ownership creates coupling quickly. When multiple services write the same data directly, schema changes become coordinated releases and business invariants become difficult to locate.

I also distinguish transactional data from analytical or historical data. A primary operational database should not carry unlimited reporting pressure just because the information is available there. Read replicas, projections, warehouses, archival stores or asynchronous exports can protect the transactional path.

Performance methodology

I do not begin tuning with configuration switches. I start with a representative slow operation and evidence: execution plan, rows/keys examined, buffer/cache behavior, lock waits, I/O, CPU, connection saturation and the distribution of latency.

The first optimization is frequently reducing work: read fewer rows, project fewer columns/fields, index the actual predicate, remove a query loop, batch work, or change the data model so the hot path does not reconstruct a large object graph.

After a change, I measure the write cost as well. Indexes, materialized projections, denormalized fields and additional replicas all improve some reads by moving work elsewhere.

Concurrency and transaction boundaries

A transaction should protect one coherent consistency decision and then finish. I avoid remote network calls while database locks or snapshots are held. When a workflow crosses services, I prefer local transactions plus explicit messaging/outbox/saga patterns instead of attempting to stretch a database transaction across remote dependencies.

Concurrency failures are normal production behavior. Deadlocks, serialization failures, duplicate messages and failover retries need bounded retry policies and idempotent business behavior. Retrying blindly can duplicate a payment, booking, order or notification even if the database itself remains consistent.

High availability is application behavior

A replica, standby or cluster only provides infrastructure capability. The application still needs timeouts, reconnect behavior, read-consistency rules and a tested response to role changes.

I document which requests can tolerate stale data, which writes require stronger acknowledgement and what happens during a failover window. This is especially important for confirmation pages, inventory, payments and other workflows where users expect read-after-write behavior.

Backup and recovery

Replication is not a backup. A bad migration, accidental delete or corrupted logical state can replicate successfully.

For each production database I want a recovery-point objective and recovery-time objective. Backups are encrypted, retained independently and tested by restoring into another environment. The test is not complete when files are restored; it is complete when the application can connect and critical integrity checks pass.

Point-in-time recovery also needs enough log history—binary log, WAL or equivalent—to reach the target moment. Retention therefore needs to match the recovery policy.

Observability I expect

At database level I monitor query latency, throughput, active connections, connection-pool wait, replication lag, lock waits, storage growth and slow-query evidence. Engine-specific signals such as vacuum/bloat, buffer-pool behavior or document/index size are then layered on top.

At application level, database spans and metrics should identify the logical operation without emitting sensitive SQL parameters or document payloads. The goal is to connect a slow user request to a specific database operation and its saturation signal.

Failure modes I design against

• Putting core relational fields into one giant JSONB document. I treat this as a production risk because it can increase latency, widen the failure domain or make recovery behavior ambiguous.
• Partitioning by a key users never filter on. I treat this as a production risk because it can increase latency, widen the failure domain or make recovery behavior ambiguous.
• Adding a generic GIN index when only one JSON path is queried. I treat this as a production risk because it can increase latency, widen the failure domain or make recovery behavior ambiguous.
• Creating a partition per customer when the tenant count is very large. I treat this as a production risk because it can increase latency, widen the failure domain or make recovery behavior ambiguous.

These failure modes are useful review prompts because they turn a generic “database best practice” discussion into a concrete production scenario. If the team cannot explain how the system behaves under one of these conditions, that behavior is still an architectural unknown.

Deployment and migration strategy

Schema and index changes are production deployments. I prefer backward-compatible migrations that allow old and new application versions to overlap. Large index builds, backfills, partition changes or validation work are scheduled and monitored rather than hidden inside application startup.

When a change can create heavy I/O or locks, I test it against production-like volume and define a stop condition. A migration plan should include how to pause, roll forward or recover if runtime behavior differs from the estimate.

Production checklist

• Partition for pruning or operations: the workload assumption and operational owner are documented.
• Keep the partition key in important query predicates: the workload assumption and operational owner are documented.
• Use JSONB for variable attributes, not every column: the workload assumption and operational owner are documented.
• Index the JSON operators actually used: the workload assumption and operational owner are documented.
• Keep partition count operationally reasonable: the workload assumption and operational owner are documented.
• Make retention cheap: the workload assumption and operational owner are documented.
• Query/operation p95 and p99 are observable.
• Connection pools have explicit maximums and wait metrics.
• Backup restore has been tested recently.
• Replica/standby lag has an alert threshold.
• Schema/index migrations have a rollback or roll-forward plan.
• Sensitive values are excluded from logs and telemetry.

Related guides

• Start with the PostgreSQL production architecture pillar
• PostgreSQL Production Architecture: Indexing, MVCC, Replication and Scaling
• PostgreSQL Indexing: B-Tree, GIN, GiST and BRIN in Production

Closing perspective

For PostgreSQL, my rule is to choose structures from the workload outward: access pattern, consistency, concurrency, failure behavior, recovery and only then the specific database feature. That approach produces systems that remain understandable when data volume, traffic and team size grow.