Partition Pruning That Silently Drops Seeded Rows

Your seed script ran clean, the row count looks right, and your query returns zero results. No error. No warning. Just an empty result set where your test data should be. Partition pruning is one of the quietest data killers in a test suite — the query optimizer eliminates entire partitions before the executor even looks at them, and your seeded rows disappear without a trace.

The problem is structural: test data engineers seed rows with fixed timestamps, synthetic IDs, or hard-coded date ranges that made sense when the schema was flat. Once a DBA adds range partitioning on created_at or event_date, those seeds may land in a partition the query never touches. The optimizer is doing its job perfectly; your data pipeline is just feeding it the wrong inputs.

By the end of this article you'll know exactly how pruning interacts with seeded data, how to instrument your Postgres or BigQuery setup to catch mismatches before CI, and how to write seeds that stay partition-aware across schema migrations.

How the Optimizer Prunes Your Seeds Out of Existence

Partition pruning is the query planner's optimization where it skips scanning any partition whose bounds cannot satisfy the WHERE clause. In Postgres 14+ with declarative range partitioning, this happens at plan time (constraint exclusion) and again at execution time (run-time pruning) for parameterized queries. A query like WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31' will never touch a partition bounded to 2023-01-01–2023-12-31, no matter how many rows you seeded there.

In a test architecture this matters because seed data is almost always authored independently of partition DDL. A factory generates a row with Faker().date_between(start_date='-2y', end_date='today'), the row lands in whatever partition covers that date, and the test query — which uses a hard-coded range matching the current sprint — never intersects it. The failure mode is indistinguishable from a legitimately empty result: no exception, no plan warning, just 0 rows. This is structurally similar to clock skew silently voiding date-range seeds — both corrupt the data without touching it.

Building Partition-Aware Seeds That Can't Go Stale

The fix has two parts: make seeds partition-aware at generation time, and add a post-seed assertion that proves the rows are actually visible to the query under test. Neither is optional.

Step 1 — Introspect partition bounds at seed time

Query pg_partitions (or the information schema equivalent) to retrieve the current live partition range, then constrain your factory to that window. This keeps seeds valid across partition rollovers without manual updates.

import psycopg2
from datetime import datetime, timezone

def active_partition_bounds(conn, parent_table: str) -> tuple[datetime, datetime]:
    """Return (start, end) of the partition whose range includes NOW()."""
    sql = """
        SELECT
            (pg_get_expr(c.relpartbound, c.oid))::text AS bound_expr,
            c.relname
        FROM pg_inherits i
        JOIN pg_class c ON c.oid = i.inhrelid
        JOIN pg_class p ON p.oid = i.inhparent
        WHERE p.relname = %s
    """
    with conn.cursor() as cur:
        cur.execute(sql, (parent_table,))
        rows = cur.fetchall()
    # parse FOR VALUES FROM (...) TO (...) — simplified for range partitions
    now = datetime.now(tz=timezone.utc)
    for bound_expr, _ in rows:
        start, end = _parse_range_bound(bound_expr)
        if start <= now < end:
            return start, end
    raise RuntimeError(f"No active partition found for {parent_table}")

Pass those bounds into your factory. With factory_boy this is a one-liner override; with raw Faker you clamp date_between to (start, end). Seeds generated this way survive a monthly partition rotation without any fixture edits.

Step 2 — Assert row visibility after seeding

A row-count check on the parent table is not enough — the optimizer can still exclude it. Assert visibility through the exact query predicate your test will use:

def assert_seed_visible(conn, table: str, seed_id: int, predicate: str) -> None:
    sql = f"SELECT COUNT(*) FROM {table} WHERE id = %s AND ({predicate})"
    with conn.cursor() as cur:
        cur.execute(sql, (seed_id,))
        count = cur.fetchone()[0]
    assert count == 1, (
        f"Seeded row {seed_id} is invisible under predicate '{predicate}'. "
        "Check partition bounds vs seed timestamp."
    )

Run this inside your pytest fixture teardown, not in the test body. If the assertion fires, the fixture fails fast with a clear message instead of a cryptic AssertionError: expected 1 result, got 0 three layers deep in a service call.

Step 3 — Confirm via EXPLAIN

For CI pipelines, capture the query plan and assert that the expected partition appears in it. A dropped partition shows up as a missing Seq Scan on <partition_name> node:

EXPLAIN (FORMAT JSON)
SELECT * FROM events
WHERE created_at BETWEEN '2025-01-01' AND '2025-03-31'
  AND tenant_id = 42;

Parse the JSON output with JQ in your GitHub Actions step and fail the job if the target partition name is absent. Before this approach, a mis-dated seed caused silent test passes that masked a real reporting bug for two sprints; after adding the EXPLAIN assertion to the pipeline, the same class of mistake is caught in under 4 seconds at plan time.

Two Mistakes Senior Engineers Still Make With Partitioned Seeds

Seeding against the parent table and trusting row count. A SELECT COUNT(*) FROM events after insert confirms the row exists somewhere in the partition tree — it says nothing about which partition it landed in. Engineers check the count, see 1, and move on. The query under test uses a range predicate that prunes the actual partition. This happens because the mental model of "insert into parent = queryable everywhere" is correct for unpartitioned tables and silently wrong for range-partitioned ones. Fix: always assert visibility through the same predicate the production query uses, not just existence.

Using relative dates in seeds without anchoring to the partition calendar. datetime.now() - timedelta(days=30) looks safe until a partition boundary sits at -29 days. The seed lands in the previous partition; the query targets the current one. This is especially common in dbt test seeds where the CSV was authored once and never updated. The org-level cause is that partition DDL lives in a migration file owned by the infra team, and the test data team doesn't get notified when it changes. The fix is the introspection approach above — let the database tell you the bounds rather than hard-coding them.

Myths That Let Partition Pruning Bugs Survive Code Review

"If the seed script doesn't error, the data is queryable." Inserts into a range-partitioned table succeed as long as the value falls within any defined partition. A missing or wrong partition doesn't raise an error — in Postgres it raises ERROR: no partition of relation found for row only if no partition matches at all. If a partition exists but the query predicate excludes it, the insert succeeds and the read silently returns nothing. Teams conflate insert success with read visibility, which are completely independent concerns in a partitioned schema. This same silent-pass failure pattern shows up in other layers too — for example, JSONPath assertions that silently pass on bad data follow identical logic: the operation succeeds, the result is wrong, nothing complains.

"Partition pruning only matters at scale." Pruning is enabled by default in Postgres even on tables with two partitions and ten rows. It's not a performance feature that kicks in above some row threshold — it's a correctness-affecting optimizer behavior that runs on every range-predicated query. Small test databases are just as affected as multi-terabyte prod tables. Teams that treat partitioning as a "production concern" and leave test schemas flat are testing a different system than the one they're shipping. If your test data doesn't reflect the real schema structure, your tests aren't testing what you think they are.

Partition pruning bugs are invisible by design — that's what makes them expensive. The defense is mechanical: introspect partition bounds at seed time, assert row visibility through the real query predicate, and parse EXPLAIN output in CI. Add the active_partition_bounds helper to your shared test utilities, wire the EXPLAIN assertion into your GitHub Actions workflow, and the entire class of problem becomes a fast, noisy failure instead of a silent wrong answer.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.