测量 PostgreSQL 表行的大小

Q2：way to measure page size

PostgreSQL 提供了许多数据库对象大小函数。我在这个查询中打包了最有趣的，并在底部添加了一些统计访问函数。（附加模块pgstattuple提供了更多有用的功能。）

这将表明测量“行大小”的不同方法会导致非常不同的结果。这完全取决于您想要测量的内容。

此查询需要Postgres 9.3 或更高版本。对于旧版本，请参见下文。

在子查询中使用VALUES表达式LATERAL，以避免拼写每一行的计算。

替换public.tbl为可选的模式限定表名，以获得收集的行大小统计信息的紧凑视图。您可以将其包装到 plpgsql 函数中以重复使用，将表名作为参数提交并使用EXECUTE...

SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

结果：

              公制 | 字节 | 字节漂亮 | bytes_per_row
------------------------------------+----------+--- ------------+---------------
 core_relation_size | 44138496 | 42 MB | 91
 可见性地图 | 0 | 0 字节 | 0
 free_space_map | 32768 | 32 KB | 0
 table_size_incl_toast | 44179456 | 42 MB | 91
 索引大小 | 33128448 | 32 MB | 68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB | 159
 live_rows_in_text_representation | 29987360 | 29 MB | 62
 ------------------------------ | | |
 行数 | 483424 | |
 live_tuples | 483424 | |
 死元组 | 第2677章 |

对于旧版本（Postgres 9.2 或更早版本）：

WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS bytes
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

结果相同。

Q1：anything inefficient?

您可以优化列顺序以节省每行的一些字节，目前浪费在对齐填充中：

integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

这样每行可以节省 8 到 18 个字节。我称之为列俄罗斯方块。看：

• 为读取性能配置 PostgreSQL
• 在 PostgreSQL 中计算和节省空间

还要考虑：

• 当所有值都是 36 个字符时，使用 char 与 varchar 进行索引查找会明显更快吗

通过查询整行的 TEXT 表示的长度，很容易获得行大小的近似值，包括TOAST的内容：

SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;

这是执行时将在客户端检索的字节数的近似值：

SELECT * FROM tablename WHERE primary_key=:value;

...假设查询的调用者以文本格式请求结果，这是大多数程序所做的（二进制格式是可能的，但在大多数情况下不值得麻烦）。

可以应用相同的技术来定位N“文本中最大”的行tablename：

SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
   ORDER BY 2 DESC LIMIT :N;

有几件事可能会发生。一般来说，我怀疑长度是近端问题。我怀疑你有一个与长度相关的问题。

你说文本字段最多可以达到几k。主存储中的一行不能超过 8k，并且您的较大文本字段可能已被TOASTed或从主存储移出到单独文件中的扩展存储中。这使您的主存储更快（因此 select id 实际上更快，因为要访问的磁盘页面更少）但 select * 变得更慢，因为有更多的随机 I/O。

如果您的总行大小仍然低于 8k，您可以尝试更改存储设置。但是，我会警告说，在将超大属性插入主存储时可能会发生不好的事情，因此如果您不必这样做，最好不要碰它，如果这样做，请通过检查约束设置适当的限制。所以交通可能不是唯一的事情。它可能正在整理许多需要随机读取的字段。大量随机读取也可能导致缓存未命中，并且所需的大量内存可能需要在磁盘上实现事物和大量宽行，如果存在连接（如果涉及 TOAST，则存在连接）可能需要更昂贵连接模式等

我要做的第一件事是选择更少的行，看看是否有帮助。如果可行，您也可以尝试向服务器添加更多 RAM，但我会先开始查看由于计划更改和缓存未命中而导致性能开始下降的位置。

使用上面提到的数据库对象大小函数：

SELECT primary_key, pg_column_size(tablename.*) FROM tablename;

如果需要当前行大小的平均值，您可以使用 pg_column_size：

SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename;

每列使用它：

SELECT SUM(pg_column_size(table_name.column_name))/COUNT(*) FROM tablename;