大型数据库表

使用分区（以及更多）导航不断增长的 PostgreSQL 表

An inflated PostgreSQL elephant balloon that is shooting up, just like your database tables, if you don't use PostgreSQL partition strategies and more.

假设您已经使用 PostgreSQL 一段时间了，现在事情开始变得越来越大。也许您的用户表已经增长到数百万行，或者您引入了需要在多个大型表之间进行连接的新功能。

事实上，您已经注意到查询开始运行得有点慢，或者可能完全超时。您可能还会注意到一些维护开销：以前例行公事现在开始变得棘手或需要很长时间才能完成。令人担忧的是，您看不到这些问题的尽头，因为您的 PostgreSQL 表将继续不断增长。

是时候求助于 PostgreSQL 分区策略等等了。本文分享了一些技巧，可帮助您浏览不断增长的 PostgreSQL 表。

考虑 PostgreSQL 分区

在 Timescale，我们是 PostgreSQL 分区的专家，所以我们必须从这里开始。表分区是一种数据库设计技术，其中一个大型表被分成更小、更易于管理的部分，每个部分称为一个分区（或者用 Timescale 术语来说，是一个块）。每个分区都可以有自己的索引，充当包含特定数据子集的子表，并确保每行唯一地存储在一个 PostgreSQL 分区中。

这种技术可以成为您管理大型 PostgreSQL 表的最强大盟友之一。它可以帮助您提高查询性能并简化数据维护操作，从而使 PostgreSQL 数据库即使在大规模情况下也能更加灵活和响应迅速。

但是，分区并不是万能的。在选择分区之前，您应该真正评估您的独特用例和需求，确保您将从中受益。本文将帮助您评估分区是否是适合您的解决方案。

如果您决定继续实施分区，那么您绝对应该考虑 Timescale。对于那些考虑对大型 PostgreSQL 表进行分区的人来说，Timescale 提供了一个最佳解决方案，特别是那些希望按时间进行分区的人。

通过超表，您可以自动完成创建和维护分区的整个过程：您可以像使用标准 PostgreSQL 表一样与超表进行交互。同时，Timescale 在后台管理所有分区复杂性。查看本文以详细了解为什么要使用超表。

Timescale’s hypertables make partitioning by time automatic

Timescale 的超表使按时间分区自动化。您可以免费试用 Timescale 或 将 TimescaleDB 扩展添加到您的 PostgreSQL 数据库。

微调您的 PostgreSQL 数据库表

PostgreSQL 的美妙之处在于其通用性，但与任何强大工具一样，它需要一点技巧才能充分发挥其潜力。当您的 PostgreSQL 数据库和表开始增长时，可能是时候深入了解 PostgreSQL 并开始微调您的数据库了。

我们最近发表了一系列关于这个主题的文章，特别涵盖了

如何确定 PostgreSQL 数据库的大小：首先要了解您实际需要多少 CPU/内存以及如何优化资源使用。
PostgreSQL 关键参数微调：熟悉基本的 PostgreSQL 参数，例如 max_worker_processes、max_parallel_workers、work_mem、shared_buffers 或 max connections。
优化索引：获取有关如何了解索引是否得到有效使用以及索引最佳实践的说明。
设计和优化数据库模式：获取有关模式设计的一些技巧，这是有效管理大型 Postgres 数据库的基本要素。

优化 PostgreSQL 查询

很多时候，您最需要的是一种快速简便的方法来获取数据，以便开始对其进行操作或向用户显示。例如，考虑用户列表及其所在的团队。从 users 和 teams 表中选择数据进行连接非常简单。但是，如果您的 users 表中有 1 亿行数据会发生什么情况？

当您的 users 表增长到很大的规模时，以前简单的查询数据任务将成为一项重大挑战——基本的 PostgreSQL 查询可能会变得资源密集型。

关于查询优化的话题有很多可以说的。这不是一种一劳永逸的操作。如果您要管理不断增长的 PostgreSQL 数据库，则需要通过定期监控（稍后会详细介绍）不断检查查询的执行情况，并偶尔对其进行优化。也就是说，让我们通过分享一些快速技巧来开始这个聚会。

了解您要检索多少数据

获得更高效查询的一个重要且经常被低估的方法是简单地了解您要使用它检索哪些数据。例如，请考虑以下查询

SELECT * FROM users INNER JOIN teams ON users.team_id = teams.id;

这里有几个问题。第一个是使用通配符 (*)。对于生产数据库，这可能是数百万行包含数十或数百列的数据。根据您的数据集，此查询可能需要几秒钟甚至超时，从而导致用户体验不佳。

如果您只需要每个用户的团队名称和用户名，则只需获取这些列

SELECT users.name, teams.name FROM users INNER JOIN teams ON users.team_id = teams.id;

通常，避免使用通配符检索所有列，而是仅指定特定操作所需的列。这减少了处理和传输的数据量，减轻了数据库和网络的负载。

Postgres LIMIT 和 OFFSET 的使用

深入了解查询优化并减少结果集的大小，在 PostgreSQL 中策略性地使用 LIMIT 和 OFFSET 子句可以显着提高性能，但明智地使用它们至关重要。

通过使用 LIMIT，您可以限制返回的记录数，有效减少 I/O、CPU 和内存的使用。但是，虽然 Postgres LIMIT 子句通常是有效的，但 OFFSET 有时可能是一把双刃剑，尤其是在大型数据集中。它通常用于分页，在返回结果集之前跳过指定数量的行。但这就是需要注意的地方：即使 OFFSET 跳过了初始行集，PostgreSQL 仍然必须遍历它们才能到达所需的数据。

例如，在下面的查询中，LIMIT 100 确保只返回 100 条记录，而 OFFSET 500 告诉 PostgreSQL 跳过前 500 条记录。但是，如前所述，PostgreSQL 仍然必须扫描这 500 条记录，这在偏移量较大时会导致性能问题。

SELECT * FROM users
ORDER BY user_id
LIMIT 100 OFFSET 500;

优化查询性能的一种策略是尽量减少大型偏移量的使用。随着偏移量数字的增加，请考虑其他方法，例如索引列和 WHERE 子句。

选择适当的 PostgreSQL 数据类型

选择足够 PostgreSQL 数据类型对于优化查询性能（和存储使用）至关重要，尤其是在包含数百万或数十亿条记录的大型表中。

每种数据类型都有其独特的特性，每种数据类型都针对特定的操作和数据模式进行了优化。在大型 PostgreSQL 数据库的上下文中，数据类型之间的细微差别可以提高数据检索和操作的速度和效率。

选择适当的数据类型可确保数据库引擎能够以最高效率处理查询，并利用针对特定数据类型的最佳算法和操作。

例如，选择紧凑、高效的 Postgres 数据类型可以加快索引扫描和数据过滤过程，从而加快查询响应速度。当您处理大量数据时，这种优化尤为明显。

数据类型的选择也会影响存储使用。当您的 PostgreSQL 表很大时，每条记录存储空间的微小减少可能会转化为存储容量的显着节省。适当的数据类型选择可确保每条数据都尽可能紧凑地存储，而不会牺牲数据的完整性或精度。

💡 有关如何了解使用哪种数据类型的具体建议，请查看此博客文章。

运行 PostgreSQL ANALYZE 以改进查询计划

Postgres 有一个内部查询规划器，在查询的执行中起着至关重要的作用。我们可以详细了解一下，而无需深入了解：PostgreSQL 采用 SQL 查询，并通过一个严重依赖于有关数据库结构和数据分布的基础统计信息的流程，找出执行它的最佳方式。

在对数据库模式进行重大修改或进行大量数据更改后，数据库的统计信息可能会过时。在这种情况下，运行 PostgreSQL ANALYZE 命令来更新有关表内容的统计信息非常有用。此命令会仔细检查表的当前数据，更正统计信息，并为查询规划器提供最新信息，以便其做出明智的决策。准确的统计信息可确保查询规划器能够正确优化查询，并在资源利用率和执行速度之间取得平衡。ANALYZE users;

需要记住的一点是，添加索引会更新有关该索引的元数据，但它不一定会像特意执行 ANALYZE 命令那样更新查询规划器。这是一个常见的误解——索引优化了数据访问路径，但它没有封装表数据中固有的粒度数据分布细节和差异，而这是 PostgreSQL 中优化查询计划的一个关键方面。

在 Timescale 中实现 PostgreSQL 物化视图或连续聚合

PostgreSQL 物化视图在处理复杂的分析查询（如聚合和连接）和大型数据集时非常有用。它们以表格形式存储计算出的查询结果，从而使数据库无需在每次访问视图时都重新计算繁重的操作，从而加快查询速度。

在普通的 PostgreSQL 中，需要刷新物化视图以更新存储的数据。您可以手动执行此操作，也可以将其安排为定期执行。如果您使用的是 Timescale，连续聚合可以节省您的工作量，因为它们会按指定的时间间隔自动刷新物化视图，始终为您提供实时结果，而无需手动干预。

CREATE VIEW daily_sales_summary WITH (timescaledb.continuous) AS 
SELECT time_bucket('1 day', order_time) as day, SUM(total_amount) as total_sales
FROM sales_orders
GROUP BY 1;

SELECT day, total_sales
FROM daily_sales_summary
WHERE day BETWEEN '2023-01-01' AND '2023-01-31'
ORDER BY day ASC;

连续聚合的优势在于能够增量高效地刷新数据。它们只处理自上次刷新以来新增或修改的数据，而不是重新计算整个数据集。这种增量方法确保了即使在数据不断增长的情况下，刷新操作也能保持快速和资源高效。

如果您要对大型数据集运行分析查询或报告，请考虑实施连续聚合或物化视图，以提高性能和工作效率。

💡详细了解 PostgreSQL 物化视图及其查找位置。

性能监控是您的朋友

正如我们在上一节中提到的，在谈论优化查询时，不可能不谈论监控。如果您没有进行监控，那么您就已经落后了——了解 PostgreSQL 查询执行情况的唯一方法是能够跟踪它们。随着 PostgreSQL 表大小的增长，这一点变得越来越重要。

您的监控方法可能会有所不同，具体取决于您运行 PostgreSQL 的位置。让我们介绍一些流行的工具。

Amazon RDS 中的 AWS 监控

如果您将 Amazon RDS 用于 PostgreSQL，则可以使用 Amazon RDS Performance Insights 工具，该工具可以很好地了解数据库中正在发生的事情。在“Top SQL”选项卡中，您可以识别等待时间最长的 SQL 查询，并接收按特定等待类型分类的全面细分。每个分类都提供了一条通往性能问题根源的途径，揭示了 CPU 利用率、I/O 操作等方面的见解。

AWS Monitoring "Top SQL" tab in Amazon RDS for PostgreSQL

来源

Timescale 中的查询统计信息

如果您使用的是 Timescale，我们在 Timescale 控制台中有我们自己的监控工具，称为查询统计信息。您可以使用查询统计信息快速分析查询性能，例如检索查询返回的行数、运行所需的时间，以及它们如何影响 CPU、内存和 I/O 消耗。

Query Stats view in the Timescale Console

Timescale 控制台中的查询统计信息视图

pg_stat_statements

我们在 Timescale 中经常谈论 pg_stat_statements，因为它是一个优秀的工具，可以识别慢查询，并精确定位那些导致高 I/O 使用率和大量使用索引的查询。

让我们来看一些例子。有关 pg_stat_statements 的更多指南，请阅读这篇博文。

使用 pg_stat_statements 识别慢查询 要识别慢查询，您可以查询 pg_stat_statements 视图，并按每个查询所花费的总时间对结果进行排序，如下所示：

SELECT query, total_time, calls 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

使用 pg_stat_statements 识别具有高 I/O 使用率的查询 I/O 通常是数据库性能的瓶颈，因此识别和优化读取或写入大量数据的查询至关重要。

SELECT query, shared_blks_read + shared_blks_written AS total_io, calls 
FROM pg_stat_statements 
ORDER BY total_io DESC 
LIMIT 10;

在本例中，查询按读取和写入的块总数排序，为您提供执行最多 I/O 操作的查询列表。

使用 pg_stat_statements 识别低效的索引使用 这是 pg_stat_statements 非常棒的一点——它可以帮助您区分哪些查询有效地利用了索引，哪些没有。下面是一个查询示例，可用于根据读取的块数识别前 10 个查询，这可能表明索引使用不足。

SELECT query, shared_blks_read, calls 
FROM pg_stat_statements 
ORDER BY shared_blks_read DESC 
LIMIT 10;

附言：pg_stat_user_indexes 也能帮助您，正如我们在关于索引的文章中所解释的那样。.

PostgreSQL EXPLAIN

PostgreSQL 中的 EXPLAIN 命令是一个强大的工具，用于了解查询的执行计划，提供有关 PostgreSQL 优化器如何处理查询的见解。在输出中，您可以查找一些内容，以提供有关如何修复慢查询的快速反馈。

识别顺序扫描 要查找的主要内容是注意任何顺序扫描。这些操作在大型表上是相当消耗资源的，会导致更长的查询执行时间。识别顺序扫描是识别索引候选者的绝佳方法（也就是说，请务必阅读我们关于索引的文章，以了解所有注意事项）。

让我们以一个示例查询为例，使用 PostgreSQL EXPLAIN 命令检查其执行计划： EXPLAIN SELECT * FROM employees WHERE salary > 50000;

如果输出包含如下行：

Seq Scan on employees (cost=0.00..18334.00 rows=4834 width=78)

这表示正在对 employees 表执行顺序扫描。要对此进行优化，请考虑在 salary 列上创建索引，以便 PostgreSQL 能够快速定位相关行，而无需扫描整个表。

CREATE INDEX idx_employees_salary ON employees(salary);

观察高成本估算 成本估算是 EXPLAIN 输出的另一个重要方面。它们提供了对查询执行各个部分的预期运行时间的洞察。高成本值通常表示查询效率低下。索引、连接优化、限制和偏移量的使用都有助于降低成本估算。

以下是检查执行计划中成本的方法。

EXPLAIN SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

输出可能如下所示：

Nested Loop (cost=0.00..132488.25 rows=5000 width=256)

高成本（如本例中的 132488.25）表明查询中可能存在效率低下的问题。在这种情况下，仔细检查 JOIN 操作、创建或优化索引或重新考虑查询设计可以提高性能。

大型 PostgreSQL 表的硬件注意事项：硬件可以解决我的问题吗？

最后，让我们来谈谈硬件问题。

在处理大型 PostgreSQL 表并开始遇到一些性能问题时，您会很想通过增加硬件来解决问题。这似乎是一个简单的解决方案：您可以增加 CPU 和内存来解决性能问题。但这（通常）不是一个好主意。

依靠硬件来修复未经优化的数据库就像在需要缝针的伤口上贴创可贴。随着 PostgreSQL 表的不断增长，增加 CPU 和内存可能只能提供暂时的解决方案，但从长远来看，情况会恶化。潜在的性能问题很可能会再次出现，而且由于数据量和复杂性的增加，问题会更加严重。

这条规则也有一些例外。例如，在某些情况下，使用只读副本可能是一个可行的选择，尤其是在工作负载以读取为主的情况下。只读副本可以通过处理读取查询来减轻主数据库的负载，从而提高性能和响应能力。

但是，只读副本需要成本：请务必评估工作负载的具体性质和需求，以确定只读副本是否是您要寻找的解决方案，或者您是否应该探索其他优化策略。

此外，不可否认的是，有时增加内存或 I/O 是可行的方法。也许您的负载性质发生了变化，或者现在的需求量更大了。但是，在选择硬件扩展之前，请先尝试所有其他的优化途径。

在运行大型 Postgres 数据库时，您的目标应该是实现最佳性能，而无需不必要地增加运营成本，因为您的账单会快速增加。确保您养成分析查询、评估索引、优化分区策略以及我们今天介绍的所有其他建议的习惯。

增长是好事（如果您知道如何处理）

在我们结束关于处理不断增长的 PostgreSQL 表的介绍时，很明显，采用积极主动的战略态度至关重要。规模挑战是不断增长的 PostgreSQL 数据库固有的一个方面。这是一个好消息，证明了您的应用程序的成功！

随着数据量的增长和查询变得更加复杂，您将开始面临一些性能和管理挑战，但不要不知所措。有很多 PostgreSQL 分区策略和工具（如 Timescale）可以帮助您保持 PostgreSQL 数据库的快速运行。如果您在探索 PostgreSQL 优化的新世界时需要建议，我们随时欢迎您在我们的社区 Slack 和论坛中提问。您并不孤单！请务必查看我们关于此主题的其他资源。