database - Postgresql:优化数字字段的列大小

Question

我不明白 Postgresql (9.2) 如何计算列大小(以 kb 为单位)，我有这个表:

Table d2:
 Column  |     Type      |
---------+---------------|
 id      | serial        |
 n       | numeric(17,2) |

Table d4:
 Column  |     Type      |
---------+---------------|
 id      | serial        |
 n       | numeric(19,4) |

Table d18:
 Column  |     Type      |
---------+---------------|
 id      | serial        |
 n       | numeric(35,18)|

Table b1:
 Column  |     Type      |
---------+---------------|
 id      | serial        |
 n       | numeric(16,2) |

Table b2:
 Column  |     Type      |
---------+---------------|
 id      | serial        |
 n       | numeric(4,2)  |

然后我用这段代码填充它们，以便每个表有 10000 行；

$tests = array(2, 4, 18);
foreach($tests AS $n)
{
  $m = number_format(999999999999999.66549865, $n, '.', '');
  $prp_name = "insert_$n";
  $prp = pg_prepare($db, $prp_name, "INSERT INTO d_$n (n) VALUES ($1)");
  for($i = 0; $i < 10000; $i++)
  {
    pg_execute($db, $prp_name, array($m));
  }
}

$prp = pg_prepare($db, 'insert_b1', "INSERT INTO b1 (n) VALUES ($1)");
$m = 16512.67;
for($i = 0; $i < 10000; $i++)
{
  pg_execute($db, 'insert_b1', array($m));
}
$prp = pg_prepare($db, 'insert_b2', "INSERT INTO b2 (n) VALUES ($1)");
$m = 99.36;
for($i = 0; $i < 10000; $i++)
{
  pg_execute($db, 'insert_b2', array($m));
}

现在，我不明白的是怎么会这样:

SELECT pg_size_pretty(pg_total_relation_size('d2')) AS size_d2;
 size_d2 
---------
 752 kB

SELECT pg_size_pretty(pg_total_relation_size('d4')) AS size_d4;
 size_d4 
---------
 752 kB

SELECT pg_size_pretty(pg_total_relation_size('d18')) AS size_d18;
 size_d18 
----------
 752 kB

SELECT pg_size_pretty(pg_total_relation_size('b1')) AS size_b1;
 size_b1 
---------
 440 kB

SELECT pg_size_pretty(pg_total_relation_size('b2')) AS size_b2;
 size_b2 
---------
 680 kB

因此，d_* 表具有相同的大小，即使精度(和存储数据的长度)非常不同；

即使精度更高，表 b1 也比 b2 小。

在 pg_total_relazion_size 之前，所有表都已被刷新(vacuum、analize)。

我无法在 Postgresql's datatypes documentations 中找到答案，所以我要在这里问:以 kb 为单位的大小如何与数字列的精度相关？

我做这个测试是为了决定在 CMS 的数据库中使用什么精度/标度来存储货币类型，我希望所有项目的价格只有 1 个精度/标度值(不是总计，其中比例必须是 2 位小数)。

对于用户而言，我可以存储的小数位数越多越好(因此当客户要求为特定项目存储 12 位小数时我没有限制)，但我想了解此决定将如何影响数据库大小和性能.

Answer 1

来自manual :

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

pg_total_relation_size 的结果函数包括索引。您要插入的每个值的正确列大小是:

select pg_column_size(a)
from (values
    (999999999999999.62::numeric(17,2)),
    (999999999999999.6250::numeric(19,4)),
    (999999999999999.625000000000000000::numeric(35,18)),
    (16512.67::numeric(16,2)),
    (99.36::numeric(4,2))
) s(a)
;
 pg_column_size 
----------------
             16
             16
             16
             12
             10

因此，如果您想让用户拥有最多 n 位小数，只需将其定义为 numeric(35, n)。它只会使用不超过现有小数位数的空间，因为不会存储尾随零。