PostgreSQL的逻辑结构管理
- PostgreSQL的逻辑结构管理
- 逻辑结构介绍
- 1️⃣ 数据库集群（cluster）
- 2️⃣ 数据库（database）
- 3️⃣ 模式（schema）
- 4️⃣ 表、索引、序列
- 5️⃣ 视图、物化视图
- 6️⃣ 函数、存储过程、触发器
- 7️⃣ 类型系统
- 8️⃣ 表空间（tablespace）
- 9️⃣ 扩展（extension）
- 数据库基本操作
- 创建数据库
- 修改数据库
- 删除数据库
- 模式
- 什么是模式
- 模式的使用
- 公共模式
- 模式的搜索路径
- 模式的权限
- 模式的可移植性
- 表
- UNLOGGED表
- 表继承
- 通过表继承实现分区表
- 声明式分区
- 触发器
- 事件触发器
- 表空间
- 视图
- 索引
- 并发创建索引（在线创建索引）
- 用户和权限管理
- 事务、并发和锁
- 两阶段提交
- 锁机制
- 表级锁模式
- 死锁及防范
- 锁的查看
- 逻辑结构介绍
逻辑结构介绍
在一个PostgreSQL数据库系统中，数据的组织结构可以分为以下3层。
- ·数据库：一个PostgreSQL数据库服务可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不
能访问其他数据库中的内容（除非使用DBLink等其他手段）。 - ·表、索引：一个数据库中有很多表、索引。一般来说，在PostgreSQL中表的术语为“Relation”，而在其他数据库中则叫“Table”。
- ·数据行：每张表中都有很多行数据。在PostgreSQL中行的术语一般为“Tuple”，而在其他数据库中则叫“Row”。
在PostgreSQL中，一个数据库服务（或叫实例）下可以有多个数据库，但一个数据库不能属于多个实例，这与Oracle数据库不同。在Oracle数据库中，一个实例只能有一个数据库，但一个数据库可以在多个实例中（如RAC）。
PostgreSQL 的“逻辑结构”可以看成 “一个实例 → N 个数据库 → 每个库里再套 N 层对象” 的嵌套层次。
PostgreSQL 实例（postmaster 进程+共享内存）
├─ 数据库集群（cluster，物理上对应 $PGDATA）
│ ├─ 数据库 A
│ │ ├─ 模式（schema）
│ │ │ ├─ 表 table
│ │ │ ├─ 索引 index
│ │ │ ├─ 视图 view
│ │ │ ├─ 序列 sequence
│ │ │ ├─ 函数/存储过程 function / procedure
│ │ │ ├─ 类型 domain / composite / enum
│ │ │ └─ 其他对象：触发器、规则、物化视图、外部表…
│ │ └─ 表空间（tablespace）引用
│ ├─ 数据库 B
│ └─ …
└─ 全局共享目录（pg_global）
1️⃣ 数据库集群（cluster）
物理：一个 $PGDATA 目录即一个 cluster。
逻辑：由 一个或多个数据库 组成，外加 共享系统目录 pg_global（存放用户、表空间、复制槽等跨库对象）。
2️⃣ 数据库（database）
每个库之间 完全隔离（连接时必须指定 dbname）。
拥有 独立的系统表（pg_class, pg_attribute…）。
默认库：postgres, template0, template1。
创建：CREATE DATABASE mydb OWNER alice;
3️⃣ 模式（schema）
数据库内部的 命名空间。
默认：public；搜索路径由 search_path 控制。
解决“同名表不同业务”问题，也方便权限隔离。
创建：CREATE SCHEMA sales;
4️⃣ 表、索引、序列
表：数据主体；索引：加速；序列：serial/identity 的底层。
都归属某个 schema，命名规则 schema.object。
5️⃣ 视图、物化视图
视图：虚拟表；物化视图：快照结果，可刷新。
6️⃣ 函数、存储过程、触发器
函数：支持多语言（SQL、PL/pgSQL、Python…）。
触发器：行级/语句级、BEFORE/AFTER/INSTEAD OF。
7️⃣ 类型系统
内置类型：int, text, timestamptz…
用户可扩展：domain、composite、enum、range、数组、JSON…
8️⃣ 表空间（tablespace）
逻辑：集群级对象；创建：CREATE TABLESPACE fast LOCATION '/ssd/pg';
用途：把热表/索引映射到不同物理路径，做 IO 分流。
9️⃣ 扩展（extension）
逻辑单元打包：PostGIS、pgcrypto、pg_stat_statements…
一个扩展可以在多个数据库里独立 CREATE EXTENSION。
常用系统目录速查
数据库列表：SELECT oid, datname FROM pg_database;
当前库下所有模式+表：SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace;
数据库基本操作
数据库的基本操作包括创建、删除和修改数据库等。
创建数据库
创建数据库的语法如下：
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace ]
[ CONNECTION LIMIT [=] connlimit ] ]参数说明如下。
·OWNER [=] user_name：用于指定新建的数据库属于哪个用户，如果不指定，新建的数据库就属于当前执行命令的用户。
·TEMPLATE [=] template：模板名（从哪个模板创建新数据库），如果不指定，将使用默认模板数据库（template1）。
·[ENCODING [=] encoding]：创建新数据库使用的字符编码。比如使用ISO-8859-1（LATIN1）编码创建一个数据库时，代码如下。
·TABLESPACE [=] tablespace：用于指定和新数据库关联的表空间名称。
·CONNECTION LIMIT [=] connlimit]：用于指定数据库可以接受多少并发的连接。默认值为“-1”，表示没有限制。
一般情况下，创建数据库不需要上面那么多的参数，最简单的创建数据库的示例如下：
CREATE DATABASE osdbadb;eg:
CREATE DATABASE testdb01 ENCODING 'LATIN1' TEMPLATE template0;
通常使用时很少会用到指定数据库的字符集，因为PostgreSQL数据库服务端并不支持汉字字符集GBK GB180 30，所以一般都是使用UTF8字符集来支持中文的。
修改数据库
修改数据库的语法格式如下：
ALTER DATABASE name [ [ WITH ] option [ ... ] ]这里的“option”可以以下几种语法结构：
·CONNECTION LIMIT connlimit。
·ALTER DATABASE name RENAME TO new_name。
·ALTER DATABASE name OWNER TO new_owner。
·ALTER DATABASE name SET TABLESPACE new_tablespace。
·ALTER DATABASE name SET configuration_parameter {TO |=} {value|DEFAULT}。
·ALTER DATABASE name SET configuration_parameter FROM CURRENT。
·ALTER DATABASE name RESET configuration_parameter。
·ALTER DATABASE name RESET ALL。
示例1，将数据库“testdb01”的最大连接数修改为“10”，命令
如下：
postgres=# alter database testdb01 CONNECTION LIMIT 10;
ALTER DATABASE
示例2，将数据库“testdb01”的名称改为“mydb01”，命令如下：
postgres=# alter database testdb01 rename to mydb01;
ALTER DATABASE
示例3，改变数据库“testdb01”的配置参数，使用户一旦连接到这个用户，某个配置参数就设置为指定的值。比如，关闭在数据库
“testdb01”上的默认索引扫描，命令如下：
ALTER DATABASE testdb01 SET enable_indexscan TO off;
删除数据库
删除数据库的命令比较简单，语法格式如下：
DROP DATABASE [ IF EXISTS ] name
示例1，直接删除数据库“mytestdb01”，命令如下
osdba=# drop database mytestdb01;
DROP DATABASE
示例2，如果某数据库存在，则将其删除，如果不存在，使用删除
命令时也不报错：
osdba=# drop database if exists mytestdb01;
NOTICE: database "mytestdb01" does not exist, skipping
DROP DATABASE
注意，如果还有用户连接在这个数据库上，将无法删除该数据
库，命令如下：
osdba=# drop database mytestdb01;
ERROR: database "mytestdb01" is being accessed by other
users
DETAIL: There is 1 other session using the database.
问题二：能否在事务块中修改数据库？
答：可以，示例如下：
osdba=# begin;
BEGIN
osdba=# alter database mytestdb01 rename to mydb01;
ALTER DATABASE
osdba=# rollback;
ROLLBACK
模式
模式是数据库领域的一个基本概念，有些数据库把模式和用户合二为一了，而PostgreSQL是有清晰的模式定义。
什么是模式
模式（Schema）是数据库中的一个概念，可以将其理解为一个命名空间或目录，不同的模式下可以有相同名称的表、函数等对象而不
会产生冲突。提出模式的概念是为了便于管理，只要有权限，各个模式的对象可以互相调用。
在PostgreSQL中，一个数据库包含一个或多个模式，模式中又包含了表、函数以及操作符等数据库对象。在PostgreSQL中，不能同时访问不同数据库中的对象，当要访问另一个数据库中的表或其他对象时，需要重新连接到这个数据库，而模式却没有此限制，一个用户在连接到一个数据库后，就可以同时访问这个数据库中多个模式的对象。从这个特性来说，PostgreSQL中模式的概念与MySQL中的Database的概念是等价的，在MySQL中也可以同时访问多个Database中的对象，就与PostgreSQL中可以同时访问多个Schema中的对象是一样的。在Oracle数据库中，一个用户就对应一个Schema。大家在以后的学习过程中需要注意在不同的数据库系统（Oracle、MySQL）中Database、模式这些概念的不同。
我们需要模式的主要原因有以下几个：
·允许多个用户使用同一个数据库且用户之间又不会互相干扰。
·把数据库对象放在不同的模式下组织成逻辑组，使数据库对象更便于管理。
·第三方的应用可以放在不同的模式中，这样就不会和其他对象的名字产生冲突了。
模式的使用
创建模式的语法如下：
CREATE SCHEMA schemaname [ AUTHORIZATION username ] [
schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [
... ] ]
下面举例说明如何创建、查看、删除和修改模式。
创建一个名为“osdba”的模式：
osdba=# CREATE SCHEMA osdba;
CREATE SCHEMA
查看已有模式的命令，如下：
osdba=# \dn
List of schemas
Name | Owner
--------+-------
osdba | osdba
public | osdba
删除模式的示例如下：
osdba=# DROP SCHEMA osdba;
DROP SCHEMA
为用户“osdba”创建模式，模式名称也为“osdba”，命令如
下：
osdba=# CREATE SCHEMA AUTHORIZATION osdba;
CREATE SCHEMA
osdba=# \dn
List of schemas
Name | Owner
--------+-------
osdba | osdba
public | osdba
在模式中可以修改名称和属主，语法格式如下：
ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner公共模式
要创建或者访问模式中的对象，需要先写出一个受修饰的名字，这个名字包含模式名及表名，它们之间用一个“.”分隔开，语法如下
schema_name.table_name
通常情况下，创建和访问表的时候都不用指定模式，实际上这时访问的都是public模式。每当我们创建一个新的数据库时，PostgreSQL都会为我们自动创建一个名为“public”的模式。当登录到该数据库时，如果没有特意指定，都是以该模式（public模式）操作各种数据对象的。
模式的搜索路径
使用数据库对象时，虽然可以使用全称来定位该对象，但是这样一来，每次都不得不键入schema_name.object_name，这显然很烦琐。对此，PostgreSQL中提供了模式搜索路径，这种形式有些类似Linux中$PATH环境变量的用法，当我们执行一个Shell命令时，只有该命令位于$PATH的目录列表中时才可以通过命令名直接执行，否则就需要输入它的全路径名。
显示当前搜索路径
SHOW search_path;
模式的权限
默认情况下，用户无法访问模式中不属于它们的对象。若要访问此类对象，模式的所有者必须在模式下赋予它们“USAGE”权限。为了让用户使用模式中的对象，可能需要赋予适合该对象的额外权限。
用户也可以在别人的模式中创建对象，当然，这得被赋予了在该模式下的CREATE权限。请注意，默认情况下每个人在public模式下都有CREATE和USAGE权限，也就是说，允许所有可以连接到指定数据库上的用户在这里创建对象。当然，也可以撤销这个权限，命令如下：
模式的可移植性
-
在SQL标准里，同一个模式下的对象是不能被不同的用户拥有的，而且有些数据库系统不允许创建和它们的所有者不同名的模式，如Oracle数据库。实际上，在那些只实现了标准中规定的基本模式的数据库系统里，模式和用户的概念几乎是一样的，比如Oracle数据库。因此，许多用户考虑对名字加以修饰，使它们真正由“username.tablename”组成。如果在PostgreSQL中为每个用户都创建一个与用户名同名的模式，那么就能与Oracle数据库兼容了。
-
同样，在SQL标准中也没有public模式的概念。为了最大限度地遵循标准，并且与其他数据库兼容（如Oracle数据库），建议不要使用（甚至是应该删除）public模式。
-
当然，有些数据库系统中可能根本没有模式，而是通过允许跨数据库访问来提供模式的功能，如MySQL。如果需要在这些数据库上实现最大的可移植性，或许不应该使用模式。假设MySQL实例中有3个数据库，在移植到PostgreSQL中时，或许你应该建3个模式，使其与MySQL实例中的3个数据库相对应，而不是在PostgreSQL中建3个数据库与之对应。
表
UNLOGGED表
UNLOGGED表是从PostgreSQL9.1版本开始新增的一种表，主要是通过禁止产生WAL日志的方式提升写性能。因为没有WAL日志，所以表的内容无法在主备库直接同步，如果此时数据库异常宕机，表的内容将丢失，所以可以把UNLOGGED表称为“半临时表”。当然如果数据库是正常关机的，则UNLOGGED表的内容不会丢失。
创建UNLOGGED表的命令是“CREATE UNLOGGED TABLE”，如下：
osdba=# CREATE UNLOGGED TABLE unlogged01(id int primary
key, t text);
CREATE TABLE
UNLOGGED表在使用上与普通表没有区别，仅仅在插入、删除、更新数据时不产生WAL日志，所以做这些DML操作的性能会更高。另外需要注意的是，数据库异常宕机时，UNLOGGED表的数据可能会丢失。
表继承
表继承是PostgreSQL中特有的。假设有一张人员表“persons”：
CREATE TABLE persons (
name text,
age int,
sex boolean
);
现在要再加一个学生表“student”，学生表比人员表多了一个班级号字段“class_no”，查询“persons”可以查询到这两条数据：
CREATE TABLE students (
class_no int
)INHERITS (persons);
这时如果向“students”表中插入两条数据，命令如下：
osdba=# insert into students values('张三',15,true,1);
INSERT 0 1
Time: 29.364 ms
osdba=# insert into students values('翠莲',14,false,2);
INSERT 0 1
Time: 32.840 ms
osdba=# select * from persons;
name | age | sex
------+-----+-----
张三 | 15 | t
翠莲 | 14 | f
(2 rows)
Time: 0.434 ms
osdba=# select * from students;
name | age | sex | class_no
------+-----+-----+----------
张三 | 15 | t | 1
翠莲 | 14 | f | 2
(2 rows)
Time: 0.322 ms
更改students表中的数据后，通过查看persons表也可以看到上述变化：
osdba=# update students set age=13 where name='张三';
UPDATE 1
Time: 78.830 ms
osdba=# select * from persons;
name | age | sex
------+-----+-----
翠莲 | 14 | f
张三 | 13 | t
(2 rows)
但如果向persons表中插入一条数据，查询student表是看不到这条数据的：
osdba=# insert into persons values('王五',30,true);
INSERT 0 1
Time: 12.971 ms
osdba=# select * from persons;
name | age | sex
------+-----+-----
王五 | 30 | t
翠莲 | 14 | f
张三 | 13 | t
(3 rows)
osdba=# select * from students;
name | age | sex | class_no
------+-----+-----+----------
翠莲 | 14 | f | 2
张三 | 13 | t | 1
(2 rows)
这里对表继承中父表与子表的关系总结如下，当查询父表时会把父表中子表的数据也查询出来，反之则不行。
如果只想把父表本身的数据查询出来，只需要在查询的表名前加ONLY关键字，示例如下：
osdba=# select * from only persons;
name | age | sex
------+-----+-----
王五 | 30 | t
(1 row)
所有父表的检查约束和非空约束都会自动被所有子表继承。不过其他类型的约束（唯一、主键、外键）不会被继承。
一个子表可以从多个父表继承，这种情况下它将拥有所有父表字段的总和，并且子表中定义的字段也会加入其中。如果同一个字段名出现在多个父表中，或者同时出现在父表和子表的定义里，那么这些字段就会被“融合”，因此在子表里就只有一个这样的字段。要想融合，字段的数据类型必须相同，否则就会报错。融合的字段将会拥有其父字段的所有检查约束，并且如果某个父字段存在非空约束，那么融合后的字段也必须是非空的。
采用SELECT、UPDATE、DELETE等命令访问或操作父表时，也会同时访问或操作相应的子表，而使用ALTER TABLE命令修改父表的结构定义时，大多数情况下也会同时修改子表的结构定义，但“REINDEX”“VACUUM”命令不会影响到子表。此外，唯一约束、外键的使用域也不会扩大到子表上。
通过表继承实现分区表
PostgreSQL内部是通过表继承来实现分区表的。PostgreSQL10.X之前的版本只能通过表继承来实现分区表。而PostgreSQL10.X提供了相应的DDL语句可以直接创建分区表，这种方式在PostgreSQL中被称为声明式分区（Declarative Partitioning），但内部原理仍是表继承。
多大数据适合使用分区表？一般取决于具体的应用，不过也有个简单的基本原则，即表的大小超过了数据库服务器的物理内存大小时使用。
表分区的好处
- ·使删除历史数据更快，如果是按时间分区的，在删除历史数据时，直接删除历史分区即可，如果没有分区，通过DELETE删除历史数据时会很慢，还容易导致VACUUM超载。
- ·某些类型的查询性能可以得到极大提升。特别是在表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。在按时间分区的表中，如果大多数查询发生在时间最近的一个或几个分区中，而较早时间的分区较少查询，那么，在建分区表后，各个分区表均有各自的索引，使用率较高的分区表的索引就可能完全缓存在内存中，这样效率就会提高很多。
- ·当查询或更新一个分区的大部分记录时，连续扫描该分区而不是使用索引离散地访问整个表，可以获得巨大的性能提升。
- ·很少用到的历史数据可以使用表空间的技术移动到便宜一些的慢速存储介质上。因为使用分区表可以将不同的分区安置在不同的物理介质上。
下面来看一个实现分区表的例子。假设有一张销售明细表，定义
如下：
CREATE TABLE sales_detail (
product_id int not null, --产品编号
price numeric(12,2), --单价
amount int not null, --数量
sale_date date not null,--销售日期
buyer varchar(40),--买家名称
buyer_contact text --买家的联系方式
);
先建主表，主表就是上面的sales_detail表。按销售日期进行分
区，每个月为一个分区，建各个分区的语句如下：
CREATE TABLE sales_detail_y2014m01 (CHECK (sale_date >=DATE '2014-01-01' AND sale_date < DATE '2014-02-01' ) ) INHERITS (sales_detail);
CREATE TABLE sales_detail_y2014m02 (CHECK (sale_date >=DATE '2014-02-01' AND sale_date < DATE '2014-03-01' ) ) INHERITS (sales_detail);
CREATE TABLE sales_detail_y2014m03 (CHECK (sale_date >=DATE '2014-03-01' AND sale_date < DATE '2014-04-01' )) INHERITS (sales_detail);
...
CREATE TABLE sales_detail_y2014m12 (CHECK (sale_date >=
DATE '2014-12-01' AND sale_date < DATE '2015-01-01' ) )
INHERITS (sales_detail);
每个分区实际上都是一张完整的表，只不过是从sales_detail表中继承定义的。父表“sales_deail”中实际是不存放数据的。以后要删除旧数据，只需要删除月份最早的表。不知大家是否注意到每个分区表中都加了一个约束，这表示只允许插入本月内的数据。
声明式分区
所谓“声明式分区”就是PostgreSQL提供了相应的DDL语句来创建分区表，而不需要像6.4.8节中那样以表继承的方式创建分区表，声明式分区方式更简单。
eg：
CREATE TABLE sales_detail (
product_id int not null, --产品编号
price numeric(12,2), --单价
amount int not null, --数量
sale_date date not null,--销售日期
buyer varchar(40),--买家名称
buyer_contact text --买家的联系方式
) PARTITION BY RANGE (sale_date);
注意，因为现在还没有创建分区表的分区，所以是不能插入数据
的，示例如下：
osdba=# insert into sales_detail values(1, 99.99, 2,now(), 'tangcheng', 'HangZhou');
ERROR: no partition of relation "sales_detail" found for row
DETAIL: Partition key of the failing row contains(sale_date) = (2019-01-11).
下面我们来创建分区表的分区：
CREATE TABLE sales_detail_y2014m01 PARTITION OF sales_detail FOR VALUES FROM ('2014-01-01') TO ('2014-02-01');
CREATE TABLE sales_detail_y2014m02 PARTITION OF sales_detail FOR VALUES FROM ('2014-02-01') TO ('2014-03-01');
CREATE TABLE sales_detail_y2014m03 PARTITION OF sales_detail FOR VALUES FROM ('2014-03-01') TO ('2014-04-01');
触发器
事件触发器
表空间
有时我们需要把不同的表放到不同的存储介质或文件系统下，这时就需要用到表空间，在PostgreSQL中，表空间实际上是为表指定一个存储目录。
在创建数据库时可以为其指定默认的表空间。创建表、创建索引的时候可以指定表空间，这样表、索引就可以存储到表空间对应的目录下了。
表空间的使用方法
创建表空间的语法如下：
CREATE TABLESPACE tablespace_name [ OWNER user_name ]
LOCATION 'directory'
创建表空间的示例如下：
osdba=# CREATE TABLESPACE tbs_data location
'/data/pgdata';
CREATE TABLESPACE
创建数据库时可以指定默认的表空间，这样以后在此数据库中创
建表、索引时就可以自动存储到表空间指定的目录下：
create database db01 tablespace tbs_data;
改变数据库的默认表空间的语法如下：
osdba=# ALTER DATABASE db01 set TABLESPACE tbs_data;
ALTER DATABASE
注意，在执行该操作时，不能有用户连接到这个数据库上，否则会报如下错误：
osdba=# ALTER DATABASE db01 set TABLESPACE pg_default;
ERROR: database "db01" is being accessed by other users
DETAIL: There is 1 other session using the database.
创建表时也可以指定表空间，命令如下：
osdba=# create table test01(id int, note text) tablespace
tbs_data;
CREATE TABLE
创建索引时同样可以指定表空间，命令如下：
osdba=# create index idx_test01_id on test01(id)
tablespace tbs_data;
CREATE INDEX
创建唯一约束时可指定约束索引的表空间，命令如下：
osdba=# ALTER TABLE test01 ADD CONSTRAINT
unique_test01_id unique(id) USING INDEX TABLESPACE
tbs_data;
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit
index "unique_test01_id" for table "test01"
ALTER TABLE
增加主键时也可以指定主键索引的表空间，命令如下：
ALTER TABLE test01 ADD CONSTRAINT pk_test01_id primary
key(id) USING INDEX TABLESPACE tbs_data;
把表从一个表空间移到另一个表空间的命令如下：
osdba=# alter table test01 set tablespace pg_default;
ALTER TABLE
注意，在移动表的时候表会被锁定，对此表的所有操作都将无法执行，包括SELECT操作，所以请考虑在合适的时机做这个操作。
视图
使用视图一般出于几个原因：
- ·可使复杂的查询易于理解和使用。
- ·安全原因，视图可以隐藏一些数据，如在一张用户表中，可以通过定义视图把密码等敏感字段去掉。
- ·把一些函数返回的结果映射成视图。
索引
建立索引的好处是加快对表中记录的查找或排序。但建索引要付出以下代价：
- ·增加了数据库的存储空间。
- ·在插入和修改数据时要花费较多的时间，因为索引也要随之更
新。
并发创建索引（在线创建索引）
通常情况下，在创建索引的时候PostgreSQL会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引的操作。
在此过程中，其他用户仍然可以读取表，但是插入、更新、删除等操作将一直被阻塞，直到索引创建完毕。如果这张表是更新较频繁且比较大的表，那么创建索引可能需要几十分钟，甚至数个小时，这段时间内都不能做任何插入、删除、更新操作，这在大多数的在线数据库中都是不可接受的。
鉴于此，PostgreSQL支持在不长时间阻塞更新的情况下建立创建索引，这是通过在CREATE INDEX中加CONCURRENTLY选项来实现的。当该选项被启用时，PostgreSQL会执行表的两次扫描，因此该方法需要更长的时间来建索引。尽管如此，该选项也是很有用的。
用户和权限管理
用户的权限分为两类，一类是在创建用户时就指定的权限，有如下几种：
- ·超级用户的权限。
- ·创建数据库的权限。
- ·是否允许LOGIN的权限。
这些权限是创建用户时指定的，后面可使用ALTER ROLE命令来修改。
另一类权限是由GRANT命令和REVOKE命令来管理的，有如下几种：
- ·在数据库中创建模式（SCHEMA）。
- ·允许在指定的数据库中创建临时表的权限。
- ·连接某个数据库的权限。
- ·在模式中创建数据库对象的权限，如创建表、视图、函数等。
- ·在一些表中做SELECT、UPDATE、INSERT、DELETE等操作的权限。
- ·在一张具体的表的列上进行SELECT、UPDATE、INSERT操作的权限。
- ·对序列进行查询（执行序列的currval函数）、使用（执行序列的currval函数和nextval函数）、更新的权限。
- ·在声明表上创建触发器的权限。
- ·把表、索引等建到指定表空间的权限。
PostgreSQL中的权限是按以下几个层次进行管理的：
- 1）首先管理赋在用户特殊属性上的权限，如超级用户的权限、创建数据库的权限、创建用户的权限、LOGIN权限，等等。
- 2）然后是在数据库中创建模式的权限。
- 3）接着是在模式中创建数据库对象的权限，如创建表、索引等。
- 4）之后是查询表、向表中插入数据、更新表、删除表中数据的权限。
- 5）最后是操作表中某些字段的权限。
事务、并发和锁
在PostgreSQL中，可使用多版本并发控制（MVCC）来维护数据的一致性。
相比于锁定模型，其主要优点是在MVCC下对检索（读）数据的锁请求与写数据的锁请求不冲突，读不会阻塞写，而写也从不阻塞读。在PostgreSQL中也提供了表和行级别的锁定语句，让应用能更方便地操作并发数据。
两阶段提交
PostgreSQL数据库支持两阶段提交协议。
在分布式系统中，事务中往往包含了多台数据库上的操作，虽然单台数据库的操作能够保证原子性，但多台数据库之间的原子性就需要通过两阶段提交来实现了，两阶段提交是实现分布式事务的关键。
两阶段提交协议有如下5个步骤。
1）应用程序先调用各台数据库做一些操作，但不提交事务。然后应用程序调用事务协调器（该协调器可能也是由应用自己实现的）中的提交方法。
2）事务协调器将联络事务中涉及的每台数据库，并通知它们准备提交事务，这是第一阶段的开始。PostgreSQL中一般是调用PREPARETRANSACTION命令。
3）各台数据库接收到PREPARE TRANSACTION命令后，如果要返回成功，则数据库必须将自己置于如下状态：确保后续能在被要求提交事务的时候提交事务，或后续能在被要求回滚事务的时候回滚事务。所以PostgreSQL会将已准备好提交的信息写入持久存储区中。如果数据库无法完成此事务，它会直接返回失败给事务协调器。
4）事务协调器接收所有数据库的响应。
5）在第二阶段，如果任何一个数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令“ROLLBACK PREPARED”给各台数据库。如果所有数据库的响应都是成功的，则向各台数据库发送COMMITPREPARED命令，通知各台数据库事务成功。
锁机制
PostgreSQL数据库中有两类锁：表级锁和行级锁。当要查询、插入、更新、删除表中的数据时，首先会获得表上的锁，然后再获得行上的锁。
表级锁模式
PostgreSQL 把“锁”按粒度分成
- 表级锁（table-level lock）
- 行级锁（row-level lock）
- 页级/咨询锁（advisory lock）
下面只聚焦表级锁的 8 种模式，按“强度”从弱到强排列，记住它们的名字和冲突矩阵就能判断“谁能跟谁同时存在”。
八种表级锁一览
| 锁模式（内部名） | SQL 语句/场景 | 与哪些锁冲突 | 备注 |
|---|---|---|---|
| ACCESS SHARE | 普通 SELECT |
只与 ACCESS EXCLUSIVE 冲突 | 最弱，读不挡读 |
| ROW SHARE | SELECT … FOR UPDATE/FOR SHARE |
与 EXCLUSIVE、ACCESS EXCLUSIVE 冲突 | 行级意向锁 |
| ROW EXCLUSIVE | UPDATE/DELETE/INSERT |
与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 | 写意向锁 |
| SHARE UPDATE EXCLUSIVE | VACUUM、ANALYZE、CREATE INDEX CONCURRENTLY |
与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 | 并发维护类操作 |
| SHARE | CREATE INDEX（非 CONCURRENTLY） |
与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 | 建索引时读仍允许 |
| SHARE ROW EXCLUSIVE | ALTER TABLE … SHARE ROW EXCLUSIVE（极少手动使用） |
与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 | 替代全表 EXCLUSIVE |
| EXCLUSIVE | REFRESH MATERIALIZED VIEW CONCURRENTLY |
与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、ACCESS EXCLUSIVE 冲突 | 写仍允许，读被挡 |
| ACCESS EXCLUSIVE | DROP TABLE、TRUNCATE、ALTER TABLE、LOCK TABLE … ACCESS EXCLUSIVE |
与所有其它七种锁冲突 | 最强锁，读写全停 |
图形化冲突矩阵（✓=兼容，✗=冲突）
| 锁模式 | ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ |
| ROW SHARE | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ |
| ROW EXCLUSIVE | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |
| SHARE UPDATE EXCLUSIVE | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |
| SHARE | ✓ | ✓ | ✗ | ✗ | ✓ | ✗ | ✗ | ✗ |
| SHARE ROW EXCLUSIVE | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| EXCLUSIVE | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| ACCESS EXCLUSIVE | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
死锁及防范
死锁是指两个或两个以上的事务在执行过程中相互持有对方期待的锁，若没有其他机制，它们都将无法进行下去。例如，事务1在表A上持有一个排它锁，同时试图请求一个在表B上的排它锁，而事务2已经持有表B的排它锁，同时却在请求表A上的一个排它锁，那么两个事务就都不能执行了。PostgreSQL能够自动侦测到死锁，然后退出其中一个事务，从而允许其他事务执行。不过，哪个事务会被退出是很难预测的。
死锁的发生必须具备以下4个必要条件。
- ·互斥条件：指事务对所分配到的资源加了排它锁，即在一段时间内只能由一个事务加锁占用。如果此时还有其他进程请求排它锁，则请求者只能等待，直至持有排它锁的事务释放排它锁。
- ·请求和保持条件：指事务已经至少持有了一把排它锁，但又提出了新的排它锁请求，而该资源上的排它锁已被其他事务占有，此时请求被阻塞，但同时它对自己已获得的排它锁又持有不放。
- ·不剥夺条件：指事务已获得的锁在未使用完之前不能被其他进程剥夺，只能在使用完时由自己释放。
- ·环路等待条件：指在发生死锁时，必然存在一个事务—资源的环形链，即事务集合{T0，T1，T2，…，Tn}中的T0正在等待T1持有的排它锁；P1正在等待P2持有的排它锁，……，Pn正在等待已被P0持有的排它锁。
防止死锁最好的方法通常是保证使用一个数据库的所有应用都以
相同的顺序在多个对象上请求排它锁。
由于数据库可以自动检测出死锁，所以应用也可以通过捕获死锁异常来处理死锁。但这个方法并不是很好，因为数据库检测死锁需要付出一定的代价，可能会导致应用程序过久地持有排它锁而使系统的并发处理能力下降。
排它锁持有的时间越长也就越容易导致死锁，所以在进行程序设计时要尽量短时间地持有排它锁
锁的查看
查看当前表级锁
SELECT pid, mode, granted, relation::regclass
FROM pg_locks
WHERE locktype = 'relation';
字段总览（v12+ 通用）
| 字段名 | 类型 | 中文说明 | 典型值 / 备注 |
|---|---|---|---|
| locktype | text | 锁的粒度类别 | relation / tuple / page / transactionid / virtualxid / advisory / object / extend / frozenid |
| database | oid | 数据库 OID | 0 表示共享对象，非 0 时使用 oid = 16384 对应具体库 |
| relation | oid | 表/索引/序列 OID | 与 pg_class.oid 关联；非 relation 锁为 0 |
| page | int4 | 页号 | 仅 locktype = page 时有效 |
| tuple | int2 | 页内行号 | 仅 locktype = tuple |
| classid | oid | 系统 catalog 类 OID | locktype = object 时使用 |
| objid | oid | 系统 catalog 对象 OID | 同上 |
| objsubid | int4 | 子对象号 | 如列号、过程参数序号；无子对象时为 0 |
| transactionid | xid | 事务号 | locktype = transactionid |
| virtualxid | text | 虚拟事务号 | 形如 4/123，会话内部唯一 |
| pid | int4 | 持有或等待的后端进程 ID | 与 pg_stat_activity.pid 关联 |
| mode | text | 锁模式 | AccessShare / RowExclusive / Exclusive … |
| granted | bool | 是否已获取锁 | true = 已持有，false = 正在等待 |
| fastpath | bool | 是否走“快速路径” | true 表示通过共享锁表快速授予 |
| waitstart | timestamptz | 开始等待时间 | 仅当 granted = false 时非空，用于计算等待时长 |