postgresql - 最优数据库表优化方法

Question

我有一个增长太大(几亿行)的数据库表需要优化，但在我开始对它进行分区之前，我想我会询问一些建议。

用法如下:

0 。表包含大约 10 列，每列长度约为 20 字节。

1. INSERTS 以每秒数百次的速度执行。

2. SELECT 语句基于“a”列(其中 a='xxxx')每小时执行几次。

3. DELETE 语句基于 DATE 列执行。 (删除超过 1 年的日期)通常每天一次。

关键需求是加速INSERT和SELECT语句，并且能够保留1年前的历史数据，而无需在删除时锁定整个表。

我猜我必须有两个索引，一个用于“a”列，另一个用于日期字段。还是可以同时优化两者？

选择速度和删除速度之间是否存在必要的权衡？

分区是唯一的解决方案吗？对此类表进行分区的好的策略是什么？

我使用的是 PostgreSQL 8.4 数据库。

Answer 1

您是否查看过PostgreSQL partitioning，而不是将其保留在单个物理表中？ ？从 8.1 版开始支持。

分区可以帮助您避免在快速 INSERT 与快速 DELETE 性能之间进行选择的问题。您始终可以按年/月对表进行分区，只需删除不再需要的分区即可。删除分区极快，插入小分区也极快。

来自手册:

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:

• 某些方面的查询性能可以显着提高 各种查询。
• 更新性能也可以提高，因为 表的索引小于 整个数据集的索引将是。 当索引不再适合时 内存，读写操作 在索引上逐步采取更多 磁盘访问。
• 可以通过简单地删除其中一个来完成批量删除 分区，如果该要求是 规划到分区设计中。 DROP TABLE 比批量快得多 删除，更不用说随后的 VACUUM 开销。
• 可以将很少使用的数据迁移到更便宜、更慢的存储空间 媒体。

The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

Currently, PostgreSQL supports partitioning via table inheritance. Each partition must be created as a child table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set. You should be familiar with inheritance (see Section 5.8) before attempting to implement partitioning.