PostgreSQL常见面试题
Oracle、MySQL、PG之间的区别？
1、Oracle 12c、MySQL和PG都可以是单实例多数据库模式。
2、PG和Oracle是多进程模式，MySQL是单进程多线程模式。在并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。
3、PostgreSQL没有回滚段，而Oracle与MySQL innodb都有回滚段。
4、PostgreSQL完全免费，而且是BSD协议，Oracle是商业数据库，不开源，MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制。
5、PostgreSQL在复杂SQL的执行、存储过程、触发器方面比MySQL支持的要好的多，是去O最佳的数据库。
6、Oracle和PG支持物理备库，而MySQL只支持逻辑主从复制。
7、Oracle支持堆表，也支持索引组织表；PostgreSQL只支持堆表，不支持索引组织表；Innodb只支持索引组织表。
8、PG、SQL Server、Oracle的默认的事务隔离级别为Read Committed（提交读），MySQL的默认的事务隔离级别为Repeatable Read（可重复读）。
9、PG、Oracle默认的块大小为8KB，而MySQL默认数据页大小为 16 KB
详情请参考：https://www.xmmup.com/pgoraclehemysqldequbieduibi.html
PG中有关职工表SQL查询的20道题
请参考：https://www.xmmup.com/pgzhongyouguanzhigongbiaosqlchaxunde20daoti.html
谈谈你对PG体系结构的理解
主要从日志文件、参数文件、控制文件、数据文件、redo日志(WAL)、后台进程这几个方面来介绍一下PostgreSQL的架构，请参考：
常见高可用方案
主备流复制：
PG 14 + Pgpool-II + Watchdog 实现高可用（自动故障转移+读写分离+负载均衡）
PostgreSQL使用Pgpool-II实现读写分离+负载均衡
PostgreSQL高可用之repmgr（1主2从+1witness）+Pgpool-II实现主从切换+读写分离+负载均衡
PG高可用集群之Patroni + etcd + HAProxy + keepalived + Prometheus + Grafana监控 部署
PostgreSQL 11版本基于Pacemaker+Corosync+pcs的高可用搭建
常见分布式方案：
PG高可用之Citus分布式集群搭建及使用：https://www.xmmup.com/pggaokeyongzhicitusfenbushijiqundajianjishiyong.html
PG分布式之PGXL安装部署：https://www.xmmup.com/pgfenbushizhipgxlanzhuangbushu.html
PG分布式之Greenplum安装配置：https://www.xmmup.com/greenplumanzhuangpeizhi.html
双主方案：
1、使用使用Bucardo搭建PG的双主：参考：https://www.xmmup.com/shiyongbucardodajianpgdeshuangzhu.html
2、使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
3、OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递
PG中的事务回卷
请参考：https://www.xmmup.com/pgzhongdemvccduobanbenbingfakongzhi.html#shi_wu_hui_juan
PostgreSQL简介
PostgreSQL是对象关系数据库管理系统最常用的语言之一。它主要用于大型 Web 应用程序。它是一个面向对象的关系型开源数据库系统。它非常强大，允许用户轻松扩展任何系统。它扩展了 SQL 语言并采用多种功能来安全地扩展和存储复杂的数据工作负载。
Postgres或在 SQL 世界中简称为Postgresql是广泛和流行的对象关系数据库管理系统之一，主要用于大型 Web 应用程序。它是功能强大的开源对象关系数据库系统之一。它通过结合四个基本概念来提供额外的强大功能，以便用户可以毫无问题地扩展系统。它扩展并使用结合各种功能的 SQL 语言来安全地扩展和存储复杂的数据工作负载。
PostgreSQL 的特点
- 语言支持：PostgreSQL 支持四种标准的过程语言，包括 PL/pgSQL、PL/Tcl、PL/Perl 和 PL/Python。
- 索引：PostgreSQL 索引支持 B+ 树、哈希、广义搜索树和广义倒排索引。个人还可以设计自己的个性化索引。
- 视图：PostgreSQL 支持生成虚拟表的视图。
- 规则：PostgreSQL 支持规则，允许重写查询的层次结构。
- 继承：在 PostgreSQL 中，可以将表配置为通过继承从“父”表继承属性。
- 用户定义的函数：它有助于构建几乎所有新的数据库对象，包括转换、转换、数据类型、域、函数、聚合函数和索引，包括自定义种类运算符的自定义索引。
以下是 PostgreSQL 的一些优点：
- 稳定的
- 可靠的
- 可扩展
- 简单易学
- 开源
- 专为高容量环境而设计
- 跨平台
- 更好的支持
灵活的
以下是 Postgresql 的一些主要功能：
- 对象关系数据库
- 支持主流操作系统
- 支持 SQL 和复杂 SQL 查询的可扩展性
- 嵌套事务
- 灵活的 API 和数据库验证
- 多版本并发控制 (MVCC) 和过程语言
- WAL 和客户端服务器
- 表继承和异步复制
PostgreSQL的核心优势是什么？
以下是 PostgreSQL 的一些特性：
- 关系对象数据库。
- SQL 支持和可扩展性
- API 和数据库验证的灵活性。
- MVCC 和过程语言，
- WAL 和客户端-服务器。
PostgreSQL有什么缺点吗？
是的。有一些缺点。其中一些包括以下内容：
- 它在性能方面比 MySQL 慢。
- 与 MySQL 相比，它没有大量开源应用程序的支持。
- 由于它更注重兼容性，因此为提高速度而进行的更改需要更多的工作。
PostgreSQL中表空间的用途是什么？
它是磁盘中的一个位置。在此，PostgreSQL 存储数据文件，其中包含索引和表等。
列出PostgreSQL支持的各种数据类型。
以下是添加到 PostgreSQL 的一些新数据类型：
- UUID
- 数值类型
- 布尔值
- 字符类型
- 时间类型
- 几何图元
- 任意精度数值
- XML
- 数组等
如何在 PostgreSQL 中存储二进制数据？
我们可以使用字节或使用大对象功能将二进制数据存储在 PostgreSQL 中。
简要说明 Postgresql 中的功能
在任何地方，函数都是重要的部分，因为它们有助于在服务器上执行代码。编程函数的一些语言是 PL/pgSQL，一种 PostgreSQL 的本地语言，以及其他脚本语言，如 Perl、Python、PHP 等。也可以使用名为 PL/R 的统计语言来提高函数的效率。
PostgreSQL 中的字符串常量是什么？
PostgreSQL 中的字符串常量是由单引号 (') 限定的某个字符的序列。它在插入字符或将字符传递给数据库对象时使用。PostgreSQL 允许使用单引号，但由 C 样式的反斜杠集成。它用于数据解析。
1 | 'This is a string Constant' |
PostgreSQL 的令牌是什么？
在 PostgreSQL 中，令牌充当源代码的构建元素。它们由各种特殊字符符号组成。标记是主要由预定义的 SQL 命令和含义组成的关键字。命令包含由分号 (';') 分隔的标记序列。这些可能包括常量、带引号的标识符、附加标识符、关键字或常量。通常，空格分隔标记。
PostgreSQL中的令牌是任何源代码的构建块。众所周知，它们包含许多特殊字符符号。这些可以被视为常量、引用标识符、其他标识符和关键字。作为关键字的标记由预定义的 SQL 命令和含义组成。标识符用于表示变量名称，如列、表等。
什么是 PostgreSQL 索引？
索引是一种特殊的 PostgreSQL 工具，用于改进从数据库中检索数据。数据库索引类似于书籍索引。索引提供对索引列中所有值的访问。PostgreSQL 索引允许数据库服务器定位和检索特定框架中包含的特定行。B-tree、hash、GiST、SP-GiST、GIN 和 BRIN 是其他示例。在 PostgreSQL 中，用户确实可以自由地创建自己的索引。然而，索引增加了数据处理操作的复杂性并且很少被使用。
PostgreSQL 的索引是内置的函数或方法，例如GIST 索引、哈希表和B-tree（二叉树），用户可以使用这些函数或方法以向后的方式扫描索引。用户还可以定义他们的 PostgreSQL 索引。
如何建立 PostgreSQL 数据库？
有两种方法可以生成数据库。CREATE DATABASE SQL 命令首先出现。
使用以下语法，我们可以构建数据库：
CREATE DATABASE ;
第二种方法使用 createdb 命令。
我们可以使用以下语法建立数据库：createdb [option…] [description]。
根据使用场景，createDB 命令可能需要很多参数。
在PostgreSQL中定义删除数据库的过程。
我们可以使用以下命令删除数据库：
- 我们可以使用 SQL 命令 - DROP DATABASE
- 另外，我们可以使用“dropdb”，这是一个可行的命令行
如何在 PostgreSQL 中创建表？
您可以通过定义表的名称以及每列的名称和类型来创建新表：
1 2 3 4 5 6 7 8 9 | CREATE TABLE [IF NOT EXISTS] table_name ( column1 datatype(length) column_contraint, column2 datatype(length) column_contraint, . . . columnn datatype(length) column_contraint, table_constraints ); |
对比“PostgreSQL”和“MongoDB”
PostgreSQL 是一个 SQL 数据库，其中数据存储在表的行和列中。它支持实体关系完整性和 JOINS 等概念。PostgreSQL 查询语言是 SQL。PostgreSQL 支持垂直缩放。这就需要使用大型服务器来存储数据。这导致需要停机时间来更新。如果您的应用程序需要关系数据库，或者您需要执行超出 SQL 功能的复杂查询，它的性能会更好。相比之下，MongoDB 是一个 NoSQL 数据库。不需要模式，因此可以存储非结构化数据。数据保存在 BSON 文档中，用户可以修改其结构。MongoDB 查询是用 JavaScript 编写的。由于其对水平扩展的适应性，可以根据需要添加额外的服务器，而停机时间很少甚至没有。
什么是PostgreSQL的多版本并发控制？
PostgreSQL 使用 MVCC（也称为多版本并发控制）来实现事务。它用于防止系统中意外的数据库锁定。每个查询数据库的事务都会看到不同版本的数据库。这可以防止显示不一致的数据并为每个数据库会话提供事务隔离。用于读取数据的 MVCC 锁不会干扰为写入数据而获取的锁。
pgAdmin 到底是什么？
pgAdmin 是一个基于 Web 的 GUI 实用程序，用于与 Postgres 数据库会话进行交互。它适用于本地和远程服务器。它的最新版本 pgAdmin4 是在 PostgreSQL 许可证下分发的。创建 pgAdmin4 需要完全重建原始 pgAdmin 程序。这个版本是由 Javascript/jQuery 和 Python 混合而成的。pgAdmin 现在可以用作桌面运行时或 Web 应用程序服务器，具体取决于您的需要。
Pgadmin 是一个众所周知的功能，可以形成图形前端管理工具。此功能在艺术许可下发布的免费软件中可用。Pgadmin iii 是在艺术许可下发布的新数据库管理工具。
Postgresql 中使用了哪些不同类型的数据库管理工具？
有很多数据管理工具，它们是
- phppgadmin
- psql
- Pgadmin
其中，phppgadmin 是最受欢迎的。这些工具中的大多数是前端管理工具和基于 Web 的界面。
PostgreSQL中的数据库是如何删除的？
使用该语法，可以在 PostgreSQL.DROP DATABASE [IF EXISTS] 中删除数据库；
请注意，只能丢弃不活动的数据库。
模式由什么组成？
模式是数据库的元素，包括表。它们包括数据类型、函数和运算符，以及其他命名对象类型。对象名称跨模式兼容；与数据库不同，模式划分更自由。这表明用户可以访问他们链接到的数据库中任何模式中的对象，直到他们被授予适当的权限。当多个用户必须访问单个数据库而不相互干扰时，模式非常有用。它有助于将数据库项目组织成逻辑类别，以便更好地管理。为了防止基于名称的冲突，第三方应用程序可以放置在不同的模式中。
SQL 和 PostgreSQL 最显着的区别是什么？
PostgreSQL 是一个复杂的 SQL 变体。无法更新 PostgreSQL 视图。PostgreSQL 不支持计算列。但是，它确实提供了功能索引。在 PostgreSQL 中，复制采用报告的形式。PostgreSQL 提供动态的操作。
PostgreSQL 服务器提供多个级别的加密和灵活性，以提高数据安全性，防止在不安全的网络场景中泄露。同时，SQL server 旨在提供一个安全的数据库平台。为此，它包含多种功能，可以加密数据、减少授权和保护数据免受不道德行为的影响。
PostgreSQL 是 SQL（结构化查询语言）的扩展或高级版本。您可以在这两者之间看到以下差异：
- 我们不能像在 SQL 中那样更新 PostgreSQL 数据库中的视图。
- 在 PostgreSQL 中，不需要像在 SQL 数据库中那样创建 DLL（动态链接库）来查看代码的活动。
- 此外，SQL 提供计算列，但 PostgreSQL 不提供。
- SQL 不支持动态操作，但 PostgreSQL 支持它们。
详细解释预写日志
预写日志 (WAL) 对于 Postgres 的持久性和数据一致性至关重要。所有修改最初都被捕获到这个仅附加的日志中，然后被捕获到磁盘上的数据文件中。使用预写日志记录，您可以保护您的数据免受损坏。由于这种方法，所有操作和更改的完整记录得以保存。众所周知，在实施或更新数据库更改之前备份它们可以提高某些数据库的稳定性。备份日志保存在这里，以防数据库出现故障。当 Postgres 强制执行写操作时，它会创建 WAL（例如，INSERT、UPDATE 等）。在 WAL 创建超出磁盘外 WAL 归档的能力或高数据库负载降低归档器性能的情况下，WAL 容量是有限的。
WAL 或预写日志记录是确保数据完整性的标准方法。将操作和更改写入事务日志是一种协议或正确规则。众所周知，此功能通过在任何更改或更新数据库之前记录更改来提高数据库的可靠性。这在数据库崩溃的情况下提供数据库的日志。这有助于从中断的点开始工作。
非聚集索引的定义是什么？
PostgreSQL 中的非聚集索引是一种简单的索引，用于快速检索数据，不保证唯一性。它包括对保存数据的其他地方的引用。这也称为二级索引。您可以根据需要在给定表上拥有该类别的多个索引。非聚集索引类似于文档的“目录”。我们先检查页码，然后再检查页码以查看整个内容。它保留一个指向相应堆数据的指针，以根据索引获取整个数据。这与知道页码然后转到该页面以检索页面的实际内容完全相同。
在非聚集索引中，索引行的顺序与实际数据中的顺序不匹配。
聚集索引和非聚集索引有什么区别
- 聚簇索引 是一种索引类型，用于根据键值对表数据行进行排序。在 RDBMS 中，主键允许我们基于该特定列创建聚集索引。
- 非聚集索引（或常规 b 树索引）是行的顺序与实际数据的物理顺序不匹配的索引。相反，它按构成索引的列排序。
PostgreSQL如何提供安全性？
PostgreSQL 采用两层安全性。
- 网络级安全——利用 Unix 域套接字、TCP/IP 套接字和防火墙实现网络级安全。
- 传输级安全性- 传输级安全性采用 SSL/TLS 来确保安全的数据库通信
- 数据库级安全性——角色和权限、行级安全性 (RLS) 和审计都是数据库级安全性的特征。
什么是 Ctid字段？CTIDs 字段有什么用？
CTIDs 是一个字段，存在于每个 PostgreSQL 表中，已知它可以根据特定表中的块和偏移位置来识别特定的物理行。它们被索引条目用来指向物理行。它对于表中的每条记录都是唯一的，并且很容易表示元组的位置。逻辑行的 CTID 在更新时会发生变化，因此 CTID 不能用作长期行标识符。但是，当预期没有竞争更新时，识别事务中的行有时很有用。
CTIDs 字段有助于根据其块定位表中的某些物理行，并平衡该表中的位置。
了解多版本并发控制（MVCC）？
MVCC 或更广为人知的多版本并发控制用于避免不必要的数据库锁定。消除了用户的时间延迟，以便人们可以轻松地登录到他的数据库。所有的交易都保存完好作为记录。当其他人在内容上时会发生时间延迟。
PostgreSQL 中的多版本并发控制或 MVCC 用于避免数据库不必要的锁定。这消除了用户登录其数据库的时间延迟。当其他人访问内容时，会出现此功能或时间延迟。所有的交易都被保存为记录。
更多请参考：https://www.xmmup.com/pgzhongdemvccduobanbenbingfakongzhi.html
可以在 PostgreSQL 中使用什么选项来使事务看到在事务的先前部分中受影响的行？
SQL标准主要针对三种现象定义了四个级别的事务隔离。并发事务之间必须防止这三种现象。不希望出现的现象是：
- 幻读：重新执行查询的事务，返回一组满足搜索条件的行，然后发现已经满足条件的行集由于另一个最近提交的事务而发生了变化。
- 不可重复读取：一个事务重新读取它之前读取的数据，然后发现该数据已经被另一个事务（自初始读取后提交）修改。
- 脏读：读取由并发未提交事务写入的数据时的事务是脏读。
PostgreSQL 中的命令 enable-debug 是什么？
命令 enable-debug 用于启用所有应用程序和库的编译。此过程的执行通常会阻碍系统，但也会放大二进制文件的大小。存在的调试符号通常有助于开发人员发现可能与他们的脚本相关的错误和其他问题。
解释 PostgreSQL 的历史
PostgreSQL的起源可以追溯到 1986 年，作为加州大学伯克利分校 POSTGRES 项目的一部分，并在核心平台上进行了 30 多年的积极开发。它可以在所有主要操作系统上运行，并且自 2001 年以来一直符合 ACID。它还具有诸如 PostGIS 数据库扩展器之类的附加组件。在 MAC OS 中，Postgresql 是默认数据库。Michel Stonebraker 是 Postgresql 之父，他发起了支持当代数据库系统的 Post Ingres 项目。PostgreSQL 的开发人员将 PostgreSQL 发音为 Postgres缩写 因为在大多数关系数据库中普遍支持 SQL 标准。PostgreSQL，最初称为 Postgres，是由 UCB 的计算机科学教授 Michael Stonebraker 创建的，他后来成为 Informix Corporation 的 CTO。
Stonebraker于 1986 年创建了 Postgres，作为其前身 Ingres 的后续项目，Ingres 现在归 Computer Associates 所有。因此，Postgres 这个名字从它的前身（如“在 Ingres 之后”）中脱颖而出。从 1977 年到 1985 年开发的 Ingres 一直是根据经典 RDBMS 理论创建数据库系统的练习。Postgres 开发于 1986-1994 年，是一个旨在在数据库概念（例如探索“对象-关系”技术）方面开辟新天地的项目。作为企业级数据库，PostgreSQL 拥有复杂的功能，例如多版本并发控制 (MVCC)、时间点恢复、表空间、异步复制、嵌套事务（保存点）、在线/热备份、复杂的查询计划器/优化器和用于容错的预写日志记录。
Postgre 9.1 的新特性是什么？
现在最新版都pg 15了。。。。
在更新项目的过程中，人们永远无法确定哪些功能会进入，哪些功能不会成功。该项目具有精确和严格的质量标准，并且某些补丁可能会或可能不会在设定的截止日期之前匹配它们。目前，9.1 版本正在开发一些重要功能，包括 JSON 支持、同步复制、最近邻地理搜索、列级别的排序规则、SQL/MED 外部数据连接、安全标签以及仅索引访问。然而，这个列表很有可能在 Postgre 9.1 发布时完全改变。
比较“PostgreSQL”和“NoSQL”
表达“NoSQL”包含了作为非关系数据库一部分的广泛的实现集合。这包括诸如 TokyoCabinet 之类的微型嵌入式数据库、诸如 Hadoop 之类的大规模数据处理平台以及介于两者之间的一切。简而言之，几乎不可能评论 NoSQL 作为典型类所包含的范围。
在非关系数据库和关系数据库之间进行选择也很普遍，因为两者已经并存了四十多年。事实上，用户应该根据他们当前的应用需求来选择数据库的特性、社区支持和实现。此外，在大型项目中使用多种不同的数据库正变得越来越普遍，而不是趋势。此外，PostgreSQL 的用户也不例外。
Postgresql 中的统计信息如何更新？
为了在 PostgreSQL 中更新您的统计信息，需要调用一个称为显式“真空”的特殊函数。要做的方法是创建一个 Vacuum，其中使用 Analyze 选项更新 Postgresql 中的统计信息
1 | VACUUM ANALYZE; |
是语法。
我们必须使用特殊功能“vacuum”来更新 PostgreSQL 数据库中的统计信息。
我们如何更改 PostgreSQL 中的列数据类型？
使用更改列类型语句和 ALTER TABLE 命令来更改 PostgreSQL 中的列类型。
例子
1 2 | ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type; |
PostgreSQL 中的立方根运算符 (||/) 是什么？
PostgreSQL 立方根运算符(||/)用于获取数字的立方根。
例子
1 | SELECT ||/40 AS "Cube Root of 40"; |
如何在 PostgreSQL 中启动数据库服务器？
在您可以访问数据库之前，您必须能够启动数据库服务器。数据库的服务器程序称为 Postgres。Postgres 程序必须知道在哪里可以找到它应该使用的数据。这是通过 -D 选项完成的。因此，启动服务器的最简单方法是：
1 2 | /usr/local/etc/rc.d/010.pgsql.sh 启动 /usr/local/etc/rc.d/PostgreSQL 启动 |
我们使用以下方式停止服务器：
- /usr/local/etc/rc.d/010.pgsql.sh 停止
- /usr/local/etc/rc.d/postgresql 停止
我们可以通过以下方式验证 postgresql 服务器是否已启动并正在运行：
- /usr/local/etc/rc.d/010.pgsql.sh
- /usr/local/etc/rc.d/postgresql
定义Postgre中的安全措施
PostgreSQL 数据库使用 SSL 连接来帮助加密客户端或服务器报告以确保安全服务。
解释PostgreSQL数据库中原子性属性的作用
PostgreSQL 中的这个属性确保一个工作单元中的所有操作都成功完成。
列出在 PostgreSQL 中控制事务的命令
PostgreSQL 中的事务控制命令包括：BEGIN、COMMIT、TRANSACTION 和 ROLLBACK。
PostgreSQL 中事务的各种属性及其首字母缩写词。
PostgreSQL 中的事务属性包括原子性、一致性、隔离性和持久性，它们统称为 ACID 属性。
从现有表中删除完整数据的方法。
我们可以通过 PostgreSQL TRUNCATE TABLE 命令从当前表中完全删除数据。
在创建表时在列中指定数据类型的好处。
在列中指定数据类型的优点包括一致性、简洁性、验证/确认和性能。
SQL 语句中的记号表示什么？
在 SQL 语句中，标记表示标识符、关键字、带引号的标识符、特殊字符符号或常量。
什么是 PL/Python？
PL/Python 是 PostgreSQL 提供支持的过程语言。
PostgreSQL 中的全文搜索是什么意思？
回答： 这是指在全文数据库中搜索存储在计算机上的单个或一组文档的技术或方法。它可以简单地与根据元数据或数据库中描述的原始文本的部分进行的搜索区分开来。虽然 PostgreSQL 不如 SOLR 和 Elasticsearch 先进，但这两个工具专门用于全文搜索。而在 PostgreSQL 中，全文搜索只是一个特性，而且相当不错。
什么是表分区？
回答： 在 PostgreSQL 中，表分区是一个将大表划分为称为分区的小块的过程。PostgreSQL 通过表继承支持范围和列表分区。用户必须像主表中的子表一样创建每个分区。
参考
https://www.onlineinterviewquestions.com/postgresql-interview-questions/
https://hkrtrainings.com/postgresql-interview-questions
https://www.analyticsvidhya.com/blog/2022/07/most-frequently-asked-postgresql-interview-questions/
