原文

尽管许多人知道流复制，这篇博客是为初学者准备的，我将从必要条件和一些关于流复制的介绍开始。:-)

必要条件：

1. 在各自的服务器上安装相同版本的PostgreSQL数据库。

2. 配置”postgres”用户的无密码ssh认证（译注：如使用ssh的公私密匙）

3. 生产环境的服务器必须在postgresql.conf文件配置通过设置archive_mode和archive_command参数来开启WAL归档模式。

4. 生产环境的服务器和备份服务器之间应该一直拥有连接以让归档的WAL文件从生产环境的服务器传输到备份服务器.

5. 明确地配置好你的备份机的环境和目录结构要与生产服务器的一样。

介绍

它是一个异步机制；所以备机是延迟于主服务器。但不同于其他复制方法，这个延迟是非常短的，可能是一个单事务的延迟，这取决于网速，数据库负载以及流复制配置。此外，在主机上对每个备机的负载是最小的，允许一个主服务器支持多个备机。

这个特性包含在PostgreSQL 9.0中，伴随的第二个数据库实例（译注：指stand-by是依赖于主服务器的二进制日志，在这期间会标记备机服务器只能接受只读请求）

这是实践步骤所需命令：

1.到主服务器，然后创建一个带有复制权限的replication用户。例如：

1
2
3
4
5
6
7

$ psql  
Password for user postgres:  
psql.bin (9.2.1) Type "help" for help.  
postgres=# create user replication with replication password '<password>';

2.我们需要在主服务器的/opt/PostgreSQL92/data/（译注：就是postgresql的数据目录）目录修改postgresql.conf参数以及pg_hba.conf的验证参数。设置好连接及验证，以让备机服务器可以成功地连接到主服务器的replication这个假的数据库。

1
2
3
4
5
6
7
8

$ $EDITOR postgresql.conf   
listen_addresses = '*'  
$ $EDITOR pg_hba.conf  
#备机服务器必须有超级用户身份的访问权限
host replication replication 10.176.0.0/16 md5

3.在主服务器上设置流复制相关的参数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

 $EDITOR postgresql.conf  
#为了在备机服务器上开始“只读”查询，wal_level必须设置成“hot_standby”. 但是，如果你一直不会在stand-by模式下连接到备机，你可以选择“archive”  
wal_level = hot_standby  
#设置允许备机的最大并发连接数  
max_wal_senders = 5   
#为了防止主服务器在循环利用WAL段文件前移动备机服务器所要求的WAL段文件，设置保留在 pg_xlog 目录里最小的段文件数。   
wal_keep_segments = 32  
#在主服务器上开启WAL归档到一个归档目录以让备服务器获取。如果"wal_keep_segments"值足够大以保留备机所要求的WAL段文件数，这可能就不必要开启。  
archive_mode    = on   
archive_command = 'cp %p <archive location>%f && scp %p postgres@10.176.112.189:<archive location>/%f'

注意：在 postgresql文件里修改以上参数时，要重启服务器。

4.在主服务器上重启postgres并检查参数是否生效。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

postgres=# show archive_command ;  
      archive_command  
     -----------------  
 cp %p /var/PG92_Archives/%f  
(1 row)  
postgres=# show archive_mode ;  
 archive_mode  
-------------  
 on  
(1 row)  
postgres=# show wal_level ;  
  wal_level  
------------  
 hot_standby  
(1 row)  
postgres=# show max_wal_senders ;  
 max_wal_senders  
----------------  
 5  
(1 row)  
postgres=# show wal_keep_segments ;  
 wal_keep_segments  
------------------  
 32

5.对主服务器的数据目录做一个基础备份。

1
2
3
4
5
6
7
8
9
10
11

$ psql ­c "SELECT pg_start_backup('label', true)"   
$ cp /opt/PostgreSQL92/data/* backup/   
$psql ­c "SELECT pg_stop_backup()"   
-- 打包备份目录并传输到备机的数据目录下  
$tar ­cvzf backup.tar backup/  
$scp backup.tar postgres@10.176.112.189:/opt/PostgreSQL92/

6.移动备机的数据内容到其他的位置，解压备份的文件并复制解压后的数据到备机的数据目录。

7.在备服务器上像主服务器那样设置复制相关的参数，连接以及验证，以便让备服务器在主服务器宕机之后可以切换成主服务器。

8.在备服务器上开启只读查询。但如果在主服务器上的wal_level参数值是archive，那hot_standby就不需要更改（即是：off）

1
2

$ $EDITOR postgresql.conf  
hot_standby = on

9.在备机服务器上创建一个恢复命令文件，以下参数对于流复制是必需的。、

1
2
3
4
5
6
7
8
9
10
11
12
13
14

$ $EDITOR recovery.conf  
# 指明是否开启服务器作为一个备机。在流复制里，这个参数必须要开启。
standby_mode          = 'on'  
# 指明用于备服务器连接到主服务器的连接字符串。  
primary_conninfo      = 'host=10.176.112.188 port=5432 user=replication  
password=<password> application=<app_name>'  
# 指定一个触发文件让备服务器感觉到它的时候就会停止流复制（即：故障转移）  
trigger_file = '<any path="">' ===> 不要创建这个文件。当你想主从切换的时候才需要创建它。
# 指定一个命令从WAL归档中加载归档段文件。如果“wal_keep_segments”是一个足够大的数值以保留WAL段文件满足备机的要求，这可能不是必要的。但一个高负载情况下可能会导致段文件在备服务器完全同步之前就已经循环利用的，这时就要求你重新开始一个新的基础备份。  
restore_command = 'cp <archive_location>%f "%p"'  
</archive_location></any>

10.在备服务器上启动postgres。它就会开始流复制并且你会看到像以下的信息：

1
2
3
4

LOG:  entering standby mode  
LOG:  consistent recovery state reached at 0/1D000078  
LOG:  record with zero length at 0/1D000078  
LOG:  streaming replication successfully connected to primary

11.你可以通过比较主服务器上的当前的WAL写位置与备服务器上的最新“接收/重做”的WAL位置来计算复制的延迟。它们各自可以通过在主服务器端使用pg_current_xlog_location函数来获取，在备服务器上通过pg_last_xlog_receive_location或者pg_last_xlog_replay_location来获取。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

$ psql ­c "SELECT pg_current_xlog_location()" ­h192.168.0.10  
(primary host)  
 pg_current_xlog_location  
 0/2000000  
(1 row)  
$ psql ­c "select pg_last_xlog_receive_location()" ­h192.168.0.20  
(standby host)  
 pg_last_xlog_receive_location  
 0/2000000  
(1 row)  
$ psql ­c "select pg_last_xlog_replay_location()" ­h192.168.0.20  
(standby host)  
 pg_last_xlog_replay_location  
 0/2000000  
(1 row)

12.其他检查流复制的方法有：最简单的方法是在备服务器上执行“select now()-pg_last_xact_replay_timestamp();”。pg_last_xact_replay_timestamp()函数给出在恢复期间最近的事务重做的时间戳，这些是在主服务器产生事务产生的提交或中止WAL记录的时间。如果在恢复期间没有事务重做，这个函数就会返回NULL。否则，如果恢复仍然在进行的话，它会一直递增。如果恢复已经完成，这时的值会保留在恢复期间中最近事务重做的值而不会变。当服务器已经正常启动并没有恢复，这个函数就返回NULL.你可以尝试在主服务器上做一些操作，然后检查这个函数的输出。
如果你想手工检查主备之间的延迟，那可以做以下步骤：

1
2
3
4
5
6
7
8
9
10
11

步骤1：在主服务器上使用以下命令来创建表。
create table stream_delay (tstamp timestamp without time zone );  
insert into stream_delay select now();  
步骤2: 在主服务器上使用调度工具cronjob每分钟执行以下命令。 
update stream_delay set tstamp='now()';  
步骤3: 通过在备服务器上检索"stream_delay"表来证实延迟。
它应该显示在主服务器上最近更新的时间。这个时间戳和备服务器的当前时间戳之差就是主服务器和备服务器之间的延迟。

你也可以通过使用ps命令来检查流复制的进展。显示的”LSNs”位置指明了备服务器已经写到了xlog的字节位置。

1
2
3
4
5
6
7

[primary] $ ps ­ef | grep sender  
postgres  6879  6831  0 10:31 ?        00:00:00 postgres: wal sender  
process postgres 127.0.0.1(44663) streaming 0/2000000  
[standby] $ ps ­ef | grep receiver  
postgres  6878  6872  1 10:31 ? receiver process   streaming 0/2000000

谢谢大家，请让我知道我是否遗漏了什么。

原文

译记：该文章开头还有一段内容就不翻译了。主要翻译这些log的主要内容。

pg_log

$PGDATA/pg_log是数据库运行活动日志的默认保存目录，它包括错误信息，查询日志以及启动/关闭数据库的信息。当PostgreSQL启动失败时，这里应该是你第一个应该查看的信息。一些Linux发行版以及其他的软件包管理系统会将这个日志目录移到某些地方，比如：/var/log/postgresql

你可以在pg_log目录里自由地删除、重命名、压缩或者移动文件而不会有什么不好的结果，只要Postgres用户仍然有权限写该目录。如果pg_log随着许多大文件而膨胀，你可能需要在postgresql.conf里减小你想记录日志的事件。

pg_xlog

$PGDATA/pg_xlog是PostgreSQL的事务日志。 这是一些二进制日志文件的集合，文件名类似00000001000000000000008E，它包含最近事务的一些描述数据。这些日志也被用于二进制复制。如果复制、归档或者PITR失败了，当归档正在恢复时，这个目录保存的数据库日志可能会膨胀数GB。这可能会导致你用完你的磁盘空间。不像pg_log，你不能自由地删除、移动或者压缩这个目录的文件。你甚至不能在没有符号链接到该目录的情况下移动这个目录。删除pg_xlog的文件可能会导致不可恢复的数据库损坏。

如果你发现自己处在这样的情况：你发现有100G大小的文件在pg_xlog目录并且数据也启动不了，并且你已经禁止归档/复制并且尝试清理磁盘空间等任何其他的方式，请做以下两个步骤：

1. 从pg_xlog目录里移动文件到一个备份磁盘或者共享网络驱动器中，也不要删除它们，并且
2. 移动一些最老的文件，直到足够允许PostgreSQL启动起来。

pg_clog

$PGDATA/pg_clog包含了事务的元数据。这种日志用于告诉PostgreSQL哪个事务已经完成、哪个还没有完成。clog是比较小的并且没有任何理由会膨胀，所以，你应该没有任何理由去碰触它。在任何时候你都不应该从pg_clog里删除文件，如果你这样子做，还不如完全地删除整个数据库目录。缺少clog是不可恢复的。请注意，这意味着，如果你在$PGDATA目录里备份文件，你应该确定同时包含pg_clog和pg_xlog，否则你可能会发现你的备份是不可用的。

原文

在Postgres里有许多种索引类型，也有不同的方式来使用它们。在本文中,我们概述一下可用的索引类型，并解释不同的使用和维护最常见的索引类型的方式:B树。

索引是一种从表中检索的行数相对较小的有效方式。如果从一个表中检索的行数相对较小时索引是非常有用的（例如：按条件来检索行——WHERE子句选择）。对于免排序的，B树索引也是非常有用的。

索引类型

Postgres支持许多不同的索引类型：

• B树对于当你执行CREATE INDEX时是默认的索引类型。实际上，所有数据库都有一些B树索引。字母B代表是Balanced（平衡），这个大意是,两边的树的数据量是大致相同的。因此，必须遍历去找行的层次数总是在相同的范围内。B树索引可以有效地用于等值和范围查询，并且也可以用于检索NULL值。B树的设计与缓存可以很好地工作，即使只是缓存部分。

• 哈希索引只在等值比较时才有用，但是你几乎从来没有想使用它们，因为它们不是事务安全的，崩溃后需要手工重建，并且是不会被复制到从库的，因此，比B树索引的优势是相当小的。

• 当一个索引必须映射多个值到一行时通用反转索引（GIN，Generalized Inverted Indexes）（通用逆向索引、广义倒排索引）是非常有用的，然而当一行有一个单键值时，B树索引会做一些优化。GIN对于索引数组值以及实现全文搜索是比较好的。

• 通用搜索树（广义搜索树）（GiST，Generalized Search Tree）索引允许你建立普通平衡树结构，也能用于等值和范围比较之外的操作。它们用于索引几何数据类型，也可用于全文搜索。

本文是关于如何最有效地使用默认的B树索引。GIN和GiST索引使用例子，请参考扩展包说明。

为什么我的查询没有用上索引？

有许多原因导致为什么Postgres查询计划没有选择使用索引。大多数的时候，查询计划器是正确的，即使它不是那么明显地为什么会这样。
如果相同的查询有几次会使用索引扫描，但其他时候不使用索引扫描，这是没问题的。从表中检索到的行数可以基于特定的常数值的查询检索而变化。（我注：即不同的常量值，会导致不同的结果行数）。因此，例如，查询计划器对于查询select * from foo where bar = 1使用索引可能是正确的，但对于查询select * from foo where bar = 2却可能不使用索引，这发生于对于bar值为2的行有比较多的话。发生这种情况时，顺序扫描实际上是最有可能比索引扫描快得多，因此，查询计划器实际上是判断正确的，这时顺序扫描的性能代价相比索引扫描更低。

部分索引

部分索引包含表数据的一个子集。它是一个带有WHERE子句的索引。这个概念是为了提高索引的效率,减少索引的大小。小的索引占更少的存储空间，易于维护，扫描更快。

例如，假设你允许用户评论你的网站，从而设置标记布尔值为真（true）。然后你批量处理标标记的评论。你可能想创建一个类似这样子的索引：

1

CREATE INDEX articles_flagged_created_at_index ON articles(created_at) WHERE flagged IS TRUE;

这个索引将会是相当小的， 在复杂查询中也能使用其他索引来结合它一起使用。

表达式索引

表达式索引对于匹配一些函数或修改的数据是有用的。Postgres允许你索引函数结果，以便搜索变得像通过原始数据值搜索一样有效。例如，你可能要求用户保存他们的邮箱登录地址，但你想大小写不敏感的认证。在这种情况下可以保存邮件地址，但是搜索上使用WHERE lower(email) = '<lowercased-email>'。在这种查询下，唯一使用索引的方式是通过表达式索引，例如这样：

1

CREATE INDEX users_lower_email ON users(lower(email));

另一个常见例子是，查找给定日期的行，这里我们已经保存时间戳在一个datatime字段，但是想通过转换的date值来查找他们。一个像这样子的索引：

1

CREATE INDEX articles_day ON articles ( date(published_at) )

可以用在包含WHERE date(articles.created_at) = date('2011-03-07')的查询中使用。

唯一索引

唯一索引保证表不会有超过1行的相同值的行。创建唯一索引的有利的两个原因：数据完整和性能。在唯一索引上查找通常是非常快的。
在数据完整性方面，使用模型类的validates_uniqueness_of并不是真正保证唯一的，因为这可能在并发用户时会创建无效的记录。
因此，你应该问题在数据库级别创建约束——通过索引或者唯一约束。

唯一索引和唯一约束之间有点差别。唯一索引可以想成是更低级别，因为表达式索引和部分索引不能创建唯一约束。表达式上的部分唯一索引就有可能。

多列索引

虽然Postgres已经能够创建多列索引，重要的是要理解这样子做的意义。Postgres查询计划器通过位图索引扫描（bitmap index scan）在一条多列查询中有能力结合和使用多个单列索引。通常，你可以在覆盖查询条件的每个列上创建索引并且大部分情况下Postgres将会使用到它们，所以在你创建一个多列索引之前做一个基准测试并证明创建一个多列索引是有效的。正如之前一样，索引是有代价的，并且一个多列索引仅能在查询引用的列是与创建索引时的列的顺序是一样的才会被优化，虽然多个单列索引提供大量的查询的性能改进。

然而在某些情况下,一个多列索引显然是有意义的。一个在列（a,b）上的索引能够用在查询包含WHERE a = x AND b = y，或者查询仅使用WHERE a = x的情况，但是不会用于查询使用WHERE b = y的情况。所以，如果这匹配到你的应用程序的查询模式，多列索引就是值得考虑的。也要注意在本例中创建一个独立的索引（我注：我理解是在这种情况下，创建多个单列索引）是多余的。

B树和排序

B树索引项默认按升序排序保存的。在某些情况下,它可以为索引提供一个不同的排序顺序。比如当你正分页显示文章的情况，首先按最近发布的来排序。我们在articles表上可能有一列published_at。对于未发表的文章，published_at的值是NULL。在这种情况下，我们可以创建一个这样子的索引：

1

CREATE INDEX articles_published_at_index ON articles(published_at DESC NULLS LAST);

在Postgre 9.2及之后版本，值得注意的是索引并不总是需要去查找表的，我们可以提供一切需要从索引得到的数据。（也就是，没有索引的列是不感兴趣的）。这个特性叫“只读索引扫描”。

由于我们会按published_at排序以及限制结果数来查询表，我们可以从创建同样的顺序的索引中得到一些好处。Postgres会以正确的顺序在索引中找到这些行，然后去数据块里检索数据。如果索引不是排序的，Postgres就有一个好机会来顺序读取数据块并且排序结果。

这种技巧主要是当你要求相关的单列索引并且“null在排序的最后”的行为上，否则的话顺序已经是可用的，索引扫描可以在任何方向扫描。当用一个多列索引时来对付一个当查询要求一个混合排序时，例如：a ASC, b DESC，它会变得更加的相关。

管理和维护索引

在Postgres里索引并不拥有所有的行数据。即使使用索引来查询和匹配查找到的行，Postgres还会到磁盘中去取行数据。另外，行可见性信息（在MVCC文章中讨论）是不保存到索引的，因此，Postgres必须去磁盘取到这些信息。有了这一点，您可以看到在某些情况下如何使用索引并没有真正意义。索引必须足够选择性地减少磁盘的查找,这才是值得的使用索引的。例如，在一个足够大的表里按一个主键查找，这就很好地利用索引：代替匹配查询条件的顺序扫描，Postgres能够在索引里查找到目标行，然后从磁盘里选择性地取出它们。对于非常小的表，例如，一张查找城市的表，索引可能是不可取，即使你是通过城市名来搜索。在那种情况下，Postgres可能决定忽略索引而支持顺序扫描。Postgres在一些会命中很大一部分表数据的查询上将决定执行顺序扫描。如果你在那些列上有索引，它将会是一个永不被使用的死索引——并且 索引并不是“免费”的：他们在存储和维护方面是有代价的。

当调优一条查询并且了解索引是非常有意义的，从没有在你的开发机上尝试过的话。是否使用索引决定于许多因素，包括Postgres服务器配置、表的数据、索引和查询。例如，在你的开发机上带有一小部分的测试数据的表上尝试使一条查询用上索引是会令你挫败的：Postgres会决定数据集是如此小以致不值得使用索引的额外的“读”开销，然后从磁盘取数据。随机 I/O 比顺序 I/O 是非常慢的，所以，顺序扫描的代价比通过索引读取然后从磁盘查找数据的随机I/O更少。进行索引调优应该在生产或在暂存环境中完成，尽可能在生产环境中。在Heroku 的Postgres数据库平台，可以很容易地复制你的生产数据库到一个不同的环境。

当你准备在你的生产数据库上应用一个索引时，请记住创建索引会锁表并阻塞写操作。对于大表，这可能意味着你的网站是停机几个小时。幸运的是，Postgres允许你CREATE INDEX CONCURRENTLY（并发创建索引），这会导致花更多的时间来建索引，但是不要求锁住写锁。正常的CREATE INDEX命令要求一个锁来锁住写操作，但允许读。最终，在之后一段时间，索引会变得碎片和未优化，如果在表中的行经常更新或删除就特别容易这样。在这种情况下，就可能需要执行一个REINDEX命令来平衡及优化你的索引了。然而，要谨慎重建索引会在父表中获得写锁。有个策略在在线的网站来实现相同的结果就是并发地在相同的表和列但有一个不同的名称的索引，然后，删除旧的索引并且重新命名新的索引。这个过程可能持续比较久，但不要求在在线的(活跃）表有任何长久执行的锁。当准备创建B树索引时Postgres提供了许多灵活性来优化你特定的使用情况，也有许多选项来管理你应用程序不断增长的数据库。这些建议应该会帮你保持你的数据库健康，以及你的查询非常爽快。

原文

在讨论死锁之前，让我们看一下锁的类型以及它们在PostgreSQL中的获取方法。

锁的类型：

• 表级锁 以及
• 行级锁

表级锁：

• AcessShareLock（访问共享锁）：通过在一张表或多张表一条 SELECT 语句检索数据时就会自动获取。这个模式会阻塞在同一张表下的 ALTER TABLE，DROP TABLE 以及 VACUUM (AccessExclusiveLock，访问排他锁)操作。

• RowShareLock （行共享锁）：通过一条SELECT...FOR UPDATE子句自动获取。它会在同一张表上阻塞并发的ExclusiveLock(排他锁）以及AccessExclusiveLock(访问排他锁）。

• RowExclusiveLock （行排他锁）：通过UPDATE，INSERT，或者 DELETE命令自动获取。它会在同一张表上阻塞ALTER TABLE，DROP TABLE,VACUUM 和 CREATE INDEX命令。（ShareLock[共享锁]，ShareRowExclusiveLock[共享行排他锁]，ExclusiveLock[排他锁]，AccessExclusiveLock[访问排他锁]）。

• ShareLock（共享锁）: 通过CREATE INDEX命令自动获取。它会在同一张表上阻塞INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE, 以及 VACUUM命令.(RowExclusiveLock[行排他锁], ShareRowExclusiveLock[共享行排他锁], ExclusiveLock[排他锁], 以及 AccessExclusiveLock[访问排他锁])

• ShareRowExclusiveLock（共享行排他锁）：这个锁模式与ExclusiveLock（排他锁）是一样的，但是它允许获取并发的 RowShareLock（行共享锁）

• ExclusiveLock（排他锁）：”每个事务在它的事务ID的整个时间里都会持有一个Exclusive Lock（排他锁）”。如果一个事务发现它需要特别地等待另一个事务，它就会尝试地在另一个事务ID上获取 Share Lock（共享锁）。这仅当另一个事务结束并释放它自己的锁时才会成功。（注意，冲突）。Exclusive Lock（排他锁）会在同一张表上阻塞INSERT, UPDATE, DELETE,CREATE INDEX, ALTER TABLE,DROP TABLE, SELECT...FOR UPDATE 以及 VACUUM命令。

• AccessExclusiveLock（访问排他锁）：通过ALTER TABLE, DROP TABLE, 或者 VACUUM命令来修改表时自动获取。从开始在表上获取锁时，会阻塞任何并发命令或者其他锁模式。

行级锁：

行级锁有两种类型：共享锁和排他锁。不要混淆锁的命名，你可以通过在视图pg_locks中的列lock_type来区分是表级锁，还是行级锁。

• Exclusive lock（排他锁）：当通过UPDATE或DELETE命中行时就会自动获取该锁。锁会一直被持有，直到一个事务提交或回滚了。为了手动获取exclusive-lock（排他锁），可以使用SELECT FOR UPDATE。

• Share-Lock（共享锁）：当通过SELECT...FOR SHARE命中行时就会自动获取该锁。

注意：在这两种情况下的行级锁，数据检索是一点也不会影响的。行级锁会阻塞“写”（即，“写”会阻塞“写”）。

死锁:

现在到死锁了，你已经知道锁的模式以及获取这些锁的方法，有些情况下事务会陷入死锁中。我相信应用程序设计是导致死锁的罪魁祸首。死锁大多数是由于ExclusiveLock（排他锁）例如UPDATE或DELETE操作导致的。

什么是死锁？

进程A持有对象X的锁，并且正等待对象Y的锁。进程B持有对象Y的锁，并且正等待对象X的锁。在这时，这两个进程就会进入所谓的“死锁”，每个进程都想获取另一个进程持有的锁。在这个状态下，他们都在永远等待对方释放锁。他们之一必须放弃并释放自己拥有的锁。现在，死锁检测器就会检测到并且允许一个进程成功提交事务，而另一个则进行回滚。

为了解决死锁，要用这样一个方法来设计应用程序——任何事务“UPDATE”或“DELETE”都应该成功地取得表的所有权。通过SHARE UPDATE EXCLUSIVE 模式或者SELECT...FOR UPDATE，又或者ACCESS EXCLUSIVE 模式来锁表并且完成事务。在这个模型下，死锁检测器永远不会抛出它已经检测到一个EXCLUSIVE LOCK（排他锁）。

你可通过这个办法来解决上面的图的情况，你会看到死锁检测器永远不会抛出错误。

锁查询语句

1

\set locks 'SELECT w.locktype AS waiting_locktype,w.relation::regclass AS waiting_table,w.transactionid, substr(w_stm.current_query,1,20) AS waiting_query,w.mode AS waiting_mode,w.pid AS waiting_pid,other.locktype AS other_locktype,other.relation::regclass AS other_table,other_stm.current_query AS other_query,other.mode AS other_mode,other.pid AS other_pid,other.granted AS other_granted FROM pg_catalog.pg_locks AS w JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.procpid = w.pid) JOIN pg_catalog.pg_locks AS other ON ((w.\"database\" = other.\"database\" AND w.relation  = other.relation) OR w.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.procpid = other.pid) WHERE NOT w.granted AND w.pid <> other.pid;;'

关于锁的信息链接

http://www.postgresql.org/docs/9.0/static/sql-lock.html
http://developer.postgresql.org/pgdocs/postgres/explicit-locking.html

希望你有一个关于PostgreSQL锁的概念了。 希望在之后的博文中可以再次见到你。:)

—Raghav

By Raghavendra

我附：
现在的PG有8种表级锁了，除了上面作者提到的7种，还有一种是：

• SHARE UPDATE EXCLUSIVE 锁

与”Share update exclusive,Share,Share row ,exclusive,exclusive,Access exclusive”模式冲突，这种模式保护一张表不被并发的模式更改和VACUUM;

“Vacuum(without full), Analyze ”和 “Create index concurrently”命令会获得这种类型锁。

资料：

http://www.postgresql.org/docs/9.0/static/explicit-locking.html

原文

在PostgreSQL,备份和恢复相对于其他的数据库是非常友好的。许多人也许不赞同，但我们不要陷入争论中。来讨论下备份，PostgreSQL不支持增量备份，然而，有许多非常统一的备份工具和操作系统级别的解决办法来实现这个目的。

我的关于PostgreSQL备份和恢复的图解给出了一个完整的概念的想法。看图，你可以辨别出哪个备份可以用来还原或恢复。

逻辑备份

pg_dump,pg_restore和pg_dumpall工具是用来进行逻辑备份的。pg_dump和pg_restore可以帮助你在数据库级别、模式级别和表级别备份。pg_dumpall用于集群级别备份。

pg_dump支持三种格式，Plain SQL 格式（纯文本SQL格式）， Custom 格式（自定义格式）和 Tar格式（打包格式）。Custom和Tar格式的备份与pg_restore工具是兼容的，然而，Plain SQL（纯文本SQL）格式的备份是与psql工具兼容的用于还原。

以下是每个备份级别和相关的还原命令的例子。

注意：在.bash_profile里设置默认的PGDATABASE, PGUSER, PGPASSWORD和PGPORT值。（在Windows里意思是环境变量）

纯文本SQL格式的备份和还原

1
2
3
4
5
6
7
8
9
10
11

$ pg_dump -U username -Fp dbname  >  filename 
or 
$ pg_dump -U username dbname -f  filename 
or 
$ pg_dump -Fp -U username dbname -f  filename 
 
使用 psql 命令来还原
 
$ psql -U username -f filename dbname 
or 
postgres=# \i SQL-file-name     //in psql terminal with \i option

自定义格式

1
2

$ pg_dump -Fc dbname -f filename 
$ pg_restore -Fc -U username -d dbname filename.dmp

Tar格式

$ pg_dump -Ft dbname -f filename
$ pg_restore -U username -d dbname filename
or
$ cat tar-file.tar | psql -U username dbname

注意：模式级别和表级别备份执行方式是一样的，通过添加相关的选项就可以了。

集群级别备份

1
2
3
4
5

$pg_dumpall -p portnumber > filename 
 
使用 psql 命令来还原
 
$ psql -f filename

这些都是非常好的备份和恢复的方法。特别地，www.2ndQuadrant.com 出版的，作者是Simon Riggs 和 Hannu Krosing 的书： “PostgreSQL 9 Administration Cookbook - 2010”，是学习PostgreSQL备份和恢复的很好方式。

物理备份（文件系统备份）

冷备份

在冷备份中，当Postgre实例关闭时，进行一个非常简单的/data目录的文件系统备份，意思是，实现一个前后一致的数据目录备份，在复制之前，数据库服务器应该关闭。PostgreSQL通过软链接来灵活地保持pg_xlog和pg_tablspce在不同挂载点。当复制/data目录并想包括那些软链接的数据时，可以使用以下命令。

1
2
3
4
5

tar czf backup.tar.gz $PGDATA 
or 
cp -r $PGDATA /backup/ 
or 
rsync -a $PGDATA /wherever/data

热备份（在线备份）:

在热备份中，集群会一直在启动和运行，并且数据库应该是在归档日志模式中。两个系统函数将会唤醒实例来开始、关闭热备份处理（pg_start_backup(), pg_stop_backup()）。在开始进一步进行在线热备前，让我们讨论一下对于在线备份中强制的数据库归档模式。

开启 WAL（预写式日志）归档:

下面将简要介绍一下 PITR/调整 WAL 等等。目前，我们先看看WAL归档。在PostgreSQL数据库系统，实际上数据库“写”一些额外被称为预写日志（WAL）文件到磁盘。它包含了一些在数据库系统中的写记录。在崩溃的情况下，数据库可以从这些记录中还原或恢复。

一般地，写日志记录会在定期匹配时（叫做checkpoints,检查点）记录该检查点，然后在它不再需要时删除它。你也可以使用WAL作为备份，因为它是一个所有数据库的写记录。（我注：就是更改数据库的操作）。

WAL归档概念:

WAL是由每16MB大小（我注：文件）组成的，这被称为segments（段）。WAL驻留在pg_xlog目录下，这是一个位于数据目录下的子目录。文件名会被PostgreSQL实例按数字升序来命名，执行一个WAL基础备份，这需要一个基础备份，一个数据目录的完整备份，并且WAL段会介于基础备份和当前日期之间。

配置WAL段归档，可以通过在postgresql.conf里的两个配置参数来设置:archive_command和archive_mode。使集群进入归档模式需要重启PostgreSQL。

1
2

archive_mode= on/off (boolean parameter) 
archive_command = 'cp –i %p /Archive/Location/f% '

注意：%p为通过路径来复制文件来作为一个文件名，%f没有为目标文件设置目录。（我注：也就是%p代表pg_xlog的绝对路径的文件，%f表示pg_xlog目录下的文件名，所以%p前没有目录，%f前有目录前缀）。

关于归档进程的更加多信息，请参考“PostgreSQL 9.0 内存 & 进程”

在线备份 :

采取在线备份：

1
2
3
4
5
6
7
8
9
10

步骤 1 : 在psql终端执行 pg_start_backup('lable')  
postgres=# select pg_start_backup('fb'); 
步骤 2 : 操作系统级别地复制 $PGDATA 目录到任何一个备份的位置。
$ cp -r $PGDATA  /anylocation 
步骤 3 : 在psql终端执行 pg_stop_backup()
postgres=# select pg_stop_backup(); 
注意：这两个函数不需要在数据库的同一个会话里执行。备份模式是全局的并且是持久的。

在PostgreSQL中，没有目录来保存在线备份的开始时间和结束时间。然而，当在线备份正在进行时，会有数个文件会被创建和删除。

pg_start_backup('label')和pg_stop_backup是两个执行在线备份的系统函数。通过pg_start_backup('label')会创建一个文件backup_label在$PGDATA目录下，通过pg_stop_backup()会创建一个文件wal-segement-number.backup在$PGDATA/pg_xlog目录下。backup_label会给出开始时间以及WAL(预写式日志）段的检查点位置，也会通知PostgreSQL实例是处于备份模式。在$PGDATA/pg_xlog目录下的wal-segment-number.backup文件描述了开始和停止时间，带有WAL段号的检查点位置。

注意： 在pg_stop_backup()之后，PostgreSQL实例就会删除backup_label文件。

提交你的评论，建议。

—Raghav

By Raghavendra

原文

在PostgreSQL,备份和恢复相对于其他的数据库是非常友好的。许多人也许不赞同，但我们不要陷入争论中。来讨论下备份，PostgreSQL不支持增量备份，然而，有许多非常统一的备份工具和操作系统级别的解决办法来实现这个目的。

我的关于PostgreSQL备份和恢复的图解给出了一个完整的概念的想法。看图，你可以辨别出哪个备份可以用来还原或恢复。

逻辑备份

pg_dump,pg_restore和pg_dumpall工具是用来进行逻辑备份的。pg_dump和pg_restore可以帮助你在数据库级别、模式级别和表级别备份。pg_dumpall用于集群级别备份。

pg_dump支持三种格式，Plain SQL 格式（纯文本SQL格式）， Custom 格式（自定义格式）和 Tar格式（打包格式）。Custom和Tar格式的备份与pg_restore工具是兼容的，然而，Plain SQL（纯文本SQL）格式的备份是与psql工具兼容的用于还原。

以下是每个备份级别和相关的还原命令的例子。

注意：在.bash_profile里设置默认的PGDATABASE, PGUSER, PGPASSWORD和PGPORT值。（在Windows里意思是环境变量）

纯文本SQL格式的备份和还原

1
2
3
4
5
6
7
8
9
10
11

$ pg_dump -U username -Fp dbname  >  filename 
or 
$ pg_dump -U username dbname -f  filename 
or 
$ pg_dump -Fp -U username dbname -f  filename 
 
使用 psql 命令来还原
 
$ psql -U username -f filename dbname 
or 
postgres=# \i SQL-file-name     //in psql terminal with \i option

自定义格式

1
2

$ pg_dump -Fc dbname -f filename 
$ pg_restore -Fc -U username -d dbname filename.dmp

Tar格式

$ pg_dump -Ft dbname -f filename
$ pg_restore -U username -d dbname filename
or
$ cat tar-file.tar | psql -U username dbname

注意：模式级别和表级别备份执行方式是一样的，通过添加相关的选项就可以了。

集群级别备份

1
2
3
4
5

$pg_dumpall -p portnumber > filename 
 
使用 psql 命令来还原
 
$ psql -f filename

这些都是非常好的备份和恢复的方法。特别地，www.2ndQuadrant.com 出版的，作者是Simon Riggs 和 Hannu Krosing 的书： “PostgreSQL 9 Administration Cookbook - 2010”，是学习PostgreSQL备份和恢复的很好方式。

物理备份（文件系统备份）

冷备份

在冷备份中，当Postgre实例关闭时，进行一个非常简单的/data目录的文件系统备份，意思是，实现一个前后一致的数据目录备份，在复制之前，数据库服务器应该关闭。PostgreSQL通过软链接来灵活地保持pg_xlog和pg_tablspce在不同挂载点。当复制/data目录并想包括那些软链接的数据时，可以使用以下命令。

1
2
3
4
5

tar czf backup.tar.gz $PGDATA 
or 
cp -r $PGDATA /backup/ 
or 
rsync -a $PGDATA /wherever/data

热备份（在线备份）:

在热备份中，集群会一直在启动和运行，并且数据库应该是在归档日志模式中。两个系统函数将会唤醒实例来开始、关闭热备份处理（pg_start_backup(), pg_stop_backup()）。在开始进一步进行在线热备前，让我们讨论一下对于在线备份中强制的数据库归档模式。

开启 WAL（预写式日志）归档:

下面将简要介绍一下 PITR/调整 WAL 等等。目前，我们先看看WAL归档。在PostgreSQL数据库系统，实际上数据库“写”一些额外被称为预写日志（WAL）文件到磁盘。它包含了一些在数据库系统中的写记录。在崩溃的情况下，数据库可以从这些记录中还原或恢复。

一般地，写日志记录会在定期匹配时（叫做checkpoints,检查点）记录该检查点，然后在它不再需要时删除它。你也可以使用WAL作为备份，因为它是一个所有数据库的写记录。（我注：就是更改数据库的操作）。

WAL归档概念:

WAL是由每16MB大小（我注：文件）组成的，这被称为segments（段）。WAL驻留在pg_xlog目录下，这是一个位于数据目录下的子目录。文件名会被PostgreSQL实例按数字升序来命名，执行一个WAL基础备份，这需要一个基础备份，一个数据目录的完整备份，并且WAL段会介于基础备份和当前日期之间。

配置WAL段归档，可以通过在postgresql.conf里的两个配置参数来设置:archive_command和archive_mode。使集群进入归档模式需要重启PostgreSQL。

1
2

archive_mode= on/off (boolean parameter) 
archive_command = 'cp –i %p /Archive/Location/f% '

注意：%p为通过路径来复制文件来作为一个文件名，%f没有为目标文件设置目录。（我注：也就是%p代表pg_xlog的绝对路径的文件，%f表示pg_xlog目录下的文件名，所以%p前没有目录，%f前有目录前缀）。

关于归档进程的更加多信息，请参考“PostgreSQL 9.0 内存 & 进程”

在线备份 :

采取在线备份：

1
2
3
4
5
6
7
8
9
10

步骤 1 : 在psql终端执行 pg_start_backup('lable')  
postgres=# select pg_start_backup('fb'); 
步骤 2 : 操作系统级别地复制 $PGDATA 目录到任何一个备份的位置。
$ cp -r $PGDATA  /anylocation 
步骤 3 : 在psql终端执行 pg_stop_backup()
postgres=# select pg_stop_backup(); 
注意：这两个函数不需要在数据库的同一个会话里执行。备份模式是全局的并且是持久的。

在PostgreSQL中，没有目录来保存在线备份的开始时间和结束时间。然而，当在线备份正在进行时，会有数个文件会被创建和删除。

pg_start_backup('label')和pg_stop_backup是两个执行在线备份的系统函数。通过pg_start_backup('label')会创建一个文件backup_label在$PGDATA目录下，通过pg_stop_backup()会创建一个文件wal-segement-number.backup在$PGDATA/pg_xlog目录下。backup_label会给出开始时间以及WAL(预写式日志）段的检查点位置，也会通知PostgreSQL实例是处于备份模式。在$PGDATA/pg_xlog目录下的wal-segment-number.backup文件描述了开始和停止时间，带有WAL段号的检查点位置。

注意： 在pg_stop_backup()之后，PostgreSQL实例就会删除backup_label文件。

提交你的评论，建议。

—Raghav

By Raghavendra

原文
作者：Raghav

在PostgreSQL构架基础上进一步了解，在这里，通过信息链接我将会讨论关于实用进程和内存。许多提交者已经好好地记录了关于进程和内存，在这里有提供链接。在我这里有适当关于PostgreSQL实用进程的描述。

每个PostgreSQL实例启动，会有一组实用进程（包括强制性和可选性进程）和内存。两个强制性进程（bgwriter后台写进程和walwriter预写式日志写进程）。你可以通过命令ps -ef | grep postgres检测一下，结果如图10.1.

图10.1

进程和内存概要

图10.2

关于图10.2，它表明了进程附加到PostgreSQL共享内存。

BGWriter/Writer Process后台写进程或者叫写进程:

后台写进程或者叫写进程是一种强制性进程:

所有PostgreSQL服务器进程从磁盘读取数据然后将它们移到共享缓冲池（Shared Buffer Pool）里。 共享缓冲池使用ARC算法或者LRU（最近最少使用）机制来淘汰页数据。BGWRITER后台写进程很多时候都是在休眠，但每次唤醒，它通过搜索共享缓冲池（Shared Buffer Pool）来寻找被修改的页。每次搜索完之后，BGWRITER后台写进程就会选择那些被修改的页，将它们写到磁盘，然后将它们从共享缓冲池里淘汰出来。后台写进程通过三个参数BGWRITER_DELAY、BGWRITER_LRU_PERCENT以及BGWRITER_LRU_MAXPAGES来控制。

http://www.enterprisedb.com/docs/en/9.0/pg/kernel-resources.html
http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-resource.html

WAL Writer Process预写式日志写进程:

预写式日志写进程是一个强制性进程。

预写式日志写进程在适当间隔时会写入并进行文件同步。为了保证事务安全，预写式日志缓冲区在事务日志里持有数据库的更改操作。预写式日志缓冲区在每次事务提交时写到磁盘，预写式日志写进程负责写到磁盘。WAL_WRITER_DELAY参数是用于调用预写式日志写进程的，然而，还有其他参数同样会使预写式日志写进程比较繁忙。下面有一些链接。

http://www.enterprisedb.com/docs/en/8.4/pg/wal-configuration.html

Stats Collector Process状态收集进程:

状态收集进程是可选进程，默认是开启的。

状态收集进程会收集一些关于服务器运作的信息。它会计算访问表和索引二者磁盘块的数量和个别的行项数（我注：一个block有可能多个row item，可以通过 select ctid from tbname来查看，第一个数字就是block数，第二个就是row item数)。它同样会跟踪每一个表的总行数，每一个表关于VACUUM（清理）和ANALYZE（分析）动作的信息。收集这些统计数据会对查询执行有额外的开销，自己决定收不收集这些信息。以下的链接有更多关于状态收集进程以及相关参数的说明。

http://www.enterprisedb.com/docs/en/9.0/pg/monitoring-stats.html

Autovacuum Launcher Process自动清理启动器进程:

自动清理进程是一个可选进程，默认是开启的。

为了自动执行VACUUM和ANALYZE命令，自动清理启动器进程是由许多被称为autovacuum workers(自动清理工作者)组成的后台进程。自动清理启动器进程负责启动autovacuum workers(自动清理工作者)进程来处理所有数据库。启动器会按交叉时间地分发工作，在每个时间间隔里会试图在每一个数据库里启动一个工作者（我注：指autovacuum workers），通过参数autovacuum_naptime来设置间隔时间。每个数据库都会启动一个工作者，通过参数autovacuum_max_workers来设置最大数。每一个工作者进程都会在它所在的数据库里检查每一张表，然后在有需要的时候执行VACUUM或者ANALYZE命令。以下的链接有更多关于AUTOVACUUM自动清理启动器进程的相关参数的说明。

http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-autovacuum.html

Syslogger Process / Logger Process系统日志进程或者叫日志进程 :

图10.3

日志是一个可选进程，默认是关闭的。

依据图10.3， 可以清楚地理解所有 实用进程+用户后台进程 + Postmaster守护进程都附加到系统日志进程来记录这它们的活动信息。每一个进程信息都会被记录在PGDATA/pg_log 目录下的.log文件里。
注意：如果数据目录是通过INITDB命令创建的，pg_log目录不会在数据目录里自动创建。需要显式地创建该目录。

调试更多的进程信息会导致服务器的一些额外开销。总是建议日志是最低级别的，如果有要求的话再提高调试级别。以下的链接有更多关于日志参数的说明。

http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-logging.html

Archiver Process归档进程:

图10.4

归档进程是可选进程，默认是关闭的。

上面图10.4 是从我观察PostgreSQL的归档进程而制作的。设置数据库为归档模式，意味着捕捉预写式日志（WAL）数据填充到每个段文件。在段文件重新回收利用之前，会将数据保存到某些地方。

图中每个数字标签的解释。

1.在数据库的归档模式，一旦预写式日志（WAL）数据填充满了预写式日志（WAL）段文件，填充满的段文件会被预写式日志写进程（WAL Writer）在目录$PGDATA/pg_xlog/archive_status下创建一个后缀为”.ready”的文件。文件名将会是“段文件名.ready”。

2.归档进程就会触发去查找那些被预写式日志写进程创建的“.ready”状态的文件。归档进程选择那些后缀是”.ready”的”段_文件号”文件，然后从$PGDATA/pg_xlog复制这些文件到archive_command参数（在postgresql.conf）指定的目的地里。

3.成功地从源目录复制到目的目录，归档进程会重命名”段-文件名.ready”为段-文件名.done。这就完成了归档的过程。

不用说，如果在$PGDATA/pg_xlog/archive_status目录里有任何名为”段-文件名.ready”的文件都是正等待着被复制到归档目的地里（我注：通过参数archive_command来指定）。

更多关于参数和归档的信息，请看以下链接。

http://www.enterprisedb.com/docs/en/9.0/pg/continuous-archiving.html

请把你的意见/建议提交在这篇文章中,将不胜感激。

献上我真诚的问候
Raghav

原文

作者：Raghavendra

很高兴在这里发布我的第一篇博客，是关于 PostgreSQL 构架的。

在很长一段时间里，我在工作、学习上都广泛地接触PostgreSQL。作为一个初学者，想到尝试给出一张关于 PostgreSQL 的架构图。PostgreSQL构架包括几部分：内存、进程和文件存储系统，这难以在一张图里展示所有东西。我尽我所能地给出一个关于PostgreSQL构架的概要。

大部分的设计都是在我们的PostgreSQL提交者（Heikki,Robert Haas,Bruce）的帮助下完成的， 我从他们身上学习到了很多关于PostgreSQL内部的东西。 非常感谢他们的协作让我了解到关于PostgreSQL的一切。我不是黑客，也不是构架师，仅仅是为PostgreSQL新手写了一篇文章。请留下你的评论、建议或者发现到我写文章的任何错误也可留言。

PostgreSQL 9.0 构架概述

PostgreSQL实例由一系列进程和内存组成。PostgreSQL 使用一个简单的 “每个用户一个进程” 的 客户/服务器 模型。PostgreSQL 有许多种类型进程。

• postmaster进程，是后台监听进程，postmaster附加到共享内存段（我注：其实就是通过共享内存来进行进程间的通信），但是尽量避免访问它（我注：避免我们自定义去访问该共享内存，而是由PG内部各进程进行协调）。

• 实用进程（bgwriter后台写进程，walwriter预写式日志写进程，syslogger系统日志进程，archiver归档进程，statscollector状态收集器进程 以及 autovacuum自动清理进程）以及

• 用户后台进程（postgres进程自身，服务器进程）

当有一个客户端请求连接到数据库时，首先，请求被postmaster后台进程执行身份认证，受权之后会复制一个服务器后台进程（postgres进程）来处理该请求。从那时起，客户端进程和服务器端进程进程通信，而不再需要postmaster介入。因此，postmaster进程是一直在运行的，一直等待连接请求，然而客户端和服务器端进程会继续进行通信。libpq库允许一个单客户端连接到多个服务器进程。

然而，每个后台进程都是单线程的，一次仅仅只能执行一条查询；所以，任何的一个前端-后台连接都是单线程的。postmaster进程和postgres进程都是以PostgreSQL的”超级用户”身份的用户ID来运行的。每个打开数据库的会话里都会存在一个postgres进程。一旦经过身份验证的用户连接,它就会与共享内存直接连接（与谁，目的是做什么）。

内存

• Shared Buffers，共享缓冲区
• WAL Buffers，预写式日志缓冲区
• clog Buffers，是一种 SLRU 类型的缓冲区(Commit log，提交日志缓冲区）
• Other Buffers，其他缓冲区

PostgreSQL共享内存是非常大的并且所有缓冲区都没有同步的，这意味着都是独立的。一些专家/提交者已经将他们的大量关于PostgreSQL的经验信息放在网站上。结合PostgreSQL文档和这个构架图就会对PostgreSQL构架的有个基础的了解。以下链接有更多概述.

http://www.postgresql.org/docs/9.0/interactive/runtime-config-resource.html
http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-resource.html
http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/0.html

实用进程:

强制性进程：这些进程是没有选项来 开启/关闭 的

• BGWriter

• WAL Writer

可选进程：这些进程是有选项来 开启/关闭 的

• Stats-collector，状态收集进程
• Autovacuum launcher，自动清理进程
• Archiver，归档进程
• Syslogger，系统日志进程
• WAL Sender，预写式日志发送进程
• WAL Receiver，预写式日志接收进程

不久，我将会提交一张关于实用性进程和用户后台进程的概要图

献上我真诚的问候
Raghav

原文

本文经过原作者Dan Robinson同意翻译。（我在twitter发送推文和他互动）

你知道PostgreSQL支持表的部分索引吗？这使得读取部分索引是非常快速的，几乎没有什么索引开销。如果你想重复地分析那些匹配给定的 WHERE 子句的行数据，那部分索引是最好的索引数据的方式。这使得PostgreSQL非常适合于那种涉及预聚集额外的特别分析的工作流。在这一点上，我将通过一个非常适合使用部分索引的查询优化示例来实践下。

思考一下，具有以下模式的事件表：

1
2
3
4
5
6
7

CREATE TABLE event (
  user_id BIGINT,
  event_id BIGINT,
  time BIGINT NOT NULL,
  data JSON NOT NULL,
  PRIMARY KEY (user_id, event_id)
)

每一个事件都与用户关联，并且有一个ID，一个time，以及一个 JSON 表示事件。
JSON包括页面路径，事件类型（例如：点击，查看页面，提交表单），以及其他任何描述事件的属性。

我们可以使用这张表来保存许多不同的事件，为了我们以后可以分析数据，让我们假设有许多自动跟踪并记录每次点击，查看页面以及表单提交的事件。我们可能想要一个内部的表盘可以显示一些高值指标，例如每周的注册数或者我们的每天收益数。与表盘相关的事件只是这个表的一个小的组成部分——在你的网站上最终购买的点击数占非常小的百分比。但它们被混合在表的其余部分，所以我们的“信噪比”比较低。

我们可能喜欢索引我们的数据来加快表盘的查询[1]。让我们从注册事件开始，我们定义一个表单提交到我们的 /signup 页面。获取9月份第一周的注册数可以这样子写：

1
2
3
4
5
6

SELECT COUNT(*)
FROM event
WHERE
  (data->>'type') = 'submit' AND
  (data->>'path') = '/signup/' AND
  time BETWEEN 1409554800000 AND 1410159600000

在一个1000万个事件数据集里，有3000个是注册事件，并且没有任何索引，这条查询耗时45秒。

在每个单列里建立索引：混合

一个天真地认为提高这个性能的方法是：为每个相关事件的特性：(data->>'type'), (data->>'path'), and time 都创建一个单列索引。

我们使用这三个索引的位图索引扫描得出结果，如果该查询是有选择性的并且相关的索引部分都在内存的这可能会比较快速。的确，这些索引在适当的位置时，该查询在初始化时耗时200ms，随后再耗时20ms在执行合并数据集上——这显著改善了耗时45秒的顺序扫描。

但是这种索引策略有一些非常大的缺点:

• 写开销. 我们需要在每个 插入/更新/删除 操作该表数据时都要写这三个索引的数据[2]。 对于在该例子里需要频繁写数据来说，这可能代价太高了。

• 限制查询结果集。这种策略约束了我们定义高值事件类型的能力。如果我们需要一些比在JSON里其中之一的字段的范围更复杂的查询就无法工作了。 假如我们想要匹配一个正则，或者查看所有以/signup/开头，后面可以接任何字符的页面路径？

• 磁盘使用。在我们测试的数据集合里，该表的大小高达 6660 MB，并且这三个索引一总占用1026MB，为了支持该表，我们需要大幅增加硬盘空间。[3]

进入部分索引

我们只是仅仅分析 0.03% 的组成注册事件的表数据，但是上面这种索引策略索引了所有行。我们希望能够高效地执行查询表的一小部分数据。类似像这种情况，最好的结果是使用部分索引。

如果我们索引一个不相关的列并且限制我们的索引是匹配注册定义事件的，PostgreSQL可以非常容易地确定注册事件的数据行在哪里，并且比在相关字段里建立完整的索引更高效地查询这些数据。特别注意，考虑索引time字段，但仅仅是匹配那些过滤好的注册事件的行。这是：

1
2

CREATE INDEX event_signups ON event (time)
WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

使用这个索引，我们的执行测试查询初始化耗时200ms，并且紧接耗费2ms来执行，因此，如果我们经常执行这种查询，这会提高性能。更重要的是，部分索引解决了上面提到的三个组合的索引的三个缺点。

• 它仅仅占用 96KB 空间， 这比完整索引所有这三个字段占用1026MB空间提升了10000 倍。

• 当新行匹配我们过滤好的注册事件时，我们才需要写部分索引。由于这里有 0.03% 的注册事件，这种数据分布显著提高了写性能：实际上这是由于部分索引而免费得到的。

• PostgreSQL只是允许那些完全表达式过滤的部分加入。我们定义索引的 WHERE 子句可以使用我们可能在一条查询里使用的任何行过滤表达式。我们使用这方式来匹配更多复杂事件的定义，比如正则表达式，函数结果，或者上面提到的前缀匹配的例子。

避免索引那些谓词结果为布尔值的数据

我见过的另一种方式是试图索引布尔表达式：

1

(data->>'type') = 'submit' AND (data->>'path') = '/signup/'

直接地，将time放在第二个字段，像这样：

1
2

CREATE INDEX event_signup_time ON event
(((data->>'type') = 'submit' AND (data->>'path') = '/signup/'), time)

这比以上两种方式更加糟糕，它产生的结果是PostgreSQL的查询计划不会理解我们的查询例子来约束那些在索引里第一个字段为true的行。这是因为，查询计划不知道 WHERE 子句：

1

WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

是与下面我们索引的这个字段为必须为true的是相等的。

1

((data->>'type') = 'submit' AND (data->>'path') = '/signup/')

因此， 在我们的扫描里，使用该索引来限制事件的时间范围，但是它无论这个正则表达式是true还是false都会读取所有事件(译者注：这里指的是通过时间范围查出来的所有事件)，加载完毕后才对每一行进行检查条件。[4]

因此，我们就会从磁盘里读取比实际需要的更加多的行，并且还要对每一个结果行执行重要的检查条件。在我们的数据集里，该查询开始执行时耗时25秒，之后再连续执行8秒。事实的真相是，这比仅索引time字段差一点，在执行上是等同的。

部分索引对于预先计算那些通过谓词来匹配表的子集来说是一个非常强大的方式。通过流量判断 #postgresql IRC 并没有被充分利用。相比完全索引，它们允许一个更好的谓词范围。它们明显地更加轻量级，需要更少地写操作以及更少的磁盘空间，特别是那些高选择性的过滤。如果你重复地查询一个表的一小部分行，那么部分索引应该是你的默认策略。

爱上PostgreSQL了吗？还有人可能知道更多关于部分索引的知识？发推给我 @danlovesproofs

对构建使强大的技术变得易用的系统感兴趣？向我们投递留言到 jobs@heapanalytics.com.

• [1] 我们可能使用分表来解决这种情况，将高值事件和低值事件分到不同的子表，但是如果有许多不同的高值事件时这种方式就比较笨拙了，并且我们想要添加一个新类型的高值事件时，每次都需要重新分表。

• [2] 我们可能得到一些通过优化只在堆元组上的免更新，但是，至少在每次的插入和删除时将需要写这三个索引。

• [3] 我们可以索引这三个字段为一个组合索引，例如在：((data->>’type’), (data->>’path’), time)。这占用 755MB 空间，节约了 26% ，但这仍然是非常大的，其他的缺点也一样有。更重要的是，在这些数据上，索引会更少地应用到其他的查询，因此，如果我们正支持一些不同的同值事件，这可能不会节约我们的任何空间。

• [4] 相应的查询计划：

1
2
3
4
5
6
7

                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=820866.05..820866.06 rows=1 width=0)
   ->  Index Scan using event_signup_time on event  (cost=0.43..820865.99 rows=27 width=0)
         Index Cond: (("time" >= 1409554800000::bigint) AND ("time" <= 1410159600000::bigint))
         Filter: (((data ->> 'type'::text) = 'submit'::text) AND ((data ->> 'path'::text) = '/signup/'::text))
(4 rows)

原文地址

我也不知道，也不敢肯定为什么PostgreSQL的logo是一头大象 … 我正试着找出这个答案。

到现在为至，我最好的猜测是因为以下这个事实：在许多文化里，大象是众所周知的，具有非常好的记忆力的，或者至少是一个非常好记忆力的象征…因此，我们可以说：大象是可以非常棒地保存（数据库！）记忆（数据！）…或者换句话说：“大象是永不忘记的”…

这仅仅是我自己认为和大胆猜测的。

不过，我已经找到几个关于PostgreSQL logo原型的有趣的和确定的事情：

PostgreSQL的logo的大象名字叫”Slonik”

“Slonik”意思是”小象”并且名字来自”slony”(复数）或”slon”(单数）其中之一，分别来自俄罗斯单词“слоны”意思是“大象”（复数）或者“大象”（单数）

是不是很有趣？无论PostgreSQL的logo历史是如何的，它是一个非常棒的开源数据库。如果我发现更多关于”Slonik”的资料，我会再来更新的。