了解 PostgreSQL

An elephant in a high-tech data center in neon colors.

作者 Dylan Paulus

PostgreSQL 是世界上使用最广泛的开源数据库系统之一，像微软和亚马逊这样的巨头都在使用它。它的流行源于其强大的功能集、可靠性和活跃的开发社区。对于任何开发人员、分析师或数据库管理员来说，学习 PostgreSQL 都是至关重要的。在本指南中，我们将向您介绍 PostgreSQL，探讨其优势和劣势，以及它如何为您的应用程序提供动力。

PostgreSQL 概述

什么是 PostgreSQL？

PostgreSQL 是一个开源的对象关系数据库管理系统 (ORDBMS)，其根源可以追溯到 POSTGRES，POSTGRES 是 1986 年在加州大学伯克利分校计算机科学系开发的关系数据库。多年来，它不断发展壮大，在 1994 年增加了 SQL 查询语言支持，并发展成为我们今天所知的 PostgreSQL。从地下采矿到太空任务存档，改善老年人护理，在日本提供基础设施等等，PostgreSQL 在各个行业的应用证明了它的多功能性。

如前所述，PostgreSQL 被认为是一个 ORDBMS 数据库。ORDBMS 是关系数据库 (RDBMS) 的超集，关系数据库将数据存储在相互关联的表、行和列中。ORDBMS 增加了对面向对象功能的支持，例如用户定义的数据类型和继承。

PostgreSQL 的核心遵循客户端-服务器架构。服务器（PostgreSQL）存储数据，管理 ACID（原子性、一致性、隔离性和持久性）合规性，并处理查询。客户端（如应用程序或命令行工具）连接到 PostgreSQL 服务器并提交 SQL 命令以修改或检索数据。这种架构的优势在于允许多个客户端同时访问数据库。

A diagram representing the client-server connection in PostgreSQL.

PostgreSQL 的优缺点

与任何技术一样，PostgreSQL 也有其自身的优点和局限性。

优点

  • 丰富的功能和扩展： PostgreSQL 开箱即用地提供了广泛的功能，包括事务、外键、视图、触发器以及数组和 JSON 等复杂数据类型。其可扩展性允许添加自定义数据类型、函数甚至编程语言。

  • 开源： 作为一个开源项目，PostgreSQL 受益于庞大而活跃的开发者社区，他们为其持续改进和维护做出了贡献。

  • 性能、可靠性和可扩展性： PostgreSQL 以其卓越的性能、可靠性和可扩展性而闻名，使其适用于处理大型数据集和高流量应用程序。

  • 久经考验： PostgreSQL 已被各行各业的一些最大公司使用超过 30 年。

缺点

  • 单一入口： PostgreSQL 一次只能从一个源摄取数据，这可能会限制其在高容量数据摄取场景中的吞吐量

  • 配置过载： PostgreSQL 具有高度可定制性，这既是它的优点，也使得新手很难充分利用该数据库（例如，微调自动清理可能很麻烦）

  • 列存储限制： PostgreSQL 以基于行的格式存储数据，这对于主要访问列子集的分析工作负载来说效率较低。

“一切皆可 Postgres”

多年来，PostgreSQL 凭借其活跃的开发社区和丰富的扩展集，已发展成为一个高度可靠且功能丰富的数据库系统。扩展增强了 PostgreSQL 的功能，使其能够处理传统关系数据库以外的许多用例。

例如，TimescaleDB 构建在 PostgreSQL 之上，增加了时间序列支持。Pgvector 将 PostgreSQL 转变为向量数据库（适用于机器学习和人工智能），而 pgai 和 pgvectorscale 则对 pgvector 进行了补充，以 使 PostgreSQL 成为更好的 AI 数据库。PostGIS 为 PostgreSQL 增加了地理空间数据和查询功能。最后，PostgreSQL 内置了对 JSON/B 的支持，使其能够支持 NoSQL 模式。

将扩展与 PostgreSQL 广泛的功能集相结合，意味着 PostgreSQL 可以处理您遇到的任何用例。您不再需要为狭窄的用例专门构建各种数据库。PostgreSQL 可以完成所有工作，而不会牺牲速度、安全性和可靠性！

A diagram that shows a complex setup with multiple tools, and a much simpler one where you build your application with Postgres.

要详细了解 PostgreSQL 的多功能性和可用的各种扩展，请查看我们关于“一切皆可 Postgres”的文章。

PostgreSQL 开发

现在我们已经了解了 PostgreSQL 是什么，让我们看看如何使用它。在本节中，我们将快速了解如何使用 Python 连接和查询 PostgreSQL 以及数据管理实践。

使用 PostgreSQL 和 Python 进行构建

Python 是世界上最流行的应用程序开发语言之一。与 PostgreSQL 结合使用时，开发人员可以利用这两种技术的强大功能来构建稳健且可扩展的解决方案。

有许多不同的库允许我们使用 Python 与 PostgreSQL 进行通信。psycopg 库是一个用于 Python 的 PostgreSQL 适配器，它是最受欢迎的。

首先，确保已安装 Python 并且 PostgreSQL 实例正在运行。使用 pip（pip install psycopg 或 python -m pip install psycopg）安装 psycopg。安装完成后，我们可以使用以下示例连接和查询 PostgreSQL（将 psycopg.connect(".....") 替换为指向 PostgreSQL 实例的 有效连接字符串：import psycopg

# Connect to PostgresQL
with psycopg.connect("dbname=mydb user=postgres password=secret") as conn:

    # Create a cursor to perform database operations
	with conn.cursor() as cursor:


		# Execute a SQL query 
		cursor.execute("SELECT * FROM my_table;") 

		# Fetch the results from PostgreSQL, and bring it into memory
		results = cursor.fetchall() 

		# Print all resulting rows from the SQL query   
		for row in results:
			print(row)

此代码段演示了如何建立连接、执行 SQL 查询以及获取结果。有关更深入的示例和最佳实践，请参阅我们关于 使用 PostgreSQL 和 psycopg3 构建 Python 应用程序 的指南。

使用 PostgreSQL 进行数据管理

有效的数据管理对于确保基于 PostgreSQL 的应用程序的完整性、可用性和性能至关重要。我们关于 Postgres 数据管理最佳实践 的指南概述了四个关键支柱：可用性、安全性、文档和自动化。

1. 可用性：您在 PostgreSQL 中的数据应易于访问且可以快速查询。根据您的确切用例设计数据库模式，并使用正确且一致的命名约定。

2. 安全性：安全性对于建立和维护客户信任至关重要，任何有关数据管理的决策都应从安全性开始。

3. 文档：没有比通过文档更好的方式来传达最佳实践和技术决策了。拥有清晰准确的文档可确保您的数据随着时间的推移易于理解和维护。

4. 自动化：一旦建立了其他支柱，我们就可以将它们自动化，以实施最佳实践、测试安全漏洞、创建文档等等。

在处理大量生成的数据（例如，在 时间序列数据 中）时，数据生命周期管理 (DLM) 流程是必要的。DLM 分为五个步骤

1. 摄取和存储： 在 PostgreSQL 中高效地摄取和存储数据。

2. 近期数据查询： 优化用于访问近期数据的查询。

3. 聚合汇总： 为分析工作负载创建和维护聚合数据汇总。

4. 归档数据： 有效地归档和管理历史数据。

5. 删除数据： 识别并安全地删除过时的数据。

The data management lifecycle.

遵循这些最佳实践，您可以使用 PostgreSQL 构建稳健、安全且可扩展的应用程序。

PostgreSQL 性能

随着应用程序变得越来越复杂并且数据量不断增加，优化 PostgreSQL 的性能对于确保平稳运行和积极的用户体验至关重要。

PostgreSQL 性能

我们关于 PostgreSQL 性能 的指南涵盖了表设计的关键方面，包括以下内容

表设计

正确的表设计对 PostgreSQL 的性能起着重要作用。例如，规范化是将数据组织到更小、相关的表中的过程，以减少冗余并提高数据完整性。但是，在规范化和查询复杂性之间需要权衡。过度规范化会导致复杂的连接，并可能降低性能。为您的特定用例在 窄表和宽表 之间取得适当的平衡，对于 PostgreSQL 中的快速查询来说意义重大。

在某种程度上，您的 PostgreSQL 数据库会变得太大，并遇到性能问题。表分区 是一种技术，它可以帮助您使用日期范围或地理区域等条件将大型表分解为更小的物理部分。

查询设计

高效地设计我们的表很重要，但我们还需要优化查询 PostgreSQL 的方式。使用 SELECT 语句时，避免使用 * 返回所有列，而只返回所需的数据（例如，SELECT id, first_name FROM people;）。我们可以通过在表和列上应用 适当的索引 来加速利用 WHERE 子句和连接的查询。重写复杂查询以使用 CTE（公用表表达式） 允许查询计划器缓存结果。

所有这些技术不仅可以使您的查询非常快，还可以减少 PostgreSQL 使用的内存和 I/O 量。

扩展 PostgreSQL

随着应用程序数据和需求的增长，您将需要扩展 PostgreSQL 以保持性能和可用性。在我们关于 扩展 PostgreSQL 的最佳实践 的指南中，我们讨论了多进程和批量摄取、物化视图和分区等主题。

  • 多进程和批量摄取： 利用多进程和批量摄取来提高数据摄取吞吐量。

  • 物化视图： 创建和维护物化视图以提高分析工作负载的查询性能。

  • 分区： 根据时间范围或地理区域等条件对表进行分区，以提高查询性能和维护效率。

Timescale 通过添加以下内容改进了 PostgreSQL 现有的扩展功能

  • 存储空间优化

    • 分层存储 根据数据访问频率的不同，将数据存储在不同的硬件上，从而优化成本并释放空间。

    • 列压缩 合并多行数据以提高查询速度、降低成本并减少磁盘空间。

  • 超级表分区

    • 超级表 支持跨时间和可选的附加属性对时间序列数据进行无缝分区。

  • 连续聚合

结论

PostgreSQL 被证明是一个健壮、功能丰富且高度可扩展的数据库，能够为各行各业的广泛应用提供支持。通过了解其优势、局限性和最佳实践，您可以充分利用其潜力来构建安全、高效和可扩展的解决方案。这只是我们旅程的开始。Timescale 提供了各种各样的 文章和指南 来增强您对 PostgreSQL 的掌握。

要详细了解 Timescale 如何增强您的时间序列工作负载，请查看我们关于 PostgreSQL 和 TimescaleDB：查询速度提高 1000 倍、数据压缩率达 90% 及更多 的博文。