06. Database Partitioning

Introduction to Database Partitioning

Partitioning is a database technique that splits a large table into multiple smaller tables, allowing the database to automatically determine which partition to query based on the WHERE clause conditions. This elegant design addresses fundamental scalability challenges in managing massive datasets.

What is Partitioning?

Consider a customer table containing one million rows—a relatively modest size by modern standards. To retrieve a customer’s name with ID 700,001, you execute SELECT name FROM customers WHERE id = 700001. The database has two options: if an index exists on the ID column, it traverses the index to locate the row on disk; without an index, it performs a sequential scan, examining every row until finding the target.

Scanning one million rows is expensive, and the cost scales dramatically with table size. Tables with ten million, one hundred million, or one billion rows become progressively slower to query, even with indexes. Large tables inherently suffer from query performance degradation regardless of optimization efforts.

The Partitioning Solution

Partitioning addresses this by dividing the table into smaller, manageable segments. Instead of working with the entire dataset, the database operates on a subset of rows, dramatically reducing the search space.

For the one million row customer table, partitioning might divide it into five segments: customers_200k (IDs 1-200,000), customers_400k (IDs 200,001-400,000), customers_600k (IDs 400,001-600,000), customers_800k (IDs 600,001-800,000), and customers_1m (IDs 800,001-1,000,000). Each partition is physically a separate table, but the database presents them through a unified parent table called “customers” that contains no data itself—it serves only as an abstraction layer.

When executing SELECT name FROM customers WHERE id = 700001, the database first determines which partition contains the target row. This decision happens rapidly because partition metadata defines the ID ranges for each segment. The ID 700,001 falls between 600,001 and 800,000, so the query targets only the customers_800k partition.

The query now operates on 200,000 rows instead of one million—an 80% reduction in search space. This principle scales: the fastest way to query a table with one billion rows is to avoid querying a table with one billion rows. The database minimizes the dataset examined rather than optimizing the search through the full dataset.

If an index exists on the partitioned table, the benefit compounds. The index within the customers_800k partition is smaller and faster to traverse than an index spanning the entire million-row table. The combination of partition pruning and index access makes retrieval extremely efficient, quickly locating customer 700,001 (named “Kim” in this example).

Partitioning transforms large table queries into small table queries, fundamentally changing the performance characteristics of data access operations.

Vertical vs Horizontal Partitioning

Partitioning strategies divide into two fundamental approaches: horizontal and vertical, each addressing different performance and storage optimization needs.

Horizontal Partitioning

Horizontal partitioning splits tables by rows, dividing the dataset into segments where each partition contains a subset of the total rows. This is conceptually like slicing a table horizontally with a knife, separating it into multiple pieces where each piece holds different rows but maintains the same column structure.

The example with customer IDs demonstrates this clearly: customers with IDs 1-200,000 reside in one partition, IDs 200,001-400,000 in another, and so forth. Each partition contains complete customer records, but only for a specific subset of customers.

Horizontal partitioning supports multiple strategies for dividing data:

• Range partitioning divides based on value ranges, such as grouping customers by ID ranges or orders by date ranges.
• List partitioning assigns specific discrete values to partitions, such as placing all customers with zip code 90210 in one partition and zip code 91111 in another.

These strategies allow the database to quickly determine which partition contains the data for any given query, enabling efficient partition pruning.

Vertical Partitioning

Vertical partitioning splits tables by columns rather than rows, dividing the schema so that different subsets of columns reside in separate physical structures. This is conceptually like slicing a table vertically from top to bottom, separating columns while keeping all rows intact in each partition.

This approach is particularly valuable when certain columns have distinctly different access patterns or storage characteristics from others. Consider a customer table where most columns (name, email, address, phone) are frequently accessed and relatively small, but one column contains a large binary blob—perhaps a profile image or document attachment. This blob column is rarely queried and consumes substantial disk space.

With vertical partitioning, the frequently accessed columns remain in the primary table on fast SSD storage, keeping the table compact and query-efficient. The blob column moves to a separate partition, potentially stored on cheaper, slower storage in a different tablespace. The database maintains the logical connection between these partitions, so they still appear as a single table to applications.

When a query requests only name, email, and address, the database accesses only the primary partition, benefiting from its compact size and fast storage. Pages load quickly into memory, cache efficiency improves, and I/O operations decrease because the database isn’t reading large unused blob data alongside the small fields.

When a query does request the blob column, the database recognizes that this column resides in a separate partition and performs an additional access to retrieve it. This trade-off is acceptable because blob access is infrequent, and separating it has already improved performance for the common case.

Vertical partitioning optimizes storage costs and access speed simultaneously: frequently accessed columns consume premium fast storage while remaining compact, and infrequently accessed large columns use appropriate storage without degrading common query performance.

Partitioning Types

Partitioning strategies vary based on how data is distributed across partitions. Each type serves different use cases and query patterns.

Range Partitioning

Range partitioning divides data based on continuous value ranges. The customer ID example demonstrated this: IDs 1-200,000 in one partition, 200,001-400,000 in another, and so forth.

Time-based range partitioning is particularly common for log data and IoT measurements. Consider an IoT device generating continuous measurements with timestamps. The data can be partitioned by year (2012 data, 2013 data, 2020 data), by quarter, or by month. Any measurement with a timestamp falling within a specific date range lands in the corresponding partition.

This approach offers a powerful data lifecycle management benefit: old partitions naturally age out of active use. Nobody queries 1995 data anymore, so that partition can be migrated to slower, cheaper archival storage—perhaps even tape-based systems where access speed is irrelevant. The recent partitions containing actively queried data remain on fast SSD storage, optimizing both cost and performance.

List Partitioning

List partitioning assigns data based on discrete, enumerated values rather than continuous ranges. This is ideal for categorical data where specific values determine partition placement.

Geographic partitioning exemplifies this approach. All customers in California route to one partition, all Alabama customers to another. This isn’t a range—it’s a specific categorical value. The same principle applies to zip codes or countries: each distinct value maps to a designated partition.

The distinction from range partitioning is subtle but important: California and Alabama have no numeric relationship or ordering. They’re discrete categories, and the partitioning scheme explicitly maps each category to its destination.

Hash Partitioning

Hash partitioning applies a hash function to determine partition placement, distributing data based on the computed hash value. The partition key is hashed, and the resulting hash determines which partition receives the data.

Cassandra extensively uses hash partitioning in its architecture. The hash function spreads values uniformly across partitions, preventing hotspots where one partition becomes disproportionately larger or more heavily accessed than others.

This technique appears in other distributed system contexts as well. Proxy servers often use IP hash to select which backend server handles a request—the client IP is hashed, and the hash determines the target backend. This ensures that requests from the same client consistently route to the same backend, which can be valuable for session affinity or caching strategies.

The Difference Between Partitioning and Sharding

Partitioning and sharding both divide large tables into smaller pieces, but they differ fundamentally in scope and complexity. Understanding this distinction is crucial for making informed architectural decisions.

Horizontal Partitioning

Horizontal partitioning splits a large table into multiple smaller tables within the same database instance. The database manages partition routing transparently—clients remain completely unaware of which partition their queries access. When executing SELECT name FROM customers WHERE id = 700001, the client sends the query to the database, and the database analyzes the WHERE clause to determine which partition contains the target data.

This transparency is the defining characteristic: the client is agnostic to partitioning. The database handles all complexity internally, examining query predicates and routing to the appropriate partition without any client-side logic or configuration.

Table names do change at the physical level—partitions like customers_200k, customers_400k exist as distinct tables—but these names are implementation details hidden from the client. The client queries the logical “customers” table, and the database translates this to the appropriate physical partition.

Sharding

Sharding also splits large tables into multiple smaller tables, but distributes them across completely separate database servers. This distribution enables geographic optimization and load distribution, but introduces significant architectural complexity.

Consider a geographic sharding strategy: California customers reside in a database server on the West Coast, while Asian customers reside in a server in Asia. When a California user makes a request, a proxy layer (like ProxySQL) detects the user’s location—perhaps from their IP address—and routes the connection to the California database shard. Asian users connect to the Asian shard. Each shard has a different IP address and operates as an independent database instance.

The critical difference is client awareness. Unlike partitioning where the database handles everything transparently, sharding requires some component in the system—whether the application code, a proxy layer, or a routing service—to understand which shard contains the requested data. The client (or middleware acting on its behalf) must decide which database server to connect to before executing the query.

This client awareness is one of the primary drawbacks of sharding. Technologies like Vitess attempt to abstract this complexity and restore transparency, but implementing sharded architectures still represents a substantial engineering investment requiring careful planning and operational overhead.

Table Names and Query Structure

An interesting technical detail distinguishes the two approaches. In horizontal partitioning, physical table names differ from the logical table name that clients use. The partition tables have distinct names like customers_200k, but clients query “customers.”

In sharding, table names typically remain identical across all shards. Each shard contains a table named “customers” with the same schema. The query SELECT name FROM customers WHERE id = 700001 executes identically regardless of which shard receives it. What changes is not the query structure or table name, but the database server IP address against which the query executes.

This consistency simplifies application code—queries don’t need shard-specific modifications—but the routing logic must still determine which server to contact before sending the query.

When to Use Each Approach

Horizontal partitioning is appropriate when a single database server can handle the workload but table size impacts query performance. The database remains a single logical unit with transparent partition management.

Sharding becomes necessary when a single database server cannot handle the load—whether due to storage constraints, CPU limitations, or geographic distribution requirements. The trade-off is accepting the complexity of distributed database management, cross-shard transaction limitations, and client-aware routing in exchange for horizontal scalability and geographic optimization.

Preparing: Postgres, Database, Table, Indexes

This demonstration uses Docker to spin up a PostgreSQL instance, populate it with 10 million rows, create table partitions, attach them to a master table, and execute queries with explain plans to observe partition behavior.

Setting Up PostgreSQL with Docker

Docker simplifies this process by eliminating the need to install PostgreSQL directly on the host machine. The setup requires only Docker itself.

The PostgreSQL container doesn’t need port exposure since all SQL commands will execute inside the container via bash access. The Docker command specifies a container name (pg_main), runs in detached mode to free the terminal, and sets the required POSTGRES_PASSWORD environment variable:

docker run --name pgmain -d -e POSTGRES_PASSWORD=postgres postgres

This pulls the PostgreSQL image and starts the container. Running docker ps confirms the container is active. To access PostgreSQL, execute an interactive bash session inside the container and connect using psql:

docker exec -it pgmain bash
psql -U postgres

Since the user is the owner, authentication succeeds immediately, providing direct access to the PostgreSQL command line.

Creating the Grades Table

The demonstration uses a simple table called grades_original containing student grades. The schema includes only two columns: an auto-incrementing ID and a grade value.

CREATE TABLE grades_original (
    id SERIAL NOT NULL,
    g INTEGER NOT NULL
);

The NOT NULL constraint is mandatory for both columns. Partitioning requires that columns used as partition keys cannot contain null values—this is a fundamental requirement for proper partition routing.

The SERIAL type for the ID column automatically generates sequential integers (1, 2, 3, …) for each inserted row without requiring explicit values.

Inserting 10 Million Rows

Populating the table with 10 million rows uses PostgreSQL’s generate_series() function combined with a random number generator for grade values. The index is intentionally created after data insertion—building an index on an empty table and then maintaining it during 10 million inserts is slower than bulk inserting first and building the index once afterward.

The grade values are generated using floor(random() * 100), which produces integers from 0 to 99. The random() function returns decimal values between 0 and 0.99, multiplying by 100 gives 0 to 99.99, and floor() truncates to integers.

Here’s the full command:

INSERT INTO grades_original (g)
SELECT floor(random() * 100)
FROM generate_series(1, 10000000);

Note the explicit column specification (g) in the INSERT statement. The ID column is omitted because SERIAL auto-generates values, but the grade column must be explicitly specified so the generated random values map correctly.

The generate_series(1, 10000000) function executes 10 million times, generating one row per invocation. This approach efficiently creates large datasets for testing.

Creating an Index on Grades

After inserting 10 million rows, an index is created on the grade column to enable efficient queries:

CREATE INDEX grades_original_index ON grades_org(g);

This B-tree index organizes grade values for rapid lookup, allowing queries like WHERE g = 85 to use index seeks rather than sequential scans. Building the index after bulk insertion is significantly faster than maintaining it incrementally during each of 10 million inserts.

Describing the table with \d grades_original confirms the structure: an ID column, a grade column (g), and the newly created index:

The environment is now prepared with a 10-million-row table ready for partitioning experiments and query performance analysis.

Execute Multiple Queries on the Table

With 10 million rows inserted and an index on the grade column, query performance can now be examined using both simple queries and PostgreSQL’s EXPLAIN ANALYZE feature.

Single Value Query

A straightforward query counts how many students received a grade of 30:

SELECT COUNT(*) FROM grades_original WHERE g = 30;

This returns approximately 100,000 rows—roughly 1% of the 10 million total, which aligns with the random distribution where each grade value from 0-99 appears with equal probability.

To understand how PostgreSQL executes this query, EXPLAIN ANALYZE provides detailed execution metrics:

EXPLAIN ANALYZE SELECT COUNT(*) FROM grades_original WHERE g = 30;

The execution takes approximately 2,000 milliseconds (2 seconds), with 0.097 milliseconds spent on query planning. The database chooses a bitmap index scan strategy, which means it consults the index to identify all row IDs where grade equals 30, collects these IDs into a bitmap structure, and then accesses the table heap to retrieve the actual rows.

Range Query

A range query introduces additional complexity by searching for grades between 30 and 35:

SELECT COUNT(*) FROM grades_original WHERE g BETWEEN 30 AND 35;

This query targets approximately 6% of the table (six distinct grade values out of roughly 100 possible values, each representing about 1% of rows).

EXPLAIN ANALYZE SELECT COUNT(*) FROM grades_original WHERE g BETWEEN 30 AND 35;

Execution time increases to approximately 3 seconds, reflecting the larger result set. Interestingly, PostgreSQL switches execution strategies—this time using a parallel index scan rather than a bitmap index scan.

Create and Attach Partitioned Tables

Implementing partitioning requires creating a master table with partition configuration, creating individual partition tables, and attaching them to the master table.

Creating the Master Partition Table

The master table is created with the same column structure as the original table, but includes a partition specification:

CREATE TABLE grades_parts (
    id SERIAL NOT NULL,
    g INTEGER NOT NULL
) PARTITION BY RANGE (g);

The critical addition is PARTITION BY RANGE (g), which designates this as a partitioned table where the grade column (g) serves as the partition key. This declaration doesn’t define the actual partitions—it only establishes that the table will be partitioned by range on column g. The database doesn’t automatically create partitions; that responsibility falls to the database administrator.

Creating Individual Partition Tables

Each partition is created as a separate table matching the master table’s structure. The first partition handles grades from 0 to 35:

CREATE TABLE g_0_35 (LIKE grades_parts INCLUDING INDEXES);

The LIKE grades_parts INCLUDING INDEXES clause copies the exact structure from the master table. However, since the master table has no indexes yet, this clause has no effect in this case—it’s included for completeness.

Describing this table confirms it contains columns id and g with no indexes:

The remaining partitions are created following the same pattern:

CREATE TABLE g_35_60 (LIKE grades_parts INCLUDING INDEXES);
CREATE TABLE g_60_80 (LIKE grades_parts INCLUDING INDEXES);
CREATE TABLE g_80_100 (LIKE grades_parts INCLUDING INDEXES);

This creates four partitions covering the entire grade range: 0-35, 35-60, 60-80, and 80-100.

Attaching Partitions to the Master Table

Creating the partition tables isn’t sufficient—they must be explicitly attached to the master table with their range definitions:

ALTER TABLE grades_parts ATTACH PARTITION g_0_35 FOR VALUES FROM (0) TO (35);

This command links the g_0_35 table to grades_parts and specifies that this partition handles grades from 0 to 35. The same process repeats for the remaining partitions:

ALTER TABLE grades_parts ATTACH PARTITION g_35_60 FOR VALUES FROM (35) TO (60);
ALTER TABLE grades_parts ATTACH PARTITION g_60_80 FOR VALUES FROM (60) TO (80);
ALTER TABLE grades_parts ATTACH PARTITION g_80_100 FOR VALUES FROM (80) TO (100);

After attachment, describing any partition table shows its relationship to the master table. For example, \d g_80_100 displays “partition of grades_parts for values from 80 to 100.”

Current State

All partitions are now attached, but the master table remains empty. No data has been inserted yet. Additionally, neither the master table nor any partition tables have indexes on the grade column. The structure exists, but both data and indexes still need to be added.

Populating the Partitions and Creating Indexes

With the partition structure in place, the next steps are migrating data from the original table into the partitioned table and creating indexes.

Inserting Data into the Partitioned Table

Data is copied from the original table using a simple INSERT SELECT statement:

INSERT INTO grades_parts SELECT * FROM grades_original;

This operation inserts 10 million rows one by one, and the database automatically routes each row to the appropriate partition based on the grade value. If a row has grade 20, the database recognizes that 20 falls within the range 0-35 and inserts it into the g_0_35 partition. If a row has grade 75, it routes to g_60_80. The partitioning logic executes transparently during insertion.

The operation completes relatively quickly, transferring all 10 million rows into the partitioned table structure.

Verifying Data Distribution

Querying the master table confirms the row count:

SELECT COUNT(*) FROM grades_parts;

This returns 10,000,001 rows (the extra row is incidental). Querying for maximum values works identically:

SELECT MAX(g) FROM grades_parts;

This returns 99 as the maximum grade, as expected.

The partitions themselves are now populated. Querying individual partition tables reveals the data distribution:

SELECT COUNT(*) FROM g_0_35;

This partition contains approximately 3 million rows—roughly 35% of the data, which corresponds to grades 0-34 out of the 0-99 range.

SELECT COUNT(*) FROM g_35_60;

This partition contains approximately 2 million rows, representing grades 35-59.

The distribution reflects the random grade generation—each partition receives rows proportional to its grade range.

Checking the maximum grade in a partition confirms proper routing:

SELECT MAX(g) FROM g_35_60;

This returns 59, confirming that no grade at or above 60 entered this partition. The database correctly enforced the partition boundaries during insertion.

Creating Indexes on Partitioned Tables

Initially, neither the master table nor any partition has indexes on the grade column. Creating an index requires only a single command on the master table:

CREATE INDEX grades_parts_index ON grades_parts(g);

In PostgreSQL 11 and later, creating an index on the master partitioned table automatically creates corresponding indexes on all attached partitions. Prior to version 11, indexes had to be created manually on each partition.

The master table itself shows the index when described with \d grades_parts, but the master table is empty—it’s a virtual abstraction layer. The actual indexes exist on the partition tables.

Examining a partition confirms automatic index creation:

\d g_0_35

This displays an index on column g that wasn’t explicitly created—the database generated it automatically when the index was created on the master table.

The same applies to all other partitions:

\d g_35_60

Every partition has its own index on the grade column, all created from the single command on the master table.

Class Project - Querying and Checking the Size of Partitions

With data populated and indexes created, query execution behavior and storage characteristics can now be examined.

Query Execution Against Partitioned Tables

Executing a query against the partitioned table demonstrates partition pruning:

EXPLAIN ANALYZE SELECT COUNT(*) FROM grades_parts WHERE g = 30;

The execution plan reveals that only the first partition (g_0_35) is accessed. The explain output shows an index scan on g_0_35_index, confirming the database correctly identified that grade 30 falls within the 0-35 range and queried only that partition.

The query execution time is approximately one second, similar to querying the original non-partitioned table. This might seem surprising—partitioning was supposed to improve performance. However, the similarity in execution time relates to the hardware environment: the system has 16GB of RAM, and the Docker container has no memory limit. The entire index fits comfortably in memory, eliminating I/O bottlenecks.

If the container were limited to 500MB of RAM and the index exceeded available memory, the performance difference would become apparent. Querying a massive index that requires disk I/O is substantially slower than querying a smaller partition-specific index that fits entirely in memory. In this demonstration, both indexes reside in memory, so no performance difference manifests.

Comparing Index Sizes

Examining the physical size of tables and indexes reveals the storage benefits of partitioning. PostgreSQL’s pg_relation_size() function returns the size in bytes for any database object:

SELECT pg_relation_size(oid), relname 
FROM pg_class 
ORDER BY pg_relation_size(oid) DESC;

The results show the original grades_original table consumes approximately 362MB. The original index on the grade column occupies about 69MB.

The individual partition tables are substantially smaller. The g_0_35 partition (the largest because it covers 35 grade values) is approximately 126MB. The individual partition indexes are correspondingly smaller—the g_0_35 index is only 24MB, roughly three times smaller than the 69MB monolithic index.

Querying a 24MB index is inherently faster than querying a 69MB index, particularly when memory constraints force disk access. The size difference becomes more pronounced with larger datasets—scaling from 10 million to 100 million or 500 million rows would magnify these differences proportionally.

The Critical Importance of Partition Pruning

PostgreSQL has a configuration parameter called enable_partition_pruning that controls whether the optimizer eliminates irrelevant partitions from query execution plans. This setting must be enabled for partitioning to provide any benefit.

Checking the current value:

SHOW enable_partition_pruning;

This should return “on” by default. To demonstrate the impact of disabling it:

SET enable_partition_pruning = off;

Now executing the same query:

EXPLAIN SELECT COUNT(*) FROM grades_parts WHERE g = 30;

The execution plan shows all four partitions are scanned: g_0_35, g_35_60, g_60_80, and g_80_100. Even though the database knows grade 30 can only exist in the first partition, it scans all partitions anyway. This completely negates the value of partitioning—the query examines all four indexes and all four partition tables, making it slower than querying the original non-partitioned table.

Re-enabling partition pruning:

SET enable_partition_pruning = on;

Executing the query again shows the correct behavior—only the g_0_35 partition is accessed.

Partition pruning must remain enabled for partitioning to function correctly. Without it, partitioned tables become strictly worse than monolithic tables due to the overhead of managing multiple physical structures with no benefit of reducing the search space.

The Advantages of Partitioning

Partitioning offers significant benefits, though understanding these requires recognizing the conditions under which they manifest. Nothing in backend engineering is perfect—every technique represents trade-offs that must be carefully evaluated.

Improved Query Performance

Partitioning improves query performance when working with smaller partitions compared to querying massive single tables. The demonstration didn’t show dramatic performance differences because the environment wasn’t constrained—a laptop with ample RAM running a Docker container with no memory limits meant the entire dataset and all indexes fit comfortably in memory, eliminating I/O bottlenecks.

Performance gains become apparent when systems are memory-bound or I/O-bound. With billions of rows and limited memory, queries against non-partitioned tables must fetch data from disk repeatedly. Partitioning reduces the working set: instead of scanning a billion-row table, the query targets a single partition containing perhaps 100 million rows. The smaller dataset requires less memory and generates less disk I/O, directly translating to faster query execution.

The key is knowing how to target specific partitions. Queries that can leverage partition pruning—where the WHERE clause allows the database to eliminate irrelevant partitions—gain the full benefit. Queries that must scan all partitions derive no advantage.

Better Query Planner Decisions

The database query planner decides between sequential scans and index scans based on cost estimates. When a query will return most rows from a table, using an index becomes counterproductive. Traversing the index requires jumping between the index structure and the table heap repeatedly—fetching an index entry, following its pointer to the table, fetching the row, returning to the index for the next entry. This scattered access pattern is slower than simply scanning the entire table sequentially when the result set is large.

The planner uses heuristics to make this decision, which involves computational overhead and uncertainty. With partitions, this decision becomes easier and more accurate. A query that would trigger a sequential scan on a billion-row table might efficiently use an index on a 100-million-row partition. The smaller partition makes index access viable where it wouldn’t be for the full table. Partitioning simplifies the planner’s decision-making and increases the likelihood of optimal execution plans.

Simplified Bulk Loading

Partitioning enables efficient bulk data loading strategies. Rather than inserting millions of rows into a partitioned table (which triggers partition routing overhead for each row), an entire table can be created independently, populated with bulk data, and then attached to the partition structure:

CREATE TABLE new_partition (LIKE grades_parts);
-- Bulk load data into new_partition
ALTER TABLE grades_parts ATTACH PARTITION new_partition FOR VALUES FROM (...) TO (...);

This approach loads data without partition routing overhead, then integrates the complete table atomically. The attachment operation validates that all data respects the partition’s range constraints before completing.

MySQL and MariaDB offer additional flexibility with CSV storage engines. A CSV file containing 300 million rows can be directly accessed as a table by pointing the storage engine to the file, immediately making the data queryable. This table can then be attached as a partition or used to populate one, providing rapid integration of external data.

Cost-Effective Data Archival

Partitioning enables tiered storage strategies based on access patterns. Rarely accessed partitions can be moved to cheaper, slower storage without affecting frequently queried data. A partition containing 2001 data that’s almost never queried can be relocated to a tablespace on inexpensive spinning disk drives, freeing premium SSD space for active partitions.

PostgreSQL tablespaces facilitate this: partitions can reside in different tablespaces pointing to different physical storage. The database transparently accesses data from the appropriate location based on which partition the query targets. Recent data remains on fast SSDs for optimal performance, while historical data archives to cost-effective storage. This tiered approach optimizes both performance and infrastructure costs without requiring application changes.

The Disadvantages of Partitioning

Partitioning introduces complexities and potential performance pitfalls that must be understood before implementation.

Slow Cross-Partition Updates

Updates that move rows from one partition to another are substantially slower than updates within a single table. In a non-partitioned table, updating a row is straightforward: locate the row and modify it in place. With partitioned tables, updating a value that determines partition placement transforms the operation into a delete from one partition followed by an insert into another.

Consider the grades example where partitions divide by grade ranges. A student initially has grade 30, placing their row in the g_0_35 partition. Updating that grade to 60 requires deleting the row from g_0_35 and inserting it into g_35_60. This cross-partition movement involves substantially more I/O than a simple in-place update: the database must remove the row from one physical location, potentially reorganize that partition’s structure, then insert into a different partition, potentially triggering page splits and index maintenance in the destination.

Frequent cross-partition updates can degrade SSD performance due to the scattered write patterns—jumping between different physical locations on disk repeatedly. If your workload frequently updates partition key values, partitioning may harm rather than help performance. Choose partition keys carefully to minimize cross-partition movements, ideally selecting values that rarely or never change.

Inefficient Queries Scan All Partitions

Partition pruning only activates when the WHERE clause contains predicates on the partition key that allow the database to eliminate partitions. Queries without such predicates must scan all partitions, potentially making them slower than scanning a single non-partitioned table.

Consider a query like SELECT * FROM grades_parts WHERE id > 1. The partition key is the grade column (g), but the WHERE clause filters on the ID column. The database cannot determine which partitions contain relevant rows based on ID values—grade and ID have no correlation—so all partitions must be scanned.

Instead of scanning one contiguous table, the database now scans five separate tables (if there are five partitions), jumping between different physical structures. This fragmentation can degrade performance compared to a sequential scan of a single table. Partitioning only helps when queries consistently filter on the partition key. If query patterns don’t align with the partitioning scheme, the overhead of managing multiple tables provides no benefit.

Challenging Schema Changes

Schema modifications on partitioned tables require database support that varies across systems. In the demonstration, creating an index on the master table automatically created corresponding indexes on all child partitions—PostgreSQL 11 and later handle this transparently.

However, not all database systems support this automatic propagation, and not all schema changes propagate even in systems that do. Some modifications require manually altering each partition individually, which becomes tedious and error-prone with many partitions. Schema evolution strategies must account for the additional complexity partitioning introduces, and database selection should consider how well the system manages partitioned table schemas.