PostgreSQL：如何查询表中的已压缩字段？

1. 查看字段的压缩配置 #

先查询哪些字段明确设置了压缩选项：

SELECT 
    n.nspname AS schema_name,
    c.relname AS table_name,
    a.attname AS column_name,
    a.attoptions AS compression_settings
FROM 
    pg_attribute a
JOIN 
    pg_class c ON a.attrelid = c.oid
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
WHERE 
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attoptions IS NOT NULL
    AND array_to_string(a.attoptions, ',') LIKE '%compress%'
ORDER BY 
    schema_name, table_name, column_name;

该语句会列出所有带压缩配置的字段，以及对应的压缩算法（比如compress=pglz或compress=zstd）。

2. 统计压缩实际使用率 #

要了解压缩的实际使用情况（即有多少行的字段数据被压缩），需要关联TOAST表的统计信息：

WITH toast_info AS (
    SELECT 
        c.relname AS main_table_name,
        n.nspname AS schema_name,
        a.attname AS column_name,
        t.relname AS toast_table_name,
        t.oid AS toast_oid
    FROM 
        pg_attribute a
    JOIN 
        pg_class c ON a.attrelid = c.oid
    JOIN 
        pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN 
        pg_class t ON a.atttoastrelid = t.oid
    WHERE 
        a.attnum > 0
        AND NOT a.attisdropped
        AND a.atttoastrelid IS NOT NULL
),
toast_stats AS (
    SELECT 
        relid AS toast_oid,
        n_live_tup AS toast_live_rows,
        n_dead_tup AS toast_dead_rows
    FROM 
        pg_stat_user_tables
)
SELECT 
    ti.schema_name,
    ti.main_table_name,
    ti.column_name,
    ts.toast_live_rows AS compressed_rows,
    mt.n_live_tup AS total_main_rows,
    CASE 
        WHEN mt.n_live_tup = 0 THEN 0
        ELSE ROUND((ts.toast_live_rows::NUMERIC / mt.n_live_tup) * 100, 2)
    END AS compression_rate_percent
FROM 
    toast_info ti
JOIN 
    toast_stats ts ON ti.toast_oid = ts.toast_oid
JOIN 
    pg_stat_user_tables mt ON mt.relname = ti.main_table_name AND mt.schemaname = ti.schema_name
ORDER BY 
    compression_rate_percent DESC;

结果中的compression_rate_percent是该字段被压缩的行占总行数的比例。如果这个比例极低（比如接近0），说明该字段的大部分数据无需压缩，关闭压缩能节省CPU开销。

3. 关闭字段压缩 #

根据需求选择以下操作：

• 完全禁用TOAST（仅当字段数据不会超过页面大小限制时使用）：

ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET STORAGE PLAIN;

• 保留TOAST但禁用压缩（PostgreSQL 14及以上版本支持）：

ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET COMPRESSION NONE;

• 重置压缩配置（恢复默认行为）：

ALTER TABLE your_schema.your_table ALTER COLUMN your_column RESET (compress);

内容的提问来源于stack exchange，提问作者Matthias