Build it

We will partition sale by month, watch Postgres route and prune, automate new months, retire an old month, and map it in Prisma. Run this on a test database with PostgreSQL 16 or newer. Two of the steps, routing rows into partitions and making a report prune, are also in the Practice section below, where they run in your browser.

1. Create the partitioned parent

   The parent declares the columns, the rule and the primary key. It will never hold rows itself.

   SQLCopy

   CREATE TABLE sale (
     id           bigserial,                       -- what Prisma generates for autoincrement()
     site_id      bigint         NOT NULL,
     sale_date    date           NOT NULL,         -- the partition key
     sold_at      timestamptz(6) NOT NULL DEFAULT now(),
     total        numeric(12,2)  NOT NULL,
     PRIMARY KEY (id, sale_date)                   -- must include the partition key
   ) PARTITION BY RANGE (sale_date);
   
   -- Without sale_date in the key, Postgres refuses:
   -- ERROR: unique constraint on partitioned table must include all partitioning columns

2. Create monthly partitions, a default and one index

   The lower bound is included and the upper bound is not, so months never overlap. An index created on the parent is created on every partition, now and in the future.

   SQLCopy

   CREATE TABLE sale_2026_08 PARTITION OF sale FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
   CREATE TABLE sale_2026_09 PARTITION OF sale FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
   CREATE TABLE sale_2026_10 PARTITION OF sale FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
   CREATE TABLE sale_default PARTITION OF sale DEFAULT;   -- catches any other date
   
   CREATE INDEX sale_site_id_sale_date_idx ON sale (site_id, sale_date);

   On a big table that already has partitions, CREATE INDEX CONCURRENTLY on the parent is not allowed. Create the parent index with ON ONLY sale, build each partition's index concurrently, then attach each one with ALTER INDEX ... ATTACH PARTITION.

3. Load test rows and see where they went

   tableoid::regclass tells you which physical table each row lives in.

   SQLCopy

   INSERT INTO sale (site_id, sale_date, total)
   SELECT (g % 400) + 1, date '2026-08-01' + (g % 92), (g % 900) + 50
   FROM generate_series(1, 300000) AS g;
   ANALYZE sale;
   
   SELECT tableoid::regclass AS partition, count(*) AS rows
   FROM sale GROUP BY 1 ORDER BY 1;

   OutputCopy

     partition   |  rows
   --------------+--------
    sale_2026_08 | 101090
    sale_2026_09 |  97830
    sale_2026_10 | 101080

4. Prove pruning with EXPLAIN

   A date filter reads one partition. A site_id filter alone reads all of them, each through its own index.

   SQLCopy

   EXPLAIN (COSTS OFF)
   SELECT site_id, sum(total) FROM sale
   WHERE sale_date >= '2026-09-01' AND sale_date < '2026-10-01'
   GROUP BY site_id;
   
   EXPLAIN (COSTS OFF)
   SELECT sum(total) FROM sale WHERE site_id = 42;

   OutputCopy

    HashAggregate
      Group Key: sale.site_id
      ->  Seq Scan on sale_2026_09 sale          <- only September
            Filter: ((sale_date >= '2026-09-01'::date) AND (sale_date < '2026-10-01'::date))
   
    Aggregate
      ->  Append                                <- every partition
            ->  Bitmap Heap Scan on sale_2026_08 sale_1
                  ->  Bitmap Index Scan on sale_2026_08_site_id_sale_date_idx
            ->  Bitmap Heap Scan on sale_2026_09 sale_2
                  ->  Bitmap Index Scan on sale_2026_09_site_id_sale_date_idx
            ->  Bitmap Heap Scan on sale_2026_10 sale_3
                  ->  Bitmap Index Scan on sale_2026_10_site_id_sale_date_idx
            ->  Seq Scan on sale_default sale_4

5. Create future months automatically

   Create partitions a few months ahead so inserts never hit a missing month. Use a small function run by a daily job, or the pg_partman extension.

   Plain SQL + pg_cronpg_partman

   SQLCopy

   CREATE OR REPLACE FUNCTION ensure_sale_partition(p_month date)
   RETURNS text LANGUAGE plpgsql
   SET lock_timeout = '5s'      -- fail fast instead of queueing behind a long query
   AS $$
   DECLARE
     v_from date := date_trunc('month', p_month)::date;
     v_to   date := (date_trunc('month', p_month) + interval '1 month')::date;
     v_name text := 'sale_' || to_char(v_from, 'YYYY_MM');
   BEGIN
     IF to_regclass(v_name) IS NULL THEN
       EXECUTE format('CREATE TABLE %I PARTITION OF sale FOR VALUES FROM (%L) TO (%L)',
                      v_name, v_from, v_to);
       RETURN 'created ' || v_name;
     END IF;
     RETURN 'exists ' || v_name;
   END $$;
   
   -- Keep this month and the next 3 ready
   SELECT ensure_sale_partition((date_trunc('month', current_date)
          + make_interval(months => m))::date)
   FROM generate_series(0, 3) AS m;
   
   -- Run it every night at 03:15 with the pg_cron extension
   SELECT cron.schedule('sale-partitions', '15 3 * * *',
     $$SELECT ensure_sale_partition((date_trunc('month', current_date)
       + make_interval(months => m))::date) FROM generate_series(0, 3) AS m$$);

6. Retire an old month

   In this test, treat sale_2026_08 as the month that has aged out. Because we kept a default partition, the CONCURRENTLY form is refused, so we use a plain DETACH with a short lock timeout.

   SQLCopy

   -- With a default partition, CONCURRENTLY is refused:
   -- ERROR: cannot detach partitions concurrently when a default partition exists
   SET lock_timeout = '3s';                      -- give up rather than block the POS
   ALTER TABLE sale DETACH PARTITION sale_2026_08;
   
   -- Without a default partition (PG 14+), use the lighter lock instead.
   -- Run it on its own, never inside BEGIN ... COMMIT:
   -- ALTER TABLE sale DETACH PARTITION sale_2026_08 CONCURRENTLY;
   -- If that was interrupted, finish it with:
   -- ALTER TABLE sale DETACH PARTITION sale_2026_08 FINALIZE;

   TerminalCopy

   # Archive the detached month, check the file, then drop it
   pg_dump -Fc -t sale_2026_08 -d mangrove -f sale_2026_08.dump
   pg_restore -l sale_2026_08.dump | grep "TABLE DATA"
   psql -d mangrove -c "DROP TABLE sale_2026_08;"

7. Map it in Prisma

   Prisma has no syntax for partitioning (as of Prisma 6). The model maps to the parent table, and the partitioning lives in raw SQL inside the migration. Generate the migration with npx prisma migrate dev --create-only --name partition_sale, add PARTITION BY RANGE ("sale_date") to the generated CREATE TABLE, add the partitions below it, then run npx prisma migrate dev.

   PrismaCopy

   model Sale {
     id          BigInt   @default(autoincrement())
     siteId      BigInt   @map("site_id")
     saleDate    DateTime @map("sale_date") @db.Date
     soldAt      DateTime @default(now()) @map("sold_at") @db.Timestamptz(6)
     total       Decimal  @db.Decimal(12, 2)
   
     @@id([id, saleDate])           // composite key, includes the partition key
     @@index([siteId, saleDate])
     @@map("sale")
   }

   TypeScriptCopy

   // Always pass the date range so Postgres can prune
   const rows = await prisma.sale.findMany({
     where: {
       siteId: 42,
       saleDate: { gte: new Date('2026-09-01'), lt: new Date('2026-10-01') },
     },
   });
   // Lookups by id now need both parts of the key
   const one = await prisma.sale.findUnique({
     where: { id_saleDate: { id: 98765n, saleDate: new Date('2026-09-14') } },
   });

   Since Prisma 4.10, introspection ignores the child partitions and shows only the parent as a model, so migrate dev should not try to drop them. Old versions did, so test once: run migrate dev against a copy and read the SQL it generates before you trust it. Create future months with the job from step 5, not with Prisma migrations.