Hash partitioning for uniform distribution: the third type

Capsule description

Range covers time-series (capsule 03). List covers multi-tenant SaaS and discrete categories (capsule 04). There's a third type — the least common of the three but the one that shows up when the other two don't apply —: hash partitioning. This type doesn't use an ordered column or a discrete set of values; it uses a hash of the column to distribute rows evenly across N predefined partitions.

The typical case is "I need to partition this table to scale I/O or reduce contention, but I have no natural criterion". If you have a users table with 200M rows and the dominant queries are WHERE user_id = ?, range by id groups the new ones into a single hot partition and leaves the old ones cold. List doesn't apply (high cardinality). Hash distributes the IDs uniformly across 8 partitions, each with 25M rows, and the insert/query load spreads evenly.

This capsule teaches you the PARTITION BY HASH syntax, the real use cases (sharding by user_id, balanced multi-tenant, load distribution), the explicit trade-offs vs range and list, and why hash partitioning rarely appears alone — it's generally combined with list (tier-based sub-partitioning, which you already saw in capsule 04).

By the end you'll be able to recognize when hash is the answer, write the complete DDL with the right choice of modulus, and understand why hash is the default tool when there's nothing better.

Mental model: hash is "throw the row into one of N boxes, equiprobably"

If range is "this contiguous range goes to this partition" and list is "these discrete values go to this partition", hash is "I compute hash(column) mod N and the row goes to that partition". PostgreSQL applies a deterministic hash function to the partition key, takes the modulus against N (the partition count), and routes the row to the partition whose remainder matches.

┌──────────────────────────────────────────────────────────────┐
│             INSERT INTO users (id=12345, ...)                │
│                                                              │
│                            │                                 │
│                            ▼                                 │
│              ┌─────────────────────────┐                     │
│              │ users (parent, router)  │                     │
│              │ PARTITION BY HASH (id)  │                     │
│              └────────────┬────────────┘                     │
│                           │                                  │
│                           │  hash(12345) mod 4 = 2           │
│                           │                                  │
│      ┌────────────────────┼────────────────────┐             │
│      ▼                    ▼                    ▼             │
│ ┌───────────┐      ┌───────────┐         ┌───────────┐       │
│ │users_h0   │      │users_h1   │         │users_h2   │ ◀──── │
│ │modulus 4  │      │modulus 4  │         │modulus 4  │       │
│ │remainder 0│      │remainder 1│         │remainder 2│       │
│ │(50M rows) │      │(50M rows) │         │(50M rows) │       │
│ └───────────┘      └───────────┘         └───────────┘       │
│                                                              │
│              users_h3 (modulus 4, remainder 3, 50M rows)     │
└──────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. The distribution is uniform by construction. If you have 200M rows and 4 hash partitions, each will hold ~50M rows, regardless of the distribution of values in the column. PostgreSQL uses hash_extended (a uniformly-distributing hash function) to guarantee even spread.

2. There's no "ordered" partition key in hash. There are no contiguous ranges or explicit values. The planner can't prune for range queries (WHERE id BETWEEN 100 AND 200) because rows with IDs 100-200 are spread across all partitions. Pruning only works for exact equality (WHERE id = 12345).

3. The partition count is chosen up front and changing it is expensive. If you start with modulus 4 and want to move to modulus 8 later, every row has to recompute its hash and migrate partitions. It's not a trivial operation. Choose the count well from the start (typically a power of 2: 4, 8, 16, 32).

This explains hash partitioning's sweet spot: tables where (a) queries are exact-equality lookups on the partition key, (b) there's no natural range/list criterion, and (c) you want to balance load across partitions to parallelize I/O or reduce contention.

The complete SQL: users table partitioned by hash

You're going to build the setup for a users table with 200M rows that need to spread evenly across 8 partitions.

Step 1: parent table with PARTITION BY HASH

CREATE TABLE users (
    id          BIGSERIAL,
    email       TEXT NOT NULL,
    username    TEXT NOT NULL,
    profile     JSONB DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (id, email),
    UNIQUE (id, username)
) PARTITION BY HASH (id);

Note:

• PARTITION BY HASH (id) declares that routing is done by the hash of id.
• PRIMARY KEY (id) works because id is the partition key (it's already included).
• UNIQUE (id, email) and UNIQUE (id, username) are an unavoidable concession: unique constraints on partitioned tables must include the partition key. A globally-unique email is not enforceable at the DB level with this structure — you have to validate it at the app level or use an auxiliary table.

This limitation is the main reason hash partitioning on users is rarely done naively. If your schema requires a global UNIQUE(email), hash is not the answer. It's one of the explicit trade-offs.

Step 2: the 8 hash partitions

CREATE TABLE users_h0 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 0);
CREATE TABLE users_h1 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 1);
CREATE TABLE users_h2 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 2);
CREATE TABLE users_h3 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 3);
CREATE TABLE users_h4 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 4);
CREATE TABLE users_h5 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 5);
CREATE TABLE users_h6 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 6);
CREATE TABLE users_h7 PARTITION OF users
    FOR VALUES WITH (modulus 8, remainder 7);

modulus 8 declares that there are 8 partitions; remainder N indicates which one this is. PostgreSQL computes hash_extended(id) mod 8 and routes to the partition with the matching remainder.

Important: the 8 partitions must cover every remainder (0 through 7). If you forget one, inserts whose hash lands on that remainder fail (no default partition can save you here — hash must be exhaustive).

Step 3: a default partition? Hash flatly doesn't allow one

Unlike range and list, hash partitioning does not allow a default partition. It's not that it's "inadvisable": PostgreSQL rejects it outright.

CREATE TABLE users_default PARTITION OF users DEFAULT;

ERROR:  a hash-partitioned table may not have a default partition

And it makes sense: if you declare every partition from 0 to modulus - 1, you cover the whole space. Every row has its partition by construction. There's no "out of range" case that, in range or list, would justify a safety net.

The corollary is that hash partitions must be exhaustive. If you declare 7 out of 8, inserts whose hash lands on the missing remainder fail:

ERROR:  no partition of relation "users" found for row
DETAIL:  Partition key of the failing row contains (id) = (7).

That's a configuration error — you're missing a CREATE TABLE — not something a default should absorb.

Step 4: indexes that propagate

-- Index on the JSONB profile for search queries
CREATE INDEX users_profile_gin_idx ON users USING GIN (profile);

-- Index on created_at to list recent users
CREATE INDEX users_created_at_idx ON users (created_at DESC);

Each partition gets its own local indexes. The key difference vs range: the index on created_at here gets no benefit from pruning (recent users are spread across all 8 partitions), but it still helps speed up the sort within each one.

When hash is the right answer

Hash partitioning is niche. These are the 4 scenarios where it wins.

Scenario 1: equality lookup on a massive table with no time criterion

Case: a users table with 200M rows. Dominant query: SELECT * FROM users WHERE id = ? (auth, profiles). No significant time-range queries.

Why hash: range by id (e.g. 0-25M, 25M-50M, ...) would create a hot partition — new users always go to the last one, inserts concentrate there, and queries for recently-registered users do too. List doesn't apply (200M unique values). Hash spreads evenly: each partition receives 1/8 of the inserts and 1/8 of the queries. Balanced load.

Observable benefit: distributed I/O, per-partition autovacuum doesn't block the others, the connection pool can parallelize better.

Scenario 2: append-only table with write contention

Case: an events_aggregate table receiving 50k inserts/second, all going to "now". Without partitioning, there's contention on the right edge of the B-tree (hot page) and on the table's last block.

Why hash: it distributes the inserts across N partitions. Each one receives 50k/N inserts/second, each one has its own independent B-tree right edge. Reduces contention drastically.

Observable benefit: insert throughput grows almost linearly with the partition count (up to the disk's I/O limit).

Scenario 3: sub-partitioning "small tenants" in tier-based multi-tenant

Case: you already saw this pattern in capsule 04. You have 5 enterprise tenants with a dedicated partition (LIST) and 1500 small tenants in a "small" partition. That "small" partition might hold 100M rows with ~67k per tenant on average. A query from tenant 472 inside "small" would scan the entire partition… unless you sub-partition "small" by hash.

Why nested hash: it splits "small" into 8 sub-partitions by hash of tenant_id. Tenant 472's query now scans only 1/8 of "small" (12M rows instead of 100M). Natural balance.

-- Recapping the pattern from capsule 04:
CREATE TABLE tenant_data_small PARTITION OF tenant_data
    FOR VALUES IN (6, 7, 8, /* ... */ 1505)
    PARTITION BY HASH (tenant_id);

CREATE TABLE tenant_data_small_h0 PARTITION OF tenant_data_small
    FOR VALUES WITH (modulus 8, remainder 0);
-- ... h1 through h7

This is probably the most common use of hash in real production: as the second level of a combined partitioning scheme, not as the first level.

Scenario 4: parallelizing I/O across tablespaces or disks

Case: you have a massive table and want to spread its storage across multiple physical disks to parallelize I/O. Each hash partition can live in its own tablespace.

CREATE TABLESPACE disk_a LOCATION '/mnt/disk_a';
CREATE TABLESPACE disk_b LOCATION '/mnt/disk_b';

CREATE TABLE users_h0 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0)
    TABLESPACE disk_a;
CREATE TABLE users_h1 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1)
    TABLESPACE disk_a;
CREATE TABLE users_h2 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2)
    TABLESPACE disk_b;
CREATE TABLE users_h3 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3)
    TABLESPACE disk_b;

Today it's less common (cloud storage like EBS or GCP PD already parallelizes internally), but it's still valid for on-premise deployments or when you want to segregate I/O for operational reasons.

Explicit trade-offs: hash vs range vs list

This table is the short answer to "when do I use each?". Memorize it.

Mnemonic rule:

• Does your column have a temporal or continuous numeric order and you want retention? Range.
• Does your column have known discrete values and you want isolation by value? List.
• Neither applies, but you need to split to scale? Hash.

Worked example: 200M rows, sharding by user_id

Let's simulate the complete case. Assume PostgreSQL 16 and 200M rows spread across 8 hash partitions.

Setup: populate the 8 partitions

-- Parent table + 8 partitions (you already created them above)

-- Generate 200M distributed rows. This takes several minutes.
-- For a local test, reduce it to 5M rows.
INSERT INTO users (email, username, created_at)
SELECT
    'user_' || g || '@example.com',
    'user_' || g,
    NOW() - (random() * INTERVAL '730 days')
FROM generate_series(1, 200000000) g;

ANALYZE users;

Verify the distribution is uniform

SELECT
    'users_h0' AS partition, count(*) FROM users_h0 UNION ALL
SELECT 'users_h1', count(*) FROM users_h1 UNION ALL
SELECT 'users_h2', count(*) FROM users_h2 UNION ALL
SELECT 'users_h3', count(*) FROM users_h3 UNION ALL
SELECT 'users_h4', count(*) FROM users_h4 UNION ALL
SELECT 'users_h5', count(*) FROM users_h5 UNION ALL
SELECT 'users_h6', count(*) FROM users_h6 UNION ALL
SELECT 'users_h7', count(*) FROM users_h7
ORDER BY partition;

Expected output:

 partition | count
-----------+----------
 users_h0  | 25001234
 users_h1  | 24998456
 users_h2  | 25000891
 users_h3  | 24999102
 users_h4  | 25001543
 users_h5  | 24998987
 users_h6  | 25000234
 users_h7  | 24999553

Each partition holds ~25M rows (200M / 8), with <0.01% variation. That's the uniform distribution the hash guarantees.

Equality query (with pruning)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE id = 142857;

Real output (PostgreSQL 17):

Index Scan using users_h6_id_username_key on users_h6 users
                                          (cost=0.29..8.30 rows=1 width=55)
                                          (actual time=0.021..0.022 rows=1 loops=1)
  Index Cond: (id = 142857)
  Buffers: shared hit=6

Planning Time: 0.336 ms
Execution Time: 0.036 ms

Key reading: only users_h6 appears. PostgreSQL computed the hash of 142857, got remainder 6, and opened only that partition. The other 7 aren't even mentioned. That's partition pruning in hash.

Don't try to guess which partition an ID lands in. PostgreSQL's hash is not id mod 8 or anything you can work out in your head — it's hash_extended(), a real scattering function. 142857 lands in h6 and there's no way to deduce that by looking at the number. If you need to know, ask the database:

SELECT id, tableoid::regclass AS partition FROM users WHERE id = 142857;

   id   | partition
--------+-----------
 142857 | users_h6

Range query (no pruning)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM users WHERE id BETWEEN 100 AND 200;

Real output:

Aggregate
  ->  Append
        ->  Index Only Scan using users_h0_pkey on users_h0 users_1
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h1_pkey on users_h1 users_2
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h2_pkey on users_h2 users_3
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h3_pkey on users_h3 users_4
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h4_pkey on users_h4 users_5
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h5_pkey on users_h5 users_6
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h6_pkey on users_h6 users_7
              Index Cond: ((id >= 100) AND (id <= 200))
        ->  Index Only Scan using users_h7_pkey on users_h7 users_8
              Index Cond: ((id >= 100) AND (id <= 200))

All 8 partitions appear. PostgreSQL can't know a priori which partition each ID in the range is in (because hash scatters the sequence). It has to look at all of them.

This demonstrates hash's central trade-off: you win on equality queries, you lose on range queries. If your workload has range queries on the partition key, hash is not the right choice.

Query with IN (pruning does happen)

An IN is not a range: it's a set of equalities. PostgreSQL hashes each value in the list and only opens the partitions that come out. With two IDs that land in different partitions:

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id IN (142857, 5);

Append
  ->  Index Scan using users_h5_pkey on users_h5 users_1
        Index Cond: (id = ANY ('{142857,5}'::bigint[]))
  ->  Index Scan using users_h6_pkey on users_h6 users_2
        Index Cond: (id = ANY ('{142857,5}'::bigint[]))

Two partitions, not eight. The 5 lives in h5 and 142857 in h6; the other six were discarded. This is the practical difference between IN (...) and BETWEEN on a hash table, and it explains why the IN row in the trade-offs table says "Yes".

Comparison with a NON-partitioned table

So you can see the magnitude of the benefit on equality queries:

The sweet spot is clear: workloads with many equality lookups and high concurrency win; workloads with range queries lose a little.

Hash from SQLAlchemy 2.0 async

Just like in range and list, the ORM doesn't notice. SQLAlchemy interacts with the parent table like any normal table.

Model

# models/user.py
from datetime import datetime
from sqlalchemy import BigInteger, String
from sqlalchemy.dialects.postgresql import JSONB, TIMESTAMP
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    email: Mapped[str] = mapped_column(String, nullable=False)
    username: Mapped[str] = mapped_column(String, nullable=False)
    profile: Mapped[dict] = mapped_column(JSONB, default=dict)
    created_at: Mapped[datetime] = mapped_column(
        TIMESTAMP(timezone=True), nullable=False
    )

Unlike range, here you don't need to declare the partition key as part of the PK from the ORM, because id is already both the natural PK and the partition key.

Equality lookup (leverages pruning)

# services/user_service.py
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

async def get_user_by_id(session: AsyncSession, user_id: int) -> User | None:
    stmt = select(User).where(User.id == user_id)
    result = await session.execute(stmt)
    return result.scalar_one_or_none()

PostgreSQL computes the hash and scans only the corresponding partition. Latency ~50µs.

Lookup by email (does NOT leverage pruning)

async def get_user_by_email(session: AsyncSession, email: str) -> User | None:
    stmt = select(User).where(User.email == email)
    result = await session.execute(stmt)
    return result.scalar_one_or_none()

This query doesn't use the partition key (id). PostgreSQL scans all 8 partitions, each with its index on email (if one exists). Each lookup is fast individually, but there are 8 of them in parallel. For tables whose dominant queries are by email, hash by id isn't the best choice — consider hashing by email directly, or not partitioning at all.

Bulk insert with natural distribution

async def bulk_create_users(session: AsyncSession, users_data: list[dict]) -> None:
    """Inserts many users. PostgreSQL automatically distributes them
    across the 8 partitions according to the hash of id."""
    session.add_all([User(**data) for data in users_data])
    await session.flush()

When you insert 10000 users, PostgreSQL spreads them ~1250 per partition. Balanced load without your code doing anything special.

Why does this matter in real work?

1. It's the tool for massive tables with no time pattern. Not every large table has created_at as its dominant query column. Identity tables (users, accounts, organizations), catalog tables (products if the app is very large), relationship tables (follows, friendships) — all of them might need partitioning and none of them work well with range. Hash is the default answer.

2. It reduces insert contention on high-throughput append-only tables. Apps with telemetry, logging, metrics, or events at >10k inserts/sec suffer contention on the B-tree's "hot page". Partitioning by hash distributes that contention across N independent B-trees. A concrete operational difference: going from 8k inserts/sec to 30k+ on the same DB.

3. It's piece B of tier-based combined partitioning. As you saw in capsule 04, nested hash inside list is the canonical pattern for multi-tenant SaaS with hundreds of tenants. Knowing how to write hash partitioning is a prerequisite for implementing tier-based properly.

4. It protects you from picking the wrong tool out of inertia. The backend dev reflex is "let's partition by date" because that's the 80% case. But if your table has no dominant date queries, partitioning by date adds overhead without benefit. Recognizing when hash is the right answer saves you a failed migration.

5. A complete decision matrix for senior interviews. "When would you use hash partitioning?" is a standard depth question. Being able to answer with concrete scenarios (equality lookup on 200M rows, insert contention, tier-based sub-partitioning) sets you apart.

Traps and common mistakes

Mistake 1 (conceptual): expecting hash to prune for range queries

Symptom: you partition users by hash of id. You run SELECT * FROM users WHERE id BETWEEN 1000 AND 2000. EXPLAIN shows it scanning all 8 partitions. You get frustrated.

Why it happens: hash scatters the IDs intentionally. ID 1000 might be in users_h3, 1001 in users_h7, 1002 in users_h0. PostgreSQL can't know a priori which partitions the range touches without computing the hash of every value — and for 1000 values, walking all 8 partitions is more efficient.

How to tell: if your dominant queries are WHERE col BETWEEN ... or WHERE col >= X, hash is the wrong choice. Consider range on that column.

How to fix it: if you genuinely need range queries, change the partitioning type to range. This is a non-trivial migration. If you want to keep both efficient equality queries and range queries, consider:

• Range on one column and a secondary index on the other.
• Not partitioning and using correct indexes (the default option if the table is under 100M rows).

Mistake 2 (practical): picking a partition count that's hard to change later

Symptom: you start with modulus 4 because "it's a start". A year later the table has grown and you want to move to modulus 16 for better distribution. You realize changing the modulus requires recreating every partition and migrating every row. It's an hours/days operation.

Why it happens: changing modulus changes every row's hash bucket (hash mod 4 ≠ hash mod 16 in general). Each row probably has to move to a different partition. It's like rebuilding the table.

How to prevent it:

• Choose a power of 2: 4, 8, 16, 32. Powers of 2 give you flexibility because you can "sub-partition" each future hash without recalculating everything (in some cases, with advanced techniques).
• Think about a 3-5 year horizon. If the table will hold 1B rows, partitioning into 32 from the start is more conservative than into 4.
• Initial sweet spot: 8-16 partitions covers most cases. <4 leaves little margin, >32 adds planner overhead.

How to fix it if it's already wrong: zero-downtime migration (technique from #13). Create a new table with the desired modulus, backfill, swap. It's work, but do it before the table hits 500M rows.

Mistake 3 (conceptual): thinking hash gives per-entity isolation

Symptom: you partition users by hash of id expecting "each user to have their own partition". You realize that users_h0 holds millions of mixed users, not just one.

Why it confuses people: hash groups by the hash result, not by the value. If you have 8 partitions, each holds ~25M rows (assuming 200M total). There's no one user per partition.

How to tell: hash is for load distribution, not for per-entity isolation. If you need physical isolation per entity (typical in enterprise multi-tenant), use list partitioning (capsule 04) or combined partitioning.

How to fix it: if your real requirement is "tenant X has its own dedicated physical partition", the tool is list, not hash. If your requirement is "balance load across N partitions", hash is correct — but understand it as balance, not as isolation.

Mistake 4 (practical): unique constraints on columns not included in the partition key

Symptom: you try CREATE TABLE users (..., email TEXT NOT NULL UNIQUE, ...) PARTITION BY HASH (id). PostgreSQL fails with unique constraint on partitioned table must include all partitioning columns.

Why it happens: the same principle as in range and list — global uniqueness can't be guaranteed on a partitioned table without scanning every partition, so PostgreSQL requires the constraint to include the partition key.

How to tell: review your UNIQUE constraints before choosing hash. If email, username, slug are globally UNIQUE and critical, hash by id breaks that guarantee.

Workarounds:

1. UNIQUE (email, id) or UNIQUE (id, email): technically valid but allows the same email under different ids (useful almost never).
2. App-level validation with an advisory lock or retries.
3. An auxiliary email_reservations(email TEXT PRIMARY KEY, user_id BIGINT) table, non-partitioned, that guarantees global uniqueness.
4. Don't partition if the constraints are critical and the workarounds aren't acceptable.

Mistake 5 (conceptual): hash as the "default" without thinking the decision through

Symptom: someone on the team says "let's partition by hash, no decisions needed". They partition every large table by hash. Performance doesn't improve; in some cases it gets worse.

Why it happens: hash partitioning is not the "safe default". It's the tool for when the other two don't apply. Applying it without reason loses range's benefits (range pruning, dropping old data) and list's (isolation by category) without gaining anything equivalent.

How to tell: if your table has dominant time-range queries → range. If it has dominant discrete-category queries → list. Only if neither applies → consider hash. If hash doesn't clearly apply either → you probably don't need to partition.

How to fix it: review the project's partitioning decisions against the decision matrix. If any was made "out of inertia", evaluate whether the right type is another one (reverting partitioning is laborious but can be the right call).

Exercises

Exercise 1: decide between hash, range and list

For each of these cases, decide which partitioning type applies and why:

a) posts table with 80M rows. Queries: WHERE author_id = ? ORDER BY created_at DESC LIMIT 20, WHERE id = ?. No retention.

b) chat_messages table with 500M rows, growing 10M/month. Queries: WHERE channel_id = ? AND created_at > NOW() - INTERVAL '7 days'. Retention: 12 months.

c) sensor_readings table with 200M rows. Queries: WHERE sensor_id = ?. Cardinality of sensor_id: 50000 distinct. No retention. Insert pattern: 10k inserts/sec simultaneously from 50000 sensors.

d) notifications table with 30M rows. Queries: WHERE user_id = ? AND read = false ORDER BY created_at DESC. Retention: 6 months.

Exercise 2: detect a range-query problem on hash

Your teammate partitioned events_aggregate by hash of id with modulus 8. Now they complain: the query SELECT count(*) FROM events_aggregate WHERE id > 1000000 scans every partition and takes 4 seconds. Before (non-partitioned) it took 1.2 seconds.

What happened? How do you fix it?

-- Migration (zero-downtime, technique from #13):
-- 1. Create events_aggregate_new without partitioning (or with range if the volume warrants it)
-- 2. Backfill from events_aggregate
-- 3. Atomic swap
-- 4. Drop the old events_aggregate

-- Instead of:
SELECT count(*) FROM events_aggregate WHERE id > 1000000;

-- If the use case allows, count by specific chunks:
SELECT count(*) FROM events_aggregate WHERE id IN (1000001, 1000002, ...);
-- Hash does prune for IN.

Exercise 3: size modulus for a table that's going to grow

You have to design audit_events from scratch. Current volume: 10M rows. 5-year projection: 5B rows (1B/year). Queries: WHERE actor_id = ? (lookup by the user who generated the event). Cardinality of actor_id: ~1M distinct. No retention.

Choose a modulus and justify it.

CREATE TABLE audit_events (
    id          BIGSERIAL,
    actor_id    BIGINT NOT NULL,
    action      TEXT NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, actor_id)
) PARTITION BY HASH (actor_id);

-- Create the 32 partitions programmatically
DO $$
BEGIN
    FOR i IN 0..31 LOOP
        EXECUTE FORMAT(
            'CREATE TABLE audit_events_h%s PARTITION OF audit_events FOR VALUES WITH (modulus 32, remainder %s)',
            i, i
        );
    END LOOP;
END $$;

-- Indexes
CREATE INDEX audit_events_created_at_idx ON audit_events (created_at DESC);

Exercise 4: SQLAlchemy 2.0 async — bulk insert with natural distribution

Write a function that takes a list of 10000 User data dicts and inserts them. Verify conceptually that the distribution across the 8 hash partitions will be uniform.

# services/user_service.py
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.dialects.postgresql import insert as pg_insert

from app.models.user import User

async def bulk_create_users(
    session: AsyncSession,
    users_data: list[dict],
    chunk_size: int = 1000,
) -> int:
    """Inserts users in chunks. PostgreSQL automatically distributes them
    across the 8 hash partitions according to the id (BIGSERIAL).

    Returns: the number of rows inserted.
    """
    total_inserted = 0

    for i in range(0, len(users_data), chunk_size):
        chunk = users_data[i:i + chunk_size]
        stmt = pg_insert(User).values(chunk)
        await session.execute(stmt)
        total_inserted += len(chunk)

    await session.flush()
    return total_inserted

from sqlalchemy import text

async def verify_distribution(session: AsyncSession) -> dict[str, int]:
    """Verifies the distribution of rows across the 8 partitions."""
    result = await session.execute(
        text("""
            SELECT
                tableoid::regclass::text AS partition,
                count(*) AS rows
            FROM users
            GROUP BY tableoid
            ORDER BY partition
        """)
    )
    return {row.partition: row.rows for row in result.all()}

# Expected:
# {
#   'users_h0': 1247,
#   'users_h1': 1259,
#   'users_h2': 1244,
#   'users_h3': 1262,
#   'users_h4': 1241,
#   'users_h5': 1255,
#   'users_h6': 1248,
#   'users_h7': 1244
# }

Exercise 5: identify a case for combined partitioning

Your team has a tenant_events table with this profile:

• 50 main tenants (each with 50M-200M rows, uneven distribution).
• Queries: WHERE tenant_id = ? AND created_at > NOW() - INTERVAL '30 days'.
• Retention: 24 months per tenant.
• Compliance: each tenant demands physical isolation.

What partitioning type would you apply? Just one, or combined?

-- Level 1: LIST by tenant_id
CREATE TABLE tenant_events (
    id BIGSERIAL,
    tenant_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, tenant_id, created_at)
) PARTITION BY LIST (tenant_id);

-- Level 2: each tenant has its own table, partitioned by RANGE
CREATE TABLE tenant_events_t1 PARTITION OF tenant_events
    FOR VALUES IN (1)
    PARTITION BY RANGE (created_at);

CREATE TABLE tenant_events_t1_2026_05 PARTITION OF tenant_events_t1
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
-- ... more monthly partitions for t1

CREATE TABLE tenant_events_t2 PARTITION OF tenant_events
    FOR VALUES IN (2)
    PARTITION BY RANGE (created_at);
-- ... etc for the 50 tenants

-- pg_partman can manage the second level automatically

Exercise 6: argue against premature hash partitioning

Your lead proposes partitioning every large table by hash "to distribute load preventively". The tables under discussion: users (10M), posts (5M), comments (15M), events (60M).

Write a technical argument against partitioning everything by hash and propose a strategy per table.

Summary and next step

In this capsule you completed the repertoire of the 3 partitioning types with hash:

• PARTITION BY HASH (column) distributes rows uniformly across N predefined partitions using hash_extended(column) mod N.

• Real use cases: equality lookups on massive tables with no time criterion (users.id), reducing insert contention on append-only tables (events_aggregate), sub-partitioning tier-based multi-tenant (the "small" from capsule 04), parallelizing I/O across tablespaces.

• The central trade-off: hash wins on equality queries and load distribution, loses on range queries and on DROP PARTITION for retention. If you need either of the last two, hash is not the answer.

• Partition count (modulus): choose a power of 2 (8, 16, 32). Changing it later is expensive. Think about a 3-5 year horizon.

• The complete decision matrix: range for time-series + retention, list for multi-tenant + discrete categories, hash for uniform distribution when the others don't apply.

Before moving on, you should be able to:

• Recognize when hash is the right choice (high cardinality, equality queries, high concurrency).
• Write the complete DDL with modulus N, remainder X for each partition.
• Identify when hash does NOT apply (range queries, need for retention, critical UNIQUE constraints).
• Combine hash with list for tier-based partitioning.

Next capsule — Partition pruning and constraint exclusion. You now know the 3 types. The critical question: how do you verify the planner is actually discarding the partitions that don't apply? Without this, partitioning might not help (worse: it can slow things down). Capsule 06 trains your eye to read EXPLAIN ANALYZE on partitioned tables, shows you the cases where pruning fails (queries with OR, inadequate casts, untyped parameters), and gives you the tools to audit your setup. It's the capsule that makes sure everything you've learned so far produces the expected benefit.

Resources

1. PostgreSQL 16 — CREATE TABLE PARTITION BY HASH — the official reference. The "PARTITION BY HASH" section covers the WITH (modulus, remainder) syntax.
2. PostgreSQL 16 — Hash Partitioning details — section 5.11.2.3, goes deeper into hash routing behavior.
3. Crunchy Data — When to use Hash Partitioning — a pragmatic discussion of real use cases with examples.
4. 2ndQuadrant — Partitioning Improvements in PostgreSQL 11 — the history and improvements of hash partitioning since PG 11.
5. depesz — Hash partitioning explained — an analysis of the introduction of hash partitioning with benchmarks.
6. Citus Data — Distributed PostgreSQL with Hash Partitioning — a perspective on how Citus extends hash partitioning across multiple nodes (advanced, out of scope, but good context).
7. PostgreSQL Wiki — Partition Pruning Limitations — community documentation on when pruning works and when it doesn't, by partitioning type.

Module 4 — Advanced PostgreSQL for Backend Guide

Next capsule: Partition pruning and constraint exclusion — how to verify the planner discards the right partitions.