PostgreSQL index partition in same tablespace as table
Tags: postgres, tablespace, partition, index
Published: 2022-03-13 20:38:26

Tablespaces are used to store tables and indexes in a specific filesystem, with different characteristics like capacity or performance. It can be combined with partitioning to, for example, put cold data on larger but cheaper storage and keep the hot one on local SSD. We use also tablespaces for geo-partitioning in YugabyteDB. In both cases, we generally put the local index partition in the same tablespace as the table partition.
However, there's no option to do that automatically in PostgreSQL. I would like something like `create index ... tablespace with table` syntax, or a change in behavior where index partitions use the same tablespace as its table partition, at least when `default_tablespace`. I'm interested by your ideas there.

I this post I'm showing a way to avoid mistakes by aligning **tablespace**, table **owner**, and table **schema** for partitions.

I've run this on RDS PostgreSQL because I wanted to check that it works. But I see no reason to use tablespaces in RDS, as they all go into the same volume. It is supported to ease migration between PostgreSQL compatible databases.

## global user and partitioned table
I use a `global` user to define the partitioned table.
```sql
\c postgres postgres

create user global password 'postgres';

\c postgres global

create table demo (id int, country text, val int)
 partition by list (country);
create index demo_i0 on demo(val);
-- alter table demo add primary key(id, country);
```
I have created an index `demo_i0` to show what happens when the index is created at table level and partitions are created later. I didn't create a primary key here because I want to show what happens when adding it later. But you should create it there before creating the partitions.

## user dedicated to table partitions
Each partition will be owned by a dedicated user which I created here (I name it `eu` to store European countries):
```sql
\c postgres postgres

create user       eu password 'postgres';
grant global to   eu;
```
You must grant the table owner to the partition owner or you will get `ERROR:  must be owner of table demo`

## tablespace
I create the tablespace and set it as the default for the related user:
```sql
create tablespace eu location '/data/eu';
alter  user       eu set default_tablespace='eu';
grant create on tablespace eu to eu;
```

## schema
As I'm using per-user defaults, I'll do the same with the schema so that the partitions will have the same name, in a different namespace. But, that's my choice, you may prefer to give it a different name.
```sql
create schema if not exists eu;
grant all on schema eu to eu;
```
## create partition using the dedicated user
Now ready to connect with the user having all defaults, and create the partition
```sql
\c postgres eu

create table demo partition of demo for values in ('IS','GB','AD','FR','SI','PL','CY','MT','DE','CH','SM','EE','DK','FI','HR','MK','BE','ME','XK','PT','SK','LV','RS','MC','RU','NO','LI','BG','IE','BY','GR','C
Z','MD','BA','IT','AT','LU','LT','NL','HU','SE','ES','RO','UA','VA','AL');
create index demo_i1 on demo(val);
```
I have added another index to show what happens when the index is created from this user.

## adding unique constraints

The different owners and the privileges I have assigned prevent me to alter the table with a statement that has to create an index on all partitions:
```
postgres=> \c postgres global

postgres=> alter table demo add primary key(id,country);
ERROR:  must be owner of table demo
```
Then I create it in each partition:
```sql
\c postgres eu
alter table demo add primary key(id,country);
```

However, to show the problem when creating it at the global level, I create a unique constraint with the superuser:
```sql
\c postgres postgres
alter table demo add unique(country,val);
```

## checking what we have

Now let's check that all objects are at their right place
```sql
select relname,nsp.*, spc.*, relam, relowner
 from pg_class 
 natural left join (
  select oid reltablespace, spcname, spcoptions 
  from pg_tablespace
 ) spc 
 natural left join (
  select oid relnamespace, nspname
  from pg_namespace
 ) nsp 
 where relname like 'demo%' 
 order by oid;
```
Here is the result, showing the schema (`nspname`) and tablespace (`spcname`):
```
       relname        | relnamespace | nspname | reltablespace | spcname | spcoptions | relam | relowner
----------------------+--------------+---------+---------------+---------+------------+-------+----------
 demo                 |         2200 | public  |               |         |            |     0 |    16402
 demo_i0              |         2200 | public  |               |         |            |   403 |    16402
 demo                 |        16409 | eu      |         16408 | eu      |            |     2 |    16407
 demo_val_idx         |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo_i1              |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo_pkey            |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo_country_val_key |         2200 | public  |               |         |            |   403 |    16402
 demo_country_val_key |        16409 | eu      |               |         |            |   403 |    16407
(8 rows)
```
The table partition (`eu.demo`) and its index partitions (`eu.demo_i1` which I have created from the user, `eu.demo_val_idx` that has been created from the `demo_i0` definition at table level, and `eu.demo_pkey` created for the primary key) are all in the right schema and tablespace `eu`. This is correct thanks to using a dedicated user with the right defaults.

However, `eu.demo_country_val_key` which have been created by the unique constraint declared on the global table wasn't created in the right tablespace. 

This can be fixed in each partition:
```sql
\c postgres eu
alter index demo_country_val_key set tablespace eu;
```

Here is finally the table definition:
```
postgres=> \c postgres global
psql (15devel, server 14.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "global".

postgres=> \d+ demo
                                     Partitioned table "public.demo"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id      | integer |           |          |         | plain    |             |              |
 country | text    |           |          |         | extended |             |              |
 val     | integer |           |          |         | plain    |             |              |
Partition key: LIST (country)
Indexes:
    "demo_country_val_key" UNIQUE CONSTRAINT, btree (country, val)
    "demo_i0" btree (val)
Partitions: eu.demo FOR VALUES IN ('IS', 'GB', 'AD', 'FR', 'SI', 'PL', 'CY', 'MT', 'DE', 'CH', 'SM', 'EE', 'DK', 'FI', 'HR', 'MK', 'BE', 'ME', 'XK', 'PT', 'SK', 'LV', 'RS', 'MC', 'RU', 'NO', 'LI', 'BG', 'IE', 'BY', 'GR', 'CZ', 'MD', 'BA', 'IT', 'AT', 'LU', 'LT', 'NL', 'HU', 'SE', 'ES', 'RO', 'UA', 'VA', 'AL')

postgres=> \c postgres eu
psql (15devel, server 14.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "eu".

postgres=> \d+ demo
                                             Table "eu.demo"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id      | integer |           | not null |         | plain    |             |              |
 country | text    |           | not null |         | extended |             |              |
 val     | integer |           |          |         | plain    |             |              |
Partition of: public.demo FOR VALUES IN ('IS', 'GB', 'AD', 'FR', 'SI', 'PL', 'CY', 'MT', 'DE', 'CH', 'SM', 'EE', 'DK', 'FI', 'HR', 'MK', 'BE', 'ME', 'XK', 'PT', 'SK', 'LV', 'RS', 'MC', 'RU', 'NO', 'LI', 'BG', 'IE', 'BY', 'GR', 'CZ', 'MD', 'BA', 'IT', 'AT', 'LU', 'LT', 'NL', 'HU', 'SE', 'ES', 'RO', 'UA', 'VA', 'AL')
Partition constraint: ((country IS NOT NULL) AND (country = ANY (ARRAY['IS'::text, 'GB'::text, 'AD'::text, 'FR'::text, 'SI'::text, 'PL'::text, 'CY'::text, 'MT'::text, 'DE'::text, 'CH'::text, 'SM'::text, 'EE'::text, 'DK'::text, 'FI'::text, 'HR'::text, 'MK'::text, 'BE'::text, 'ME'::text, 'XK'::text, 'PT'::text, 'SK'::text, 'LV'::text, 'RS'::text, 'MC'::text, 'RU'::text, 'NO'::text, 'LI'::text, 'BG'::text, 'IE'::text, 'BY'::text, 'GR'::text, 'CZ'::text, 'MD'::text, 'BA'::text, 'IT'::text, 'AT'::text, 'LU'::text, 'LT'::text, 'NL'::text, 'HU'::text, 'SE'::text, 'ES'::text, 'RO'::text, 'UA'::text, 'VA'::text, 'AL'::text])))
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id, country), tablespace "eu"
    "demo_country_val_key" UNIQUE CONSTRAINT, btree (country, val), tablespace "eu"
    "demo_i1" btree (val), tablespace "eu"
    "demo_val_idx" btree (val), tablespace "eu"
Tablespace: "eu"
Access method: heap
```

## adding a new partition

Now doing the same for a new partition. Here is the creation of tablespace, user and schema. The same as above for `eu` but with `af` for African countries
```sql
\c postgres postgres
create user       af password 'postgres';
grant global to   af;
create tablespace af location '/data/af';
alter  user       af set default_tablespace='af';
grant create on tablespace af to af;
create schema if not exists af;
grant all on schema af to af;
```
Now, creating the partition when connected with this user:
```sql
\c postgres af
create table demo partition of demo for values in ('CG','TG','UG','CM','KE','BF','CD','GM','TD','SO','GA','SL','ET','CF','NE','LS','GW','SC','MG','DZ','SD','ZW','TZ','MU','EG','ER','LY','BI','MA','SN','CV','GH','DJ','BJ','MW','ST','MR','CI','KM','BW','AO','SS','ZA','ML','TN','ZM','RW','NA','LR','GN','SZ','GQ','NG','MZ');
create index demo_i1 on demo(val);
alter table demo add primary key(id,country);
```
The result is here, all good, even the index that supports the unique constraint (the problem was just for the partitions created before):
```
       relname        | relnamespace | nspname | reltablespace | spcname | spcoptions | relam | relowner
----------------------+--------------+---------+---------------+---------+------------+-------+----------
 demo                 |         2200 | public  |               |         |            |     0 |    16402
 demo_i0              |         2200 | public  |               |         |            |   403 |    16402
 demo                 |        16409 | eu      |         16408 | eu      |            |     2 |    16407
 demo_val_idx         |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo_i1              |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo_pkey            |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo_country_val_key |         2200 | public  |               |         |            |   403 |    16402
 demo_country_val_key |        16409 | eu      |         16408 | eu      |            |   403 |    16407
 demo                 |        16482 | af      |         16481 | af      |            |     2 |    16480
 demo_val_idx         |        16482 | af      |         16481 | af      |            |   403 |    16480
 demo_country_val_key |        16482 | af      |         16481 | af      |            |   403 |    16480
 demo_i1              |        16482 | af      |         16481 | af      |            |   403 |    16480
 demo_pkey            |        16482 | af      |         16481 | af      |            |   403 |    16480
```

All is good, and easy to automate. Note that I created the primary key later, but it is much better to create it at global level before creating any partition.

## On update conflict

This also solves another problem. When the primary key is created locally like I did here for the unique constraint, you cannot use `on conflict` when inserting on the parent table:
```sql
\c postgres global
postgres=> insert into demo values (1,'CH',1) on conflict(id,country) do nothing;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
```
However when you insert from the local user, seeing the table in its schema:
```sql
\c postgres eu
postgres=> insert into demo values (42,'CH',42) on conflict(id,country) do nothing;
INSERT 0 1
postgres=> insert into demo values (42,'CH',42) on conflict(id,country) do nothing;
INSERT 0 0
postgres=> insert into demo values (42,'CH',42) on conflict(id,country) do nothing;
INSERT 0 0
```