PostgreSQL 数据库操作最佳实践

在使用数据库系统时，数据库操作是用户和应用程序与数据交互的主要方式。数据库操作是允许操作和检索存储在数据库中的数据的命令和过程。它们充当用户（或应用程序）和数据之间的基本接口，支持查询、更新、插入和删除数据的操作。

在 PostgreSQL 的背景下，一个强大的开源对象关系数据库系统，数据库操作是围绕 SQL（结构化查询语言）命令构建的。这些操作是 PostgreSQL 代码中的主要对象，它们促进了存储在数据库中的数据的检索、修改和管理。

在处理数据库操作时，挑战通常在于查询的设计和执行。查询不仅必须产生准确的结果，而且必须有效地执行。这在处理大型数据库时尤其重要，因为重复查询会严重影响性能。数据库操作的效率不仅仅是关于获得正确的结果；而是关于快速以最有效的方式获取数据。

在本文中，我们将介绍一些常用的操作类型，例如 SELECT、ORDER 和 JOIN，并讨论优化这些操作性能的最佳实践。

PostgreSQL 中的数据库操作是什么？

在 PostgreSQL 中，与任何关系数据库系统一样，数据库操作构成了数据交互的支柱。它们可以创建、读取、更新和删除数据——通常称为 CRUD 操作。让我们探讨一些基本的操作：SELECT、ORDER 和 JOIN。

SELECT

The SELECT 命令是使用最广泛的 SQL 操作之一，它允许您精确地指定查询要返回哪些数据。它可以像从表中检索单个列一样简单 ，也可以像使用各种条件和函数组合来自多个表的 数据一样复杂。

示例

 postgres=# SELECT name, city FROM users WHERE city='New York'; ​

  name  |   city
 -------+----------
  Alice | New York
 (1 row)

此查询从纽约市选择所有用户的姓名。

ORDER

The ORDER BY 子句与 SELECT 结合使用，用于对查询返回的结果进行排序。排序用于将查询返回的数据按特定顺序排序，可以是升序 (ASC) 或降序 (DESC)。此操作对于以有意义的方式组织查询结果至关重要，尤其是在处理大型数据集时。

示例:

 postgres=# SELECT name, city FROM users ORDER BY city DESC;
 ​
   name   |     city
 ---------+---------------
  Bob     | San Francisco
  Ian     | San Diego
  Hannah  | San Antonio
  Fiona   | Phoenix
  George  | Philadelphia
  Alice   | New York
  Charlie | Los Angeles
  Evan    | Houston
  Julia   | Dallas
  Diana   | Chicago
 (10 rows)

JOIN

联接对于处理关系数据库至关重要，在关系数据库中，数据通常分布在多个表中。The INNER JOIN 子句用于根据共享列合并来自两个或多个表的多个行，创建组合数据集。

PostgreSQL 还支持其他类型的联接，例如 LEFT JOIN、RIGHT JOIN，以及 FULL OUTER JOIN，它们各自用于根据联接表中匹配数据的是否存在来包含或排除行。

示例

 postgres=# SELECT users.name, orders.amount
 FROM users
 INNER JOIN orders ON users.id = orders.user_id;
 ​
   name   | amount
 ---------+--------
  Alice   | 150.00
  Bob     | 200.50
  Charlie |  99.99
  Diana   | 125.75
  Evan    | 300.20
  Fiona   | 180.00
  George  |  50.50
  Hannah  | 220.00
  Ian     | 110.00
  Julia   | 199.99
 (10 rows)

PostgreSQL 操作的注意事项

随着查询的复杂性增加，优化变得越来越重要。在本节中，了解最佳实践和常见陷阱可以显著提高数据库操作的性能。

操作性能最佳实践

1. 使用 EXPLAIN 监控性能

优化 PostgreSQL 操作性能的最佳实践之一是主动监控和分析 SQL 查询的性能。PostgreSQL 的 EXPLAIN 命令在这个过程中非常有用，因为它允许您了解 PostgreSQL 如何计划执行您的查询。

使用 EXPLAIN 了解查询性能 

PostgreSQL 中的 EXPLAIN 命令显示 SQL 查询的执行计划，详细说明数据库执行查询所采取的步骤。这包括有关如何扫描表、是否使用索引、如何执行联接以及各种操作的成本和时间估计的信息。通过分析 EXPLAIN 的输出，您可以识别和优化查询中的低效之处。

使用 EXPLAIN 的示例 

考虑在 users 表上执行的简单查询，您希望找到特定城市的所有用户

 postgres=# EXPLAIN SELECT * FROM users WHERE city = 'New York';
 ​
                        QUERY PLAN
 ---------------------------------------------------------
  Seq Scan on users  (cost=0.00..10.88 rows=1 width=1036)
    Filter: ((city)::text = 'New York'::text)
 (2 rows)

2. 调整配置参数

根据性能需求调整数据库配置参数对于优化 PostgreSQL 至关重要。PostgreSQL 提供了各种 配置参数，可以调整这些参数来优化数据库环境。

标准设置包括 shared_buffers，它定义分配给缓存数据的内存，以及 work_mem，它规定用于排序和查询的内存量。正确调整这些参数会显著影响数据库性能，提高效率和响应时间。

3. 创建表分区

PostgreSQL 中的分区是一种通过将表分成更小、更易于管理的部分（称为分区）来提高性能和管理大型数据集的策略。这种技术通过允许操作针对较小的数据子集来提高查询响应时间和操作效率。

PostgreSQL 支持多种分区策略，包括范围分区、列表分区和哈希分区，允许灵活地组织数据。通过分区，数据库管理员可以显著减少查询执行时间，优化维护操作并有效地扩展数据库。

4. 创建物化视图

PostgreSQL 中的物化视图提供了一种通过物理存储查询结果并允许您定期刷新它来加快复杂查询的速度。这对于数据保持不变以及昂贵的聚合操作特别有用。

使用物化视图，您可以预先计算并存储复杂查询的结果，从而显著减少后续执行相同查询时检索数据所需的时间。

 postgres=# CREATE MATERIALIZED VIEW user_summary AS
 SELECT city, COUNT(*)
 FROM users
 GROUP BY city;
 SELECT 10

postgres=# select * from user_summary;
      city      | count
 ---------------+-------
  New York      |     1
  Phoenix       |     1
  San Francisco |     1
  Dallas        |     1
  Chicago       |     1
  Houston       |     1
  Philadelphia  |     1
  San Diego     |     1
  San Antonio   |     1
  Los Angeles   |     1
 (10 rows)

上面的示例物化视图 user_summary 将存储按城市划分的用户数量，从而无需每次都重新计算数量即可更快地检索此聚合数据。

PostgreSQL 数据库操作的陷阱

1. 使用通配符 

通配符，尤其是使用 SELECT * 语法，会导致性能问题，因为它们迫使数据库扫描整个表以检索所有列。对于具有大量列或大量数据的表来说，这可能特别缓慢。

示例: 

不要使用

SELECT * FROM users;，而应指定您需要的列，例如 

SELECT id, name FROM users;

2. 表扫描

表扫描是指查询搜索表中的每一行以查找匹配项。这可能效率低下且速度缓慢，尤其是在大型表中。有关优化以避免表扫描的示例，

优化前（表扫描）

SELECT * FROM users WHERE city = 'New York'; 如果 city 列没有索引，这个查询可能会导致全表扫描，对于大型表来说会很慢。

优化后（使用索引）：

首先，如果 city 列不存在索引，就创建一个索引。

CREATE INDEX idx_users_city ON users(city);

然后，运行查询

SELECT id, name FROM users WHERE city = 'New York'; 通过只指定所需的列（id、name）并确保 city 列有索引，PostgreSQL 可以使用索引快速定位行，从而显著减少全表扫描的需要，提高查询性能。

3. 临时表过多

临时表对于中间查询结果很有用，但过多会导致消耗大量内存，影响性能。谨慎使用临时表，确保它们对操作是必要的。考虑使用其他方法，比如公共表表达式 (CTE) 来提高性能。

使用临时表

CREATE TEMP TABLE temp_users AS
SELECT * FROM users WHERE city = 'New York';
SELECT * FROM temp_users WHERE age > 30;
DROP TABLE temp_users;

这种方法使用临时表存储中间结果，如果过度使用可能会降低效率。

使用 CTE 优化

WITH filtered_users AS (
     SELECT * FROM users
     WHERE city = 'New York'
     )
     SELECT * FROM filtered_users
     WHERE age > 30;

这种 CTE 方法在不创建临时表的情况下运行，因此有可能减少内存使用，提高性能。

4. 查询结构过于复杂

复杂的查询，尤其是那些包含多个嵌套子查询或过度连接的查询，可能会降低数据库操作的速度。将复杂的查询分解成更小、更简单的查询，或使用 CTE 来提高可读性和性能。

过于复杂的查询结构示例

SELECT *
 FROM users
 WHERE id IN (
     SELECT user_id
     FROM orders
     WHERE amount > 100 AND order_date > '2021-01-01'
     AND user_id IN (
         SELECT id FROM users WHERE signup_date < '2020-01-01'
     )
 );

这个嵌套子查询示例由于其复杂性，可能会导致执行效率低下。

Timescale 如何改进数据库操作

Timescale 是 PostgreSQL，但它更快，并且针对时间序列数据或类似时间序列的数据进行了优化。它通过自动分区、高效索引和高级聚合函数等功能增强了 PostgreSQL 的功能。这些改进可以实现更快的查询执行、显著的数据压缩和更高效的存储，使 Timescale 特别适合处理各种行业的大规模时间序列数据集，例如物联网、金融服务等。

让我们更详细地了解一下。

指标仪表板和洞察

Timescale 的 指标仪表板 通过提供详细的服务级别洞察（如 CPU、内存和查询级别统计信息）来显著增强数据库服务的监控功能。它支持各种时间范围来查看指标，包括最近一小时、24 小时、7 天和 30 天，每个范围都有特定的粒度。

此外，您可以持续监控数据库服务的健康状况和资源消耗，从而主动管理和优化数据库性能。此功能有助于识别趋势、诊断问题并优化配置以保持最佳服务健康状况。

为了更深入地监控您的查询，您可以使用 洞察，这是我们于 2023 年开发的工具，可以让您更好地了解数据库查询随时间的性能，包括深入查看。

为了构建洞察，我们必须尽可能地扩展 PostgreSQL（Timescale 的基础）。它没有让我们失望。阅读单个 Timescale 实例每天如何摄取超过 100 亿条记录（并且每天都在增长）。

超表

超表 通过时间自动分区时间序列数据，简化管理，同时提高插入和查询性能。您可以像操作常规 PostgreSQL 表一样操作超表，但它们提供了增强功能，可以有效地处理时间序列数据。

通过根据时间（以及可选的，空间）自动创建分区（或“块”），超表确保数据以适合时间序列操作的最佳结构组织。这种结构有助于更快地访问数据，使扩展和管理大型数据集变得更加容易。

持续聚合

持续聚合 通过在后台自动刷新聚合数据来有效地处理时间序列数据聚合。与常规 PostgreSQL 物化视图相比，此功能加快了数据聚合速度，并减少了维护负担。持续聚合跟踪数据集中的更改，并相应地更新超表，而无需手动刷新。它们通过将预聚合数据与尚未聚合的最新数据相结合来支持实时查询，从而确保结果是最新的。

超函数

Timescale 的 超函数 是专为在 TimescaleDB 中高效地分析时间序列数据而设计的专用函数。它们可以快速执行关键的时间序列查询，从而能够对时间序列数据集进行高级分析和提取有意义的信息。

超函数针对超表上的操作进行了优化，可以帮助执行诸如近似计数不同的值、分析计数器和量规、降采样、频率分析、间隙填充和时间加权计算等任务。

改进您的 DBOps

在本文中，我们介绍了各种 PostgreSQL 操作，重点介绍了提高性能的实践。虽然 Timescale 引入了用于时间序列数据处理的增强功能，但我们探讨的核心原则广泛适用于所有数据库管理系统。这些包括有效的监控、战略性数据分区以及高级分析函数的使用。每个实践对于优化数据库操作并确保高效有效的数据管理至关重要。

对于那些有兴趣进一步探索的人来说，Timescale 提供了一套旨在增强 PostgreSQL 功能的功能，值得在更广泛的数据库性能优化背景下进行探索。

在 Timescale 免费开始使用 并简化您的数据库操作，以便您可以专注于应用程序开发。