Migrating simple table to partitioned. How?

Recently someone asked, on irc, how to make table partitioned.

The thing is that it was supposed to be done with new partitioning, and not the old way.

The problem is that while we can create table that will be seen as partitioned – we can't alter table to become partitioned.

So. Is it possible?

For my tests I made a table:

=$ CREATE TABLE users (
    id             serial PRIMARY KEY,
    username       text NOT NULL UNIQUE,
    password       text,
    created_on     timestamptz NOT NULL,
    last_logged_on timestamptz NOT NULL
);
CREATE TABLE
 
=$ CREATE extension short_ids;
CREATE EXTENSION
 
=$ INSERT INTO users (username, password, created_on, last_logged_on)
    SELECT
        get_random_string( (random() * 4 + 5)::int4, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),
        get_random_string( 20, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' ),
        now() - '2 years'::INTERVAL * random(),
        now() - '2 years'::INTERVAL * random()
    FROM
        generate_series(1, 10000);
INSERT 0 10000

For sanity check, let's quickly see some stats about the data:

=$ SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;
 COUNT |  SUM  |  SUM   
-------+-------+--------
 1000069839200000
(1 ROW)

This will be useful to see if all data is available after partitioning.

For partitioning – let's assume we want to partition by ranges on first character.

First. We need partitioned master table. Since we want this to end up named users, we can do quick hack:

=$ BEGIN;
BEGIN
 
=$ ALTER TABLE users RENAME TO users_old;
ALTER TABLE
 
=$ CREATE TABLE users (LIKE users_old)
    PARTITION BY RANGE ( username );
CREATE TABLE
 
=$ ALTER TABLE users
    attach partition users_old DEFAULT;
ALTER TABLE
 
=$ SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;
 COUNT |  SUM  |  SUM   
-------+-------+--------
 1000069839200000
(1 ROW)
 
=$ COMMIT;
COMMIT

OK. Now we have users “partitioned" into single partition.

Let's first move users that have usernames starting with digits:

  1. =$ BEGIN;
  2. BEGIN
  3.  
  4. =$ CREATE TABLE users_0 (
  5.     LIKE users_old including ALL
  6. );
  7. CREATE TABLE
  8.  
  9. =$ WITH x AS (
  10.     DELETE FROM users_old WHERE username < 'a' returning *
  11. )
  12. INSERT INTO users_0
  13.     SELECT * FROM x;
  14. INSERT 0 1622
  15.  
  16. =$ ALTER TABLE users
  17.     attach partition users_0 FOR VALUES FROM (MINVALUE) TO ('a');
  18. ALTER TABLE
  19.  
  20. =$ SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;
  21.  COUNT |  SUM  |  SUM   
  22. -------+-------+--------
  23.  10000 | 69839 | 200000
  24. (1 ROW)
  25.  
  26. =$ commit;
  27. COMMIT

Please note that technically only queries in lines 9 and 16 need to be in transaction.

Unfortunately, it will lock the rows for the duration of move, but this can be easily alleviated by doing the migration in small steps.

For example:

  1. =$ CREATE TABLE users_a (
  2.     LIKE users_old including ALL
  3. );
  4. CREATE TABLE
  5.  
  6. =$ BEGIN;
  7. BEGIN
  8.  
  9. =$ WITH x AS (
  10.     DELETE FROM users_old WHERE username < 'am' returning *
  11. )
  12. INSERT INTO users_a
  13.     SELECT * FROM x;
  14. INSERT 0 161
  15.  
  16. =$ ALTER TABLE users
  17.     attach partition users_a FOR VALUES FROM ('a') TO ('am');
  18. ALTER TABLE
  19.  
  20. =$ commit;
  21. COMMIT
  22.  
  23. =$ BEGIN;
  24. BEGIN
  25.  
  26. =$ ALTER TABLE users detach partition users_a;
  27. ALTER TABLE
  28.  
  29. =$ WITH x AS (
  30.     DELETE FROM users_old WHERE username < 'b' returning *
  31. )
  32. INSERT INTO users_a
  33.     SELECT * FROM x;
  34. INSERT 0 132
  35.  
  36. =$ ALTER TABLE users
  37.     attach partition users_a FOR VALUES FROM ('a') TO ('b');
  38. ALTER TABLE
  39.  
  40. =$ commit;
  41. COMMIT
  42.  
  43. =$ SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;
  44.  COUNT |  SUM  |  SUM   
  45. -------+-------+--------
  46.  10000 | 69839 | 200000
  47. (1 ROW)

In here, I have two separate transactions, moving parts of the data, in lines 6-20 and 23-40

In the same way I can partition the rest of table.

Hope you'll find it useful.

2 thoughts on “Migrating simple table to partitioned. How?”

  1. Use pg_pathman, Luke!
    It has handy functions to transfer data between master and partition tables. One can use the extension for partitioning and data migration, then disable it.

  2. Unfortunately, a lot of pg-instances are now on cloud services which don’t allow 3rd party extensions like partman and pathman, so this example is quite necessary.

    I would suggest a slightly different pattern where you create an empty table with all of the partitions attached, then have that master table inherit from the original table, and then do the `WITH deleted AS (…) INSERT INTO …` commands.

Comments are closed.