Archiving an old year's partition: MySQL, Postgres, Oracle

2024/10/16
databasesqlpostgresqlmysqloraclepartitioning

As old data piles up and you want to slim a table down, partitioning is the obvious first idea: split sales by year with PARTITION BY RANGE (sale_date), then split the 2022 partition out into its own table to archive, or move it to cheaper disk.

The PARTITION BY that creates the partitions reads the same in MySQL, Postgres, and Oracle, but the steps to split one out differ. Postgres uses DETACH PARTITION. MySQL and Oracle need an empty table first, then swap it with the partition using EXCHANGE PARTITION. That is because whether a partition is a table, and where its data is stored, differ across the three.

Oh no: once part of a table is partitioned, no row is spared

Say sales has one row each for 2021 through 2024. To split out only 2023, you might write a single partition in MySQL 8.0:

ALTER TABLE sales PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2023 VALUES LESS THAN (2024)
);
-- ERROR 1526 (HY000): Table has no partition for value 2024

The whole statement fails. p2023 is only a name; its range is "less than 2024", so 2021, 2022 and 2023 would all go into it, and 2024 has nowhere to go. Once a table is partitioned, no whole table is left behind; the remaining years have to be assigned too:

ALTER TABLE sales PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p_before VALUES LESS THAN (2023),      -- 2021, 2022
  PARTITION p2023    VALUES LESS THAN (2024),      -- 2023 only
  PARTITION p_after  VALUES LESS THAN MAXVALUE     -- 2024 onward
);

Postgres can use a DEFAULT partition to take every row that matches no range:

CREATE TABLE sales_2023  PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sales_other PARTITION OF sales DEFAULT;   -- 2021, 2022, 2024 all go here

The design lets the database tell which partition a row is in from its value alone. Querying 2023 in the two examples above, MySQL's EXPLAIN lists only p2023, and Postgres's query plan scans only sales_2023; no other partition is read. If rows could stay in the parent, every query would have to scan the parent too, just to be sure nothing matching was there.

The two questions

Q1. Does a partition become its own table? Can you SELECT * FROM <partition> as you would any table, or is there still just one table that the partitions are only reachable through? This is a question about identity.

Q2. Where does a partition's data physically live? One file, many files, a specific disk? This is a question about location. All three databases use a tablespace to say where data goes. A tablespace is a name you write in SQL; behind it are files or a directory on disk, and one tablespace can hold many tables.

DatabaseQ1: its own table?Q2: where the data lives
MySQLnoeach partition has its own .ibd file, but can't be assigned a tablespace
Oraclenoa partition has no file of its own; it shares data files with the other tables in its tablespace, and can be assigned a tablespace
PostgreSQLyes, a real child tableeach partition has its own file, and can be assigned a tablespace

The two answers can differ. A MySQL partition has its own file, yet it is not a table of its own.

A MySQL tablespace can't hold a partition together with another table; Oracle and Postgres can:

  • MySQL: each partition is its own tablespace, one .ibd file holding only that partition. Several plain tables can share a tablespace made with CREATE TABLESPACE, but a partition can't go in; you get ERROR 1478 (HY000): InnoDB : A partitioned table is not allowed in a shared tablespace..
  • Oracle: each partition can be put in any tablespace alongside other tables, and one data file mixes data from several tables.
  • PostgreSQL: each partition can also be put in any tablespace, in the same directory as other tables, but every table and every partition gets its own file.

The next three sections use the same example: two tables, orders and sales, with sales split into 2022 and 2023 partitions. Each section's diagram switches between before and after partitioning; hover a table or partition to light up the files that hold its data. Every file location in the diagrams was queried in docker; the versions are in the last section.

MySQL

In MySQL (InnoDB), each table gets its own .ibd file by default, and that file is its tablespace. After partitioning, sales.ibd is gone and each partition has its own .ibd, yet there is still only one table, sales:

To query only the 2022 partition, you go through sales:

SELECT * FROM sales PARTITION (p2022);
SELECT * FROM `sales#p#p2022`;   -- ERROR 1146: Table 'shop.sales#p#p2022' doesn't exist

To turn p2022 into a plain table, create an empty table with the same columns and indexes as sales, remove its partitioning, then swap it with p2022:

CREATE TABLE sales_2022 LIKE sales;
ALTER TABLE sales_2022 REMOVE PARTITIONING;
ALTER TABLE sales EXCHANGE PARTITION p2022 WITH TABLE sales_2022;
-- sales_2022 now holds the 2022 rows; partition p2022 of sales is empty

MySQL's exchange documentation lists the conditions for the swap, such as neither table having foreign keys.

Putting p2022 on a cheap disk can't be done with a tablespace. MySQL's tablespace documentation states that partitions can't go into a shared tablespace made with CREATE TABLESPACE, so you give that partition a DATA DIRECTORY when you create it.

Oracle

Like MySQL, Oracle still has only one sales after partitioning. The difference is location: in Oracle, every table and partition lives in some tablespace. Under a tablespace are data files, shared by the tables in that tablespace.

The existing sales can be turned into a partitioned table with ALTER TABLE, assigning each partition a tablespace in the same statement, for example 2022 to ts_cold on a cheap disk and 2023 staying in ts_fast:

ALTER TABLE sales MODIFY PARTITION BY RANGE (sale_date) (
  PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01') TABLESPACE ts_cold,
  PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01') TABLESPACE ts_fast
);

Oracle is free about where data goes. The same sales has 2022 in cold01.dbf and 2023 in fast01.dbf, and fast01.dbf holds another table, orders, next to sales_2023. Every table and every partition can be given its own tablespace, so one table can spread across several files, and one file can hold data from several tables. MySQL and Postgres partitions can also go on different disks, but each file holds only one partition or one table, never a mix.

A partition is not a table, so queries go through sales:

SELECT * FROM sales PARTITION (sales_2022);
SELECT * FROM sales_2022;   -- ORA-00942: table or view "SHOP"."SALES_2022" does not exist

To turn partition sales_2022 into a plain table, create an empty sales_2022_arc and swap it with the partition:

CREATE TABLE sales_2022_arc TABLESPACE ts_fast FOR EXCHANGE WITH TABLE sales;
ALTER TABLE sales EXCHANGE PARTITION sales_2022 WITH TABLE sales_2022_arc;

PostgreSQL

A Postgres tablespace maps to a directory; without one, data goes into the default pg_default. Each table has its own data file in that directory, named by an internal number. After partitioning, sales has no file of its own; its data lives in the partitions' files:

A Postgres partition is itself a table, so you can query it directly:

SELECT * FROM sales_2022;
SELECT * FROM sales PARTITION (sales_2022);   -- no error, but it reads all of sales

The second line is MySQL and Oracle syntax. Postgres doesn't know it, yet doesn't raise an error either: it reads the line as SELECT * FROM sales AS partition(sales_2022), where partition becomes an alias for sales and (sales_2022) becomes a new name for the first column. In the test, sales had 3 rows, only 2 of them from 2022, yet this line returned all 3, with the id column now headed sales_2022:

 sales_2022 | sale_date
------------+------------
          1 | 2022-03-01
          2 | 2022-07-01
          3 | 2023-05-01

Unlike MySQL and Oracle, Postgres also can't turn an existing table into a partitioned one with ALTER TABLE; you create a new table:

CREATE TABLE sales (id int, sale_date date) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

To turn a partition into a plain table, Postgres needs no swap. sales_2022 is already a table that hangs under sales; DETACH takes it off:

ALTER TABLE sales DETACH PARTITION sales_2022;
-- sales_2022 is now a plain standalone table: archive it, drop it, move it

A tablespace's directory can sit on any disk. Create a tablespace on the cheap disk, then move sales_2022 into it, and its file moves into that directory:

CREATE TABLESPACE cold_hdd LOCATION '/mnt/hdd/pg';
ALTER TABLE sales_2022 SET TABLESPACE cold_hdd;

Does splitting one out move the data?

No. In all three databases the data stays where it is on disk; only the record of who owns it changes:

  • Postgres: after DETACH, sales_2022 is still the same file, base/5/16387.
  • MySQL: the swap only renames the two .ibd files. p2022's data was in sales#p#p2022.ibd; after the swap the same file (inode 168717) is named sales_2022.ibd.
  • Oracle: Oracle's documentation says the exchange swaps the two segments (a segment is the space a table or partition occupies in the data files). sales_2022's segment 73291 sits at block 778 of cold01.dbf; after the swap it belongs to sales_2022_arc, with the same number and place.

The data doesn't move, but splitting isn't always instant. Postgres's DETACH waits for queries running on sales to finish. The MySQL and Oracle swaps check by default that every row falls inside the partition's range, so more data means a longer check.

Test environment

  • Local machine: macOS 15.6 (Apple Silicon), Docker 23.0.5
  • MySQL: docker image mysql:8.0, actual version 8.0.46
  • PostgreSQL: docker image postgres:16, actual version 16.15
  • Oracle: docker image gvenzl/oracle-free:23-slim, actual version Oracle AI Database 26ai Free 23.26.3