Database Reference
In-Depth Information
A tablespace can only be dropped when it is empty, so how do you know when a tablespace
is empty?
Tablespaces can contain both permanent and temporary objects.
Permanent data objects are tables, indexes, and toast objects. We don't need to worry too
much about toast objects, because they are created and always live in the same tablespace
as their main table, plus, you cannot manipulate their privileges or ownership.
Indexes can exist in separate tablespaces, as a performance option, though that requires
explicit specification on the
CREATE
INDEX
statement. The default is to create indexes in the
same tablespace as the table to which they belong.
Temporary objects may also exist in a tablespace. These exist when users have explicitly
created temporary tables or there may be implicitly created data files when large queries
overflow their
work_mem
settings. These files are created according to the setting of the
temp_tablespaces
parameter. That might cause an issue, because you can't tell for certain
what the setting of
temp_tablespaces
is for each user. The users can change their setting
of
temp_tablespaces
away from the default value specified in the
postgresql.conf
.
We can identify the tablespace of each user object using the following query:
SELECT
spcname
,relname
,CASE WHEN relistemp THEN 'temp ' ELSE '' END ||
CASE
WHEN relkind = 'r' THEN 'table'
WHEN relkind = 'v' THEN 'view'
WHEN relkind = 'S' THEN 'sequence'
WHEN relkind = 'c' THEN 'type'
ELSE 'index' END
as objtype
FROM pg_class c join pg_tablespace ts
ON (CASE WHEN c.reltablespace = 0 THEN
(SELECT dattablespace FROM pg_database
WHERE datname = current_database())
ELSE c.reltablespace END) = ts.oid
WHERE relname NOT LIKE 'pg_toast%'
AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE
nspname IN ('pg_catalog', 'information_schema'))
;
spcname | relname | objtype
------------------+-----------+------------
new_tablespace | x | table
new_tablespace | y | table
new_tablespace | z | temp table
new_tablespace | y_val_idx | index
