PG初识

太阳2年前 (2023-04-14)技术文章471

PG数据库是一种典型的C/S模型应用，不同的客户端通过TCP/IP进行连接、每个连接启动一个fork进程（多进程数据库）。

一、pg逻辑架构

1.1 pg与MySQL异同对比

逻辑架构	postgres	MySQL
实例	多进程	当进程多线程
数据库	一个实例下可创建多个db，各个db下相互隔离	一个实例下可创建多个db，同实例下各db可互相访问
模式	一个db下可创建多个schema，各schema下可互相访问	没有schema的概念、或者说schema等同于db
表/视图	table、view	table、view
行	tuple	row
表空间	默认表空间、自定义表空间	独立表空间、共享表空间

1.2 索引

1、pg数据库中主要支持以下几类索引：

1）B-tree索引，适合等值/范围查询

2）hash索引，只能针对等值查询

3）GiST索引，不是一种单独的索引而是一种架构，可以在这种架构上实现不同的索引策略

4）SP-GiST，空间分区索引

5）GIN索引，反转索引

2、索引的创建

一般的索引创建会阻塞对应表的DML操作，这种情况下我们可以通过并发创建索引来创建索引，并发创建索引的过程中不会阻塞相关的DML操作。但是值得注意的几点是：1）若执行并发创建索引被中断，会留下一个无效索引，需要手动删除；2）pg的重建索引不支持concurrently，可以手动创建新索引，后续删除旧索引。

## 普通的创建索引
create index index_name on tbl_name(col);

## 并发创建索引
create index concurrently idx_name1  on t1(name);

## 删除索引（索引名称schema内唯一，所以不需要指定表名）
drop index idx_name

1.3 用户权限

PG的权限按以下几个层次进行管理：

1）首先管理附在用户特殊属性上的权限，如超级用户的权限、创建数据库的权限、创建用户的权限、Login的权限，等等

2）然后是在数据库中创建模式的权限

3）接着是在模式中创建数据库对象的权限，如创建表、创建索引等等

4）之后是查询表、向表中插入数据、更新表、删除表中数据的权限

5）最后是操作表中某些字段的权限

1.4pg与MySQL语法上的异同

操作	postgres	mysql
数据库	create/drop database dbname [option]	create/drop database dbname [option]
模式	create/drop schema schema_name [option]	-
表	create/drop table tbl_name [option]	create/drop table tbl_name [option]
表空间	create tablespace test2 location 'xx';	-
查询所有数据库	\l	show databases
进入数据库	\c dbname	user dbname
查看当前schema下所有表	\d	show tables
查看表结构信息	\d tbl_name	desc tbl_name