Partitioning Lab: try a partitioning before you commit to it

Also in the app Edgely Database Manager: Help, then Documentation (F1), Partitioning Lab

A database keeps each table in files on the server's disk. When a table grows to millions of rows, reading it costs more and more. Partitioning (also called horizontal fragmentation) splits one table into several parts by the values of a column, for example one part per month or one per country. A query that filters on that column then reads only the part it needs (the engine "prunes" the others), and old data can be dropped one part at a time. A query that does not filter on the column still reads every part, and can get slower.

The Partitioning Lab shows how this works on your own tables. On PostgreSQL, MySQL and MariaDB it shows how a table is stored and how it is already partitioned, and helps you choose a partition key: it measures the columns you pick, suggests a strategy and previews the partitions it would make (see Choosing a partition key), then builds a partitioned copy of the table beside it, leaving the original as it is (see Trying a partitioning safely), measures your queries on both (see Reading the results), and can put the copy in the table's place, keeping the original as <table>_old (see Replacing the original). It can also split a table's columns into two tables sharing its primary key (see Splitting columns). On ClickHouse, SQL Server and Oracle it does the same, each with its own rules for the key and the copy.

Opening the Lab

• Right-click a table in the explorer and choose Partitioning Lab. On a partition (under a table's Partitions group) the same entry opens the Lab of its table.
• Or choose Tools > Partitioning Lab in the ribbon: the Lab opens on the connection of the current tab, with the default schema (public on PostgreSQL, dbo on SQL Server, your login's own schema in capitals on Oracle, the connection's database on MySQL, MariaDB and ClickHouse).

There is one Lab tab per connection; opening it again selects it and shows the table you picked.

What the Lab shows

On the left, type a Schema and a Table and click Show storage (or press Enter). Below, Partitioned tables in this schema lists the tables that are already partitioned, with their number of partitions; click one to show it.

On the right:

When no file is shown, the Lab says why: a managed service may not report it, MySQL and MariaDB show InnoDB file names only to a login with the PROCESS privilege, a MyISAM table has other files, a table in a shared tablespace has no file of its own, and on Oracle only a login that may read DBA_DATA_FILES sees the files.

Partitions in the explorer

A partitioned table gets a Partitions group under its Columns, Indexes and Foreign Keys, listing each partition with its bound and its row count. On PostgreSQL a partition is itself a table: it is listed under its parent's Partitions group and no longer beside it in the Tables group, and the partitioned table, which used to be missing from the list, now shows. On SQL Server the partitions are listed by number (1, 2, 3, ...) with the values each holds.

Choosing a partition key

Under a table that is not partitioned yet, Design a partitioning helps you choose how to split it. Nothing is written: the Lab only reads the table.

1. Partition key, in order. Click the column (or columns) most of your queries filter on, for example region, then a date column. The number on each chip is its place in the key. The Lab reads, for each column, its distinct values, its NULLs, its smallest and largest value and its most frequent values.
2. The suggestion. From what it read about the first column, the Lab proposes a strategy and selects it:
   • List when the column holds few distinct values (20 or fewer): one partition per value, for example one per country.
   • Range when it holds dates (one partition per month, or per year when the dates span more than ten years) or growing numbers that are nearly all different, such as an id (equal intervals between the smallest and the largest value).
   • Hash otherwise: the rows are spread evenly over a number of partitions you choose.
3. Try another strategy. Click List, Range or Hash; the Lab keeps your choice when you change the key. Range is offered for dates and numbers only. For a range choose one per month or per year (dates) or the number of intervals (numbers); for a hash, the number of partitions. Default partition for other values and NULL adds a partition for the rows no other partition takes. With two or more key columns, Then hash by splits each list or range partition again by a hash of the second column (sub-partitions).
4. The preview lists each future partition with what it would hold, its estimated rows and its share of the table, with a bar. On a large table (more than 200,000 rows by the server's estimate) the Lab reads a sample instead of the whole table and says so ("Estimated from a 20 % sample"); the estimated rows are then the sample's shares applied to the server's estimate of the table's rows, which MySQL and MariaDB can be off by a good margin. ClickHouse is always read whole: it scans quickly, and its own sampling needs a sampling key the table may not have. Cancel stops a read that takes too long; SQL Server, Oracle and ClickHouse cannot interrupt a running read from the Lab, so it finishes on the server and its answer is ignored.
5. Each engine places NULL its own way, and the preview follows it: in a range, MySQL, MariaDB and SQL Server put NULL keys in the lowest partition, Oracle in the last one (MAXVALUE), ClickHouse in a partition of their own, and PostgreSQL in the default partition. SQL Server cuts a list as range boundaries and ClickHouse makes one partition per value as rows arrive, so on those two every value has a partition and no default partition is offered. SQL Server partitions by one column and has no sub-partitions, so Then hash by is not offered there.

Below the preview the Lab warns about what would make the split cost more than it pays:

Trying a partitioning safely

Below the design, Build a partitioned copy turns it into a real table you can look at and query, without touching the original. The copy is named after the table with _part at the end (orders_part for orders) and sits beside it in the same schema.

1. Read the SQL. The SQL that builds it shows every statement the copy runs: on PostgreSQL a CREATE TABLE ... (LIKE orders ...) PARTITION BY ... and one CREATE TABLE ... PARTITION OF ... per partition; on MySQL and MariaDB a CREATE TABLE ... LIKE orders and an ALTER TABLE ... PARTITION BY ...; on SQL Server a partition function (CREATE PARTITION FUNCTION), a partition scheme on it, an empty copy of the columns (SELECT ... INTO) and a clustered index built on the scheme; on Oracle a CREATE TABLE ... PARTITION BY ... AS SELECT that takes no row; on ClickHouse a CREATE TABLE ... AS orders ENGINE = MergeTree PARTITION BY ...; then an INSERT ... SELECT that copies the rows. Copy the SQL puts it on the clipboard, to run it yourself or keep it.
2. Check the room. The copy takes about as much space as the table, shown under the SQL. The Lab cannot see the server's free disk space, so make sure the server has room before copying a large table.
3. Build the copy. The Lab asks first, naming the copy, the table, its rows and its size. On a Production connection the Production question comes before it. The progress line shows the statement running and then "Copying the rows"; Cancel stops the copy on the server. SQL Server, Oracle and ClickHouse cannot interrupt a running statement from the Lab: the line then says the copy stops after the running statement, and once it ends the copy is dropped.
4. Look at it. Once built, the Lab says how many rows were copied and how long it took. Show the copy opens it in the Lab: its partitions, their rows and files, as for any partitioned table. Back to orders returns to the original, with the design, the copy's buttons and Before and after as you left them.
5. Drop the copy when you are done (on the design, or on the copy itself). The Lab asks first; the copy and all its partitions are dropped (on SQL Server its partition function and scheme too, on Oracle without going to the recycle bin), the original stays as it is.
6. Or replace the original by the copy (PostgreSQL, MySQL and MariaDB): see Replacing the original.

What the copy holds on PostgreSQL, MySQL and MariaDB: the table's columns, defaults, check constraints and indexes, and all its rows. It has no foreign keys (neither engine copies them), and a generated or identity column becomes a plain column holding the copied values on PostgreSQL. List and range partition on the key's first column; hash on all its columns; with Then hash by, each list or range partition is split again on the second column.

The other three engines copy less, and the panel says what:

On Oracle, partitioning is an option of some editions only. The Lab asks the database (V$OPTION): when it says Partitioning is not enabled, the panel says so and offers no Build; the SQL is still shown. The Lab reports what the database says, nothing more: it is not a statement about your licence.

The Lab refuses to build when the server would refuse the copy or some rows would have no partition: a primary key or unique index without the key (not on SQL Server, whose copy has no unique index), more than 8,192 partitions (100 on ClickHouse), NULLs or values no partition takes, or on MySQL a list that does not name every value (MySQL has no default list partition). The SQL is still shown. It also refuses while a transaction is open on the connection: commit or roll it back first, so the copy does not join your work. When a statement fails, you cancel, or you close the Lab tab during the build, the Lab drops what the copy had created: no half-built table stays behind. A copy already built stays on the server when the tab closes; drop it from the Lab before closing, or with DROP TABLE. If a table named like the copy already exists, the build stops at the first statement and leaves that table alone.

Reading the results

Once the copy is built, Before and after under it measures what the partitioning changes for your own queries.

1. Your queries. The box starts with two queries on the original table: one that filters on the partition key (with one of its values, the least frequent of its common ones) and one that does not. Replace them with the queries you really run, written against the original table; end each one with a semicolon. The Lab runs each query on the original as you wrote it, and on the copy with the table's name replaced by the copy's (bare, quoted or with its schema, also as a column prefix such as orders.id; text in quotes and comments is left alone).
2. The copy's version, optional. When a name replacement could hit the wrong word (a column named like the table, for example), write the copy's queries yourself in the second box, one for each query above, in the same order; an empty box means "replace the name".
3. Compare. Each query runs through the Benchmark runner, with the Benchmark tab's settings: 5 warm-up runs, then 100 timed runs, a 30-second timeout per run, first on the original, then on the copy. The line beside the button says which run is going. On a Production connection the Production question comes first when the queries write.
4. Read each query's card:
   • Reads 1 of 6 partitions: how many of the copy's partitions the server's plan reads. PostgreSQL lists one scan per partition it keeps (partitions it prunes, while planning or when the query starts, are left out); MySQL and MariaDB name them in the plan's partitions column. SQL Server reports them in the actual plan only, so the Lab runs the copy's query once more to get it. Oracle gives them in the plan's Pstart and Pstop columns; when they read KEY the partitions are chosen as the query runs and the card shows no count. ClickHouse counts parts rather than partitions, so the count is shown only while each partition of the copy is one part (as just after it is built), and a COUNT(*) ClickHouse answers from its metadata reads no partition and shows no count.
   • Average: the mean time of one run on the original and on the copy, and how many times faster or slower the copy is ("About the same" within 10 %).
   • Side by side opens the Benchmark comparison of the two runs (average, median, percentiles, throughput, histogram); Plan on the copy opens the copy's plan.
5. Find it again later. Both runs of every query are saved in the Benchmark history, like any benchmark: open a Benchmark tab on the same connection to reload, compare or export them (HTML, CSV, JSON).

What to expect: a query that filters on the key reads only the partitions that hold its values, so it reads less and gets faster. A query that does not filter on the key reads every partition, one after the other, and can be a little slower than on the plain table: the server plans and opens each partition. That is the trade-off of partitioning: choose the key your most frequent or most expensive queries filter on. A small table, or one that already fits in memory, shows little difference either way; a query served by an index on the original may gain little too.

The copy's partitions are counted for one partitioned table: on MySQL, MariaDB and SQL Server a query that joins two partitioned tables adds both tables' partitions.

Replacing the original

Once the copy is built and compared, Replace the original (beside Drop the copy, on the design or on the copy itself) puts the copy in the table's place. Nothing is dropped: the original is renamed with _old at the end (orders_old for orders) and the copy takes the table's name, so your queries now run on the partitioned table, and the original rows stay in orders_old until you drop it yourself.

1. The checks. On a Production connection the Production question comes first. The Lab then refuses while a transaction is open on the connection (commit or roll it back first: the rename would join it on PostgreSQL and commit it on MySQL) and when a table, view or index named orders_old already exists (the Lab names it and never overwrites it: rename or drop it first).
2. What depends on the table. The Lab reads what depends on orders and lists it in the question, as it will be after the rename:
   • PostgreSQL ties views, foreign keys, grants, triggers, row security policies and owned sequences (a serial or identity column's) to the table itself, not to its name, so all of them stay on `orders_old`: the views read orders_old, the foreign keys of other tables point at it, and the grants, triggers and policies apply to it. The partitioned orders starts with none of them. A serial column's default on it still draws from the sequence orders_old owns, so move the sequence (ALTER SEQUENCE ... OWNED BY orders.id) before you drop orders_old.
   • MySQL and MariaDB move foreign keys and triggers with the table, so other tables' foreign keys and the table's triggers stay on `orders_old`; views and table grants name the table, so they now reach the partitioned `orders`. MySQL and MariaDB refuse foreign keys on a partitioned table: those foreign keys cannot be moved to orders, only kept on orders_old or dropped.
   • The table's own foreign keys stay on orders_old on every engine (the copy has none).
3. Replace. The question names the table, the copy and that list. Replace renames both in one step: on PostgreSQL the two ALTER TABLE ... RENAME run in one transaction (a DO block), on MySQL and MariaDB one RENAME TABLE orders TO orders_old, orders_part TO orders. Either both names change or neither does. If the server refuses, the Lab says so in its words and the copy stays built, so you can still drop it.
4. After. The Lab shows orders, now partitioned, with its partitions, and above it what still depends on orders_old (and, on MySQL and MariaDB, what now reaches the partitioned orders). Move what can move to orders, then drop orders_old yourself once nothing needs it: the Lab never drops it. The partitions keep the copy's names (orders_part_p0, ...) on PostgreSQL.

Replace is not offered on SQL Server, Oracle and ClickHouse: rename the tables yourself there.

Splitting columns

Partitioning splits a table's rows. Database courses teach the other way too, vertical fragmentation: a wide table whose queries use a few columns often is split by its columns into two tables that share the primary key. A query on the often-used columns then reads a much smaller table; a query that needs a column of the other half has to join the two.

On PostgreSQL, MySQL and MariaDB, under a table that is not partitioned yet, choose Split columns (beside Split rows (partitions), which shows the partition design above).

1. Columns your queries use often. The primary key's columns are shown as (key): they go in both halves. Click the other columns your frequent queries use, for example name and email in a users table whose bio and photo are large and rarely read.
2. The SQL that builds it lists the statements: <table>_hot gets the key and the columns you ticked, <table>_cold the key and the others, each with its rows and its primary key, then the view <table>_vsplit, which joins them back on the key, with the original's columns in their order, so the queries you wrote for the table run on it unchanged. Copy the SQL puts it on the clipboard. Other indexes, defaults and constraints are not copied.
3. Build the split asks first (on a Production connection the Production question comes before), then creates the two tables and the view beside the original, which is only read. It is refused inside an open transaction; a statement that fails, or Cancel, drops what the split had created. The two halves take as much room as the table.
4. Before and after then works as for a partitioned copy, with one difference: each query runs on <table>_hot when it uses only the key and the ticked columns, otherwise on the view, which pays the join. The second box, The split's version, lets you choose the table or view yourself, one query for each query above. The box starts with two queries: one on a ticked column and one on a column of the other half. No partition is counted.
5. Drop the split removes the view and both tables; the original is not changed.

The Lab refuses to split a table that has no primary key (there would be no safe way to join the halves back, and it does not invent one), a split with no column ticked outside the key, and one with every column ticked (nothing would be left for the second table); each reason is written under the columns.

What to expect: the query on the narrow table reads less and gets faster, often by several times on a wide table; the query through the view joins both halves on the key and is slower than on the original. Split by columns when your frequent queries need only a few columns and the rest are large.

Good to know

• Measuring and designing only read. Building and dropping the copy write, and only to the copy (<table>_part), and building and dropping a column split only to <table>_hot, <table>_cold and <table>_vsplit; the original is only read. Before and after runs the queries you typed, on the original and on the copy or the split, as they are.
• On PostgreSQL, inside an open transaction, each read runs in a savepoint: a refused read (an exact count on a table you may not read) leaves your transaction usable.
• A table you may not see is reported as "not found, or this login may not see it".