Choosing Daily vs Monthly Range Partitions

Granularity is usually chosen once, early, from intuition — and then inherited for years by a table that has grown tenfold. This guide derives it from four measurements instead, and shows how to change it later without moving a single historical row. It applies the mechanics of Range Partitioning Strategies within Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Measure volume per candidate interval

SELECT date_trunc('day', occurred_at)::date            AS day,
       count(*)                                        AS rows,
       pg_size_pretty(sum(pg_column_size(t.*))::bigint) AS approx_bytes
FROM   events t
WHERE  occurred_at >= now() - interval '30 days'
GROUP  BY 1 ORDER BY 1 DESC LIMIT 7;
    day     |   rows   | approx_bytes
------------+----------+--------------
 2026-08-02 |  1418204 | 1284 MB
 2026-08-01 |  1390118 | 1259 MB
 2026-07-31 |  1502991 | 1361 MB

At roughly 1.4 million rows and 1.3 GB a day, a monthly partition is about 42 million rows and 39 GB — large for a worst-case scan but entirely normal for a pruned one. A daily partition is comfortably sized and produces 365 children a year.

Operational note: pg_column_size sums the logical row size and excludes index and page overhead. Multiply by roughly 1.4 for a realistic on-disk figure including indexes.

DBA tip: Look at the variance, not just the mean. A table with a 6× seasonal peak needs its granularity chosen for the peak month, or use mixed bounds as in step 4.

The same table under three granularities For a table ingesting 1.4 million rows a day with 25 months of retention: daily partitions give 760 children, 1.3 gigabytes each, day-resolution retention and a planning cost that has become significant. Weekly gives 109 children of 9 gigabytes. Monthly gives 25 children of 39 gigabytes, negligible planning cost, and retention that can only be expressed in whole months. Criterion Daily Weekly Monthly children over 25 months size per partition worst-case unpruned scan retention resolution planning time, pruned query index build / vacuum unit 76010925 1.3 GB9 GB39 GB 1.3 GB9 GB39 GB 1 day1 week1 month 3.1 ms0.9 ms0.4 ms minutes~20 min~90 min No column wins outright. Weekly is the compromise most of these numbers point at, and it is the option teams rarely consider because the choice is usually framed as daily versus monthly.

Step 2 — Check the distribution of query windows

Granularity finer than your smallest common query window buys nothing:

-- approximate: how wide are the time ranges queries ask for?
SELECT query, calls
FROM   pg_stat_statements
WHERE  query ~* 'occurred_at\s*>=' AND query ILIKE '%from events%'
ORDER  BY calls DESC LIMIT 5;

Instrument the application if the SQL does not reveal the window — most ORMs bind the bounds as parameters, so the widths are only visible from the caller.

Operational note: Dashboards usually ask for “last 24 hours” and “last 30 days”. The first is served well by daily partitions; the second reads the same bytes under either granularity.

DBA tip: If the dominant window is “since the start of the month”, monthly bounds align exactly with it and every such query prunes to one child — an alignment worth more than a finer granularity.

Step 3 — Size for the worst case, not the average

The number that matters is what an unpruned query costs, because that query will eventually be written:

Granularity sets the worst-case scan, not the average one A daily partition of 1.3 gigabytes scans in about half a second. A weekly partition of nine gigabytes takes three seconds. A monthly partition of thirty-nine gigabytes takes eight seconds. The average query is unaffected by any of this because it prunes; the number matters only for the queries that do not, which is why it is a worst-case budget rather than a performance target. daily · 1.3 GBweekly · 9 GBmonthly · 39 GB 0.5 s3.1 s8.2 s — acceptable in a report, not on a request path Pick the granularity whose worst case you are willing to absorb when a query eventually fails to prune, because one eventually will. That single number is a better guide than any rule of thumb about partition counts. It is also the index-build and vacuum unit, so it sets how long routine maintenance holds a lock on one partition. 
-- how long does a full scan of one partition take?
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM events_2026_08 WHERE payload->>'kind' = 'checkout';
 Aggregate (actual time=8214.118..8214.119 rows=1 loops=1)
   Buffers: shared hit=2104 read=4988112
   ->  Seq Scan on events_2026_08 (actual time=0.021..7902.441 rows=41208114 loops=1)

Eight seconds for a monthly partition; under a second for a daily one. If unpruned queries are rare and asynchronous, eight seconds is acceptable. If they arrive on a request path, it is not, and that alone justifies finer bounds.

Operational note: This is also the index-build and vacuum unit. A 39 GB partition takes about ninety minutes to reindex; a 1.3 GB partition takes a few minutes, and can be done during the day.

SRE tip: Consider the restore case too. Restoring one month from an archive is one object of ~12 GB compressed; restoring one day is 400 MB. Support requests are usually day-shaped.

Step 4 — Change granularity without migrating history

Bounds are per child, so the transition costs nothing:

-- last monthly partition ends 2026-09-01; daily bounds begin exactly there
CREATE TABLE events_2026_09_01 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-09-02');
CREATE TABLE events_2026_09_02 PARTITION OF events
  FOR VALUES FROM ('2026-09-02') TO ('2026-09-03');
-- confirm no gap and no overlap across the boundary
SELECT c.relname, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM   pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE  i.inhparent = 'events'::regclass
ORDER  BY c.relname DESC LIMIT 4;
Mixed granularity: monthly history, daily future Partitions before the transition date remain monthly and are never rewritten. Partitions after it are daily. Pruning works across the mixed set because it reads each child's declared bounds rather than assuming a uniform interval. After one retention window the monthly partitions have aged out and the table is uniformly daily, with no migration having taken place. 2026_062026_072026_08 09-0109-0209-03 09-0409-05… transition monthly — untouched daily — created going forward Nothing is rewritten and no lock is taken beyond ordinary partition creation. Queries spanning the boundary prune correctly because the planner compares each child's own bounds — uniform intervals were never a requirement. The only rule: the first daily bound must equal the last monthly bound exactly. A gap sends rows to DEFAULT; an overlap is rejected at creation time, which is the safer of the two mistakes.

Operational note: Update the maintenance job at the same time. A job that still creates monthly partitions will eventually create one that overlaps the daily ones and fail — loudly, which is fine, but at an inconvenient hour.

SRE tip: Do the same in reverse when coarsening. Daily history plus monthly future is equally valid and is the usual response to a partition count that has grown too large.

Verification

Confirm pruning still works across the mixed boundary:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE  occurred_at >= '2026-08-28' AND occurred_at < '2026-09-03';
 Aggregate
   ->  Append
         ->  Seq Scan on events_2026_08 events_1
         ->  Seq Scan on events_2026_09_01 events_2
         ->  Seq Scan on events_2026_09_02 events_3

Exactly the three children that overlap the range, and nothing else. Then check that the retention job still selects the right partitions when both shapes are present.

Failure mode table

Failure mode Root cause SRE mitigation
Rows land in DEFAULT after a granularity change the first new bound did not exactly meet the last old one, leaving a gap assert bound continuity in the maintenance job; alert on DEFAULT row count
Planning time rises after switching to daily the partition count grew by an order of magnitude for a workload that never queries single days measure query window distribution before changing; consider weekly as the middle option
Retention now deletes too much or too little the policy was expressed in months and the partitions are now daily, or the reverse express retention as an interval and derive the cutoff from bounds, not from partition names

FAQ

What partition size should I aim for?

Small enough that a full scan of one partition is an acceptable worst case, and large enough that the partition count stays bounded over the retention window. A few gigabytes or a few million rows per partition suits most workloads, because an unpruned scan of that size degrades a query rather than stalling the database.

Does finer granularity make queries faster?

Only for queries whose windows are smaller than the coarser granularity. A one-day query against monthly partitions reads a month and filters; against daily partitions it reads a day. If your smallest common window is a month, daily partitions add planning cost and buy nothing.

Can I change granularity later?

Yes, and without migrating history. Partition bounds are declared per child, so a table can hold monthly partitions for its past and daily ones going forward as long as the bounds abut exactly. Old partitions age out on their own, and after one retention window the table is uniformly on the new granularity.

Back to Range Partitioning Strategies