64.3. Compression Storage Manager

64.3. Compression Storage Manager
Prev	Up	Chapter 64. Database Physical Storage	Home	Next

---

64.3. Compression Storage Manager #

64.3.1. Overview

64.3.2. Comparison with Other Compression Mechanisms

64.3.3. Supported Objects

64.3.4. Storage Parameters

64.3.5. How CSM Stores Data

64.3.6. Choosing Compression Parameters

64.3.7. Changing Parameters of Existing Objects

64.3.8. Performance and Maintenance

64.3.9. Configuration

64.3.10. Diagnostics

64.3.11. Limitations

64.3.12. Examples

Compression Storage Manager (CSM) is a physical storage mechanism that stores pages of supported tables, materialized views, and indexes on disk in compressed form. From the perspective of users and applications, these objects remain ordinary PostgreSQL objects: they are accessed with the same SQL commands, while compression and decompression happen transparently at the storage manager level.

CSM helps reduce on-disk data size and can lower the amount of I/O for objects that compress well. When pages are written, they are compressed with the selected algorithm; when they are read, they are restored to the normal PostgreSQL page format. CSM is controlled through the storage parameters of the object itself, so compression can be enabled and tuned separately for specific tables, materialized views, and indexes.

64.3.1. Overview #

CSM works at the storage manager level. This means that it is applied to physical relation pages, not to individual rows or column values. PostgreSQL continues to use ordinary pages in memory, while CSM can store those pages more compactly on disk.

Compression is enabled with storage parameters:

compression = pglz | lz4 | zstd
compression_page = 1024 | 2048 | 4096

If compression = off is specified, CSM is not used for the relation.

In practice, CSM is useful for large tables and indexes where saving disk space and reducing I/O can be more important than the additional CPU work required for compression and decompression.

64.3.2. Comparison with Other Compression Mechanisms #

CSM does not replace TOAST or column-level compression. These mechanisms solve different problems and operate at different levels.

Mechanism	Level	Main purpose
TOAST	Large values of individual varlena attributes	Store values that do not fit inline in a row
Column-level compression	Individual column values	Compress values before they are stored in a tuple
CSM	Physical relation pages	Compress physical storage for tables, materialized views, and indexes

TOAST is used for large values, such as large text, bytea, or jsonb values. Column-level compression controls compression of individual column values. CSM works below that: it receives already formed relation pages and compresses their physical representation.

Because of this, CSM can be applied not only to heap tables, but also to supported indexes. This is an important difference from mechanisms focused only on table data.

64.3.3. Supported Objects #

64.3.3.1. Tables #

CSM is supported for regular tables that use the heap access method.

CREATE TABLE events (
    id bigint,
    created_at timestamptz,
    payload jsonb
) WITH (
    compression = zstd,
    compression_page = 2048
);

64.3.3.2. Indexes #

CSM is supported for indexes that use the btree and hash access methods.

CREATE INDEX events_created_at_idx
ON events USING btree (created_at)
WITH (
    compression = lz4,
    compression_page = 2048
);

Indexes that use other access methods should not be considered supported by CSM unless this is explicitly documented for the specific product version.

64.3.3.3. Materialized Views #

Materialized views that use heap storage can use CSM in the same way as regular heap tables.

CREATE MATERIALIZED VIEW recent_events
WITH (
    compression = zstd,
    compression_page = 2048
) AS
SELECT *
FROM events
WHERE created_at >= now() - interval '30 days';

64.3.3.4. Support Summary #

Object or access method	CSM support
Regular table with the heap access method	Yes
Materialized view with heap storage	Yes
Index with the btree access method	Yes
Index with the hash access method	Yes
Table with the tde_heap access method	No
Table with an access method other than heap	No
Index with an access method other than btree and hash	No
System catalog	No
Partitioned parent table or index without its own physical storage	No
Temporary relation	Not documented as a supported scenario

64.3.3.5. Unsupported Objects #

CSM is not applied to objects that do not have suitable physical storage or a supported access method.

Such objects include:

• regular views;

• sequences;

• foreign tables;

• partitioned parent tables and indexes without their own physical storage;

• tables of system catalogs;

• tables with access methods other than heap;

• materialized views with access methods other than heap;

• indexes with access methods other than btree and hash;

• temporary relations in the current implementation.

For partitioned tables, CSM parameters should be set on physical partitions, if those partitions support CSM.

64.3.4. Storage Parameters #

CSM is controlled by two object storage parameters:

• compression;

• compression_page.

These parameters are specified in WITH (...) when a supported object is created, or changed with ALTER ... SET (...) for an existing object.

64.3.4.1. compression #

The compression parameter sets the compression algorithm that CSM uses for relation pages.

compression = off | pglz | lz4 | zstd

Documented values:

Value	Behavior
off	CSM is disabled and standard storage is used
pglz	PostgreSQL's built-in PGLZ algorithm is used
lz4	LZ4 is used
zstd	Zstandard is used

Default: off.

If compression = off, the compression_page parameter does not affect the physical storage of the object.

64.3.4.2. compression_page #

The compression_page parameter sets the main fork page size that CSM uses for compressed page records.

compression_page = 1024 | 2048 | 4096

Default: 4096.

The smaller the compression_page, the more compact the main fork can be. However, if a compressed page record does not fit in the selected size, CSM uses overflow storage. Therefore, a value that is too small can increase the number of overflow fork accesses and reduce the expected benefit.

64.3.4.3. Where Parameters Are Set #

CSM parameters can be specified when supported objects are created:

CREATE TABLE ... WITH (...);
CREATE INDEX ... WITH (...);
CREATE MATERIALIZED VIEW ... WITH (...) AS ...;

For existing objects, parameters can be changed with:

ALTER TABLE ... SET (...);
ALTER INDEX ... SET (...);
ALTER MATERIALIZED VIEW ... SET (...);

64.3.5. How CSM Stores Data #

CSM sits between the buffer manager and the low-level file storage manager. The general path can be represented as follows:

SQL commands
    |
Table / Index access method
    |
Buffer Manager
    |
Storage Manager Switcher
    |
    +-- standard storage manager
    |
    +-- Compression Storage Manager
            |
            +-- main fork
            +-- overflow fork
            +-- CSM metadata
            +-- CSM caches

CSM stores the main part of a compressed page in the main fork. If a compressed page record does not fit in the selected compression_page, the remaining part is stored in the overflow fork.

In regular PostgreSQL physical storage, a relation can have several fork files, such as main, fsm, vm, and init. CSM extends this layout with service forks and changes the contents of the main fork for CSM objects:

• main fork stores compressed page records. The size of such a record is determined by the selected compression_page: 1024, 2048, or 4096 bytes;

• ctl fork stores CSM metadata that lets the storage manager determine that the relation uses CSM and which compression parameters are applied;

• overflow fork is used only for compressed pages that do not fit in the selected compression_page;

• standard PostgreSQL forks, such as fsm, vm, and init, keep their usual role and are not used to store compressed page contents.

This distinction is transparent to upper PostgreSQL layers: table access methods, index access methods, and the buffer manager work with ordinary PostgreSQL pages. What changes is the physical representation of those pages on disk.

CSM also uses relation metadata. This metadata allows the storage manager to determine which storage method is used for the object and with which parameters. It is part of the object's physical storage and must be preserved together with relation files during physical backup, restore, and replication.

On read, CSM returns a normal PostgreSQL page. On write, CSM receives a normal page from PostgreSQL and decides how to store it on disk: compressed, with overflow storage, or without an actual size reduction if the page does not compress well.

When estimating the physical size of a CSM object, keep in mind that savings in the main fork can be accompanied by data in the overflow fork. For analysis, it is therefore useful to look not only at the total relation size, but also at overflow storage statistics through pg_csm.

64.3.6. Choosing Compression Parameters #

Choosing CSM parameters depends on the data and workload. There is no universal setting that works equally well for all tables and indexes.

Recommended workflow:

1. Choose a table, materialized view, or index.

2. Estimate compressibility on real data.

3. Compare pglz, lz4, and zstd.

4. Estimate how many pages fit into 1 KB, 2 KB, and 4 KB after compression.

5. Choose compression.

6. Choose compression_page.

7. Apply the parameters.

8. For an existing object, perform a physical rewrite or rebuild if required.

9. Compare the relation size before and after.

10. Test the behavior on the real workload.

64.3.6.1. Choosing compression #

Use off if the relation is small, the data compresses poorly, minimal CPU overhead is important, or standard physical storage is required.

Use pglz if you want PostgreSQL's built-in compression algorithm and predictable behavior without focusing on the maximum compression ratio.

Use lz4 if compression and decompression speed and low latency are important. It is often a good candidate for read-heavy or mixed workloads where a balance between space savings and CPU cost is needed.

Use zstd if the main goal is to maximize disk and I/O savings, and the workload can tolerate additional CPU cost. It is usually worth testing zstd on large relations where the potential size reduction is noticeable.

64.3.6.2. Choosing compression_page #

Consider 1024 if most pages fit into 1 KB after compression. This value can provide the largest main fork savings, but it increases the risk of overflow for pages that compress less effectively.

2048 is a middle-ground option when a noticeable part of the pages does not fit into 1 KB but does fit into 2 KB.

4096 is the default. It is the most conservative choice when data compresses moderately or preliminary analysis has not yet been performed.

64.3.6.3. Relation to pg_csm #

Use the pg_csm extension for preliminary analysis. It does not enable CSM and does not change the physical storage of the relation; instead, it helps you understand in advance what effect to expect from different algorithms and compression_page values.

The main function for choosing parameters is csm_compress_analysis. It reads relation pages, compresses them in a temporary buffer, and returns statistics for the algorithms. When choosing compression and compression_page, look primarily at:

• avg_compress: average compression ratio;

• avg_page_sz: average page size after compression;

• pages_over_1k, pages_over_2k, pages_over_4k: how many pages do not fit into the corresponding size.

For example, if almost all pages fit into 2 KB with the selected algorithm, compression_page = 2048 can be a good compromise. If many pages fall into pages_over_2k or pages_over_4k, consider 4096 or another algorithm to reduce overflow storage usage.

After CSM is enabled, pg_csm can also be used to check the result: smgr_info shows which storage manager and parameters the relation uses, and csm_overflow_fork_stat helps assess actual overflow fork usage. CSM cache statistics are more relevant for operational diagnostics than for the initial choice of parameters.

This page does not duplicate the pg_csm reference. For detailed function descriptions, arguments, and returned columns, see the pg_csm extension documentation.

64.3.7. Changing Parameters of Existing Objects #

Changing storage parameters defines how the object should be stored going forward. For existing data, it is important to understand whether a physical rewrite is required.

64.3.7.1. ALTER TABLE ... SET (...) #

For tables, changing compression or compression_page can cause a physical rewrite of the object if the new CSM parameters differ from the previous ones.

ALTER TABLE events
SET (
    compression = zstd,
    compression_page = 1024
);

After a physical rewrite, table pages are written with the current CSM parameters. To evaluate the effect of the change, compare the relation size before and after the operation and test the workload.

64.3.7.2. ALTER INDEX ... SET (...) #

For indexes, changing CSM parameters can require rebuilding the index.

ALTER INDEX events_created_at_idx
SET (
    compression = zstd,
    compression_page = 2048
);

If you need to ensure that an index is written with the new parameters, use the rebuild scenario or REINDEX supported by your product version.

64.3.7.3. ALTER MATERIALIZED VIEW ... SET (...) #

For materialized views that use heap storage, changing CSM parameters is handled similarly to heap tables: to apply new parameters to existing physical contents, a physical rewrite may be required.

ALTER MATERIALIZED VIEW recent_events
SET (
    compression = zstd,
    compression_page = 2048
);

After a physical rewrite, materialized view pages are written with the current CSM parameters.

64.3.8. Performance and Maintenance #

CSM reduces the amount of physical storage by compressing pages. This can lower disk load and reduce the amount of data read and written. At the same time, compression and decompression require additional CPU resources, so the overall effect depends on the data and workload.

The effect depends on:

• data type and shape;

• relation size;

• selected algorithm;

• compression_page value;

• share of pages that use overflow storage;

• read/write ratio;

• CPU and storage characteristics.

Read-heavy workloads can benefit from reduced I/O, especially if the relation compresses well. Write-heavy workloads can be more sensitive to compression cost. For latency-sensitive systems, always test CSM on a real workload.

64.3.8.1. Maintenance Operations #

VACUUM maintains the relation in the usual way. No special user-visible VACUUM behavior for fully recompressing pages is documented. If you need to fully apply new parameters to a table, use an operation that physically rewrites the object.

VACUUM FULL physically rewrites the table and can be used to write the table fully with the current storage parameters.

CLUSTER physically rewrites the table in the order of the selected index and can apply the current storage parameters to the rewritten object.

REINDEX rebuilds the index and can be used to obtain an index written with the current CSM parameters.

TRUNCATE removes the contents of the relation. New pages written after TRUNCATE use the object's current storage parameters.

64.3.8.2. Backup, Restore, and Replication #

Physical backup and physical replication must preserve all physical relation files, including CSM service forks. They are part of the object's physical state.

Logical dump and restore recreate objects through DDL. Therefore, for CSM it is important that the DDL contains the required storage parameters and that the target side supports CSM and the selected algorithms.

Logical replication transmits data logically. Physical storage on the subscriber is determined by the subscriber's DDL, settings, and capabilities.

64.3.9. Configuration #

CSM uses server parameters to configure storage manager caches. These caches are allocated in shared memory and initialized at server startup, so changing the parameters requires a restart.

64.3.9.1. ctl_cache_slots #

ctl_cache_slots sets the number of entries in the storage manager metadata cache.

Default: 4096.

Minimum: 128.

Increasing this value can be useful when the system actively uses many CSM relations at the same time, or frequently opens different objects.

64.3.9.2. ovr_cache_slots #

ovr_cache_slots sets the number of entries in the caches and service structures related to CSM overflow storage.

Default: 64.

Minimum: 8.

Increasing this value can be useful when the workload actively uses relations where many pages are stored in overflow storage.

64.3.10. Diagnostics #

Use the pg_csm extension for diagnostics and preliminary analysis. It should be considered the main user-facing tool for CSM analysis.

Typical diagnostic tasks:

• check whether a relation uses CSM;

• estimate the potential page size after compression;

• compare pglz, lz4, and zstd;

• estimate overflow risk for compression_page = 1024, 2048, and 4096;

• check the effect after a physical rewrite or REINDEX;

• compare relation size before and after enabling CSM.

Detailed descriptions of pg_csm functions are not included on this page. See the pg_csm extension documentation.

64.3.11. Limitations #

CSM works at the physical relation storage level and is supported only for a limited set of objects. Before enabling CSM, consider the following limitations of the current implementation.

Warning

Some limitations are related to extensions and utilities that work with access methods or read physical relation files directly and do not parse the CSM format. These limitations do not mean that CSM objects cannot be backed up or maintained. They warn about scenarios where corruption diagnostics or compatibility with other storage mechanisms can differ from the behavior of standard PostgreSQL objects. Support for such scenarios can be improved in future versions.

• CSM does not replace TOAST.

• CSM is not column-level compression.

• CSM is not a table access method.

• CSM applies only to supported relation kinds and access methods.

• CSM is not applied to system catalogs.

• Temporary relations are not documented as a supported CSM scenario.

• Not all index access methods support CSM.

• compression = off disables CSM.

• compression_page is used only when compression is not off.

• A small compression_page value can increase overflow storage usage.

• Effectiveness depends on data and workload.

• Disk savings can be accompanied by increased CPU usage.

• Before enabling CSM for important production objects, test the effect on a representative workload.

64.3.11.1. Extensions #

64.3.11.1.1. page_repair #

The pg_repair_page function is not intended to repair pages stored in CSM format. CSM uses its own physical page representation and service fork files, so a tool designed for standard PostgreSQL pages cannot correctly repair a damaged compressed page.

Attempting to repair a damaged compressed page returns the following error:

ERROR:  csm checksum verification failed in file "base/..."

In addition, pg_repair_page does not recognize the CSM service fork files: ctl and ovr. Passing such a fork file as an argument returns the following error:

ERROR:  invalid fork name
HINT:  Valid fork names are "main", "fsm", "vm", and "init".

This is expected behavior: pg_repair_page supports only the standard PostgreSQL fork files and is not designed to work with CSM-specific fork files. Until CSM format support is added to repair tools, pg_repair_page should not be considered a repair tool for CSM pages.

64.3.11.1.2. pg_tde #

CSM compression and encryption via pg_tde cannot be used simultaneously for the same object. CSM supports only the standard heap access method, so any attempt to combine compression parameters with the tde_heap access method results in an error.

The error is raised in all three scenarios:

Creating a table with tde_heap and compression parameters:

CREATE TABLE test_enc (id SERIAL, PRIMARY KEY (id))
    USING tde_heap WITH (compression = zstd);

ERROR:  Compression storage manager is not supported for table access method "tde_heap"
DETAIL:  Compression storage manager supports only the heap access method.

Setting compression parameters on an existing tde_heap table:

CREATE TABLE test_enc (id SERIAL, PRIMARY KEY (id))
    USING tde_heap;
ALTER TABLE test_enc SET (compression = zstd);

ERROR:  Compression storage manager is not supported for table access method "tde_heap"
DETAIL:  Compression storage manager supports only the heap access method.

Changing the access method from heap to tde_heap on a table with compression:

CREATE TABLE test_enc (id SERIAL, PRIMARY KEY (id))
    WITH (compression = zstd);
ALTER TABLE test_enc SET ACCESS METHOD tde_heap;

ERROR:  Compression storage manager is not supported for table access method "tde_heap"
DETAIL:  Compression storage manager supports only the heap access method.

If both goals are required, choose one of the following approaches:

• encryption only: USING tde_heap without CSM parameters;

• compression only: standard heap with WITH (compression = zstd) and without pg_tde.

64.3.11.2. Utilities #

64.3.11.2.1. pg_basebackup #

When backing up a cluster that contains CSM tables with corrupted compressed pages, pg_basebackup does not always diagnose corruption in the same way as it does for standard 8 KB PostgreSQL pages.

Instead of the expected checksum error:

WARNING:  checksum verification failed in file "./base/5/16384", block 404: calculated 223C but expected 4F0
pg_basebackup: error: checksum error occurred

the utility can emit a buffer size mismatch warning and exit successfully:

WARNING:  could not verify checksum in file "./base/5/16384", block 32: read buffer size 1 and page size 8192 differ

This is because CSM stores pages in a physical format that differs from the standard PostgreSQL format: the block size in the main fork can differ from the size of a regular PostgreSQL page. pg_basebackup does not parse CSM structures and cannot correctly interpret such blocks during checksum verification.

Until CSM format support is added to pg_basebackup, do not rely on checksum verification performed by this utility as a reliable way to detect corruption in CSM tables. When backing up clusters with CSM tables, consider that checksum verification results for such relations can be unreliable.

64.3.11.2.2. pg_checksums #

When verifying checksums of a cluster that contains CSM tables, pg_checksums can fail with a read error instead of reporting page corruption. The utility attempts to read the service fork _ctl as a standard 8192-byte page, but the size of that fork file differs from the expected value:

ERROR: could not read block 0 in file "$PGDATA/base/5/16388_ctl": read 32 of 8192

As a result, pg_checksums does not output the expected checksum mismatch diagnostics and does not count the corrupted block in the Bad checksums total.

Until support for CSM-specific fork files and the CSM page storage format is added, pg_checksums should not be considered a reliable integrity checking tool for CSM tables.

64.3.12. Examples #

64.3.12.1. Creating a Compressed Table #

CREATE TABLE events (
    id bigint,
    created_at timestamptz,
    payload jsonb
) WITH (
    compression = zstd,
    compression_page = 2048
);

64.3.12.2. Creating a Compressed Index #

CREATE INDEX events_created_at_idx
ON events USING btree (created_at)
WITH (
    compression = lz4,
    compression_page = 2048
);

64.3.12.3. Changing Table Parameters #

ALTER TABLE events
SET (
    compression = zstd,
    compression_page = 1024
);

After changing parameters, check whether a physical rewrite has been performed if it is important to apply the new parameters to all existing data.

64.3.12.4. Changing Index Parameters #

ALTER INDEX events_created_at_idx
SET (
    compression = zstd,
    compression_page = 2048
);

To fully apply new parameters to an existing index, use the rebuild scenario or REINDEX supported by your product version.

64.3.12.5. Typical Parameter Selection Scenario #

1. Find a large table or index where disk size matters.

2. Estimate compressibility with pg_csm.

3. Compare pglz, lz4, and zstd.

4. Estimate how many pages will use overflow for different compression_page values.

5. Choose parameters and apply them to a test copy of the object.

6. Perform a physical rewrite or REINDEX.

7. Compare the relation size.

8. Check latency, CPU, and I/O on the real workload.

9. After validation, apply the parameters to the production object.

---

Prev	Up	Next
64.2. TOAST	Home	64.4. Free Space Map