主页 / dba / 问题 / 320395
Accepted
0xCAFEBABE
Asked: 2022-12-01 05:46:06 +0800 CST

PostgreSQL 中表的大小计算之间的显着大小差异

  • 772

我们正在提供 PostgreSQL 作为 Oracle 的替代品，这是我们目前唯一的数据库。为了准确预测转换后数据所需的挂载点大小，我们需要找出在转换为 PostgreSQL 时从 Oracle 中使用了多少空间。

我们有这张桌子，上面是我们的用例之一。甲骨文结构：

CREATE TABLE SAMPLETABLE
(
  REFDATE   DATE,
  ID        NUMBER(10),
  ARCVALUE  NUMBER,
  VALSTAT   NUMBER(2),
  IO        NUMBER(1),
  SOURCE    NUMBER(2),
  MODDATE   DATE                                DEFAULT sysdate
);

和 PostgreSQL 等效：

CREATE TABLE sampletable (
    refdate timestamptz NOT NULL,
    id numeric(10) NOT NULL,
    arcvalue numeric NULL,
    valstat numeric(10) NULL,
    source numeric(2) NULL,
    moddate timestamptz NULL DEFAULT clock_timestamp(),
    io numeric(1) NULL,
    CONSTRAINT sampletable_pk PRIMARY KEY (refdate, id)
);

在我们Oracle这边，对于一个具体的项目数据，4500万行的平均行长是45 bytes. 我们通过此查询计算得出：

select sum(bytes) from  DBA_EXTENTS where segment_name = 'sampletable';

除以行数（4500 万）。

查看 PostgreSQL，我们可以通过这种方式找到大小：

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
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   sampletable 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);

由此，使用简单的数学core_relation_size/ row_count，它110 bytes在 PostgreSQL 中显示一行的大小。这是一个残酷的增长，我们试图找出数据在转换后如何变得更大。

以普通行的字节大小来看，该行似乎应该小得多：

select
refdate, id,
    pg_column_size(refdate) s_refdate,
    pg_column_size(id) s_id,
    pg_column_size(arcvalue) s_arcvalue,
    pg_column_size(valstat) s_valstat,
    pg_column_size(source) s_source,
    pg_column_size(moddate) s_moddate,
    pg_column_size(io) s_io,
    pg_column_size(refdate) + pg_column_size(tid) + pg_column_size(arcvalue) + pg_column_size(valstat) + pg_column_size(source) + pg_column_size(moddate) + coalesce (pg_column_size(io), 0) rowsize
from sampletable
order by rowsize desc;

这给出48 bytes了我们拥有的最长行，以字节为单位。我们现在所拥有的只是关于如何解释显着差异的想法：

  • 是否有明显的填充？
  • 没有精度的数字字段是否以某种方式使用了比必要的多得多的字节？
  • Oracle 大小计算有误吗？

有谁知道如何找出差异？

postgresql

1 个回答

  1. Best Answer

    得益于出色的帖子，使用 INT4/INT 而不是 INT8/BIGINT 可以节省多少磁盘空间？通过 depesz，我们现在了解了每个字节，它去了哪里以及我们如何优化它。我们都分析了 NUMERIC 类型列的开销，以及需要 PostgreSQL 填充的字段的不幸订单。最后，附加字段 tableoid、cmax、xmax、cmin、xmin、ctid 也需要空间，所有这些都解释了我们看到的大小差异。

    谢谢您的帮助。

    • 0

相关问题