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.
\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):
\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:
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.
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
\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:
\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:
\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
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:
\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
\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:
\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:
\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:
\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
Top comments (0)