Partitioning: a new table? And stored where?

You write PARTITION BY RANGE (...) and assume it does one thing: split the table. It doesn't. It quietly answers two separate questions, and each database answers them differently. The SQL is identical everywhere, which is exactly what hides the difference.

The two questions

When a partition is created, two things may or may not happen, and they have nothing to do with each other.

Q1. Does it become its own table? Can you see it in the catalog and address it by name, or is there still just one table with hidden internal divisions? This is a question about identity.

Q2. Where do its bytes physically live? One file, many files, a specific disk? This is a question about location.

The trap is assuming these are the same question. They aren't. A partition can get its own storage without becoming its own table, or become its own table without getting its own storage. Identity and location are separate knobs.

How the three engines answer

Same PARTITION BY on top. Completely different answers underneath.

MySQL. No new table. There is still only sales. But each partition becomes its own file on disk:

/var/lib/mysql/shop/
  sales#P#p2022.ibd
  sales#P#p2023.ibd

You cannot query sales#P#p2022. You target a partition with SELECT ... FROM sales PARTITION (p2022). Identity: one table. Location: separate files.

Oracle. Also no new table, one logical sales. But each partition can be parked in its own tablespace, so 2022 lives on cheap cold disk and 2024 on fast SSD:

PARTITION sales_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023')) TABLESPACE ts_cold,
PARTITION sales_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025')) TABLESPACE ts_fast

Identity: one table. Location: separate tablespaces.

PostgreSQL. Same Q2 answer as Oracle: a partition can sit on its own tablespace. The split is Q1. Here a partition is a real table:

CREATE TABLE sales (id int, sale_date date) PARTITION BY RANGE (sale_date);
 
CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
  TABLESPACE fast_ssd;

So on storage, Oracle and Postgres are twins. They part on one thing: whether a partition is a table you can pick up. Here is what that buys. A Postgres partition is already a table, so you can cut it loose:

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

Oracle won't hand you a partition as a table like that. The partition is internal; to pull its data out you swap it with a table you bring yourself. Same destination, more steps, and the model is "swap," not "it was a table all along." That is the whole Oracle-versus-Postgres difference: identical on where the bytes live, split on whether a partition is something you can hold.

One quiet thing both moves share: neither touches your data. Postgres's detach and Oracle's swap only re-point which identity owns the same bytes on disk. That is why they finish instantly even on a terabyte. Identity and storage are two separate wires, and these commands just re-plug them. "Turn a partition into a table" was never a data operation at all.

The proof the two are separate: MySQL

Look at MySQL again. It does not make a new table, and it still gives each partition its own file. That single fact settles it. "No new table" does not mean "no separate storage." Identity and location move on their own. MySQL splits the bytes without ever splitting the table.

Why there are two questions at all

This is not a partitioning quirk. It is the same split between identity and location, one level up, in the words schema and tablespace. People treat them as synonyms. They aren't, and one line proves it. A single table can name both at once, and this runs in both Postgres and Oracle:

CREATE TABLE analytics.sales ( ... )  -- schema = analytics   (its name)
  TABLESPACE fast_ssd;                -- tablespace = fast_ssd (its disk)

Saying both is not saying it twice. analytics is the table's identity, fast_ssd is its location, and they move independently. A name is not a place.

Partitioning is the same split again: Q1 is identity, Q2 is location.

A small practical echo: a backup script or a runbook built around partitions is quietly written against one engine's two answers. Portable until you switch engines, then not. Worth noticing, not worth worrying about until you do.

The takeaway

PARTITION BY is a single syntax that looks portable, sitting on top of two independent decisions: become a table or not, and land where. Each engine fills in a different combination, and the SQL never tells you which.

That is the reflex worth keeping, well past partitioning. When an API looks identical across tools, that sameness is the surface. Ask how many knobs are hidden behind it, and who is turning them.