In a table partitioned by day on an event timestamp, what happens when a row arrives whose timestamp falls outside every existing partition? Describe how teams prevent that, and the tradeoffs of adding a catch-all partition (a DEFAULT partition, or a MAXVALUE range) as the safety net.
answer
- no matching partition = insert error, batch aborts
- premake N periods ahead + runway metric alert
- new partitions need indexes, grants, ANALYZE
- DEFAULT/MAXVALUE = silent dumping ground
- attach overlapping range scans and locks the default
basics
~20 sThe insert fails and aborts the transaction, since there is nowhere to route the row. Teams pre-create future partitions on a schedule with a runway buffer and alert when the runway shrinks. A catch-all partition prevents the error but silently collects rows and must be scanned and emptied before overlapping partitions can be added.
solid answer
~60 sWith no matching partition the engine raises an error such as "no partition of relation found for row" and the whole statement, and typically the whole batch, rolls back. So partition creation is a hard availability dependency, not housekeeping. The standard prevention is pre-creation: a scheduled job that always keeps N future partitions ahead of the current time, plus a monitored runway metric (time until the newest upper bound) with an alert well before it reaches zero. The job must be idempotent and must also create indexes, constraints, grants and storage settings on each new partition. Some engines automate this natively, for example Oracle interval partitioning. A catch-all partition (PostgreSQL DEFAULT, a MySQL RANGE ... VALUES LESS THAN MAXVALUE) trades the loud failure for a silent one: misrouted or late rows pile up in one unpartitioned heap. It also blocks maintenance, because adding a partition whose range overlaps rows in the catch-all requires an exclusive lock and a full scan of it, and fails outright if any row belongs in the new range. Treat it as a tripwire that must always be empty, not as the plan.
code
sql · 8 linesSELECT max(upper_bound) AS newest_bound
FROM (
SELECT (regexp_match(pg_get_expr(c.relpartbound, c.oid),
'TO \(''([^'']+)''\)'))[1]::timestamptz AS upper_bound
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'events'::regclass
) b;go deeper
Know that the insert fails when no partition matches, and that someone must create partitions ahead of time, usually with a scheduled job.
Describe the premake buffer, idempotent creation, the runway alert, and the tradeoff that a catch-all partition converts a loud failure into a silent data-quality problem.
Add the maintenance consequences: the exclusive lock and full scan when attaching a range that overlaps catch-all rows, missing grants and statistics on new partitions, and how to drain a default partition safely.
Decide the policy: fail-fast with a dead-letter path for out-of-range events versus a monitored tripwire partition, and how that choice interacts with late-arriving data, replay, and retention across many tables.
## The failure mode Declarative partitioning routes each inserted row to a partition by evaluating the partition key against the declared bounds. If no bound matches, there is nowhere to put the row, so the engine raises an error: in PostgreSQL, `no partition of relation "events" found for row`. The statement fails, and because inserts are usually batched inside a transaction, the whole batch fails with it. On a table taking live traffic this reads as an outage, not as a maintenance reminder. Partition creation therefore belongs on the availability critical path, with the monitoring that implies. ## Pre-creating future partitions The standard answer is a scheduled job that keeps a buffer of future partitions ahead of wall-clock time, often called premake: - keep enough runway that the job can fail repeatedly and nobody notices: days of buffer for daily partitions, months for monthly; - make the job idempotent (create only what is missing) and run each creation in its own short transaction; - export a runway metric such as "hours until the newest partition's upper bound" and alert on it, rather than only alerting when the creation job errors: a job that silently stops running is the common outage; - test the rollover boundary itself, including daylight-saving transitions and the difference between the session time zone and the bounds you wrote. New partitions are not just empty tables. They need the same indexes, check constraints, storage parameters, privileges and statistics targets as their siblings. Modern PostgreSQL propagates indexes and constraints declared on the partitioned parent automatically, but privileges granted directly on sibling partitions are not inherited, so a template or an explicit grant step is required. Freshly created partitions also have no statistics until autoanalyze or an explicit ANALYZE runs. ## Late and misrouted rows Even with a perfect premake job, rows can miss: back-dated events replayed from a queue after the old partition was already dropped, a client sending seconds instead of milliseconds, a time-zone bug placing a row a day off, or a corrupted key. The design question is what should happen to those rows, and the honest options are "fail the insert and let the producer retry or dead-letter it" or "land them somewhere and reconcile". ## The catch-all partition and its costs A catch-all partition answers the second way: PostgreSQL's DEFAULT partition takes everything unmatched; MySQL's `VALUES LESS THAN MAXVALUE` does the same at the top of a range. It removes the insert error, and that is its only benefit. The costs: 1. **Silence.** Data-quality bugs that would have failed loudly now accumulate invisibly. Rows sit in the wrong place, and only queries that happen to scan the catch-all find them. 2. **Unbounded growth.** The catch-all is a single ordinary heap with no further subdivision, so it inherits none of the retention or pruning benefits. Expiring its contents means row-level deletes. 3. **Blocked maintenance.** This is the one interviewers probe. Creating or attaching a partition whose range overlaps rows currently in the catch-all requires an exclusive lock on the catch-all and a full scan of it to prove that no row belongs in the new range. If even one row does belong there, the operation fails. So a fat default partition turns the routine nightly "create tomorrow" step into a long blocking scan or a hard error. MySQL has the analogous problem: you cannot add a range above MAXVALUE, you must reorganize the partition, which copies the data. 4. **Feature restrictions.** PostgreSQL will not run a concurrent detach on a table that has a default partition, and hash partitioning has no default at all. 5. **Weaker pruning.** The catch-all's implicit constraint is the negation of every other bound, so it can be pruned for values covered by a sibling, but many predicates (open-ended ranges, expressions, NULL checks) leave it in the plan, and by then it may be large. ## The practical policy Either run without a catch-all and rely on premake plus alerting plus a dead-letter path for bad rows, or keep a catch-all strictly as a tripwire: alert the moment its row count is non-zero, and drain it promptly by moving those rows into the correct partitions (an update of the partition key relocates a row in current PostgreSQL) before the next partition creation needs the lock. What you should not do is treat it as a normal home for data.
- Your premake job silently stopped running three weeks ago and nobody noticed until inserts started failing. What monitoring would have caught it earlier?Alert on the state, not on the job. Publish a runway metric such as hours or days between now and the newest partition's upper bound, and page when it drops below a threshold that is several times the job's interval. Job-failure alerts miss the common case where the scheduler itself stopped firing, and they miss partial success where the job created some tables but not others.
- You inherited a table with a large DEFAULT partition and you now need to add proper partitions for the ranges it covers. How do you get out of that state?Move the rows out first, because attaching or creating an overlapping partition takes an exclusive lock on the default and scans it, and fails if any row belongs to the new range. Work in batches: create the target partition ranges that do not overlap existing default rows where possible, relocate rows by updating the partition key or by insert-then-delete inside a transaction, then create the remaining partitions once the default is empty. Do it in a maintenance window and keep an alert on the default's row count afterwards.
saying these in an interview costs you the question
- Believing the row is silently discarded or lands in some nearest partition when no bound matches; the insert errors out.
- Treating a DEFAULT partition as a complete solution, with no alert on its contents.
- Assuming a new partition inherits privileges and statistics automatically, so the application suddenly gets permission errors or bad plans.
- Alerting only on job failure rather than on remaining runway, so a scheduler that stopped firing goes unnoticed.