Skip to content
Tutorials Center
My learning

Topics

Lesson 92 of 125 ยท Performance

Table Partitioning and Tablespaces

Split huge tables into partitions by range, list or hash. Learn partition pruning, fast data retirement, PostgreSQL and MySQL syntax, and where tablespaces fit in.

Some tables just keep growing: logs, events, sensor readings, orders. After a few years a table holds billions of rows, indexes no longer fit in memory, and a nightly job that deletes old data takes hours. Partitioning is the built-in answer. You tell the database to store one logical table as several smaller physical pieces called partitions, split by a rule such as "one partition per month". Queries and applications still see a single table.

Partitioning helps in two main ways: queries that filter on the partition column only read the relevant pieces, and old data can be removed by dropping a whole partition instead of deleting row by row. In this lesson you will learn how the three partitioning methods work, simulate one in SQL you can run, see the real syntax for PostgreSQL and MySQL, and learn when partitioning does not help.

What a partitioned table looks like

flowchart TB
    Q["Query:<br/>WHERE created_on = '2026-02-14'"] --> T["events<br/>(logical table)"]
    T -.->|"skipped"| P1["events_2026_01"]
    T ==>|"read"| P2["events_2026_02"]
    T -.->|"skipped"| P3["events_2026_03"]

The database reads only the partition that can contain matching rows. Skipping the others is called partition pruning.

The column the rule is based on is the partition key. For pruning to work, your query has to filter on that key, so choose a column you almost always filter by, usually a date.

The three partitioning methods

Method Rule Good for Example
Range A partition holds a continuous range of the key Time series, anything pruned by date One partition per month
List A partition holds an explicit list of key values Categories, regions, tenants 'DE','AT','CH' in one partition
Hash hash(key) mod N picks the partition Spreading load evenly when there is no natural range 8 partitions by customer_id

Range is by far the most common. Hash gives even sizes but little pruning, except for equality lookups on the key. You can also sub-partition, such as range by month and then hash by customer.

Simulating range partitions

SQLite has no declarative partitioning, so this example builds the same structure by hand. There is one real table per month, each with a CHECK constraint describing its range, a UNION ALL view that acts as the logical table, and an INSTEAD OF trigger that routes every insert to the right piece (see Views and Triggers).

SQL Range partitions by month, built by hand

Press Run (or Ctrl + Enter) to see the result here.

The first query shows how six rows were spread across three tables without the application choosing. The second reads just the February table. A real database does that last step for you: if you query the logical events table with a date filter, its planner works out which partitions can match and ignores the rest.

If you try to insert a row dated 2026-07-01, the trigger aborts with "no partition for this date". Real systems behave the same way: unless you define a default partition that catches stray rows, an insert outside every range fails. Create new partitions ahead of time (monthly jobs, or a tool such as pg_partman) so inserts never hit that wall.

Retiring old data instantly

Deleting a month of old rows from a 2-billion-row table is slow, generates a huge transaction log, leaves dead space behind and may lock the table. With partitions, the same cleanup is a metadata change:

SQL Dropping a partition instead of deleting rows

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL the equivalent is ALTER TABLE events DETACH PARTITION events_2026_01; followed by DROP TABLE events_2026_01;. Detaching first lets you archive the old partition to cheaper storage or export it before it is gone. Loading works the same way in reverse: bulk load into a standalone table, add constraints, then ATTACH PARTITION it, which is far faster than inserting through the big table.

List and hash routing

List and hash partitioning only change the rule that picks the partition. This query computes where rows would go under each rule:

SQL Which partition does each row go to?

Press Run (or Ctrl + Enter) to see the result here.

Real databases use their own stable hash function instead of id % 4, but the idea is identical.

Real syntax

These snippets need a server database, so they are read-only. PostgreSQL uses declarative partitioning:

SQL
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_on date NOT NULL,
  kind       text NOT NULL,
  PRIMARY KEY (id, created_on)
) PARTITION BY RANGE (created_on);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- List and hash
CREATE TABLE customers (id bigint, country text) PARTITION BY LIST (country);
CREATE TABLE orders (customer_id bigint, total numeric) PARTITION BY HASH (customer_id);
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);

-- See pruning in action
EXPLAIN SELECT * FROM events WHERE created_on = '2026-02-14';

Notice that the primary key includes the partition key. Both PostgreSQL and MySQL require every unique constraint on a partitioned table to contain the partition columns, because each partition enforces uniqueness only on its own rows. The EXPLAIN output should list only events_2026_02 (see Reading Query Plans).

MySQL declares partitions inside the table definition:

SQL
CREATE TABLE events (
  id         BIGINT NOT NULL,
  created_on DATE NOT NULL,
  kind       VARCHAR(20) NOT NULL,
  PRIMARY KEY (id, created_on)
)
PARTITION BY RANGE COLUMNS (created_on) (
  PARTITION p2026_01 VALUES LESS THAN ('2026-02-01'),
  PARTITION p2026_02 VALUES LESS THAN ('2026-03-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE events DROP PARTITION p2026_01;

SQL Server uses partition functions and schemes, and Oracle has PARTITION BY RANGE ... INTERVAL, which creates new partitions automatically. See SQL Server and Oracle.

When partitioning helps, and when it does not

Partitioning is a tool for very large tables with a clear access pattern. It does not make every query faster.

  • It helps when queries filter on the partition key, when you remove or archive data by time, and when maintenance (vacuum, index rebuilds) is easier on smaller pieces.
  • It does not help queries that skip the partition key: they must visit every partition, which can be slower than one big table with a good index.
  • Too many partitions (tens of thousands) slow down planning and use memory. A few dozen to a few hundred is typical.
  • It is not a substitute for indexes. Each partition still needs its own indexes for the other columns you search (see Indexes).
  • Partitioning keeps data on one server. To spread it across servers, see Replication, Read Replicas and Sharding.

Tablespaces

A tablespace is a named storage location: a directory or set of files where the database keeps table and index data. Partitioning decides which rows go into which table; tablespaces decide where on disk a table lives. Together they let you keep hot recent partitions on fast SSDs and old ones on cheap disks:

SQL
-- PostgreSQL
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pgdata';
CREATE TABLESPACE slow_hdd LOCATION '/mnt/hdd/pgdata';

CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01') TABLESPACE fast_ssd;

ALTER TABLE events_2026_01 SET TABLESPACE slow_hdd;

Terms differ between systems. Oracle also uses tablespaces. SQL Server calls them filegroups. MySQL's InnoDB stores each table in its own .ibd file by default and can place one with DATA DIRECTORY. Managed cloud databases hide storage placement, so you will mostly meet tablespaces on servers you run yourself.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is partition pruning?

    When a query filters on the partition key, the planner reads only the partitions whose range or list can match, which is where much of the speed-up comes from.
  2. 2. Which partitioning method is most common for time-series data such as logs?

    Range partitioning by date lets queries prune by time and lets you retire a whole month by dropping one partition.
  3. 3. Why is dropping a partition faster than DELETE FROM big_table WHERE created_on < '2026-02-01'?

    A partition is its own table, so removing it does not need to visit, log or vacuum individual rows.
  4. 4. You partition an events table by month, but a frequent query filters only by user_id. What is the likely result?

    Without a filter on the partition key, pruning cannot happen, and the planner has to search each partition.
  5. 5. How do partitioning and tablespaces differ?

    Partitioning is about which rows go into which table. A tablespace is a storage location, so the two are often combined.

Summary

  • Partitioning stores one logical table as several physical partitions chosen by a partition key.
  • Range (dates), list (explicit values) and hash (even spread) are the three main methods; PostgreSQL, MySQL, SQL Server and Oracle all support them.
  • Queries that filter on the partition key benefit from pruning; queries that do not may get slower.
  • Dropping or detaching a partition retires old data instantly; attaching a pre-loaded table makes bulk loads cheap.
  • Unique keys must include the partition columns, and new partitions must exist before data arrives (or use a default partition).
  • Tablespaces (filegroups in SQL Server) decide where on disk data lives, so hot and cold partitions can use different storage.

Next: Connection Pooling and Plan Caching, about reusing connections and prepared statements to cut per-query overhead.