0Pricing
PostgreSQL Performance & Query Optimization · Ders

Bölümleme Anahtarı ve Stratejisi Seçme

Etkili budama yapacak anahtarı seçmek için aralık, liste ve karma bölümlemeyi gerçek erişim örüntülerine göre değerlendirin.

Bölümleme Anahtarı ve Stratejisi Seçme, CoddyKit'te ücretsiz bir PostgreSQL Performance & Query Optimization dersidir. Bu, 4 dersinin 1. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, PostgreSQL Performance & Query Optimization öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

Bu dersin bazı bölümleri henüz çevrilmemiş olup İngilizce olarak gösterilmektedir.

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.

Sıkça Sorulan Sorular

“Bölümleme Anahtarı ve Stratejisi Seçme” dersi ücretsiz mi?

Evet — “Bölümleme Anahtarı ve Stratejisi Seçme” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve PostgreSQL Performance & Query Optimization kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

“Bölümleme Anahtarı ve Stratejisi Seçme” dersinde ne öğreneceğim?

Etkili budama yapacak anahtarı seçmek için aralık, liste ve karma bölümlemeyi gerçek erişim örüntülerine göre değerlendirin. PostgreSQL Performance & Query Optimization ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

PostgreSQL Performance & Query Optimization öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te PostgreSQL Performance & Query Optimization, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 1. dersidir.

“Bölümleme Anahtarı ve Stratejisi Seçme” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu PostgreSQL Performance & Query Optimization dersinde kod yazıp çalıştırabilir miyim?

Evet. Her PostgreSQL Performance & Query Optimization dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

  1. Bölümleme Anahtarı ve Stratejisi Seçme
  2. Planlama ve Çalıştırma Sırasında Bölüm Budama
  3. Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme
  4. Büyük Bir Tabloyu Çevrimiçi Olarak Bölümlere Taşıma
← PostgreSQL Performance & Query Optimization Sayfasına Dön