TheLazy.Dev
Portfolio GitHub

Blog

postgres |tablespace |partition |index

PostgreSQL index partition in same tablespace as table

franckpachot

Franck Pachot

Posted on March 13, 2022

PostgreSQL index partition in same tablespace as table

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 is not set. 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.

\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);
Enter fullscreen mode Exit fullscreen mode

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):

\c postgres postgres

create user       eu password 'postgres';
grant global to   eu;
Enter fullscreen mode Exit fullscreen mode

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:

create tablespace eu location '/data/eu';
alter  user       eu set default_tablespace='eu';
grant create on tablespace eu to eu;
Enter fullscreen mode Exit fullscreen mode

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.

create schema if not exists eu;
grant all on schema eu to eu;
Enter fullscreen mode Exit fullscreen mode

create partition using the dedicated user

Now ready to connect with the user having all defaults, and create the partition

\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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Then I create it in each partition:

\c postgres eu
alter table demo add primary key(id,country);
Enter fullscreen mode Exit fullscreen mode

However, to show the problem when creating it at the global level, I create a unique constraint with the superuser:

\c postgres postgres
alter table demo add unique(country,val);
Enter fullscreen mode Exit fullscreen mode

checking what we have

Now let's check that all objects are at their right place

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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:

\c postgres eu
alter index demo_country_val_key set tablespace eu;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

\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;
Enter fullscreen mode Exit fullscreen mode

Now, creating the partition when connected with this user:

\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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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:

\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
Enter fullscreen mode Exit fullscreen mode

However when you insert from the local user, seeing the table in its schema:

\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
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
franckpachot
Franck Pachot

Posted on March 13, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

PostgreSQL index partition in same tablespace as table
postgres PostgreSQL index partition in same tablespace as table

March 13, 2022