PostgreSQL 初探

2020 年 2月的总排名为第四名，关系型数据库中排名第四名。排名信息参考：DB-Engines

为了更好地应用的工作中，针对 postgresql 给我造成的疑惑整理了如下内容：

(1) Postgresql 版本之间的主要区别

(2) PostgreSQL 支持的数据类型

(3) PostgreSQL 存储引擎Heap介绍

(4) 围绕 JSON/jsONB 的全文检索功能以及 12.x 中的 JSON/JSONB Path

1, PostgreSQL 版本之间的主要区别

2020 年 2 月当我们访问 PostgreSQL 官网的时候，通过看到多个 Release 版本，应该怎么选择呢？

PostgreSQL 9.x 版本在 2020 年 2 月的版本是计划中的左右一个 release 版本，所以翻阅了 PostgreSQL 的相关文档后，整理了从 10.X，11.X，12.X 这三个版本的信息。如下图：

通过上面的版本我们发现，给开发人员带来的变化不大，这里列出了 4 项，开发人员经常关注的技术点：

（1）当前支持的数据类型

（2）存储引擎 Heap 具体的特性

（3）围绕 JSON/JSONB 的全文检索功能以及 12.x 中的 JSON/JSONB Path

所以下文将主要研究上面是 4 个技术点。

这里使用了一个 12.2 版本的 PostgreSQL 的 Docker 镜像：

docker pull postgre:12.2

如果对其他版本感兴趣可以访问Docker hub

启动 Postgre

docker run -e POSTGRES_PASSWord=123456 -p 5432:5432 --name potgre12 postgres:12.2

其中的 -e POSTGRES_PASSWORD=123456 是设置 superuser 用户的密码，superuser 的默认 username 是 postgres.

PS：如果不想通过密码访问也可以通过 POSTGRES_HOST_AUTH_METHOD=trust 来允许不需要密码访问。

安装 psql 客户端（如果你还是用其他客户端可以跳过该步骤）

brew install libpq

连接 postgreSQL

psql -h 127.0.0.1 -p 5432 -U postgres --passsword

2，PostgreSQL 支持数据类型

PostgreSQL 中支持多种数据类型。如下图

3，PostgreSQL 存储引擎 Heap 具体特性

PostgreSQL 的存储引擎 Heap 和 Mysql 的 InnoDB 都完全遵循ACID，使用的是行级锁。

4，围绕 JSON/JSONB 的全文检索功能以及 12.x 中的 JSON/JSONB Path

JSON 和 JSONB 的区别在于：

（1） JSON 类型以 JSON 格式存储文本数据，在查询的时候需要先做内容解析，然后进行查询。所以写入快、查询慢。

（2）JSONB 类型以 JSON 格式的二进制形式存储数据，在插入的时候需要做文本解析，查询的时候并不需要做解析，因此写入慢，查询快。

而对 JSON/JSONB 涉及到内容包括，下面的内容对对一条数据的一个简单例子：建表、新建数据、查询、模糊查询、数据更新。

（1）建表

create table boy (
    id int primary key not null, 
    name char(30) not null, 
    infORMation jsonb
);

（2）新建数据

insert into boy (id, name, information) values (1,"Page", "{"age":18}");
insert into boy (id, name, information) values (2,"Aaron", "{"age":18}");
insert into boy (id, name, information) values (3,"Join", "{"age":18, "address":"beijing donGChengqu"}");

（3）查询

# 从 9.4 版本只是的通过 操作符 来查询 age 等于 18 的 student

select * from boy where (information->>"age")::int=18;


# 从 9.4 版本只是的通过 操作符 来查询 address 中以 beijing 开头的 students

select * from boy where (information->>"address")::text like "beijing%";


# 12.x版本支持的 JSON/JSONB Path 方式查询 age 等于 18 的 student

select jsonb_path_query(information, "$.age ? (@==18)") from boy;


# 12.x版本支持的 JSON/JSONB Path 方式查询 address 中以 beijing 开头的 students

select jsonb_path_query(information, "$.address ? (@ starts with "beijing")") from boy;

通过上面的代码不难发现，PostgreSQL 12 中提供的 JSON function 非常好用。更多查询语句可以参考: 官网 json functions

（4）模糊查询（全文检索）

经过尝试默认条件下 PostgreSQL 提供的分词器对中文分词并不好。所以下面将演示英文的全文检索

新建表

create table technical (
   id int primary key not null,
   name tsvector
);

其中 tsvector 的数据类型是是分词器类型。可以执行 select "hello page"::tsvector;来查看分词结果。

插入数据

insert into technical (id, name) values (1, "Test Driven Development"), (2, "Test Driven Design"), (3, "Behave Driven Development");

当搜索关键词 Test和Driven 时应该是显示 2 条结果；

select * from technical where name@@"Test & Driven";

总结：

通过上面的几个特性的分析，不难发现 PostgreSQL 提供了强大的功能和函数，上手之前还是需要对其中的函数有个大致的了解。

参考

(1) PostgreSQL 中文网