F.46. pg_csm — estimating compressibility of tables and indexes using CSM#
F.46. pg_csm — estimating compressibility of tables and indexes using CSM #
Version: 1.0
F.46.1. Overview #
pg_csm is a PostgreSQL extension for diagnostics of the Compression Storage Manager (CSM).
The extension provides an SQL interface for 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.Statistics of already compressed storage. The
csm_compressed_statfunction outputs the actual statistics of the compressed data.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.46.2. Installing the extension #
The extension is installed in the desired database using the standard PostgreSQL command:
CREATE EXTENSION pg_csm;
F.46.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 actual statistics of an already compressed CSM table:
CHECKPOINT;
SELECT *
FROM csm_compressed_stat('public.orders'::regclass);
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.46.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.46.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.46.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.46.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.46.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.46.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.46.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.46.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.46.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.46.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.46.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.46.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.46.7. csm_compressed_stat #
csm_compressed_stat(
rel regclass,
OUT relname text,
OUT reloid oid,
OUT alg_name text,
OUT compression_page int8,
OUT page_cnt int8,
OUT compressed_size int8,
OUT avg_compress float8,
OUT avg_page_sz int8,
OUT pages_over_1k int8,
OUT pages_over_2k int8,
OUT pages_over_4k int8,
OUT uncompressed_pages int8,
OUT pages_with_overflow int8,
OUT overflow_blocks int8,
OUT avg_overflow_blocks int8
)
Returns the actual statistics of already compressed CSM storage for the specified relation. Unlike csm_compress_analysis, the function does not choose an algorithm and does not perform trial compression.
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.46.7.1. Arguments #
| Argument | Type | Default | Description |
|---|---|---|---|
rel | regclass | — | CSM table or CSM index. The relation must use the Compression Storage Manager. |
F.46.7.2. Returned columns #
| Column | Type | Description |
|---|---|---|
relname | text | Name of the relation. |
reloid | oid | OID of the examined relation. |
alg_name | text | Compression algorithm configured for the relation. |
compression_page | int8 | Configured size of a CSM main page, in bytes. |
page_cnt | int8 | Number of pages in the CSM main fork. |
compressed_size | int8 | Total size of CSM main pages according to the formula data_len + sizeof(CsmMainPageHeaderData), in bytes. |
avg_compress | float8 | Average compression ratio: compressed_size / (page_cnt * BLCKSZ), rounded to four decimal places. Includes the overhead of the CSM header. NULL if there are no pages. |
avg_page_sz | int8 | Average size of a CSM main page per page according to compressed_size / page_cnt, in bytes. NULL if there are no pages. |
pages_over_1k | int8 | Number of pages whose data_len is greater than 1 KB. |
pages_over_2k | int8 | Number of pages whose data_len is greater than 2 KB. |
pages_over_4k | int8 | Number of pages whose data_len is greater than 4 KB. |
uncompressed_pages | int8 | Number of pages stored as uncompressed payload of size BLCKSZ. |
pages_with_overflow | int8 | Number of pages that reference overflow blocks. |
overflow_blocks | int8 | Total number of overflow blocks referenced by CSM pages. |
avg_overflow_blocks | int8 | Average number of overflow blocks per page with overflow. NULL if there are no such pages. |
F.46.7.3. Examples #
Check the actual statistics of a CSM table:
CHECKPOINT;
SELECT *
FROM csm_compressed_stat('public.orders'::regclass);
relname | reloid | alg_name | compression_page | page_cnt | compressed_size | avg_compress | avg_page_sz | pages_over_1k | pages_over_2k | pages_over_4k | uncompressed_pages | pages_with_overflow | overflow_blocks | avg_overflow_blocks
---------------+--------+----------+------------------+----------+-----------------+--------------+-------------+---------------+---------------+---------------+--------------------+---------------------+-----------------+---------------------
orders | 16384 | zstd | 1024 | 1250 | 3891250 | 0.3800 | 3113 | 1180 | 948 | 118 | 0 | 118 | 944 | 8
(1 row)
For an empty CSM relation, average values are returned as NULL.
F.46.8. 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.46.8.1. Arguments #
| Argument | Type | Default | Description |
|---|---|---|---|
rel | regclass | — | Table or index. The relation must have physical storage. |
F.46.8.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.46.8.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.46.8.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.46.8.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.46.9. 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.46.9.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.46.9.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.46.9.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.46.9.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.46.9.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.46.10. 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.