Skip to main content

PostgreSQL 面试

1. 描述 PostgreSQL 与其他关系型数据库系统（如 MySQL）的主要区别。​

  • 开源许可
    • PostgreSQL: OPEN SOURCE
    • MySQL: Initially OPEN SOURCE, was Close source after bought by Oracle
  • SQL standard support
    • PostgreSQL: most standard
    • MySQL: advanced, but has custome language syntax
  • 高级功能和扩展性
    • PostgreSQL: more advance features and easy to extends
    • MySQL: less than PostgreSQL.
  • Storage engine
    • PostgreSQL: Single engine, designed to balance advanced features, data integrity and performance
    • MySQL: multiple engine, differents engines have differents use cases

对于需要高级特性和强大扩展性的复杂应用，PostgreSQL 可能是更好的选择。而对于那些寻求速度和简易性，特别是在 Web 开发环境中的应用，MySQL 可能更受青睐。

2. PostgreSQL 中的索引是如何工作的，它们为什么重要？​

在 PostgreSQL 和其他关系型数据库系统中，索引是一个数据库结构，它可以帮助加速数据检索操作。索引在数据库表的一个或多个列上创建，为这些列的数据建立一个快速查找的数据结构。

建立索引是一种使用空间换时间的做法，我们提前存储好数据的“目录”，然后通过"目录"中的某个属性快速查到数据在数据库表的准确位置，也可以查找多个数据库表中的结果。

索引可能造成什么问题？​

  1. 性能问题: 如果索引不恰当，比如索引过多或设计不合理，它们可能降低数据库的写入性能，因为每次数据变更都需要更新所有相关索引。
  2. 空间问题: 索引需要占用额外的存储空间。对于大型数据库，这可能会成为问题，特别是如果有很多索引或索引的大小非常大时。
  3. 维护成本: 索引需要定期维护，例如重建或优化，以保持其性能。如果管理不当，可能会导致性能下降。
  4. 查询性能不佳: 如果索引选择不当，可能无法有效地加速查询。例如，选择错误的索引列或在低基数（即列中有许多重复值）的列上创建索引可能不会带来预期的性能提升。
  5. 复杂性增加: 过多或复杂的索引可能使得数据库的维护和优化更加困难，特别是在大规模或高度动态的环境中。

3. 解释 PostgreSQL 中的事务及其重要性。​

在 PostgreSQL 和其他数据库管理系统中，事务是数据库操作的一个基本概念，它们用于维护数据库的完整性和一致性。

一个事务是一系列操作，这些操作作为一个单一的工作单元被执行，事务要么完整地执行，要么完全不执行。事务的重要性在于它们提供了一个稳固的机制来处理并发访问和系统故障，保证数据的准确性和可靠性。

事务的重要性和特性通常由 ACID 原则描述：

  • 原子性（Atomicity）: 事务的所有操作要么全部完成，要么全部不完成。如果事务中的任何操作失败，整个事务都会回滚到事务开始之前的状态，就像这些操作从未发生过一样。
  • 一致性（Consistency）: 一致性确保数据库在事务开始之前和事务成功提交之后都保持一个一致的状态。事务的执行不会违反数据库的完整性约束。
  • 隔离性（Isolation）: 事务的隔离性保证了并发执行的事务之间互不干扰。一个事务的中间状态不应该对其他事务可见。隔离级别可以根据需要调整，PostgreSQL 提供了几种隔离级别，包括读未提交、读已提交、可重复读和串行化。
  • 持久性（Durability）: 一旦事务被提交，它所做的更改就是永久性的，即使之后发生系统故障，这些更改也不会丢失。

4. 在 PostgreSQL 中如何实现数据备份和恢复？​

数据备份

  • SQL 转储（pg_dump/pg_dumpall）: pg_dump 工具用于备份单个数据库。它生成一个 SQL 脚本文件，包含创建数据库对象（如表、索引、视图等）和填充它们数据的 SQL 语句。 pg_dumpall 用于备份整个 PostgreSQL 实例，包括所有数据库和全局对象（如角色和表空间）。 这些工具产生的备份是逻辑备份，即以文本形式存储数据库的结构和内容。
  • 文件系统级备份: 这种备份涉及复制数据库文件系统上的文件。它可以通过文件系统的快照功能 File Snapshots 或者直接复制数据目录来完成。 为了确保一致性，在进行文件系统级备份之前，通常需要将数据库置于一致性的备份模式下（使用 pg_start_backup() 命令）并在完成后结束该模式（使用 pg_stop_backup() 命令）。
  • 连续归档（Continuous Archiving）: PostgreSQL 的 WAL（写入前日志, Write-Ahead Logging）可以用于连续归档，这里的日志文件包含了所有改变数据库状态的信息。 通过配置 postgresql.conf 文件中的归档选项，可以持续地备份 WAL 文件。这种方法允许进行点对点的恢复。

数据恢复

  • 使用 pg_dump/pg_dumpall 恢复: 对于使用 pg_dump 或 pg_dumpall 生成的备份，可以使用 psql 命令或其他 PostgreSQL 兼容的 SQL 执行工具 (pg_restore) 来运行备份文件，以恢复数据库或整个实例。
  • 文件系统级恢复: 如果进行的是文件系统级备份，恢复过程通常涉及停止数据库服务，将备份的文件复制到原始位置，然后重新启动数据库服务。
  • 使用 WAL 文件恢复: 对于使用连续归档的备份，恢复通常涉及将基础备份（基准备份）和归档的 WAL 文件放置在恢复目录中，并配置恢复参数（如 recovery.conf 文件）。然后启动数据库来应用 WAL 日志，并恢复到指定的时间点。

5. 什么是 PostgreSQL 的触发器（Trigger）和存储过程（Stored Procedure），它们有什么用途？​

触发器（Trigger）

触发器是数据库中的一种特殊类型的存储过程，它自动地在特定数据库表上的某个事件发生时被执行，被动触发。这些事件通常包括插入（INSERT）、更新（UPDATE）和删除（DELETE）操作。触发器可以在事件发生之前或之后被触发，称为 BEFORE 触发器和 AFTER 触发器。

触发器的主要用途包括

  • 数据验证: 在数据写入数据库之前进行额外的校验，以确保数据的完整性和一致性。
  • 自动化计算: 在数据插入或更新时自动计算新值，例如，更新总计或库存数量。
  • 审计和日志记录: 自动创建数据更改的历史记录，用于审计或分析目的。
  • 级联操作: 在一张表上的更改自动触发其他表的相关更改，例如，更新外键关联的表。
  • 业务逻辑封装: 在数据库级别封装业务规则，以便无论通过哪种应用程序或接口访问数据，这些规则都被执行。

存储过程（Stored Procedure）

存储过程是一组为了完成特定功能的 SQL 语句和可选的控制流语句的集合，这些语句作为一个单元编译存储在数据库中，可以通过指定存储过程的名字来调用执行，主动触发。PostgreSQL 使用其自有的 PL/pgSQL（过程语言/PostgreSQL 结构化查询语言）或其他支持的过程语言来编写存储过程。

存储过程的主要用途包括

  • 业务逻辑的封装: 将复杂的业务逻辑封装在数据库内部，以便可以在不同的应用程序中重用。
  • 性能优化: 减少应用程序和数据库服务器之间的通信次数，因为一次调用可以执行多个操作。
  • 安全性: 通过控制对存储过程的访问，可以限制数据库用户对直接操作数据的能力。
  • 模块性和维护: 将复杂的操作封装在存储过程中有助于代码模块化，便于维护和更新。

为什么选择存储过程而不是函数(User-Defined Functions，UDF)?

选择存储过程而不是函数通常基于对事务控制、复杂业务逻辑执行和安全性的需求。如果需要在单一操作中执行多个步骤，特别是涉及到事务控制（如回滚或提交）的情况下，存储过程是更合适的选择。此外，存储过程能够返回多个结果集，这在生成报告或多步骤查询中非常有用。

然而，这不是说函数不重要。在需要将逻辑嵌入到 SQL 查询中，或者需要一个可重用计算组件时，函数是一个很好的选择，特别是当这些计算需要在不同的存储过程或查询中重复使用时。

6. 解释在 PostgreSQL 中使用视图（View）的优势。​

  • 简化复杂的查询, 模块化易于管理: 视图可以用来封装复杂的 SQL 查询，使得用户不必每次都编写冗长复杂的代码。通过视图，用户可以像访问普通表一样访问这些预定义的查询结果。
  • 安全性: 视图可以限制用户对特定数据列或行的访问。通过创建仅显示特定数据的视图，可以控制用户访问的数据量和类型，增强了数据的安全性。
  • 方便重用，提供数据一致性: 视图可以在多个查询之间重用，这有助于保持数据库查询的一致性。由于所有查询都是通过相同的视图进行的，因此减少了由于查询差异造成的错误。

7. 在 PostgreSQL 中如何进行性能调优？​

  • 使用 EXPLAIN 分析查询: 使用 EXPLAIN 和 EXPLAIN ANALYZE 命令分析查询的执行计划，可以帮助你理解 PostgreSQL 如何执行你的查询，并找出任何潜在的性能瓶颈。
  • 优化查询:
    • 避免在 WHERE 子句中使用函数和类型转换，它们可能会阻止索引的使用。
    • 使用连接 (JOINs) 替代子查询，这通常更高效。
    • 限制使用 SELECT *，仅选择必要的列。
    • 在适当时使用 聚合函数 Aggregate function 和 窗口函数 Window function 来减少数据的处理量。
  • 创建和维护索引:
    • 为经常用于查询条件或排序的列创建索引。
    • 考虑使用部分索引或复合索引以满足特定查询需求。
    • 定期监控和重新评估索引的有效性，清理不再使用或重复的索引。
  • 服务器配置调整:
    • 调整 PostgreSQL 配置文件（通常是 postgresql.conf）中的参数，比如 shared_buffers、work_mem、maintenance_work_mem、checkpoint_segments、wal_buffers 等来优化内存和写入操作。
    • 根据负载类型和硬件配置调整自动清理 (autovacuum) 设置。
  • 数据库设计:
    • 正规化数据库以减少数据冗余，但请注意，在某些情况下，反规范化可能有助于减少连接并提高性能。
    • 为大表实施分区，以提高查询性能和管理的便捷性。
  • 物化视图: 对于经常执行且开销较大的查询，可以使用物化视图来存储结果，这样在查询时可以快速检索数据。
  • 硬件资源:
    • 确保服务器有足够的内存和高性能的 IO 系统（例如，使用 SSD）。
    • 考虑使用 RAID 配置或更快的网络基础设施。
  • 并发和连接管理:
    • 使用连接池来管理数据库连接，减少连接和断开连接的开销。
    • 调整 max_connections 设置，确保系统负载平衡，避免过多的并发连接导致性能下降。
  • 定期维护:
    • 定期运行 VACUUM（如非自动的话），ANALYZE 和 REINDEX 命令来清理碎片，并更新统计信息以帮助查询优化器做出更好的决策。
  • 使用专业工具和扩展:
    • 使用像 pgBadger 这样的日志分析工具来分析查询日志，找出慢查询。
    • 考虑使用性能相关的 PostgreSQL 扩展，如 pg_stat_statements 用于监控执行频率和执行时间。

8. PostgreSQL 支持哪些数据类型，它们有什么特点？​

  • 数值类型
    • smallint：小范围的整数，通常是 16 位存储。
    • integer：典型的整数类型，通常是 32 位存储。
    • bigint：大范围的整数，通常是 64 位存储。
    • decimal / numeric：可指定精度的精确数值类型，适用于金融和其他对精度要求高的领域。
    • real：浮点数，通常是 32 位。
    • double precision：双精度浮点数，通常是 64 位，提供更大的精度和范围。
    • serial：自动递增的整数，常用于自动创建唯一标识符。
    • bigserial：类似于 serial，但为 bigint 类型，提供更大的范围。
  • 字符类型
    • char(n)：固定长度的字符串，不足部分用空格填充。
    • varchar(n)：可变长度的字符串，可以指定最大长度。
    • text：可变长度的字符串，无长度限制。
  • 日期/时间类型
    • timestamp：日期和时间，无时区。
    • timestamp with time zone：日期和时间，包含时区信息。
    • date：仅日期，没有时间。
    • time：仅时间，无时区。
    • time with time zone：时间，包含时区信息。
    • interval：时间间隔。
  • 布尔类型
    • boolean：逻辑布尔类型，值为 TRUE、FALSE 或 NULL。
  • 枚举类型
    • enum：一组静态、有序的值，类似于其他编程语言中的枚举。
  • JSON 类型
    • json：JSON 数据，以文本形式存储。
    • jsonb：JSON 数据，以二进制形式存储，支持索引。
  • 几何类型
    • point：表示二维平面上的点。
    • line：表示直线。
    • lseg：线段。
    • box：矩形。
    • path：几何路径。
    • polygon：多边形。
    • circle：圆形。
  • 网络地址类型
    • cidr：IPv4 或 IPv6 网络地址。
    • inet：IPv4 或 IPv6 地址和可选的子网掩码。
    • macaddr：MAC 地址。
  • 数组类型
    • PostgreSQL 支持上述大多数数据类型的数组。
  • 二进制数据类型
    • bytea：用于存储二进制数据。
  • UUID 类型
    • uuid：用于存储通用唯一识别符（UUID）。
  • XML 类型
    • xml：用于存储 XML 数据。
  • 组合类型
    • 用户可以根据需要定义自己的组合类型，这些类型可用于表示表中的一行或复杂的数据结构。
  • 范围类型
    • PostgreSQL 允许创建表示数值范围的类型，例如日期范围。
  • 自定义类型
    • 用户可以定义自己的数据类型，甚至可以创建自己的索引和操作符。

9. 如何在 PostgreSQL 中管理用户和权限？​

  • 创建用户/角色
    • CREATE ROLE new_user WITH LOGIN PASSWORD 'password';
    • CREATE ROLE new_user WITH LOGIN CREATEDB PASSWORD 'password';
  • 删除用户/角色
    • DROP ROLE existing_user;
  • 修改用户/角色
    • ALTER ROLE user_name WITH PASSWORD 'new_password';
  • 授予权限
    • GRANT SELECT ON table_name TO user_name;
    • GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name;
  • 撤销权限
    • REVOKE SELECT ON table_name FROM user_name;
    • REVOKE ALL PRIVILEGES ON DATABASE db_name FROM user_name;
  • 锁定和解锁用户账户
    • ALTER ROLE user_name NOLOGIN;
    • ALTER ROLE user_name LOGIN;

10. PostgreSQL 是如何处理并发读写的？​

PostgreSQL 处理并发读写的主要机制是基于多版本并发控制（MVCC, Multi-Version Concurrency Control）模型。MVCC 允许在不同事务中的用户同时读取和修改数据库而互不干扰。这种方式避免了读写操作之间的锁争用，提高了系统的并发性能。

  • 事务和快照隔离
    • 事务 (Transactions)
      • 事务隔离级别
        • RAED UNCOMMITTED (no support in PostgreSQL, raise to READ COMMITTED)
        • READ COMMITTED
        • REPEATABLE READ
        • SERIALIZABLE
    • 快照隔离 (Snapshot Isolation)
  • 版本控制和可见性
    • 版本控制 (Versioning)
    • 可见性 (Visibility)
  • 锁
    • 行级锁 (Row-Level Locks)
    • 表级锁 (Table-Level Locks)
    • 意向锁 (Intent Locks)
  • 写前日志 (WAL)