ApsaraDB RDS:Use the TPC feature

Step 1: Create the TPC tablespace

Run the following statement to create the required tablespace:

CREATE TABLESPACE rds_compress LOCATION '/data/postgresql/rds_compress' WITH(COMPRESSION='zstd');

Step 2: Enable TPC for tables and indexes

Assign existing or new tables and indexes to the rds_compress tablespace.

For a new table:

CREATE TABLE <tablename> ... TABLESPACE rds_compress;

For an existing table:

ALTER TABLE <tablename> SET TABLESPACE rds_compress;

For a new index:

CREATE INDEX <indexname> ... TABLESPACE rds_compress;

For an existing index:

ALTER INDEX <indexname> SET TABLESPACE rds_compress;

(Optional) Set TPC as the default tablespace

To apply TPC automatically to all new tables and indexes without specifying the tablespace each time, set the session default:

SET default_tablespace TO 'rds_compress';

After this, any CREATE TABLE or CREATE INDEX statement without an explicit TABLESPACE clause uses TPC:

CREATE TABLE <tablename> ...;
CREATE INDEX <indexname> ...;

Check whether a table uses TPC

In the psql command line, run:

\d+ <tablename>

If the table and its indexes are in the rds_compress tablespace, TPC is active. The following is an example output for the sbtest1 table:

Table "public.sbtest1"
 Column |      Type      | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description
--------+----------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 id     | integer        |           | not null | nextval('sbtest1_id_seq'::regclass) | plain    |             |              |
 k      | integer        |           | not null | 0                                   | plain    |             |              |
 c      | character(120) |           | not null | ''::bpchar                          | extended |             |              |
 pad    | character(60)  |           | not null | ''::bpchar                          | extended |             |              |
Indexes:
    "sbtest1_pkey" PRIMARY KEY, btree (id), tablespace "rds_compress"
    "k_1" btree (k), tablespace "rds_compress"
Tablespace: "rds_compress"
Access method: heap

Monitor storage savings

Use the following PostgreSQL functions to check the actual size of compressed data:

Calculate the compression ratio

SELECT pg_relation_size('<tablename>')::float / (relpages * 8192)
FROM pg_class
WHERE relname = '<tablename>';

This query divides the actual on-disk size (from pg_relation_size) by the uncompressed logical size (relpages × 8192 bytes, where 8192 is PostgreSQL's default page size). A lower result means higher compression — for example, 0.5 means pages are compressed to half their original size.

Test 1: I/O-saturated conditions (64 threads)

Sysbench parameters: --tables=100 --table-size=<400000, 4000000, or 32000000> --report-interval=1 --time=100 --threads=64

Table size per dataset: 400,000 rows (8 GB), 4,000,000 rows (80 GB), 32,000,000 rows (640 GB).

Read-only workload (oltp_read_only)

Write-only workload (oltp_write_only)

Test 2: Normal conditions (4 threads, 640 GB dataset)

Sysbench parameters: --tables=100 --table-size=32000000 --report-interval=1 --time=100 --threads=4

What the results show

Enable TPC when your dataset is large and I/O is the bottleneck:

• At 640 GB with saturated I/O and a read workload, TPS doubled (2,222 to 4,508) after enabling TPC.

• Even in normal (non-saturated) conditions, read TPS improved modestly (720 to 795) at the cost of slightly higher CPU.

Hold off on TPC when write performance is critical:

• At 80 GB and 640 GB, write TPS dropped significantly with TPC (7,044 to 5,493 and 2,375 to 1,245 respectively).

• The 8 GB dataset showed no write penalty — the entire dataset fits in the buffer pool, so compression saves I/O without bottlenecking writes.