Partition Vectors by Tenant, Time, Language, or Model

Partitioning divides one logical PostgreSQL table into physical child tables. It can improve pruning, lifecycle operations, index size, or noisy-neighbor isolation when queries reliably include the partition key. It also multiplies indexes, statistics, migrations, and planning work. Choose tenant, time, language, or model partitions from measured query and operational needs—not row count alone.

What will you be able to do?

Identify partitioning goals and candidate keys.
Design range, list, or hash partitions.
Understand ANN index-per-partition behavior.
Test pruning and planning overhead.
Operate partition creation, detach, and retention.

Which key serves which goal?

Tenant: isolation or pruning for a bounded set of large tenants; not one partition for millions of small tenants.
Time: retention and recent-data queries; semantic search across all history touches many partitions.
Language: language-specific models/search configuration when query language is known.
Model: cleanly separates dimensions/operator classes during migrations.
Hash: distributes rows but makes lifecycle and single-tenant locality less obvious.

sql

CREATE TABLE app.embedding_events (
  tenant_id uuid NOT NULL,
  created_at timestamptz NOT NULL,
  embedding vector(768) NOT NULL
) PARTITION BY RANGE (created_at);

Every partition needs appropriate relational and vector indexes. Automated creation must be idempotent and monitored.

How does pruning work?

The planner can skip partitions when predicates constrain the partition key. A query lacking that predicate may scan many ANN indexes and merge candidates. Test the exact production SQL; implicit casts or functions can prevent expected pruning.

How do you verify it?

Compare unpartitioned and candidate designs with actual tenant/time distribution. Inspect EXPLAIN for pruning, planning time, index sizes, insert routing, retention operations, and cross-partition top-k quality. Test default partitions and missing future partitions.

What breaks in production?

Too many partitions slow planning and migrations.
A future time partition is missing and inserts fail.
Global uniqueness is assumed without the partition key.
Cross-partition ANN results use insufficient per-partition candidates.
Retention detaches data still needed by citations or legal hold.

AI pair-work prompt

Evaluate partitioning for this pgvector table [schema, distributions, queries, retention]. Compare no partitioning, tenant, time, language, model, and hash. Include pruning evidence, ANN indexes, global top-k merge, uniqueness, automation, maintenance, and rollback.

Verification contract: Approve partitioning only when a production-shaped benchmark shows a measurable benefit greater than the operational cost.

Check your understanding

Which predicate enables time pruning?
Why can many small-tenant partitions be harmful?
How does model partitioning help migration?

Official references

Partition Vectors by Tenant, Time, Language, or Model

Which key serves which goal?

Tenant: isolation or pruning for a bounded set of large tenants; not one partition for millions of small tenants.

Time: retention and recent-data queries; semantic search across all history touches many partitions.

Language: language-specific models/search configuration when query language is known.

Model: cleanly separates dimensions/operator classes during migrations.

Hash: distributes rows but makes lifecycle and single-tenant locality less obvious.

sql

CREATE TABLE app.embedding_events (
  tenant_id uuid NOT NULL,
  created_at timestamptz NOT NULL,
  embedding vector(768) NOT NULL
) PARTITION BY RANGE (created_at);

Every partition needs appropriate relational and vector indexes. Automated creation must be idempotent and monitored.

AI pair-work prompt

Evaluate partitioning for this pgvector table [schema, distributions, queries, retention]. Compare no partitioning, tenant, time, language, model, and hash. Include pruning evidence, ANN indexes, global top-k merge, uniqueness, automation, maintenance, and rollback.

Verification contract: Approve partitioning only when a production-shaped benchmark shows a measurable benefit greater than the operational cost.