DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

GBase 8c Tablespace Planning and Object Migration

#gbase #database #数据库 #operations

A tablespace in GBase 8c maps database objects to a specific directory on disk. Many on‑site problems arise not because DBAs can't create a tablespace, but because they start planning too late — after the default location is already full. This guide covers directory preparation, tablespace creation, placing new objects correctly, and batch migrating existing objects in a gbase database.

When to Consider Custom Tablespaces

Create separate tablespaces when hot tables and archives compete for I/O, when indexes are the real bottleneck, or when the default path is near capacity. However, in standardized cloud environments, additional tablespaces may not be necessary — always verify that physical isolation is truly needed.

Scenario Recommendation
Hot and historical tables mixed in default path Create ts_hot and ts_hist
Indexes are the I/O bottleneck Plan a separate tablespace for hot indexes
Default path is nearly full Point new objects to a new tablespace (safer than full migration)
Temporary sorts fight with business data Evaluate a dedicated temporary tablespace

Prepare Directories First

Tablespaces rely on existing directories on every data node. Create them ahead of time with the correct ownership and permissions, and verify they are empty before use.

for p in /data1/gbase/ts_hot /data2/gbase/ts_hist /data3/gbase/ts_temp
do
  mkdir -p "$p"
  chown -R gbase:gbase "$p"
  chmod 700 "$p"
done

# Confirm directories are empty before creating the tablespace
for p in /data1/gbase/ts_hot /data2/gbase/ts_hist /data3/gbase/ts_temp
do
  echo "== $p =="
  ls -la "$p"
done
Enter fullscreen mode Exit fullscreen mode

Create Tablespaces with Meaningful Names

Avoid generic names like space1. Include the purpose in the name so that the storage layout remains understandable months later.

CREATE TABLESPACE ts_hot  LOCATION '/data1/gbase/ts_hot';
CREATE TABLESPACE ts_hist LOCATION '/data2/gbase/ts_hist';
CREATE TABLESPACE ts_temp LOCATION '/data3/gbase/ts_temp';

GRANT CREATE ON TABLESPACE ts_hot  TO app_rw;
GRANT CREATE ON TABLESPACE ts_hist TO app_rw;
Enter fullscreen mode Exit fullscreen mode

Place New Objects in the Right Tablespace from the Start

Always specify the tablespace when creating large tables and indexes. Retrofitting is far more disruptive.

CREATE TABLE acct.bill_detail (
    bill_id        bigint primary key,
    acct_no        varchar(32) not null,
    trade_date     date not null,
    trade_amount   numeric(18,2) not null,
    status         varchar(16) not null,
    created_time   timestamp not null default current_timestamp
) TABLESPACE ts_hot;

CREATE INDEX idx_bill_detail_01
ON acct.bill_detail(trade_date, acct_no)
TABLESPACE ts_hot;
Enter fullscreen mode Exit fullscreen mode

For a batch of objects in the same session, set the default tablespace to avoid omissions:

SET default_tablespace = ts_hist;
CREATE TABLE acct.bill_detail_2024m12 (LIKE acct.bill_detail INCLUDING DEFAULTS);
Enter fullscreen mode Exit fullscreen mode

Temporary objects can be directed separately:

SET temp_tablespaces = 'ts_temp';
CREATE TEMP TABLE tmp_bill_check AS
SELECT * FROM acct.bill_detail WHERE trade_date >= date '2024-12-01';
Enter fullscreen mode Exit fullscreen mode

Migrating Existing Objects

Before moving anything, inventory the current object distribution:

SELECT
    n.nspname AS schema_name,
    c.relname AS object_name,
    c.relkind AS object_type,
    COALESCE(t.spcname, 'pg_default') AS tablespace_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE n.nspname = 'acct'
  AND c.relkind IN ('r', 'i')
ORDER BY n.nspname, c.relkind, c.relname;
Enter fullscreen mode Exit fullscreen mode

Generate migration commands for historical tables:

SELECT format(
    'ALTER TABLE %I.%I SET TABLESPACE ts_hist;',
    schemaname, tablename
)
FROM pg_tables
WHERE schemaname = 'acct' AND tablename LIKE 'bill_detail_2024%';
Enter fullscreen mode Exit fullscreen mode

And for their indexes:

SELECT format(
    'ALTER INDEX %I.%I SET TABLESPACE ts_hist;',
    schemaname, indexname
)
FROM pg_indexes
WHERE schemaname = 'acct' AND tablename LIKE 'bill_detail_2024%';
Enter fullscreen mode Exit fullscreen mode

Execute the generated commands in small batches: validate a few objects first to observe lock waits and I/O impact, then proceed with larger objects.

Common Pitfalls

  • Moving tables but forgetting indexes — the I/O profile barely changes.
  • Treating tablespaces as a space reclamation tool instead of addressing object bloat.
  • Migrating large objects during peak hours, causing unexpected latency.
  • Naming paths inconsistently, making future maintenance difficult.
  • Trying to drop a tablespace that still contains objects — it will fail.

Summary

Tablespaces control physical placement, not logical modeling. Placing objects correctly at creation time avoids the majority of migration headaches. When migration is necessary, always inventory everything, handle tables and indexes separately, and move in small, verified batches to keep your gbase database stable.

GBASE's GBase 8c provides the flexibility to design a storage layout that matches your workload. Looking ahead, a little planning upfront will save you from panic‑driven migrations and keep your analytical and transactional workloads running on the right storage tier.

Top comments (0)