sql - PK 顺序索引的填充因子

Question

是的，又是fillfactor。我花了很多时间阅读，但我无法决定每种情况的最佳选择。我不明白碎片何时以及如何发生。我正在将数据库从 MS SQL Server 迁移到 PostgreSQL 9.2。

案例一

在顺序(串行)PK 中 10-50 次插入/分钟，20-50 次读取/小时。

CREATE TABLE dev_transactions (
  transaction_id serial NOT NULL,
  transaction_type smallint NOT NULL,
  moment timestamp without time zone NOT NULL,
  gateway integer NOT NULL,
  device integer NOT NULL,
  controler smallint NOT NULL,
  token integer,
  et_mode character(1),
  status smallint NOT NULL,
  CONSTRAINT pk_dev_transactions PRIMARY KEY (transaction_id)
);

案例2

类似的结构，串行 PK 的索引，每 2 个月写入约 50.000 个寄存器的 block (一次)，读数 10-50/分钟。

50% 的填充因子是否意味着每次插入都会生成一个新页面并将现有行的 50% 移动到新生成的页面？

50% 的填充因子是否意味着在新数据页的 物理行之间分配了空闲空间？

只有在现有页面中没有可用空间时才会生成新页面吗？

如你所见，我很困惑；我将不胜感激——也许是阅读有关 PostgreSQL 和索引 fillfactor 的一个很好的链接。

Answer 1

FILLFACTOR

只有 INSERT和 SELECT你应该使用 FILLFACTOR的 100对于 tables (这是默认的)。如果您不打算“摆动”UPDATE，那么在每个数据页上留出摆动空间是没有意义的。

FILLFACTOR背后的机制很简单。 INSERT s 只填充数据页(通常为 8 kB block )，达到 FILLFACTOR 声明的百分比环境。此外，无论何时运行 VACUUM FULL或 CLUSTER在桌面上，重新建立了每个 block 的相同回旋余地。理想情况下，这允许 UPDATE在同一数据页中存储新的行版本，这在处理大量 UPDATE 时可以显着提高性能秒。与 H.O.T. 结合使用也有益。更新。见:

• Redundant data in update statements

索引在设计上需要更多的回旋余地。他们必须在叶子页面的正确位置存储新条目。一旦页面已满，就需要进行成本相对较高的“页面拆分”。所以索引比表更容易膨胀。默认 FILLFACTOR对于(默认)B-Tree 索引是 90 (因索引类型而异)。摆动空间对于 INSERT 也很有意义。最佳策略在很大程度上取决于写入模式。

示例:如果新插入的值稳定增长(serial 或 timestamp 列的典型情况)，则基本上没有页面拆分，您可以使用 FILLFACTOR = 100 (或者稍微低一点以允许一些噪音)。
对于新值的随机分布，您可能会低于默认值 90 ...

基本信息来源: CREATE TABLE 的手册和 CREATE INDEX .

其他优化

但是您可以做些别的 - 因为您似乎是优化的傻瓜...:)

CREATE TABLE dev_transactions(
  transaction_id   serial PRIMARY KEY
, gateway          integer NOT NULL
, moment           timestamp NOT NULL
, device           integer NOT NULL
, transaction_type smallint NOT NULL
, status           smallint NOT NULL
, controller       smallint NOT NULL
, token            integer
, et_mode          character(1)
);

这会在数据对齐方面优化您的表格，并避免为典型的 64 位服务器填充，并节省几个字节，平均可能只有 8 个字节 - 您通常不能用“列俄罗斯方 block ”挤出太多:

• Calculating and saving space in PostgreSQL

保留 NOT NULL表格开头的列以获得非常小的绩效奖励。

您的表有 9 列。初始(“免费”)1 字节 NULL 位图覆盖 8 列。第 9 列为扩展的 NULL 位图 触发额外的 8 个字节 - 如果该行中有任何 NULL 值。

如果你制作et_mode和 token NOT NULL , 所有列都是 NOT NULL并且没有 NULL 位图，每行释放 8 个字节。
如果某些列可以为 NULL，这甚至适用于每行。如果同一行的所有字段都有值，则该行没有 NULL 位图。在您的特殊情况下，这会导致填充 et_mode 值的悖论和 token可以使您的存储大小更小或至少保持不变:

• Do nullable columns occupy additional space in PostgreSQL?

基本信息来源:Database Physical Storage上的手册.

将行的大小(填充有值)与原始表进行比较以获得明确的证据:

SELECT pg_column_size(t) FROM dev_transactions t;

(加上行之间的填充，因为下一行从 8 字节的倍数开始。)