PostgreSQL 9.0精通：解锁数据库管理和性能优化的秘密

引言

PostgreSQL 9.0 是一款强大且流行的关系型数据库管理系统，以其可靠性、可扩展性和高级功能而闻名。对于任何希望掌握数据库管理和性能优化的专业人士来说，精通 PostgreSQL 9.0 至关重要。

第 1 部分：数据库管理

• 安装和配置：了解 PostgreSQL 9.0 的安装和配置过程，包括系统要求和最佳实践。
• 数据库创建和管理：创建、修改和删除数据库，管理用户和权限，并处理备份和恢复。
• 表和列：创建、修改和删除表，定义列、约束和索引，并组织数据以提高查询性能。

第 2 部分：查询优化

• 查询分析：使用 EXPLAIN 命令分析查询并识别瓶颈，以了解查询执行计划和优化策略。
• 索引：创建和管理索引以加快查询速度，并理解不同索引类型的优缺点。

• 查询调优：应用优化技巧，例如重写查询、使用临时表和索引连接。

第 3 部分：高级功能

• 触发器和存储过程：创建触发器和存储过程以自动化任务、增强安全性并提高性能。
• 全文搜索：配置和使用 PostgreSQL 的全文搜索功能，以快速高效地搜索文本数据。
• 复制和分片：设置复制和分片以提高数据库可用性和可扩展性。

第 4 部分：性能监视和故障排除

• 监视工具：使用内置工具和第三方软件监视数据库性能，例如 pg_stat_activity 和 pg_stat_statements。
• 日志文件：查看和分析日志文件以诊断问题和识别性能瓶颈。
• 故障排除：遵循系统化的方法诊断和解决数据库问题，包括连接错误、查询超时和数据损坏。

结论

精通 PostgreSQL 9.0 是提升数据库管理和性能优化的关键技能。通过理解本指南中介绍的核心概念和技术，您可以优化数据库性能、自动化任务并确保数据库的高可用性和可靠性。掌握 PostgreSQL 9.0 将为您的职业道路打开许多机会，并帮助您在当今竞争激烈的 IT 领域脱颖而出。

---

MySQL与PostgreSQL比较 哪个数据库更好

特性MySQLPostgreSQL实例通过执行 MySQL 命令（mysqld）启动实例。 一个实例可以管理一个或多个数据库。 一台服务器可以运行多个 mysqld 实例。 一个实例管理器可以监视 mysqld 的各个实例。 通过执行 Postmaster 进程（pg_ctl）启动实例。 一个实例可以管理一个或多个数据库，这些数据库组成一个集群。 集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。 使用 initdb 创建第一个数据库。 一台机器上可以启动多个实例。 数据库数据库是命名的对象集合，是与实例中的其他数据库分离的实体。 一个 MySQL 实例中的所有数据库共享同一个系统编目。 数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。 每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。 数据缓冲区通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。 这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。 在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。 在默认情况下分配 64 个缓冲区。 默认的块大小是 8K。 可以通过设置 文件中的 shared_buffers 参数来更新缓冲区缓存。 数据库连接客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。 使用角色管理数据库中的用户和用户组。 客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。 使用角色管理数据库中的用户和用户组。 身份验证MySQL 在数据库级管理身份验证。 基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证加密可以在表级指定密码来对数据进行加密。 还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。 可以通过 SSL 连接实现网络加密。 可以使用 pgcrypto 库中的函数对列进行加密/解密。 可以通过 SSL 连接实现网络加密。 审计可以对 querylog 执行 grep。 可以在表上使用 PL/pgSQL 触发器来进行审计。 查询解释使用 EXPLAIN 命令查看查询的解释计划。 使用 EXPLAIN 命令查看查询的解释计划。 备份、恢复和日志InnoDB 使用写前（write-ahead）日志记录。 支持在线和离线完全备份以及崩溃和事务恢复。 需要第三方软件才能支持热备份。 在数据目录的一个子目录中维护写前日志。 支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。 JDBC 驱动程序可以从 参考资料 下载 JDBC 驱动程序。 可以从 参考资料 下载 JDBC 驱动程序。 表类型取决于存储引擎。 例如，NDB 存储引擎支持分区表，内存引擎支持内存表。 支持临时表、常规表以及范围和列表类型的分区表。 不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。 索引类型取决于存储引擎。 MyISAM：BTREE，InnoDB：BTREE。 支持 B-树、哈希、R-树和 Gist 索引。 约束支持主键、外键、惟一和非空约束。 对检查约束进行解析，但是不强制实施。 支持主键、外键、惟一、非空和检查约束。 存储过程和用户定义函数支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。 存储过程可以用 SQL 和 C++ 编写。 用户定义函数可以用 SQL、C 和 C++ 编写。 没有单独的存储过程，都是通过函数实现的。 用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。 触发器支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。 支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。 系统配置文件数据库配置客户机连接文件_ 支持有限的 XML 支持。 有限的 XML 支持。 数据访问和管理服务器OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）mysql —— 命令行工具MySQL Administrator —— 客户机 GUI 工具Vacuum —— 回收未使用的空间Analyze —— 更新查询优化器所使用的统计数据psql —— 命令行工具pgAdmin —— 客户机 GUI 工具并发控制支持表级和行级锁。 InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。 使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。 支持表级和行级锁。 支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。 使用 SET TRANSACTION 语句在事务级设置隔离级别。 使用 SET SESSION 在会话级进行设置。 MySQL相对于PostgreSQL的劣势：MySQLPostgreSQL最重要的引擎InnoDB很早就由Oracle公司控制。 目前整个MySQL数据库都由Oracle控制。 BSD协议，没有被大公司垄断。 对复杂查询的处理较弱，查询优化器不够成熟很强大的查询优化器，支持很复杂的查询处理。 只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。 都支持性能优化工具与度量信息不足提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。 网上有一个开源的pgstatspack工具。 InnoDB的表和索引都是按相同的方式存储。 也就是说表都是索引组织表。 这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。 不存在这个问题。 大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。 不存在这个问题表增加列，基本上是重建表和索引，会花很长时间。 表增加列，只是在数据字典中增加表定义，不会重建表存储过程与触发器的功能有限。 可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。 也支持用C语言写存储过程。 不支持Sequence。 支持不支持函数索引，只能在创建基于具体列的索引。 不支持物化视图。 支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。 执行计划并不是全局共享的, 仅仅在连接内部是共享的。 执行计划共享MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。 不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。 都 支持不支持用户自定义类型或域(domain)支持。 对于时间、日期、间隔等时间类型没有秒以下级别的存储类型可以精确到秒以下。 身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。 支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证不支持database link。 有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。 Mysql Cluster可能与你的想象有较大差异。 开源的cluster软件较少。 复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.有丰富的开源cluster软件支持。 explain看执行计划的结果简单。 explain返回丰富的信息。 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复 DDL也是有事务的。 PostgreSQL主要优势：1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。 oracle数据库不用说了，是商业数据库，不开放。 而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。 所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。 2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。 3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。 所以很多公司都是基本PostgreSQL做二次开发的。 4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。 同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。 目前只想到这些，以后想到再添加，欢迎大家拍砖。 PostgreSQL与oracle或InnoDB的多版本实现的差别PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。 PostgreSQL的主要优势在于： 1. PostgreSQL没有回滚段，而oracle与innodb有回滚段，oracle与Innodb都有回滚段。 对于oracle与Innodb来说，回滚段是非常重要的，回滚段损坏，会导致数据丢失，甚至数据库无法启动的严重问题。 另由于PostgreSQL没有回滚段，旧数据都是记录在原先的文件中，所以当数据库异常crash后，恢复时，不会象oracle与Innodb数据库那样进行那么复杂的恢复，因为oracle与Innodb恢复时同步需要redo和undo。 所以PostgreSQL数据库在出现异常crash后，数据库起不来的几率要比oracle和mysql小一些。 2. 由于旧的数据是直接记录在数据文件中，而不是回滚段中，所以不会象oracle那样经常报ora-错误。 3. 回滚可以很快完成，因为回滚并不删除数据，而oracle与Innodb，回滚时很复杂，在事务回滚时必须清理该事务所进行的修改，插入的记录要删除，更新的记录要更新回来(见row_undo函数)，同时回滚的过程也会再次产生大量的redo日志。 4. WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。 PostgreSQL的多版本的主要劣势在于： 1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但一般的数据库都有高峰期，如果我们合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。 2、由于索引中完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。 而oracle与Innodb则可以;进程模式与线程模式的对比PostgreSQL和oracle是进程模式，MySQL是线程模式。 进程模式对多CPU利用率比较高。 进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。 线程模式对资源消耗比较少。 所以MySQL能支持远比oracle多的更多的连接。 对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。 堆表与索引组织表的的对比Oracle支持堆表，也支持索引组织表PostgreSQL只支持堆表，不支持索引组织表Innodb只支持索引组织表索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。 而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。 索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。 对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。 所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。 由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。 PostgreSQL9.0中的特色功能： PostgreSQL中的Hot Standby功能也就是standby在应用日志同步时，还可以提供只读服务，这对做读写分离很有用。 这个功能是oracle11g才有的功能。 PostgreSQL异步提交（Asynchronous Commit）的功能：这个功能oracle中也是到oracle11g R2才有的功能。 因为在很多应用场景中，当宕机时是允许丢失少量数据的，这个功能在这样的场景中就特别合适。 在PostgreSQL9.0中把synchronous_commit设置为false就打开了这个功能。 需要注意的是，虽然设置为了异步提交，当主机宕机时，PostgreSQL只会丢失少量数据，异步提交并不会导致数据损坏而数据库起不来的情况。 MySQL中没有听说过有这个功能。 PostgreSQL中索引的特色功能： PostgreSQL中可以有部分索引，也就是只能表中的部分数据做索引，create index 可以带where 条件。 同时PostgreSQL中的索引可以反向扫描，所以在PostgreSQL中可以不必建专门的降序索引了。

达芬奇安装的时候为什么不能取消PostgreSQL9.0.4.1

达芬奇的数据系统底层是基于数据库管理的（包括工作临时状态的暂存），所以这个数据库系统PostgreSQL9.0.4.1是必备的，不能取消。

PostgreSQL@K8s 性能优化记

本文作者蔡松露，作为云猿生数据的CTO和联合创始人，以及阿里云数据库的资深技术专家，他分享了在Kubernetes（K8s）环境中对比ECS（云服务器）上的PostgreSQL（ECS PG）和KubeBlocks（一个云原生数据库管理系统）的性能优化经验。 他针对K8s上的数据库性能提出了优化策略，以确保在分布式架构中数据库能稳定并满足生产需求。 随着企业倾向于全K8s架构，将数据库迁移到Kubernetes面临挑战。 KubeBlocks，由曹伟（鸣嵩）团队开发，作为数据库管控平台，支持多种数据库，如MySQL、PG、Redis等。 蔡松露通过实际操作，首先调整了PG实例的资源限制，进行了Sysbench读写压力测试，以模拟生产环境。 在一系列的压测中，他发现网络延迟、磁盘带宽瓶颈、Checkpoint与锁竞争以及WAL日志创建过程中的问题对性能有很大影响。 通过调整脏页回收比例、WAL日志大小和启用Huge Page，他逐步缩小了问题范围，最后将焦点锁定在PostgreSQL内核实现上，特别是WAL日志创建和同步机制。 在优化过程中，蔡松露调整了wal_init_zero设置，并考虑了文件系统支持，以减少加锁频率。 他还建议根据存储机制关闭full_page_write以降低IO竞争。 通过引入pgBouncer来处理高并发，最终优化后的KubeBlocks在并发量增加时表现出更好的性能和稳定性。 总结来说，蔡松露的优化策略显示，尽管K8s环境下运行PostgreSQL存在挑战，但通过细致的性能分析和针对性优化，可以显著提升数据库在Kubernetes环境下的性能，使其满足生产级别的要求。

如何从PostgreSQL9.1.7升级到9.2.4

具体过程如下：步骤1：安装postgreSQL 9.2.4具体过程参见：如何安装PostgreSQL 9.2.4 For Windows?步骤2：配置鉴权口令文件pg_upgrade会在升级过程中多次连接新旧数据库，所以可以在pg_中将鉴权方式设为Trust或者在使用MD5鉴权方式下配置文件（文件位置：%APPDATA%\postgresql\）。 这样可以避免在升级过程中提示鉴权失败而导致升级不能正常进行。 待升级成功后可以移除或清空文件内容。 步骤3：安装插件将旧数据库服务器中使用的插件同样安装至新数据库服务器中。 步骤4：停止新旧数据库服务通过命令行或服务管理界面进行出现问题：原因：未使用管理员权限运行命令提示符（CMD）步骤5：用非windows系统管理员权限用户做升级前准备RUNAS /USER:postgres SET PATH=D:\Program Files\PostgreSQL\9.2\bin;%PATH%;步骤6：进入postgres用户目录，执行pg_upgrade（如果环境变量设置正确，会使用新版本下的pg_upgrade），会有升级相应的日志文件生成于此命令格式：pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...]其中会使用 - r 参数：作用是在系统升级成功后仍然保留日志文件。 如果升级过程中出现问题，可以通过windos事件检查器及相应升级日志文件进行分析。 pg_upgrade_ pg_upgrade_ pg_upgrade_ pg_upgrade_server_ pg_upgrade_ 出现问题：(1) 没有配置或pg_，检查日志文件有如下错误信息could not connect to old postmaster started with the command:D:/Program Files (x86)/PostgreSQL/9.1/bin/pg_ctl -w -l pg_upgrade_ -D D:/Users/lv/Data/PostgreSQL/9.1 -o -p -b startcannot write to log file pg_upgrade_ (2) PATH环境变量配置错误引起的失败手册上的示例为：SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.0\bin; 这样旧数据库服务器环境变量将会使其路径优先被搜索到，导致提示该版本下的pg_upgrade不支持升级到新数据库版本。 (3) 失败后根据提示重新用initdb生成数据库时未创建用户postgres (4) 创建用户时未创建口令,检查pg_upgrade_，有如下错误信息connection to database failed: fe_sendauth: no password supplied (5) 升级进程未结束再次运行pg_upgrade不成功可以在进程管理中停止所有postgresql进程，然后重新进行升级(6) 升级成功后，日志文件被自动删除，可以在命令行中用-r参数指定保留。 步骤7：恢复对配置文件（pg_，）进行的修改主要是将鉴权方式修改回比较安全的模式步骤8：升级后处理升级完成后，pg_upgrade会提示需要运行脚本或批处理文件检查新数据库服务器及删除旧数据库服务器数据。

有用PostgreSQL的吗，哪个package好用一些

特性MySQLPostgreSQL实例通过执行 MySQL 命令（mysqld）启动实例。 一个实例可以管理一个或多个数据库。 一台服务器可以运行多个 mysqld 实例。 一个实例管理器可以监视 mysqld 的各个实例。 通过执行 Postmaster 进程（pg_ctl）启动实例。 一个实例可以管理一个或多个数据库，这些数据库组成一个集群。 集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。 使用 initdb 创建第一个数据库。 一台机器上可以启动多个实例。 数据库数据库是命名的对象集合，是与实例中的其他数据库分离的实体。 一个 MySQL 实例中的所有数据库共享同一个系统编目。 数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。 每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。 数据缓冲区通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。 这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。 在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。 在默认情况下分配 64 个缓冲区。 默认的块大小是 8K。 可以通过设置 文件中的 shared_buffers 参数来更新缓冲区缓存。 数据库连接客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。 使用角色管理数据库中的用户和用户组。 客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。 使用角色管理数据库中的用户和用户组。 身份验证MySQL 在数据库级管理身份验证。 基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证加密可以在表级指定密码来对数据进行加密。 还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。 可以通过 SSL 连接实现网络加密。 可以使用 pgcrypto 库中的函数对列进行加密/解密。 可以通过 SSL 连接实现网络加密。 审计可以对 querylog 执行 grep。 可以在表上使用 PL/pgSQL 触发器来进行审计。 查询解释使用 EXPLAIN 命令查看查询的解释计划。 使用 EXPLAIN 命令查看查询的解释计划。 备份、恢复和日志InnoDB 使用写前（write-ahead）日志记录。 支持在线和离线完全备份以及崩溃和事务恢复。 需要第三方软件才能支持热备份。 在数据目录的一个子目录中维护写前日志。 支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。 JDBC 驱动程序可以从 参考资料 下载 JDBC 驱动程序。 可以从 参考资料 下载 JDBC 驱动程序。 表类型取决于存储引擎。 例如，NDB 存储引擎支持分区表，内存引擎支持内存表。 支持临时表、常规表以及范围和列表类型的分区表。 不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。 索引类型取决于存储引擎。 MyISAM：BTREE，InnoDB：BTREE。 支持 B-树、哈希、R-树和 Gist 索引。 约束支持主键、外键、惟一和非空约束。 对检查约束进行解析，但是不强制实施。 支持主键、外键、惟一、非空和检查约束。 存储过程和用户定义函数支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。 存储过程可以用 SQL 和 C++ 编写。 用户定义函数可以用 SQL、C 和 C++ 编写。 没有单独的存储过程，都是通过函数实现的。 用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。 触发器支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。 支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。 系统配置文件数据库配置客户机连接文件_ 支持有限的 XML 支持。 有限的 XML 支持。 数据访问和管理服务器OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）mysql —— 命令行工具MySQL Administrator —— 客户机 GUI 工具Vacuum —— 回收未使用的空间Analyze —— 更新查询优化器所使用的统计数据psql —— 命令行工具pgAdmin —— 客户机 GUI 工具并发控制支持表级和行级锁。 InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。 使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。 支持表级和行级锁。 支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。 使用 SET TRANSACTION 语句在事务级设置隔离级别。 使用 SET SESSION 在会话级进行设置。 MySQL相对于PostgreSQL的劣势：MySQLPostgreSQL最重要的引擎InnoDB很早就由Oracle公司控制。 目前整个MySQL数据库都由Oracle控制。 BSD协议，没有被大公司垄断。 对复杂查询的处理较弱，查询优化器不够成熟很强大的查询优化器，支持很复杂的查询处理。 只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。 都支持性能优化工具与度量信息不足提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。 网上有一个开源的pgstatspack工具。 InnoDB的表和索引都是按相同的方式存储。 也就是说表都是索引组织表。 这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。 不存在这个问题。 大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。 不存在这个问题表增加列，基本上是重建表和索引，会花很长时间。 表增加列，只是在数据字典中增加表定义，不会重建表存储过程与触发器的功能有限。 可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。 也支持用C语言写存储过程。 不支持Sequence。 支持不支持函数索引，只能在创建基于具体列的索引。 不支持物化视图。 支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。 执行计划并不是全局共享的, 仅仅在连接内部是共享的。 执行计划共享MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。 不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。 都 支持不支持用户自定义类型或域(domain)支持。 对于时间、日期、间隔等时间类型没有秒以下级别的存储类型可以精确到秒以下。 身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。 支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证不支持database link。 有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。 Mysql Cluster可能与你的想象有较大差异。 开源的cluster软件较少。 复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.有丰富的开源cluster软件支持。 explain看执行计划的结果简单。 explain返回丰富的信息。 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复 DDL也是有事务的。 PostgreSQL主要优势：1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。 oracle数据库不用说了，是商业数据库，不开放。 而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。 所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。 2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。 3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。 所以很多公司都是基本PostgreSQL做二次开发的。 4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。 同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。 目前只想到这些，以后想到再添加，欢迎大家拍砖。 PostgreSQL与oracle或InnoDB的多版本实现的差别PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。 PostgreSQL的主要优势在于： 1. PostgreSQL没有回滚段，而oracle与innodb有回滚段，oracle与Innodb都有回滚段。 对于oracle与Innodb来说，回滚段是非常重要的，回滚段损坏，会导致数据丢失，甚至数据库无法启动的严重问题。 另由于PostgreSQL没有回滚段，旧数据都是记录在原先的文件中，所以当数据库异常crash后，恢复时，不会象oracle与Innodb数据库那样进行那么复杂的恢复，因为oracle与Innodb恢复时同步需要redo和undo。 所以PostgreSQL数据库在出现异常crash后，数据库起不来的几率要比oracle和mysql小一些。 2. 由于旧的数据是直接记录在数据文件中，而不是回滚段中，所以不会象oracle那样经常报ora-错误。 3. 回滚可以很快完成，因为回滚并不删除数据，而oracle与Innodb，回滚时很复杂，在事务回滚时必须清理该事务所进行的修改，插入的记录要删除，更新的记录要更新回来(见row_undo函数)，同时回滚的过程也会再次产生大量的redo日志。 4. WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。 PostgreSQL的多版本的主要劣势在于： 1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但一般的数据库都有高峰期，如果我们合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。 2、由于索引中完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。 而oracle与Innodb则可以;进程模式与线程模式的对比PostgreSQL和oracle是进程模式，MySQL是线程模式。 进程模式对多CPU利用率比较高。 进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。 线程模式对资源消耗比较少。 所以MySQL能支持远比oracle多的更多的连接。 对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。 堆表与索引组织表的的对比Oracle支持堆表，也支持索引组织表PostgreSQL只支持堆表，不支持索引组织表Innodb只支持索引组织表索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。 而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。 索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。 对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。 所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。 由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。 PostgreSQL9.0中的特色功能： PostgreSQL中的Hot Standby功能也就是standby在应用日志同步时，还可以提供只读服务，这对做读写分离很有用。 这个功能是oracle11g才有的功能。 PostgreSQL异步提交（Asynchronous Commit）的功能：这个功能oracle中也是到oracle11g R2才有的功能。 因为在很多应用场景中，当宕机时是允许丢失少量数据的，这个功能在这样的场景中就特别合适。 在PostgreSQL9.0中把synchronous_commit设置为false就打开了这个功能。 需要注意的是，虽然设置为了异步提交，当主机宕机时，PostgreSQL只会丢失少量数据，异步提交并不会导致数据损坏而数据库起不来的情况。 MySQL中没有听说过有这个功能。 PostgreSQL中索引的特色功能： PostgreSQL中可以有部分索引，也就是只能表中的部分数据做索引，create index 可以带where 条件。 同时PostgreSQL中的索引可以反向扫描，所以在PostgreSQL中可以不必建专门的降序索引了。

---

---

相关标签： PostgreSQL、 9.0精通、 解锁数据库管理和性能优化的秘密、

上一篇：深入浅出PostgreSQL90适用于初学者和高级用

下一篇：PostgreSQL90开创数据库新时代的全面指南PO