Consider the following table, created on a specific tablespace:
```sql
CREATE TABLE example (
example_id int4 NOT NULL,
name VARCHAR(12) NOT NULL,
CONSTRAINT example_pkey
PRIMARY KEY (example_id)
) TABLESPACE tbspc01;
Accessing pg_class and pg_tablespace one may verify the good result:
SELECT c.relname,
t.spcname
FROM pg_class c
JOIN pg_tablespace t
ON c.reltablespace = t.oid
WHERE c.relname LIKE '%example%';
Producing:
relname | spcname
---------+-------------
example | chinook_spc
(1 row)
However, as the table grows in size, there will be mismatch between the table size reported by pg_total_relation_size() and that reported by pg_tablespace_size(), the table being larger than the tablespace.
That is so for the primary key is stored in the default tablespace:
SELECT indexname,
tablespace
FROM pg_indexes
WHERE indexname LIKE '%example%';
Result:
indexname | tablespace
--------------+------------
example_pkey |
(1 row)
It is possible to move the primary key to the desired index with ALTER INDEX [...] SET TABLESPACE. But why isn't it stored in the same tablespace as the table? And is there a way to force it there in CREATE TABLE instruction?