测量PostgreSQL表行的大小

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

#1 楼

问题2：way to measure page size

PostgreSQL提供了许多数据库对象大小函数。我在此查询中打包了最有趣的内容，并在底部添加了一些统计信息访问功能。 （附加的pgstattuple模块提供了更多有用的功能。）
这将显示出不同的测量“行大小”的方法会导致非常不同的结果。这完全取决于您要测量的内容。
此查询需要Postgres 9.3或更高版本。对于较旧的版本，请参见下文。
在VALUES子查询中使用LATERAL表达式，以避免拼写出每一行的计算。
将public.tbl替换为您的可选模式限定的表名，以获得所收集行的紧凑视图尺寸统计。您可以将其包装到plpgsql函数中以供重复使用，将表名称作为参数输入并使用EXECUTE ...

SELECT l.metric, l.nr AS "bytes/ct"
     , 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
 , 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);

结果：

              metric               | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 44138496 | 42 MB        |            91
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    32768 | 32 kB        |             0
 table_size_incl_toast             | 44179456 | 42 MB        |            91
 indexes_size                      | 33128448 | 32 MB        |            68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB        |           159
 live_rows_in_text_representation  | 29987360 | 29 MB        |            62
 ------------------------------    |          |              |
 row_count                         |   483424 |              |
 live_tuples                       |   483424 |              |
 dead_tuples                       |     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/ct"
     , 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 vs varchar

#2 楼

通过查询整行的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;

#3 楼

可能会发生一些事情。总的来说，我怀疑长度是近端问题。我怀疑您有一个与长度有关的问题。

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

如果总行大小仍然全部在8k以下，您可以尝试更改存储设置。但是，我警告您，将超大属性插入主存储时可能会发生不好的事情，因此最好不要触摸此属性，如果需要，请通过检查约束设置适当的限制。因此，运输并不是唯一的选择。它可能正在整理许多需要随机读取的字段。大量的随机读取也可能会导致高速缓存未命中，并且所需的大量内存可能要求将事物具体化在磁盘上，并且需要大量的宽行（如果存在联接（如果涉及TOAST，则存在一个））可能需要更高的成本连接模式等。

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

#4 楼

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

#5 楼

如果需要平均当前行大小，则可以使用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;