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   
-------+-------+--------
 10000 │ 69839 │ 200000
(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   
-------+-------+--------
 10000 │ 69839 │ 200000
(1 ROW)
 
=$ COMMIT;
COMMIT

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

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

```
=$ BEGIN;
```
```
BEGIN
```
```
 
```
```
=$ CREATE TABLE users_0 (
```
```
    LIKE users_old including ALL
```
```
);
```
```
CREATE TABLE
```
```
 
```
```
=$ WITH x AS (
```

    DELETE FROM users_old WHERE username < 'a' returning *

```
)
```
```
INSERT INTO users_0
```
```
    SELECT * FROM x;
```
```
INSERT 0 1622
```
```
 
```
```
=$ ALTER TABLE users
```

    attach partition users_0 FOR VALUES FROM (MINVALUE) TO ('a');

```
ALTER TABLE
```
```
 
```

=$ SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;

```
 COUNT |  SUM  |  SUM   
```
```
-------+-------+--------
```
```
 10000 | 69839 | 200000
```
```
(1 ROW)
```
```
 
```
```
=$ commit;
```
```
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:

```
=$ CREATE TABLE users_a (
```
```
    LIKE users_old including ALL
```
```
);
```
```
CREATE TABLE
```
```
 
```
```
=$ BEGIN;
```
```
BEGIN
```
```
 
```
```
=$ WITH x AS (
```

    DELETE FROM users_old WHERE username < 'am' returning *

```
)
```
```
INSERT INTO users_a
```
```
    SELECT * FROM x;
```
```
INSERT 0 161
```
```
 
```
```
=$ ALTER TABLE users
```

    attach partition users_a FOR VALUES FROM ('a') TO ('am');

```
ALTER TABLE
```
```
 
```
```
=$ commit;
```
```
COMMIT
```
```
 
```
```
=$ BEGIN;
```
```
BEGIN
```
```
 
```

=$ ALTER TABLE users detach partition users_a;

```
ALTER TABLE
```
```
 
```
```
=$ WITH x AS (
```

    DELETE FROM users_old WHERE username < 'b' returning *

```
)
```
```
INSERT INTO users_a
```
```
    SELECT * FROM x;
```
```
INSERT 0 132
```
```
 
```
```
=$ ALTER TABLE users
```

    attach partition users_a FOR VALUES FROM ('a') TO ('b');

```
ALTER TABLE
```
```
 
```
```
=$ commit;
```
```
COMMIT
```
```
 
```

=$ SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;

```
 COUNT |  SUM  |  SUM   
```
```
-------+-------+--------
```
```
 10000 | 69839 | 200000
```
```
(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?”

chAlx says:

2019-03-21 at 11:38

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.
corey says:

2019-03-22 at 17:08

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.