F.40. pg_csm — estimating compressibility of tables and indexes using CSM#
F.40. pg_csm — estimating compressibility of tables and indexes using CSM #
F.40.1. Overview #
pg_csm is a PostgreSQL extension for diagnostics of the Compression Storage Manager (CSM).
The extension provides an SQL interface to four areas of CSM diagnostics:
Storage manager information. The
smgr_infofunction shows which storage manager is used for a particular relation and returns its parameters — the compression algorithm and the page size.Compression analysis. The
csm_compress_analysisfunction reads the pages of a relation, compresses them in a temporary buffer, and returns statistics for each algorithm. This makes it possible to choose an algorithm and a page size before the data is physically rewritten.Overflow fork statistics. The
csm_overflow_fork_statfunction shows how the overflow fork of a CSM relation is filled: capacity, used and free blocks, and the distribution of batches by size. This is the main tool for assessing the fragmentation of overflow space.CSM cache statistics. The
csm_cache_statfunction returns the accumulated counters of operations and misses for the internal CSM caches in shared memory.
pg_csm is shipped as part of the DBMS distribution. The Compression Storage Manager is implemented at the DBMS core level and is always available, so the extension does not require a separate build of an external storage manager.
F.40.2. Installing the extension #
The extension is installed in the desired database using the standard PostgreSQL command:
CREATE EXTENSION pg_csm;
After installation, the functions smgr_info, csm_compress_analysis, csm_overflow_fork_stat, and csm_cache_stat become available.
F.40.3. Quick start #
Find out which storage manager is used for a table:
SELECT smgr_info('public.orders'::regclass);
Estimate the compressibility of a table with all algorithms:
SELECT *
FROM csm_compress_analysis('public.orders');
View the overflow fork statistics of a CSM table:
CHECKPOINT;
SELECT *
FROM csm_overflow_fork_stat('public.orders'::regclass);
View the statistics of an internal CSM cache:
SELECT * FROM csm_cache_stat('ctl');
F.40.4. Configuring compressed storage #
CSM parameters are set through the storage options of a relation.
Example of creating a table with compression:
CREATE TABLE orders (
id bigint,
customer_id bigint,
payload jsonb
) WITH (
compression = zstd,
compression_page = 1024
);
Example of creating an index with compression:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id)
WITH (
compression = zstd,
compression_page = 4096
);
Storage parameters can be changed via ALTER TABLE or ALTER INDEX:
ALTER TABLE orders
SET (
compression = pglz,
compression_page = 2048
);
ALTER INDEX orders_customer_id_idx
SET (
compression_page = 1024
);
Compression for a relation can be disabled via compression = off:
CREATE TABLE orders_plain (
id bigint,
payload text
) WITH (
compression = off
);
The extension's tests use the following values of compression_page: 1024, 2048, and 4096.
F.40.5. smgr_info #
smgr_info(
rel regclass,
do_switch bool DEFAULT false
)
RETURNS cstring
Returns a string describing the storage manager used for a table or an index. When do_switch = true, the selection of a storage manager for the relation is forcibly completed before the result is printed; user data is not modified.
F.40.5.1. Arguments #
| Argument | Type | Default | Description |
|---|---|---|---|
rel | regclass | — | Table or index. |
do_switch | bool | false | If true, a particular storage manager is selected before the information is printed. Useful when the relation is still in the selection state (Switcher). |
F.40.5.2. Return value #
Possible values of the returned string:
| Value | Description |
|---|---|
Switcher | A particular storage manager has not yet been selected. |
Storage Manager with compression (algorithm: ..., page size: ...) | The relation uses the Compression Storage Manager. |
Magnatic disk | The relation uses the regular PostgreSQL disk storage manager. |
The storage manager defined by the extension | An external storage manager defined by an extension is used. |
F.40.5.3. Examples #
Check the storage manager of a table:
SELECT smgr_info('public.orders'::regclass);
smgr_info
----------------------------------------------------------------------
Storage Manager with compression (algorithm: zstd, page size: 1024)
(1 row)
Complete the selection of a storage manager and immediately get the result:
SELECT smgr_info('public.orders'::regclass, true);
smgr_info
----------------------------------------------------------------------
Storage Manager with compression (algorithm: zstd, page size: 1024)
(1 row)
F.40.5.4. How to interpret the result #
If the function returns Switcher, a storage manager for the relation has not yet been determined. Calling the function again with do_switch = true will complete the selection and return the final value.
For a CSM relation, the returned string contains the algorithm and the page size that were set via compression and compression_page.
F.40.6. csm_compress_analysis #
csm_compress_analysis(
rel regclass,
compress_alg text DEFAULT 'all',
sample_rate float8 DEFAULT 1.0
)
RETURNS TABLE (
reloid oid,
page_cnt int4,
alg_name text,
avg_compress float4,
avg_page_sz int8,
pages_over_1k int4,
pages_over_2k int4,
pages_over_4k int4
)
Reads the pages of a relation, compresses them in a temporary buffer, and returns aggregated statistics for each algorithm. The data of the relation is not modified.
F.40.6.1. Arguments #
| Argument | Type | Default | Description |
|---|---|---|---|
rel | regclass | — | Name or OID of a table/index. It is recommended to specify a schema-qualified name, for example 'public.orders'. |
compress_alg | text | 'all' | Compression algorithm. Allowed values: all, zstd, pglz, lz4, cpy. The value all runs the analysis for all available algorithms. |
sample_rate | float8 | 1.0 | Fraction of pages to analyze. The value must be greater than 0.0 and less than or equal to 1.0. |
F.40.6.2. Returned columns #
| Column | Type | Description |
|---|---|---|
reloid | oid | OID of the analyzed relation. |
page_cnt | int4 | Number of pages used in the analysis. |
alg_name | text | Compression algorithm. |
avg_compress | float4 | Average compression ratio: the ratio of the average size of a compressed page to the size of the original page. The smaller the value, the stronger the compression. |
avg_page_sz | int8 | Average page size after compression, in bytes. |
pages_over_1k | int4 | Number of pages whose size after compression exceeded 1 KB. |
pages_over_2k | int4 | Number of pages whose size after compression exceeded 2 KB. |
pages_over_4k | int4 | Number of pages whose size after compression exceeded 4 KB. |
If the relation contains no pages, page_cnt is equal to 0, and the statistical columns are returned as NULL.
F.40.6.3. Examples #
Compare all algorithms and sort the result by the average page size:
SELECT *
FROM csm_compress_analysis('public.orders', 'all', 1.0)
ORDER BY avg_page_sz;
reloid | page_cnt | alg_name | avg_compress | avg_page_sz | pages_over_1k | pages_over_2k | pages_over_4k --------+----------+----------+--------------+-------------+---------------+---------------+--------------- 16384 | 1250 | zstd | 0.38 | 3113 | 1180 | 948 | 118 16384 | 1250 | lz4 | 0.44 | 3604 | 1212 | 1047 | 309 16384 | 1250 | pglz | 0.47 | 3850 | 1224 | 1103 | 421 16384 | 1250 | cpy | 0.53 | 4341 | 1238 | 1186 | 702 (4 rows)
Quickly estimate a large table over 5% of its pages:
SELECT *
FROM csm_compress_analysis('public.orders', 'all', 0.05)
ORDER BY avg_compress;
reloid | page_cnt | alg_name | avg_compress | avg_page_sz | pages_over_1k | pages_over_2k | pages_over_4k --------+----------+----------+--------------+-------------+---------------+---------------+--------------- 16384 | 63 | zstd | 0.37 | 3031 | 58 | 46 | 6 16384 | 63 | lz4 | 0.43 | 3522 | 60 | 51 | 15 16384 | 63 | pglz | 0.46 | 3768 | 61 | 54 | 20 16384 | 63 | cpy | 0.52 | 4260 | 62 | 58 | 34 (4 rows)
Check only zstd:
SELECT *
FROM csm_compress_analysis('public.orders', 'zstd');
reloid | page_cnt | alg_name | avg_compress | avg_page_sz | pages_over_1k | pages_over_2k | pages_over_4k --------+----------+----------+--------------+-------------+---------------+---------------+--------------- 16384 | 1250 | zstd | 0.38 | 3113 | 1180 | 948 | 118 (1 row)
Analyze an index:
SELECT *
FROM csm_compress_analysis('public.orders_customer_id_idx', 'all');
reloid | page_cnt | alg_name | avg_compress | avg_page_sz | pages_over_1k | pages_over_2k | pages_over_4k --------+----------+----------+--------------+-------------+---------------+---------------+--------------- 16402 | 275 | zstd | 0.21 | 1720 | 201 | 48 | 0 16402 | 275 | lz4 | 0.26 | 2130 | 231 | 89 | 0 16402 | 275 | pglz | 0.29 | 2376 | 249 | 121 | 2 16402 | 275 | cpy | 0.33 | 2703 | 261 | 158 | 8 (4 rows)
F.40.6.4. How to interpret the result #
The main fields are avg_compress, avg_page_sz, and the threshold counters pages_over_1k, pages_over_2k, pages_over_4k.
avg_compress = 0.45 means that the average compressed page occupies about 45% of the original size.
If avg_page_sz is significantly smaller than the chosen compression_page, the configured page size is sufficient for most of the data.
If many pages fall into pages_over_4k, a larger compression_page or a different algorithm may be required.
If zstd provides the best compression but the workload is sensitive to CPU, the cost of compression and decompression should additionally be evaluated on the actual workload.
F.40.6.5. Limitations #
Only regular tables and indexes are supported. Views, sequences, and other types of relations will return an error.
A full analysis of a large relation may take a noticeable amount of time: pages are read and compressed in the backend process.
With
sample_rate < 1.0, the result is approximate.avg_compressandavg_page_szdescribe the expected compressibility of pages, but do not replace performance verification on the actual workload.
F.40.7. csm_overflow_fork_stat #
csm_overflow_fork_stat(
rel regclass,
OUT relname text,
OUT is_csm boolean,
OUT compression_alg text,
OUT compression_page_size int4,
OUT overflow_physical_blocks int8,
OUT overflow_extent_count int8,
OUT overflow_data_capacity_blocks int8,
OUT used_blocks int8,
OUT free_blocks int8,
OUT overflow_batches int8,
OUT overflow_blocks_in_batches int8,
OUT avg_batch_len float8,
OUT batch_count_by_len int8[],
OUT block_count_by_batch_len int8[]
)
Returns the overflow fork statistics for the specified relation. The overflow fork stores compressed CSM pages that do not fit into a standard PostgreSQL block. The relation is opened with AccessShareLock; for a non-CSM relation all fields except relname and is_csm are returned as NULL.
For up-to-date statistics it is recommended to execute CHECKPOINT before the call — otherwise unflushed dirty buffers will not be reflected in the result.
F.40.7.1. Arguments #
| Argument | Type | Default | Description |
|---|---|---|---|
rel | regclass | — | Table or index. The relation must have physical storage. |
F.40.7.2. Returned columns #
| Column | Type | Description |
|---|---|---|
relname | text | Schema-qualified name of the relation. |
is_csm | boolean | true if the relation uses CSM. |
compression_alg | text | Compression algorithm (zstd, pglz, lz4, cpy) or off. NULL for a non-CSM relation. |
compression_page_size | int4 | Configured size of a compressed page in KB. NULL for a non-CSM relation. |
overflow_physical_blocks | int8 | Number of physical blocks in the overflow fork, including extent headers. |
overflow_extent_count | int8 | Number of overflow extents. |
overflow_data_capacity_blocks | int8 | Number of data blocks: overflow_physical_blocks − overflow_extent_count. |
used_blocks | int8 | Number of data blocks marked as used in the bitmap extent. |
free_blocks | int8 | Number of data blocks marked as free in the bitmap extent. |
overflow_batches | int8 | Number of overflow batches found. |
overflow_blocks_in_batches | int8 | Total number of blocks in all found batches. |
avg_batch_len | float8 | Average batch length in blocks, rounded to two decimal places. |
batch_count_by_len | int8[] | Histogram: element [N] is the number of batches with length N blocks (1–8). |
block_count_by_batch_len | int8[] | Histogram: element [N] is the number of blocks in batches of length N (1–8). |
F.40.7.3. Examples #
Statistics for a non-CSM table:
SELECT relname, is_csm
FROM csm_overflow_fork_stat('public.orders_plain'::regclass);
relname | is_csm
---------------------+--------
public.orders_plain | f
(1 row)
Statistics for a CSM table after data insertion:
CHECKPOINT;
SELECT *
FROM csm_overflow_fork_stat('public.orders'::regclass);
relname | is_csm | compression_alg | compression_page_size | overflow_physical_blocks | overflow_extent_count | overflow_data_capacity_blocks | used_blocks | free_blocks | overflow_batches | overflow_blocks_in_batches | avg_batch_len | batch_count_by_len | block_count_by_batch_len
---------------+--------+-----------------+-----------------------+--------------------------+-----------------------+-------------------------------+-------------+-------------+------------------+----------------------------+---------------+------------------------+--------------------------
public.orders | t | zstd | 1 | 512 | 1 | 511 | 304 | 207 | 38 | 304 | 8 | {0,0,0,0,0,0,0,38} | {0,0,0,0,0,0,0,304}
(1 row)
F.40.7.4. How to interpret the result #
used_blocks + free_blocks must match overflow_data_capacity_blocks.
An avg_batch_len close to 8 indicates that most of the data is packed into maximum-size batches — the overflow fork is used efficiently.
A high value of free_blocks relative to overflow_data_capacity_blocks indicates fragmentation of the overflow space.
F.40.7.5. Limitations #
The function reflects the state of the overflow fork at the moment of the last
CHECKPOINT. Unflushed dirty buffers are not included in the result.A relation without physical storage (views and similar) will return an error.
F.40.8. csm_cache_stat #
csm_cache_stat(
cache_name text
)
RETURNS TABLE (
get_total bigint,
get_miss bigint,
put_total bigint,
put_evict bigint,
blocks_used bigint
)
Returns the accumulated operation counters for an internal CSM cache in shared memory. Intended for diagnostics of caching efficiency. The statistics are accumulated since cluster startup and are not reset.
F.40.8.1. Arguments #
| Argument | Type | Default | Description |
|---|---|---|---|
cache_name | text | — | Cache name. Allowed values: ctl (cache of CSM control structures), ovr (cache of overflow page sizes), extent (cache of overflow extents). |
F.40.8.2. Returned columns #
| Column | Type | Description |
|---|---|---|
get_total | bigint | Total number of read operations from the cache. |
get_miss | bigint | Number of misses: data was not found in the cache. |
put_total | bigint | Total number of write operations to the cache. |
put_evict | bigint | Number of evictions from the cache. |
blocks_used | bigint | Current number of occupied (pinned) entries in the cache. |
F.40.8.3. Examples #
Get the statistics of the control structures cache:
SELECT * FROM csm_cache_stat('ctl');
get_total | get_miss | put_total | put_evict | blocks_used
-----------+----------+-----------+-----------+-------------
1024 | 12 | 512 | 0 | 8
(1 row)
A request for a non-existent cache returns an error:
SELECT * FROM csm_cache_stat('unknown');
-- ERROR: storage manager cache 'unknown' not exist
F.40.8.4. How to interpret the result #
The miss ratio (get_miss / get_total) is the main indicator of cache efficiency. A high value may indicate insufficient shared memory size for CSM.
A growing put_evict means that the cache is overflowing and is forced to evict entries; with frequent evictions it is worth considering an increase of the CSM memory parameters.
F.40.8.5. Limitations #
The statistics are accumulated since cluster startup and are not reset.
Allowed values of
cache_name: onlyctl,ovr,extent. Any other value will return an error.
F.40.9. Notes and limitations #
All functions operate in read-only mode: relation data is not modified, the lock taken is
AccessShareLock.The Compression Storage Manager is implemented at the DBMS core level;
pg_csmprovides an SQL interface for diagnostics and does not require a separate build of an external storage manager.