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
-----------------+-------+-----------+------------
yugabytedb.tech | aws | us-east-1 | us-east-1a
127.0.0.2 | aws | us-east-1 | us-east-1a
127.0.0.3 | aws | us-east-1 | us-east-1a
127.0.0.5 | aws | us-east-2 | us-east-2a
127.0.0.4 | aws | us-east-2 | us-east-2a
127.0.0.6 | aws | us-east-2 | us-east-2a
127.0.0.7 | aws | us-west-1 | us-west-1a
127.0.0.9 | aws | us-west-1 | us-west-1a
127.0.0.8 | 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(# );
CREATE TABLESPACE
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 https://github.com/YugaByte/yugabyte-db/issues
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;
CREATE DATABASE
yugabyte=# ALTER DATABASE us_east_2_only SET default_tablespace=us_east_2_only_t
ablespace;
ALTER DATABASE
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;
default_tablespace
---------------------------
us_east_2_only_tablespace
(1 row)
us_east_2_only=# CREATE TABLE us_east_2_only_table (pk INT PRIMARY KEY, c1 VARCHAR);
CREATE TABLE
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 | | |
Indexes:
"us_east_2_only_table_pkey" PRIMARY KEY, lsm (pk HASH), tablespace "us_east_2_only_tablespace"
Tablespace: "us_east_2_only_tablespace"
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!
