0Pricing
PostgreSQL Performance & Query Optimization · 课时

选择分区键与分区策略

结合真实访问模式评估范围、列表和哈希分区，选择能够有效裁剪分区的键。

选择分区键与分区策略 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why the Partition Key Decides Everything

Partitioning splits one logical table into many physical child tables. The single most important choice is the partition key — the column (or expression) PostgreSQL uses to route each row to a partition.

A good key lets the planner do partition pruning: it skips partitions that cannot match your WHERE clause, so a query touches a few partitions instead of all of them. A bad key forces scans across every partition and you lose most of the benefit.

  • Range partitioning — buckets by ordered ranges (dates, IDs).
  • List partitioning — buckets by a discrete set of values (region, tenant).
  • Hash partitioning — buckets by a hash of the key for even spread.

The right strategy is the one that matches how your queries actually filter the data.

Start From the Access Pattern, Not the Schema

Never pick a partition key by looking at the table alone. Look at the queries. Ask three questions:

  • Which columns appear in WHERE clauses on almost every read?
  • Do queries filter by ranges (last 7 days) or exact values (tenant_id = 42)?
  • How do you delete or archive old data — by time, by customer, or never?

If 90% of your queries filter created_at >= now() - interval '7 days', then created_at is your candidate key. The partition key must appear in the query's filter for pruning to fire.

-- Inspect real filters before deciding: which columns are filtered most?
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%events%'
ORDER BY total_exec_time DESC
LIMIT 10;

Range Partitioning: The Time-Series Default

Range partitioning assigns each row to a partition based on whether the key falls inside a half-open interval [from, to). It is the natural fit for time-series and append-mostly data, where new rows have increasing timestamps and old data is queried less.

Each partition covers one period (a month here). Inserts land in the newest partition; queries that filter by date prune to just the months they need.

CREATE TABLE events (
    id          bigint        NOT NULL,
    created_at  timestamptz   NOT NULL,
    user_id     bigint        NOT NULL,
    payload     jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Watch Pruning Work on a Range Key

The payoff is visible in EXPLAIN. When your filter overlaps only one partition's range, the plan scans just that partition. The others never appear in the plan — they were pruned at planning time.

Because the filter on created_at matches the partition key, PostgreSQL knows June rows can only live in events_2026_06. The May partition is skipped entirely.

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM events
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-15';

-- Plan shows only:
--   Seq Scan on events_2026_06
-- The May partition is pruned and never scanned.

List Partitioning: Discrete, Bounded Categories

List partitioning routes rows by matching the key against an explicit set of values. Reach for it when the key is a small, stable set of discrete categories that queries filter on exactly — region, country, status, or a fixed tenant tier.

It shines when each category has different retention, locality, or compliance needs (for example, keeping EU data physically separate). Use a DEFAULT partition to catch values you did not enumerate.

CREATE TABLE customers (
    id      bigint NOT NULL,
    region  text   NOT NULL,
    name    text
) PARTITION BY LIST (region);

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('DE', 'FR', 'NL', 'ES');

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US', 'CA');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

When List Partitioning Backfires

List partitioning assumes the value set is small and slow-changing. It breaks down when:

  • The category has thousands of values (one partition per user_id) — too many partitions hurt planning time and memory.
  • Values are highly skewed — one giant partition holds 80% of rows, so pruning gives little benefit.
  • Queries rarely filter on the key — then no partition is ever pruned.

If the values are many but you still want even distribution and pruning on equality, that is the signal to consider hash partitioning instead.

Hash Partitioning: Even Spread for Point Lookups

Hash partitioning computes a hash of the key and assigns rows by hash MOD modulus. Its purpose is even data distribution when there is no natural range or list, and when queries look up rows by exact equality on a high-cardinality key like user_id.

You define a fixed number of partitions (the modulus). Each gets a remainder. Distribution is uniform, which spreads write load and keeps every partition a similar size.

CREATE TABLE sessions (
    id       bigint NOT NULL,
    user_id  bigint NOT NULL,
    token    text
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash Prunes on Equality, Not Ranges

The critical limitation: hash partitioning can only prune for equality (and IN) on the key. A range filter is useless because the hash scatters consecutive values across all partitions.

  • WHERE user_id = 1001 → prunes to exactly one partition.
  • WHERE user_id BETWEEN 1000 AND 2000 → no pruning, scans all partitions.

So hash fits point lookups and even write spread, but it is the wrong choice if your dominant queries scan ranges of the key or need time-based retention.

-- Prunes to ONE partition (equality matches the hash key):
EXPLAIN (COSTS OFF)
SELECT * FROM sessions WHERE user_id = 1001;

-- Does NOT prune (range over a hashed key scans every partition):
EXPLAIN (COSTS OFF)
SELECT * FROM sessions WHERE user_id BETWEEN 1000 AND 2000;

Match Retention and Deletes to the Key

Deletion strategy often decides the key more than reads do. Dropping a whole partition is instantaneous and reclaims space immediately; deleting millions of rows with DELETE is slow and bloats the table.

If you must purge data older than 90 days, a range key on the date lets you DROP or DETACH an old partition in milliseconds. Hash and list keys cannot do this for time-based retention because age is not aligned to any single partition.

-- Range-by-date makes retention a metadata operation:
ALTER TABLE events DETACH PARTITION events_2026_05;
DROP TABLE events_2026_05;

-- vs. the slow, bloating alternative on a non-range layout:
-- DELETE FROM events WHERE created_at < '2026-06-01';

Composite Keys and Sub-Partitioning

Sometimes one dimension is not enough. You can partition by multiple columns, or sub-partition: range by month at the top level, then hash by user_id inside each month. This combines time-based retention with even spread for point lookups.

Keep it disciplined — every extra level multiplies the partition count. Too many partitions slow down planning and consume locks. Only sub-partition when a single key genuinely cannot serve both your retention and your lookup patterns.

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')
    PARTITION BY HASH (user_id);

CREATE TABLE events_2026_06_h0 PARTITION OF events_2026_06
    FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE events_2026_06_h1 PARTITION OF events_2026_06
    FOR VALUES WITH (MODULUS 2, REMAINDER 1);

A Decision Checklist

Put the strategies side by side against your real workload:

  • Range — key is ordered (time/sequence), queries filter by ranges, you archive or drop old data by period. The default for logs, events, metrics.
  • List — key is a small fixed set of categories, queries filter by exact category, categories need separate handling (locality, retention).
  • Hash — high-cardinality key, queries are equality point lookups, you want uniform size and write spread, and you do NOT need range pruning or time-based drops.

Pick the strategy whose pruning model matches the filter your queries actually use. If no key is filtered on most queries, partitioning will not help reads — reconsider before adding the complexity.

Quick Check

Apply the decision rules to a concrete workload.

Recap

The partition key and strategy must be driven by how queries filter and how data is retired:

  • Range for ordered keys, range filters, and period-based archival — the time-series default that makes purges a metadata operation.
  • List for a small, stable set of discrete categories filtered by equality, especially when categories need distinct handling.
  • Hash for high-cardinality keys with equality point lookups and a need for even distribution — but it cannot prune ranges or drop by age.

Always verify pruning with EXPLAIN: the right key shows only the partitions your filter can match. If most queries do not filter the key at all, partitioning adds cost without speeding up reads.

常见问题解答

「选择分区键与分区策略」课时是免费的吗？

是的 — 「选择分区键与分区策略」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「选择分区键与分区策略」这节课中我会学到什么？

结合真实访问模式评估范围、列表和哈希分区，选择能够有效裁剪分区的键。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。

「选择分区键与分区策略」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 选择分区键与分区策略
  2. 计划阶段与执行阶段的分区裁剪
  3. 自动创建分区与数据保留
  4. 在线将超大表迁移到分区表
← 返回 PostgreSQL Performance & Query Optimization