cluster 重新组织表

os: ubuntu 16.04
db: postgresql 9.6.8
pg_repack: 1.4.4

CLUSTER 是根据一个索引聚簇一个表，优点是使用这个索引高效，确定是可能会影响其它索引的效率。

语法

peiybdb=# \h cluster
Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

实验

peiybdb=# \d+ tmp_t0;
                       Table "public.tmp_t0"
 Column |  Type  | Modifiers | Storage | Stats target | Description 
--------+--------+-----------+---------+--------------+-------------
 c0     | bigint |           | plain   |              | 
 c1     | bigint | not null  | plain   |              | 
Indexes:
    "tmp_t0_pk" PRIMARY KEY, btree (c1)

peiybdb=# cluster verbose public.tmp_t0 using tmp_t0_pk;
INFO:  clustering "public.tmp_t0" using index scan on "tmp_t0_pk"
INFO:  "tmp_t0": found 0 removable, 2999983 nonremovable row versions in 16217 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.20s/7.75u sec elapsed 9.06 sec.
CLUSTER
peiybdb=# 

执行 CLUSTER 时的锁

peiybdb=# select oid,relname from pg_class where relname in ('tmp_t0','tmp_t0_pk');
   oid   |  relname  
---------+-----------
 1447752 | tmp_t0_pk
 1447603 | tmp_t0
(2 rows)



peiybdb=# select * from pg_locks where pid = '29475';
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath 
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 virtualxid    |          |          |      |       | 3/4278     |               |         |       |          | 3/4278             | 29475 | ExclusiveLock       | t       | t
 relation      |  1406609 |  1453912 |      |       |            |               |         |       |          | 3/4278             | 29475 | AccessExclusiveLock | t       | f
 relation      |  1406609 |  1447752 |      |       |            |               |         |       |          | 3/4278             | 29475 | AccessShareLock     | t       | f
 relation      |  1406609 |  1447752 |      |       |            |               |         |       |          | 3/4278             | 29475 | AccessExclusiveLock | t       | f
 relation      |  1406609 |  1447603 |      |       |            |               |         |       |          | 3/4278             | 29475 | ShareLock           | t       | f
 relation      |  1406609 |  1447603 |      |       |            |               |         |       |          | 3/4278             | 29475 | AccessExclusiveLock | t       | f
 transactionid |          |          |      |       |            |     454353304 |         |       |          | 3/4278             | 29475 | ExclusiveLock       | t       | f
(7 rows)

可以看到在对表做 cluster 时，表的锁为 ShareLock、AccessExclusiveLock。
有 AccessExclusiveLock 时，表是做不了任何操作的，包括select，在生产环境慎用。最好使用 pg_repack 操作。

参考：
http://postgres.cn/docs/9.6/sql-cluster.html