Yes — when a table is partitioned by range on a column such as a date or ID, the database routes each inserted row to the partition whose bounds cover that value, and you never name the partition in the INSERT statement. The routing is automatic only if three conditions hold: a partition exists whose range actually covers the value, the partition key is present in the inserted row, and the storage engine allows the insert path you are using. Break any one of those and the insert fails or, worse, quietly lands in a catch-all partition you forgot you created.

That's the whole answer in one breath. What follows is the part most tutorials skip: what "automatically" really means under the hood, the exact failure messages you'll see, and the edge cases where range routing surprises experienced developers.

What Range Partitioning Actually Does at Insert Time

Range partitioning maps each row to exactly one partition using a comparison function, not a lookup table you maintain by hand. The database evaluates the partition key expression against each partition's boundary definition (in PostgreSQL, FOR VALUES FROM … TO …; in MySQL, VALUES LESS THAN) and picks the partition whose lower bound is inclusive and upper bound is exclusive. So a row dated 2026-03-15 with a monthly partition defined as March 1 to April 1 lands in March. The same row with a partition defined as March 1 to March 15 fails, because the upper bound is exclusive.

The routing cost is small but not zero. PostgreSQL's declarative partitioning uses a binary search over partition bounds, which means insert overhead grows logarithmically with partition count — negligible at 12 monthly partitions, measurable at 5,000. If you're inserting millions of rows per hour, that's worth knowing before you split by day instead of month.

What the database does not do: it does not create missing partitions for you. If your range ends at 2026-12-31 and you insert a 2027-01-01 row, no partition matches and the insert throws an error. Auto-creation exists only as an add-on — PostgreSQL 11+ added DEFAULT partitions precisely as a safety net, and tools like pg_partman handle the scheduling. The core engine is strict.

There's one more subtlety: routing is defined by the partition key expression, not the raw column. If you partition on date_trunc('month', created_at), the planner must evaluate that expression on every insert. That's cheap for date_trunc but expensive for user-defined functions, and some engines refuse to partition on volatile expressions altogether.

The Three Conditions for Automatic Routing

Automatic routing looks like a single feature but is really three independent requirements stacked together. Miss any one and you'll spend a confused hour in the logs.

Condition 1 — a covering partition must exist. No partition whose bounds include the key value means the insert errors with a "no partition of relation found for row" message. The fix is either to create the partition or to add a DEFAULT partition that catches orphans.

Condition 2 — the partition key must be in the row. In PostgreSQL you cannot insert into a partitioned table without supplying the partition key column; the planner needs it to compute the target. If your application does a bulk COPY that omits the key, the whole operation fails, not just the affected rows.

Condition 3 — the insert path must support routing. Direct INSERT and COPY route automatically. Some INSERT ... SELECT, some replication streams, and some ORM-generated statements may not. MySQL's INSERT ... ON DUPLICATE KEY UPDATE and PostgreSQL's INSERT ... ON CONFLICT both route correctly, but triggers that fire on the parent table can behave differently across versions.

Here's the practical test. Run a plain INSERT with an explicit key value inside an existing range. If it succeeds and SELECT tableoid::regclass FROM tbl WHERE id = … shows the child partition, routing is working. If it errors, one of the three conditions above is your culprit — walk them in order.

Step-by-Step: Confirming Your Insert Routed to the Right Partition

Here's the drill to run before you trust the automation on a live table.

1: Pick a key value you know sits inside a specific partition's range, and note the expected child table name. 2: Run INSERT INTO parent (id, created_at, payload) VALUES (…); on the parent table — never the child. 3: Query SELECT tableoid::regclass AS partition, * FROM parent WHERE id = <your_id>; to see which child holds the row. 4: Compare the result with the boundary definition from pg_get_partkeydef or SHOW CREATE TABLE. The partition's upper bound must be strictly greater than your value. 5: Test the failure path: insert a value one day past the last boundary and confirm you get an error, not a silent insert into a DEFAULT partition.

If step 5 silently succeeds, you have a DEFAULT partition — search for it in pg_inherits or information_schema.tables before assuming routing worked. If step 5 errors, your partitioning is behaving by design.

The failure-handling close matters more than it looks. A DEFAULT partition is convenient but dangerous: any row that falls outside your intended ranges lands there, defeating the point of range partitioning and quietly bloating one child table. If you use DEFAULT, schedule a job to check its row count weekly and alarm at any non-zero value.

Boundary Rules: Upper Bounds Are Exclusive, and That Changes Everything

The single most common source of "it went to the wrong partition" bug reports is the exclusivity of the upper bound. Both PostgreSQL's FOR VALUES FROM (a) TO (b) and MySQL's VALUES LESS THAN (b) treat b as excluded. A partition defined for January 2026 as FROM ('2026-01-01') TO ('2026-02-01') holds January 1 at 00:00:00 through January 31 at 23:59:59.999, but not February 1 at 00:00:00.

Why does that matter? Because if you build partitions with a script that reads "start of month" and "start of next month" and you accidentally use the previous month's end date rather than the next month's start, you leave a one-day gap. Rows in that gap error out or hit the DEFAULT partition. I've seen teams lose hours to this because the script "looked correct" in the calendar sense.

The second boundary gotcha is time zones. If the partition key is a timestamptz and your boundaries are written as plain dates, the server interprets the boundaries in its configured time zone. A row inserted with an explicit offset near midnight can land in a different partition than the application developer expected. Store boundaries with explicit +00 offsets or partition on a date column derived by date_trunc in UTC.

A third, subtle rule: overlapping partitions are prohibited in declarative partitioning. If two partition definitions overlap, CREATE TABLE … PARTITION BY RANGE fails outright, and adding an overlapping partition via ATTACH PARTITION requires a full table scan to prove non-overlap — potentially hours on a large table. Plan boundaries up front.

When Range Routing Fails: The Catch-All and Out-of-Range Errors

The two failure modes are loud and quiet. The loud one is a good thing.

Loud failure — no matching partition. PostgreSQL raises ERROR: no partition of relation "…" found for row with a DETAIL line showing the offending value. MySQL raises ERROR 1526: Table has no partition for value …. Both are informative. If you see this, either extend your boundary or add a DEFAULT partition, and never increase the range silently — a hastily written ALTER PARTITION can lock the parent table in some engines.

Quiet failure — the DEFAULT partition. A DEFAULT partition is a catch-all child table that swallows every row that doesn't fit any explicit range. It solves the error message but breaks the design. Range partitioning exists so old data can be dropped by detaching a partition (cheap, instant) rather than by DELETE (slow, transactional, bloats the table). If your out-of-range rows pile up in DEFAULT, you can't drop them without touching live data.

The third failure, and the one that burns experienced teams, is ATTACH PARTITION on a table with mismatched types. If the parent's partition key is date and the child's column is timestamp, the attach succeeds on some engines and fails on others — and in the success case you can get wrong routing at insert time because comparison semantics differ. Always run \d+ parent and confirm the child column types match exactly.

PostgreSQL vs MySQL vs SQL Server: How Each Handles Out-of-Range Inserts

The three major engines agree on the principle (route by range, error on orphan) but diverge sharply on the safety net.

Engine Declarative syntax Out-of-range behavior Catch-all option Notes
PostgreSQL 10+ PARTITION BY RANGE (col) with FOR VALUES FROM … TO … ERROR, insert rejected DEFAULT partition (v11+) \d+ shows bounds; tableoid reveals target
MySQL 5.7+ / 8.x PARTITION BY RANGE (col) with VALUES LESS THAN ERROR 1526 MAXVALUE catch-all supported Partition key in every unique key required in 8.0
SQL Server 2016+ Partition function + partition scheme Row goes to next higher partition or fails No language-level DEFAULT partition; use staging table Partition function defines boundaries, scheme maps to filegroups

PostgreSQL's DEFAULT partition is the most convenient catch-all and the most common footgun. MySQL's MAXVALUE behaves similarly but is defined at table creation, so you decide once and re-decide by REORGANIZE PARTITION, which is expensive. SQL Server expects partition-boundary planning to be done by the DBA and has no DEFAULT clause — out-of-range inserts fail unless the partition function has a boundary above the value.

The version-specific difference worth flagging: PostgreSQL 10 introduced declarative partitioning but had no DEFAULT partition until 11, and required unique-key indexes on the partition key in some configurations through early minor versions. If you're running a version older than 11, out-of-range inserts move straight to ERROR territory with no safety net. Note the source for boundary behavior is the PostgreSQL and MySQL official documentation — I'm not citing a URL, but that's where to verify the current version's exact rules.

Choosing Partition Granularity Without Creating Future Gaps

Granularity is the decision most likely to bite you twelve months later. Monthly partitions are the common default and usually right, but only if the partition management job runs before the month starts.

The calculation is simple. A table that grows 30 GB per month needs roughly 30 GB of headroom in whichever partition is being inserted into at any moment. If you partition daily instead, each child is around 1 GB and the management job must create 365 partitions per year — that's 365 entries in the planner's partition list, and the planner has to consider each one for every query that touches the parent. PostgreSQL handles hundreds fine; thousands starts to affect planning time on some queries.

The rule of thumb: choose the coarsest granularity that lets you detach outdated partitions without touching live data. Most transactional tables want monthly partitions for retention windows under five years; tables that need to drop data within a week want daily. Weekly partitions are legal and commonly used — they split the difference and reduce partition count by four.

Whichever you choose, create the next partition before the boundary falls. If the job runs on the first of the month and the insert pattern hits effectively at 00:00:01, you have a race. Schedule creation a week early and add a DEFAULT partition for the overflow window. When the new partition is created, migrate any DEFAULT rows into it with a single INSERT ... SELECT ... WHERE key < new_upper_bound and DELETE the originals. This is the routine pg_partman automates, and it's the only routine that keeps the design honest over years.

Partition Key Mistakes That Undermine Automatic Routing

Automatic routing only helps if the partition key is the column the queries and writes use. The mistake I've seen most often is partitioning a table on a column that the application doesn't consistently populate, then relying on the DEFAULT partition to silently absorb the misses. That works until someone runs a range query on the partition key and gets rows from DEFAULT mixed in with rows from the intended partition — because the planner has to scan the DEFAULT child to be correct. Range partitioning stops pruning, and your "fast" query becomes a full scan across all partitions.

Two more errors worth flagging. First, partitioning on a column that's frequently updated is expensive because the update either moves the row between partitions (a delete plus insert in some engines) or violates the partition key immutability in others. PostgreSQL rejects updates that change the partition key's routed partition only if the outcome violates constraints — but the cost of the move is real. Second, an application that uses an ORM to insert into the parent with a computed partition key (like date_trunc('day', now())) can hit the planner's expression-evaluation limit if the function is STABLE rather than IMMUTABLE. In PostgreSQL this manifests as an insert that succeeds but lands in the DEFAULT partition. Test the routed partition for every code path that inserts.

If you're running an application that uses two writers — say a batch job and a web service — verify both paths route correctly. Batch jobs that use COPY with a partial column list are a classic source of "the row went to the wrong place" stories. Explicitly including the partition key in the column list, even when the target is a child table, is the safest habit.

Range Routing in Bulk Loads and INSERT ... SELECT

Bulk operations are where automatic routing earns its keep and where it fails most visibly. A COPY into a partitioned table in PostgreSQL routes every row, and a million-row load into a monthly partitioned table typically runs at 80–90 percent of the equivalent load into a single unpartitioned table — the overhead is small because every row goes through the same code path. That figure comes from reading execution plans, not a published benchmark, and the exact number depends on partition count and engine version; treat it as directional and re-measure on your own hardware.

INSERT ... SELECT is trickier. PostgreSQL routes correctly but the planner may choose to scan the source table in one pass and route rows internally. MySQL's INSERT ... SELECT across partitions can be slower than a LOAD DATA for large loads because each row goes through the partition-selection function individually. If you're loading millions of rows, prefer the engine's bulk-load utility.

One failure mode I've seen in production: a COPY that fails partway through because one row was out of range. The request rolls back, but the transaction log volume and lock duration can be substantial on a large load. Pre-validate the source data with a query that checks against the partition bounds (SELECT COUNT(*) FROM staging WHERE key NOT BETWEEN min AND max) before the COPY. Catching this in one scan is cheaper than rolling back a million-row load after row 800,000.

FAQ

Does partitioning slow down every insert because of the routing lookup?

Routing overhead is logarithmic in partition count, not linear, so it's far smaller than most people fear. PostgreSQL uses a binary search over partition bounds; a table with 12 monthly partitions adds microseconds per insert. If you ever see a noticeable slowdown, the cause is usually a partition key expression that isn't IMMUTABLE or a large number of partitions (thousands), not the routing itself.

Why did my insert succeed but the row went into the wrong partition?

Two likely causes. First, the upper bound is exclusive, so a row dated exactly at the boundary lands in the next partition or in DEFAULT. Second, an existing DEFAULT partition swallowed the row. Run SELECT tableoid::regclass against the inserted row to see the actual child, then compare against pg_get_partkeydef to confirm which boundary was expected.

Can I partition a table by a column that isn't its primary key?

PostgreSQL allows it, but any unique constraint (including the primary key) must include the partition key. MySQL 8.0 enforces the same rule. In practice, this means a table partitioned by created_at cannot have a primary key on id alone if you want to keep uniqueness across partitions — the key must be (id, created_at). Plan this in the schema, because changing it later requires rebuilding the primary key on every child partition.

What happens if two partitions overlap when I attach a new one?

CREATE TABLE … PARTITION BY RANGE refuses to create overlapping partitions. ATTACH PARTITION performs a full table scan on the incoming table to verify no overlap and can hold an exclusive lock on the parent for the duration — hours on very large tables. Run attach operations during maintenance windows and validate with check_partition_overlap or an equivalent query beforehand.

Does SQL Server have a DEFAULT partition like PostgreSQL does?

No. SQL Server's partition function defines boundaries, and any key value beyond the highest boundary either maps to the next partition or fails the insert, depending on the scheme. There's no language-level catch-all child. The closest approach is a staging table that receives out-of-range rows and then a merge step, which is essentially what a DEFAULT partition does but requires application code.

How many partitions before the automatic routing becomes a problem?

PostgreSQL's planner handles hundreds of partitions without visible impact; thousands starts to show up in query planning time, and tens of thousands are a design smell. The routing itself stays cheap. If you're considering daily partitions on a decade of data (3,650 children), consolidate to weekly or monthly and use B-tree indexes inside the child tables to keep old-data queries fast.

Is it safe to update the partition key column on an existing row?

Updates that change the partition key cause the row to move between partitions. On engines that implement partitioning declaratively, this is handled transparently but is more expensive than a non-key update because it becomes a delete plus insert on the physical level. If your application does bulk updates of the partition key, stagger them and monitor lock contention; if it does so routinely, reconsider your partition key.

Can I route inserts to a specific partition on purpose?

Yes, by inserting directly into the child table (INSERT INTO parent_2026_03 …) instead of the parent. This bypasses the routing lookup and is faster in bulk loads, but it forfeits the "you don't have to name the partition" benefit and creates a maintenance burden if boundaries change. Use it for one-off backfills, never for routine application writes.

Partitioning by range is one of the few database features where the documentation and the real-world experience diverge the most, because the day-to-day success is defined by what happens at 23:59:59.999 on a boundary date. Design the boundaries conservatively, test the out-of-range path, and keep the partition-creation job running ahead of the calendar.