When a YugabyteDB table is assigned to a custom tablespace, it is reasonable to assume that any secondary indexes created on that table will automatically use the same tablespace.
They do not.
A secondary index created without an explicit TABLESPACE clause does not inherit the tablespace of its base table. By default, YugabyteDB places the index in pg_default, whose tablets are distributed according to the cluster’s placement configuration.
TABLESPACE clause explicitly or set default_tablespace for the session that creates the index. Why This Matters in YugabyteDB
In traditional PostgreSQL, a tablespace primarily determines the filesystem location where an object is stored.
YugabyteDB uses tablespaces differently. A YugabyteDB tablespace describes a distributed placement policy for a table or index, including:
- ● The number of replicas.
- ● The clouds where replicas may be placed.
- ● The regions where replicas may be placed.
- ● The availability zones where replicas may be placed.
- ● Minimum replica counts within specific placement blocks.
Tablespaces can be assigned independently to tables and secondary indexes.
For example, a table could use an East Coast tablespace while one of its indexes uses a West Coast tablespace:
CREATE TABLE demo.customer_accounts (
account_id BIGINT PRIMARY KEY,
email TEXT NOT NULL
)
TABLESPACE us_east_tablespace;
CREATE INDEX customer_accounts_email_idx
ON demo.customer_accounts (email)
TABLESPACE us_west_tablespace;
This is valid YugabyteDB DDL.
A table and its secondary indexes do not need to belong to the same tablespace. However, an unintended mismatch can cause queries using the index to access tablets located outside the expected region.
Demonstrating the Default Behavior
The following demonstration assumes that a YugabyteDB tablespace named east_tablespace already exists.
The actual CREATE TABLESPACE statement is not included because its replica_placement configuration depends on the cloud, region, and availability-zone labels configured in your YugabyteDB cluster.
Create a test schema:
CREATE SCHEMA IF NOT EXISTS demo;
Remove the test table if it already exists:
DROP TABLE IF EXISTS demo.app_users CASCADE;
Create the table in east_tablespace:
CREATE TABLE demo.app_users (
user_id BIGINT PRIMARY KEY,
email TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
TABLESPACE east_tablespace;
Now create a secondary index without specifying a tablespace:
CREATE INDEX app_users_email_idx
ON demo.app_users (email);
The table uses east_tablespace, but the index does not automatically inherit it.
YugabyteDB documents that indexes are placed in pg_default when no index tablespace is specified. The pg_default placement spreads the index tablets across the cluster according to the cluster placement configuration.
Check the Current Default Tablespace
The PostgreSQL-compatible default_tablespace setting determines the implicit tablespace for objects created without an explicit TABLESPACE clause.
Check the current value:
SHOW default_tablespace;
A typical result is an empty value:
. default_tablespace
--------------------
(1 row)
An empty default_tablespace setting means that the default tablespace of the current database is used. This is normally pg_default. When default_tablespace contains a tablespace name, it acts like an implicit TABLESPACE clause for CREATE TABLE and CREATE INDEX.
TABLESPACE clause, the session’s default_tablespace, or the database default… in that order.Compare the Table and Index Tablespaces
The following query displays the effective tablespace for the table and its secondary indexes.
It handles the special pg_class.reltablespace value of 0, which means that the object uses the current database’s default tablespace.
WITH database_default AS (
SELECT dattablespace
FROM pg_database
WHERE datname = current_database()
)
SELECT
table_namespace.nspname AS schema_name,
table_class.relname AS table_name,
COALESCE(
table_tablespace.spcname,
database_tablespace.spcname
) AS table_tablespace,
index_class.relname AS index_name,
COALESCE(
index_tablespace.spcname,
database_tablespace.spcname
) AS index_tablespace,
CASE
WHEN COALESCE(
NULLIF(table_class.reltablespace, 0),
database_default.dattablespace
)
=
COALESCE(
NULLIF(index_class.reltablespace, 0),
database_default.dattablespace
)
THEN 'MATCH'
ELSE 'MISMATCH'
END AS placement_status
FROM pg_index
JOIN pg_class AS table_class
ON table_class.oid = pg_index.indrelid
JOIN pg_namespace AS table_namespace
ON table_namespace.oid = table_class.relnamespace
JOIN pg_class AS index_class
ON index_class.oid = pg_index.indexrelid
CROSS JOIN database_default
LEFT JOIN pg_tablespace AS table_tablespace
ON table_tablespace.oid =
NULLIF(table_class.reltablespace, 0)
LEFT JOIN pg_tablespace AS index_tablespace
ON index_tablespace.oid =
NULLIF(index_class.reltablespace, 0)
LEFT JOIN pg_tablespace AS database_tablespace
ON database_tablespace.oid =
database_default.dattablespace
WHERE table_namespace.nspname = 'demo'
AND table_class.relname = 'app_users'
AND NOT pg_index.indisprimary
ORDER BY index_class.relname;
The result should look similar to this:
The secondary index was created successfully, but its placement does not match the base table.
Available Approaches
There are several ways to control or correct the index tablespace.
Approach 1: Specify the Tablespace Explicitly
The recommended approach is to include a TABLESPACE clause in the index definition.
First, remove the incorrectly placed demonstration index:
DROP INDEX IF EXISTS demo.app_users_email_idx;
Re-create it in the table’s tablespace:
CREATE INDEX app_users_email_idx
ON demo.app_users (email)
TABLESPACE east_tablespace;
The base table and secondary index now use the same tablespace.
This approach makes the intended placement visible in the DDL and does not depend on session state.
TABLESPACE clause as part of the secondary-index definition. This is especially important for regional tables, geo-partitioned tables, and indexes intended to serve region-local queries. Approach 2: Set default_tablespace for the Session
A migration script that creates several indexes in the same tablespace can temporarily change default_tablespace.
SET default_tablespace = 'east_tablespace';
Create the indexes without individual TABLESPACE clauses:
CREATE INDEX app_users_email_idx
ON demo.app_users (email);
CREATE INDEX app_users_created_at_idx
ON demo.app_users (created_at DESC);
Reset the session when the migration is finished:
RESET default_tablespace;
PostgreSQL-compatible behavior treats a non-empty default_tablespace value as an implicit tablespace selection for CREATE TABLE and CREATE INDEX statements that do not contain their own explicit TABLESPACE clauses.
default_tablespace, create the required indexes, and reset the setting immediately afterward. Explicit tablespace clauses remain preferable because the placement is preserved directly in the DDL. Approach 3: Place the Index Supporting a Unique Constraint
A UNIQUE constraint creates a supporting unique secondary index.
Use USING INDEX TABLESPACE to control the placement of that index:
DROP TABLE IF EXISTS demo.customers CASCADE;
CREATE TABLE demo.customers (
customer_id BIGINT PRIMARY KEY,
email TEXT NOT NULL,
CONSTRAINT customers_email_uk
UNIQUE (email)
USING INDEX TABLESPACE east_tablespace
)
TABLESPACE east_tablespace;
In this example:
- ● The base table uses
east_tablespace. - ● The unique secondary index supporting
customers_email_ukalso useseast_tablespace.
YugabyteDB’s CREATE TABLE syntax supports USING INDEX TABLESPACE as part of the index parameters for a unique constraint.
UNIQUE constraints. :contentReference[oaicite:6]{index=6} Approach 4: Move an Existing Index
An existing secondary index can be assigned to another tablespace using ALTER INDEX:
ALTER INDEX demo.app_users_email_idx
SET TABLESPACE east_tablespace;
YugabyteDB updates the tablespace in the index configuration immediately. The load balancer then moves the index tablet replicas in the background until they satisfy the new placement policy.
Reads and writes remain safe while the data movement is in progress, although location-based query optimization may not be fully accurate until the move completes.
A successful command returns a message similar to:
NOTICE: Data movement for index app_users_email_idx
is successfully initiated.
DETAIL: Data movement is a long running asynchronous process
and can be monitored by checking the tablet placement
in the YB-Master UI.
ALTER INDEX
Find Tablespace Mismatches
The following query lists valid, non-primary indexes whose effective tablespace differs from the effective tablespace of their base table:
WITH database_default AS (
SELECT dattablespace
FROM pg_database
WHERE datname = current_database()
)
SELECT
table_namespace.nspname AS schema_name,
table_class.relname AS table_name,
COALESCE(
table_tablespace.spcname,
database_tablespace.spcname
) AS table_tablespace,
index_class.relname AS index_name,
COALESCE(
index_tablespace.spcname,
database_tablespace.spcname
) AS index_tablespace
FROM pg_index
JOIN pg_class AS table_class
ON table_class.oid = pg_index.indrelid
JOIN pg_namespace AS table_namespace
ON table_namespace.oid = table_class.relnamespace
JOIN pg_class AS index_class
ON index_class.oid = pg_index.indexrelid
CROSS JOIN database_default
LEFT JOIN pg_tablespace AS table_tablespace
ON table_tablespace.oid =
NULLIF(table_class.reltablespace, 0)
LEFT JOIN pg_tablespace AS index_tablespace
ON index_tablespace.oid =
NULLIF(index_class.reltablespace, 0)
LEFT JOIN pg_tablespace AS database_tablespace
ON database_tablespace.oid =
database_default.dattablespace
WHERE table_namespace.nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND index_class.relkind = 'i'
AND NOT pg_index.indisprimary
AND pg_index.indisvalid
AND COALESCE(
NULLIF(table_class.reltablespace, 0),
database_default.dattablespace
)
IS DISTINCT FROM
COALESCE(
NULLIF(index_class.reltablespace, 0),
database_default.dattablespace
)
ORDER BY
table_namespace.nspname,
table_class.relname,
index_class.relname;
This query compares effective tablespace OIDs instead of comparing pg_class.reltablespace directly.
That distinction matters because reltablespace = 0 does not mean that the object has no tablespace. It means that the object uses the current database’s default tablespace.
Generate Remediation Statements
The following query generates ALTER INDEX statements for mismatched secondary indexes when the base table is explicitly assigned to a custom tablespace:
SELECT format(
'ALTER INDEX %I.%I SET TABLESPACE %I;',
index_namespace.nspname,
index_class.relname,
table_tablespace.spcname
) AS remediation_statement
FROM pg_index
JOIN pg_class AS table_class
ON table_class.oid = pg_index.indrelid
JOIN pg_namespace AS table_namespace
ON table_namespace.oid = table_class.relnamespace
JOIN pg_class AS index_class
ON index_class.oid = pg_index.indexrelid
JOIN pg_namespace AS index_namespace
ON index_namespace.oid = index_class.relnamespace
JOIN pg_tablespace AS table_tablespace
ON table_tablespace.oid = table_class.reltablespace
WHERE table_namespace.nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND table_class.reltablespace <> 0
AND index_class.relkind = 'i'
AND NOT pg_index.indisprimary
AND pg_index.indisvalid
AND index_class.reltablespace
IS DISTINCT FROM table_class.reltablespace
ORDER BY
table_namespace.nspname,
table_class.relname,
index_class.relname;
Example output:
ALTER INDEX demo.app_users_email_idx
SET TABLESPACE east_tablespace;
The query generates SQL but does not execute it.
What About Partitioned Tables?
Partitioned tables require an important qualification.
Creating an index recursively on a partitioned parent creates corresponding indexes for its existing partitions. Current YugabyteDB documentation states that these automatically created partition indexes use the default tablespace. YugabyteDB recommends creating indexes separately on each partition when row-level geo-partitioning is being used and each index needs a custom regional tablespace.
For example, assume that the following tablespaces already exist:
- ●
east_tablespace - ●
west_tablespace
Create a partitioned table:
DROP TABLE IF EXISTS demo.orders CASCADE;
CREATE TABLE demo.orders (
region TEXT NOT NULL,
order_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
amount NUMERIC(12,2) NOT NULL,
PRIMARY KEY (region, order_id)
)
PARTITION BY LIST (region);
Create the regional partitions:
CREATE TABLE demo.orders_east
PARTITION OF demo.orders
FOR VALUES IN ('east')
TABLESPACE east_tablespace;
CREATE TABLE demo.orders_west
PARTITION OF demo.orders
FOR VALUES IN ('west')
TABLESPACE west_tablespace;
Create the parent index definition without recursively creating indexes on the existing partitions:
CREATE INDEX orders_customer_date_idx
ON ONLY demo.orders (
customer_id,
order_date DESC
);
The parent partitioned index is initially invalid because its leaf indexes do not yet exist.
Create the East index explicitly:
CREATE INDEX orders_east_customer_date_idx
ON demo.orders_east (
customer_id,
order_date DESC
)
TABLESPACE east_tablespace;
Create the West index explicitly:
CREATE INDEX orders_west_customer_date_idx
ON demo.orders_west (
customer_id,
order_date DESC
)
TABLESPACE west_tablespace;
Attach the leaf indexes to the parent index:
ALTER INDEX demo.orders_customer_date_idx
ATTACH PARTITION demo.orders_east_customer_date_idx;
ALTER INDEX demo.orders_customer_date_idx
ATTACH PARTITION demo.orders_west_customer_date_idx;
After every existing partition has a compatible attached index, YugabyteDB promotes the parent partitioned index out of its invalid state. This pattern also allows each leaf index to use the correct regional tablespace.
A Note About pg_partman
There is one version-specific partitioning exception worth mentioning. In YugabyteDB v2025.1 and later, when pg_partman creates and attaches a new child partition, the child table and its indexes can inherit the tablespaces assigned to their corresponding partitioned parent objects.
This is partition-to-partition inheritance during the attach process. It does not change the general rule that an ordinary secondary index does not inherit the tablespace of its base table.
Final Takeaway
A normal YugabyteDB secondary index does not inherit the tablespace of its base table.
When an index is created without a TABLESPACE clause, YugabyteDB normally places it in pg_default:
CREATE INDEX app_users_email_idx
ON demo.app_users (email);
To align the index with a regional table, specify the placement explicitly:
CREATE INDEX app_users_email_idx
ON demo.app_users (email)
TABLESPACE east_tablespace;
The primary exception involves partition automation. On YugabyteDB v2025.1 and later, a child index created when pg_partman attaches a new partition can receive the tablespace of its corresponding partitioned parent index.
For ordinary tables and indexes, however, the safest rule remains simple:
Resources
Have Fun!
After using the same lawn mower for 16 years across two homes and some pretty big yards, it finally called it quits. Goodbye, old friend… you served me well!
I was a little hesitant about switching to a battery-powered mower, wondering if it would have enough power. After the first mow, though, I’m pleasantly surprised. It handled my lawn with no problem, and I absolutely love it! Best of all, no more gas, no more oil, no more tune-ups… just charge the battery and mow. I wish I’d made the switch sooner.