Partition It Before It Hurts: The Tradeoffs and Security Traps Nobody Tells You
A school keeps its exam scripts in one giant cabinet. Every script since the school opened is in there, in the order it arrived. For years this works. Then one morning the head asks for this term’s scripts, and the clerk spends an hour searching a cabinet that holds thirty years of paper.
Nothing is broken. The clerk is not slow. The cabinet is simply built for keeping everything, not for finding what matters today.
So the school makes two changes. First, it adds drawers, one per year, so the clerk opens only the drawer that matters. Second, it takes the oldest drawers to the store room, where they are safe and cheap to keep, and out of the way of daily work. When a year’s retention period ends, the school removes the whole drawer in one move, instead of shredding it page by page.
That is partitioning and archiving. A database table gets the same treatment, and this post covers how it works in PostgreSQL, SQL Server, Oracle and MongoDB, what it costs, and what it means for security.
In a previous post here, we saw indexes, a cabinet of cards that points at the data. In a previous post here, we learned to read query plans. Partitioning works at a different level: instead of making the search faster, it makes the pile smaller.
Partitioning in plain words
Partitioning splits one large table into smaller pieces, called partitions, using a key such as the order date. To the application it still looks like one table. PostgreSQL supports range, list and hash partitioning [S1]. Range partitions use an inclusive lower bound and an exclusive upper bound, so a partition for October runs from 1 October up to, but not including, 1 November [S1].
The first payoff is partition pruning. When your query filters on the partition key, the database can prove which partitions cannot contain the rows and skip them [S1]. SQL Server calls it partition elimination and says the engine might skip some partitions when the query predicate references the partitioning column [S2]. Oracle gives a clear example: a query for one week of a two-year weekly-partitioned Orders table accesses one partition instead of 104, and Oracle says pruning can often improve query performance by several orders of magnitude [S3]. Notice the wording in each: might, can, often. The speed-up depends on the query filtering on the partition key.
The second payoff is removing old data, and it is the one I think teams underrate. PostgreSQL’s manual says dropping a partition with DROP TABLE or DETACH PARTITION is far faster than a bulk operation, and avoids the VACUUM overhead of a bulk DELETE [S1]. SQL Server lets you switch individual partitions out of a table and into an archive table [S2]. Oracle describes a rolling window, where a new partition is added by an exchange load, which is much more efficient than modifying the entire table [S3].
The four databases, side by side
1. PostgreSQL needs you to create the partitions yourself. Rows that fit no partition cause an error unless you define a default partition [S1]. A monthly setup looks like this:
CREATE TABLE orders (
id bigint,
order_date date NOT NULL,
seller_id bigint,
total numeric,
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2026_10 PARTITION OF orders
FOR VALUES FROM (‘2026–10–01’) TO (‘2026–11–01’);
— retire a month later
ALTER TABLE orders DETACH PARTITION orders_2026_10 CONCURRENTLY;
— back it up or process it, then:
— DROP TABLE orders_2026_10;
Note the primary key includes the partition key. That is not optional, and I come back to it under the tradeoffs. DETACH PARTITION CONCURRENTLY takes only a SHARE UPDATE EXCLUSIVE lock on the parent instead of ACCESS EXCLUSIVE, and a detached partition stays a regular table you can still read, so you can back it up or process it before dropping it [S1].
2. SQL Server builds partitioning from two objects. A partition function sets the boundaries, and a partition scheme maps the partitions to filegroups [S2]. Microsoft’s own example uses RANGE RIGHT, so each boundary date is the lower bound of its partition [S2]:
CREATE PARTITION FUNCTION pf_orders_month (datetime)
AS RANGE RIGHT FOR VALUES (‘2026–09–01’, ‘2026–10–01’, ‘2026–11–01’);
CREATE PARTITION SCHEME ps_orders_month
AS PARTITION pf_orders_month ALL TO ([PRIMARY]);
— later, move the oldest partition out into an archive table
ALTER TABLE dbo.orders SWITCH PARTITION 1 TO dbo.orders_archive;
The engine supports up to 15,000 partitions per table or index, and since SQL Server 2016 SP1 the feature is no longer limited to certain editions [S2]. The SWITCH statement needs an archive table with a matching structure, and I did not verify the full list of requirements, so check Microsoft’s SWITCH documentation before you rely on it.
3. Oracle partitions are transparent to applications: from the application’s perspective, a partitioned table is identical to a nonpartitioned one, with no changes needed to SQL queries or DML [S3]. Range partitioning is the most common type and is often used with dates [S3]. Interval partitioning goes a step further and tells the database to automatically create partitions of a specified interval when data arrives beyond the existing ranges [S3]:
CREATE TABLE orders (
id NUMBER, order_date DATE NOT NULL, seller_id NUMBER, total NUMBER
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH’))
(PARTITION p_start VALUES LESS THAN (DATE ‘2026–01–01’));
Oracle’s recommendations add some pitfalls. You cannot manually add partitions to an interval-partitioned table, and hash partitions are not an effective way to manage historical data, because only equality or IN-list predicates are supported for pruning [S4]. Oracle also notes a licensing detail: in its 11g licensing table, Partitioning is an Enterprise Edition option marked as extra cost [S5]. That page is old, so check the current terms for your version before you plan around it.
Get Rufus Kehinde’s stories in your inbox
Join Medium for free to get updates from this writer.
4. MongoDB is different. It does not split a collection into partitions the way these three do. Splitting data across machines is sharding, which we cover later. For retention, MongoDB gives you the TTL index. A TTL index is a single-field index on a date that removes documents after a set number of seconds [S6]:
// delete documents 90 days (7,776,000 seconds) after createdAt
db.events.createIndex({ createdAt: 1 }, { expireAfterSeconds: 7776000 })
For time series data, a time series collection can remove expired data automatically, and it does so by whole bucket: a bucket goes only when every document in it has expired [S7].
Archiving is not deleting
Archiving means moving rarely used data to somewhere cheaper and safer. In PostgreSQL that is a detached partition you back up and then drop, or move to cheaper storage, which the manual lists as a benefit [S1]. In SQL Server it is the archive table you switch into [S2]. Oracle’s guide describes spreading groups of data across different types of storage and managing them individually [S3]. In MongoDB it is a copy you take out before the TTL removes the original.
The point is the decision. Each piece of old data should have a rule: keep it live, keep it archived, or delete it, and a date for each.
The tradeoff
Partitioning looks like free speed. It is not. Here is what it costs.
- Only queries that filter on the key get faster. Microsoft is direct about this: queries that cannot skip partitions could take longer to execute as the number of partitions increases, TOP, MAX or MIN queries on non-partitioning columns might get slower because every partition must be evaluated, and partitioning rarely improves performance in OLTP systems where such queries are common [S2]. Joins between tables that are not partitioned on the join field can even be slowed by partitions [S2]. Worse, Microsoft says to avoid designs with many hundreds or thousands of partitions unless strictly necessary [S2].
- More partitions means more planning work. PostgreSQL’s planner handles partition hierarchies of up to a few thousand partitions fairly well, as long as pruning leaves only a few; planning time and memory grow with the number of partitions left after pruning [S1].
- It may not be needed at all. PostgreSQL gives a rule of thumb that a table should exceed the server’s physical memory before partitioning pays off [S1]. If your table is smaller, try an index first, as in a previous post here, and check your plan, as in a previous post here.
- Uniqueness gets harder. In PostgreSQL, a primary key or unique constraint on a partitioned table must include all the partition key columns [S1]. A unique order id alone can no longer be enforced by the database across all partitions. If duplicates are expensive, for example, duplicate payments, that guarantee has to come from somewhere else. This is where the idempotency idea from a previous post here earns its keep.
- You must plan the future. PostgreSQL errors on rows with no matching partition unless you define a default one [S1], so someone has to create next month’s partition before the month starts. Oracle’s interval partitioning avoids this, at the cost of the restrictions above [S3] [S4].
- It may cost money. Oracle lists Partitioning as an extra-cost option, at least in its 11g documentation [S5].
- TTL is not a stopwatch. MongoDB’s background task runs every 60 seconds, deletion can start between 0 and 60 seconds after the index is built, and under heavy workloads expired documents may remain for some time [S6]. Each cycle also has limits: up to 50,000 documents or one second of deleting per index [S6].
My rule: partition when most queries filter on one key, when the table is big, and when you want to remove old data cheaply. Do not partition just because the table feels large.
The security angle
Old data is still data, and “we archived it” is not the same as “we protected it”. Here is what I check.
- Keep only what you are allowed to keep. GDPR’s storage limitation principle says personal data should be kept in a form which permits identification for no longer than is necessary for the purposes it is processed for, with longer storage allowed only for archiving in the public interest, research or statistics, subject to appropriate safeguards [S8]. Commentary on Nigeria’s Data Protection Act 2023 summarizes the same principle: the retention period should not be longer than necessary to achieve the lawful purposes [S9]. That source is a KPMG commentary, not the Act itself, so check the statute or ask your counsel before you quote it as law. Either way, the technical lesson is the same: partitioning by date is what makes a retention rule cheap to enforce.
- An archive is a copy that needs a guard. A detached PostgreSQL partition remains a regular table you can still access [S1]. So a forgotten detached table can quietly outlive its retention date. Give every archive an owner, an expiry date and the same protections as live data: encryption at rest, access rules and logging. GDPR also requires processing that ensures appropriate security, including protection against unauthorised processing and accidental loss [S8]. That rule applies to archives too.
- Give the retention job its own limited account. A job that can drop partitions can drop your data. Use a dedicated maintenance role that can only do that one job, and log what it drops. This is engineering judgment, not a quote from a manual.
- Back up and test a restore before you drop. Dropping a partition is quick, and that is the danger. PostgreSQL’s design lets you back up or process the detached table before dropping it [S1]. Use that window, and prove you can restore.
- Do not count on instant deletion. If a rule says data must be gone by a certain moment, remember MongoDB’s TTL does not guarantee immediate deletion [S6] [S7]. Also remember that deleted rows can still exist in backups and on replicas. In a previous post here, we saw that a replica applies changes a little later, so a deletion is not finished until every copy agrees. That is judgment, not a documented guarantee.
- Check what the partition key reveals. If you partition by customer or region, the partition names can leak facts about your business to anyone who can list tables. This one is judgment too.
- Watch the uniqueness gap. A weaker uniqueness guarantee [S1] is also a security matter when it protects payments or one-time tokens. Re-check any rule that used to rely on a single unique column.
Three questions before you partition:
1. Do most of my queries filter on one key, such as a date?
2. How long must each kind of data stay, and who decided?
3. Who can open the store room, and who proves the drawers are really gone?
Decide the retention rule before you choose the partition key.
Sources
S1: PostgreSQL manual, Table Partitioning. https://postgresql.org/docs/current/ddl-partitioning.html
S2: Microsoft Learn, Partitioned tables and indexes. https://learn.microsoft.com/sql/relational-databases/partitions/partitioned-tables-and-indexes
S3: Oracle Database 19c VLDB and Partitioning Guide, Partitioning Concepts. https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-concepts.html
S4: Oracle Database 21c VLDB and Partitioning Guide, Recommendations for Choosing a Partitioning Strategy. https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/recommendations-partition-strategy.html
S5: Oracle Database 11g Release 2 Licensing Information, Feature availability by edition (older page). https://docs.oracle.com/cd/E17116_01/doc/license.112/e10594/editions.htm
S6: MongoDB manual, TTL Indexes. https://mongodb.com/docs/manual/core/index-ttl/
S7: MongoDB manual, Automatic Removal for Time Series Collections. https://mongodb.com/docs/rapid/core/timeseries/timeseries-automatic-removal
S8: GDPR, Article 5, principles relating to processing of personal data (consolidated text). https://gdpr-text.com/read/article-5/
S9: KPMG Nigeria, The Nigeria Data Protection Act, 2023 (commentary). https://assets.kpmg.com/content/dam/kpmg/ng/pdf/the-nigeria-data-protection-act-2023.pdf
