PostgreSQL：额外列的性能影响 [英] PostgreSQL: performance impact of extra columns

问题描述

给定一个大表（1-10亿行），最好的方法是向它添加一些额外的（未索引的）列？

Given a large table (10-100 million rows) what's the best way to add some extra (unindexed) columns to it?

1. 只需添加列。


2. 为每个额外列创建单独的表，并在要访问额外值时使用连接。

答案会根据额外列是密集的（大部分不为空）还是稀疏>

Does the answer change depending on whether the extra columns are dense (mostly not null) or sparse (mostly null)?

推荐答案

具有 NULL 值的列可以添加到行，到大多数情况下 的数据页的其余部分 。在 NULL位掩码中只需设置一个位。因此，在大多数情况下，添加稀疏列的成本要低得多。

A column with a NULL value can be added to a row without any changes to the rest of the data page in most cases. Only one bit has to be set in the NULL bitmask. So, yes, a sparse column is much cheaper to add in most cases.

是否建立单独的1：1表格列非常依赖于用例。它通常更贵。对于初学者，每行有28字节（heaptuple头加条目指针）的开销和每个表的一些额外开销。在查询中 JOIN 行比在一个文件中读取更昂贵。您需要添加主/外键列及其上的索引。如果在大多数查询中不需要其他列，则拆分可能是一个好主意。大多是一个坏主意。

Whether it is a good idea to create a separate 1:1 table for additional columns very much depends on the use case. It is generally more expensive. For starters, there is an overhead of 28 bytes (heaptuple header plus item pointer) per row and some additional overhead per table. It is also much more expensive to JOIN rows in a query than to read them in one piece. And you need to add a primary / foreign key column plus an index on it. Splitting may be a good idea if you don't need the additional columns in most queries. Mostly it is a bad idea.

在PostgreSQL中添加列很快。 更新列中的值是可能很昂贵的，因为每个 UPDATE 写入一个新行（由于 MVCC 模型）。因此，最好一次更新多个列。

Adding a column is fast in PostgreSQL. Updating the values in the column is what may be expensive, because every UPDATE writes a new row (due to the MVCC model). Therefore, it is a good idea to update multiple columns at once.

数据库页面布局。

Database page layout in the manual.

如何计算行大小：

• 有意义的Postgres行大小


• 在PostgreSQL中计算和保存空间

• Making sense of Postgres row sizes
• Calculating and saving space in PostgreSQL

这篇关于PostgreSQL：额外列的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！