pg_column_size如何小于octet_length？ [英] How can pg_column_size be smaller than octet_length?

问题描述

我正在通过引用列类型和长度大小来获取预期的表大小。我正在尝试为此使用 pg_column_size 。

I'm looking for getting anticipated table size by referring column type and length size. I'm trying to use pg_column_size for this.

在测试该功能时，我意识到这似乎有问题

When testing the function, I realized something seems wrong with this function.

pg_column_size（...）的结果值有时甚至小于 octet_length（...）在同一字符串上。

The result value from pg_column_size(...) is sometimes even smaller than the return value from octet_length(...) on the same string.

该列中只包含数字字符。

There is nothing but numeric characters in the column.

postgres=# \d+ t5
                           Table "public.t5"
 Column |       Type        | Modifiers | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+--------------+-------------
 c1     | character varying |           | extended |              | 
Has OIDs: no

postgres=# select pg_column_size(c1), octet_length(c1) as octet from t5;
 pg_column_size | octet 
----------------+-------
              2 |     1
            704 |   700
            101 |  7000
            903 | 77000
(4 rows)

这是错误还是什么？是否有人用某些公式根据列的类型和长度值来计算预期的表大小？

Is this the bug or something? Is there someone with the some formula to calculate anticipated table size from column types and length values of it?

推荐答案

我会说 pg_column_size 报告了 TOAST ed值的压缩大小，而 octet_length 报告未压缩的大小。我尚未通过检查函数源或定义来验证这一点，但这很有用，尤其是数字字符串可以很好地压缩时。您正在使用 EXTENDED 存储，因此这些值可以进行 TOAST 压缩。请参见 TOAST 文档。

I'd say pg_column_size is reporting the compressed size of TOASTed values, while octet_length is reporting the uncompressed sizes. I haven't verified this by checking the function source or definitions, but it'd make sense, especially as strings of numbers will compress quite well. You're using EXTENDED storage so the values are eligible for TOAST compression. See the TOAST documentation.

关于计算期望的数据库大小，这是一个全新的问题。从下面的演示中可以看到，它取决于诸如字符串可压缩性之类的东西。

As for calculating expected DB size, that's whole new question. As you can see from the following demo, it depends on things like how compressible your strings are.

这里是一个演示如何显示 octet_length 可以大于 pg_column_size ，说明TOAST的插入位置。首先，让我们在没有 TOAST 发挥作用：

Here's a demonstration showing how octet_length can be bigger than pg_column_size, demonstrating where TOAST kicks in. First, let's get the results on query output where no TOAST comes into play:

regress=> SELECT octet_length(repeat('1234567890',(2^n)::integer)), pg_column_size(repeat('1234567890',(2^n)::integer)) FROM generate_series(0,12) n;
 octet_length | pg_column_size 
--------------+----------------
           10 |             14
           20 |             24
           40 |             44
           80 |             84
          160 |            164
          320 |            324
          640 |            644
         1280 |           1284
         2560 |           2564
         5120 |           5124
        10240 |          10244
        20480 |          20484
        40960 |          40964
(13 rows)

现在让我们将相同的查询输出存储到表中并获取存储的行的大小：

Now let's store that same query output into a table and get the size of the stored rows:

regress=> CREATE TABLE blah AS SELECT repeat('1234567890',(2^n)::integer) AS data FROM generate_series(0,12) n;
SELECT 13

regress=> SELECT octet_length(data), pg_column_size(data) FROM blah;
 octet_length | pg_column_size 
--------------+----------------
           10 |             11
           20 |             21
           40 |             41
           80 |             81
          160 |            164
          320 |            324
          640 |            644
         1280 |           1284
         2560 |             51
         5120 |             79
        10240 |            138
        20480 |            254
        40960 |            488
(13 rows)

这篇关于pg_column_size如何小于octet_length？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！