PK的顺序索引的填充因子 [英] Fill factor for a sequential index that is PK

问题描述

是的，再次填充因子。我花了很多时间阅读，但我无法确定哪种情况下最好的填充因子。问题是我不知道何时以及如何进行分割。我正在将数据库从MS SQL Server迁移到PostgreSQL 9.2。

Yes, fill factor again. I spend many hours reading and I can't decide which is the better fill factor for each case. The problem is I don't understand when and how the fragmentation is made. I'm migrating a database from MS SQL Server to a PostgreSQL 9.2.

案例1）在连续（串行）PK中，每分钟10-50次插入，每分钟20-50次读取/小时。

Case 1) 10-50 inserts / minute in a sequential (serial) PK, 20-50 reads / hour.

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)
)
WITH (
  OIDS=FALSE
);

案例2）PK序列的相似结构索引将以大约50.000的块（一次）写入每2个月进行一次注册，读数为10-50 /分钟。

Case 2) Similar structure index for a PK sequential will writes in blocks (one shot) of ~50.000 registers each 2 months, readings 10-50 / minute.

50％的填充因子意味着在每个插入中将生成一个新页面并传输现有记录的50％到新的生成页面？

A 50% fill factor means that in each insert will generate a new page and transport 50% of the existent records to a new generate page?

50％的填充系数意味着在创建新页面时，将保留复制的记录以避免在两者之间插入？

A 50% fill factor means that when create a new page the records copied will be spared to avoid insertions in between?

仅当没有空间分配插入的记录时才会生成新页面吗？

The new page is generate only if no room to allocate the record inserted?

您可以看到我很困惑；我希望获得一些帮助-也许是阅读有关PostgreSQL和索引填充因子的好链接。

As you can see I'm very confused; I would appreciate some help with it — maybe a good link to read about PostgreSQL and index fill factors.

推荐答案

FILLFACTOR

仅使用 INSERT 和 SELECT 您应该在各处使用 100 的填充因子。

FILLFACTOR

With only INSERT and SELECT you should use a FILLFACTOR of 100 everywhere.

如果不打算通过 UPDATE s来摆动，就没有必要为每个内存块留出摆动的空间。

There is no point in leaving wiggle room per memory block if you are not going to "wiggle" with UPDATEs.

FILLFACTOR 背后的机制非常简单。 INSERT 仅填充每个数据页（通常为8 kb的块），达到 FILLFACTOR 设置所声明的百分比。另外，每当在表上运行 VACUUM FULL 或 CLUSTER 时，将重新建立每个块相同的摆动空间。理想情况下，这允许 UPDATE s在同一数据页中存储新行版本，这在处理大量 UPDATE <时可以显着提高性能。 / code> s。与 H.O.T。更新：

The mechanism behind FILLFACTOR is very simple. INSERTs only fill each data page (usually a 8 kb blocks) up to the percentage declared by the FILLFACTOR setting. Also, whenever you run VACUUM FULL or CLUSTER on the table, the same wiggle room per block is re-established. Ideally, this allows UPDATEs to store new row versions in the same data page, which can provide a substantial performance boost when dealing with lots of UPDATEs. Also beneficial in combination with H.O.T. updates:

• 更新语句中的冗余数据

• Redundant data in update statements

如果没有 / em>更新，请不要为此浪费空间，并设置 FILLFACTOR = 100 。

基本信息来源： 创建表 或创建索引。

但是您可以做其他事情-因为您似乎是优化的傻瓜...：）

But you can do something else - since you seem to be a sucker for optimization ... :)

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

这会根据数据对齐方式优化表并避免填充（典型的64位服务器）并节省一些字节，平均可能只有8个字节-您通常无法通过列俄罗斯方块挤出太多内容。

This optimizes your table with regard to data alignment and avoids padding for a typical 64 bit server and saves a few bytes, probably just 8 byte on average - you typically can't squeeze out much with "column tetris:

• 在PostgreSQL中计算和节省空间

• Calculating and saving space in PostgreSQL

此外，在表的开头保留 NOT NULL 列

Also, keep NOT NULL columns at the start of the table for a very small performance bonus.

此外，您的表有 9列。这意味着扩展的表会额外增加 8个字节 NULL位图-仅适合 8列的初始1字节NULL位图。

如果定义 et_mode 和令牌 不为空，所有列均为不为空，并且完全使用了NULL位图，从而释放了8个字节。 br>
如果您不将列声明为 NOT NULL ，那么这甚至对每行都有效。如果所有列都有值，则此行没有NULL位图。在您的情况下，这会导致自相矛盾的效果，即填写 et_mode 和 token 的值会使存储空间较小或至少保持不变：

Also, your table has 9 columns. This means an extra 8 bytes for the extended NULL bitmap - which would fit into the initial 1-byte NULL bitmap for just 8 columns.
If you define et_mode and token NOT NULL, all columns are NOT NULLand the NULL bitmap is used at all, freeing up 8 bytes.
This even works per row if you don't declare the columns NOT NULL. If all columns have values, there is no NULL bitmap for this row. In your case, this leads to the paradox effect that filling in values for et_mode and token can make your storage size smaller or at least stay the same:

• 可为空的列在PostgreSQL中是否占用额外的空间？

• Do nullable columns occupy additional space in PostgreSQL?

基本信息来源：《数据库物理存储。

将行的大小（用值填充）与原始表进行比较以得到确定的证据：

Compare the size of rows (filled with values) with your original table to get definitive proof:

SELECT pg_column_size(t) FROM dev_transactions t;

这篇关于PK的顺序索引的填充因子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！