Set a Default Tablespace for a Database

In PostgreSQL, tablespaces allow administrators to specify where on a disk specific tables and indexes should reside based on how users want to store and access the data.

YSQL tablespaces re-purpose this concept for a geo-distributed deployment by allowing you to specify the number of replicas for a table or index, and how they can be distributed across a set of clouds, regions, and zones. Replicating and pinning tables in specific regions can lower read latency, improve resilience, and achieve compliance with data residency laws.

You might have a use case where you want all tables and indexes in a database to default to a tablespace of your design.

For example, is a multi-region YugabyteDB cluster, you may want to isloate a database to just one region. For that we’ll first need to create a tablespace.

					yugabyte=# SELECT host, cloud, region, zone FROM yb_servers() ORDER BY cloud, region, zone;
      host       | cloud |  region   |    zone
-----------------+-------+-----------+------------ | aws   | us-east-1 | us-east-1a       | aws   | us-east-1 | us-east-1a       | aws   | us-east-1 | us-east-1a       | aws   | us-east-2 | us-east-2a       | aws   | us-east-2 | us-east-2a       | aws   | us-east-2 | us-east-2a       | aws   | us-west-1 | us-west-1a       | aws   | us-west-1 | us-west-1a       | aws   | us-west-1 | us-west-1a
(9 rows)

yugabyte=# CREATE TABLESPACE us_east_2_only_tablespace WITH (
yugabyte(#   replica_placement='{"num_replicas": 3, "placement_blocks":
yugabyte'#   [{"cloud":"aws","region":"us-east-2","zone":"us-east-2a","min_num_replicas":3}]}'
yugabyte(# );

The us_east_2_only_tablespace will store all data for each table that is assigned to it only on nodes in the US-EAST-2 region.

Unfortunately if we try to set a default tablespace to a new database at creation time, we get an error…

					yugabyte=# CREATE DATABASE us_east_2_only WITH TABLESPACE us_east_2_only;
ERROR:  Value other than default for tablespace option is not yet supported
LINE 1: CREATE DATABASE us_east_2_only WITH TABLESPACE us_east_2_onl...
HINT:  Please report the issue on

However after we create a new database, we can then set a default tablespace for it so that all tables created in the database will default to use the tablespace!

					yugabyte=# CREATE DATABASE us_east_2_only;

yugabyte=# ALTER DATABASE us_east_2_only SET default_tablespace=us_east_2_only_t

yugabyte=# \c us_east_2_only
You are now connected to database "us_east_2_only" as user "yugabyte".

us_east_2_only=# SHOW default_tablespace;
(1 row)

us_east_2_only=# CREATE TABLE us_east_2_only_table (pk INT PRIMARY KEY, c1 VARCHAR);

us_east_2_only=# \d us_east_2_only_table;
             Table "public.us_east_2_only_table"
 Column |       Type        | Collation | Nullable | Default
 pk     | integer           |           | not null |
 c1     | character varying |           |          |
    "us_east_2_only_table_pkey" PRIMARY KEY, lsm (pk HASH), tablespace "us_east_2_only_tablespace"
Tablespace: "us_east_2_only_tablespace"
Note that the us_east_2_only_table is assigned to the tablespace us_east_2_only_table_tablespace even though I did not include the tablespace option in the CREATE TABLE caommand!

Have Fun!