有用过 Postgresql 的 v 友吗? 比起其他 RDBMS 有什么好处?又有什么坑?

tabris17

2015-12-14 10:41:18 +08:00

我自己做的一些笔记，还没完成

1 、用户（ user ）和角色（ role ）的区别
用户就是不带 LOGIN 权限的角色。
CREATE ROLE pguser PASSWORD '123456' LOGIN
等价于
CREATE USER pguser PASSWORD '123456'

2 、架构 /模式（ schema ）和数据库（ database ）的关系
模式是数据库的逻辑空间。数据库的默认模式是 public 。可以为用户指定它的默认模式。
当访问数据库对象没有指定模式时， PostgreSQL 会按照 search_path 定义的模式搜索顺序来查找对象。
默认的 search_path 为 "$user", public 。"$user" 表示当前用户名称。也就是说，当访问一个未指定模式的对象时，会优先从与当前用户同名的模式下开始搜索。
通过模式可以实现访问隔离。
PS: pgAdmin 中的翻译似乎有问题， schema 同时被翻译成“架构”和“模式”。

3 、表空间（ table space ）
本质上就是一个文件系统的存储路径。
新建数据库的时候可以指定表空间，新建表和索引的时候也可以指定表空间。同一个数据库下的表和索引数据可以保存到不同的表空间下。

4 、域（ domain ）
可以理解为是一种包含约束条件的自定义类型。

5 、 json 和 jsonb
json 是以字符串格式保存的，读取时需要做解析。和 text 类型的区别是， json 类型在保存时会做格式合法性验证。而且 json 类型会保留输入时的缩进换行。
jsonb 类型以树状结构的二进制数据来保存，读取时不需要再次解析。 jsonb 的值会被映射为 PostgreSQL 的内置类型： string -> text, number -> numeric, boolean -> boolean 。映射的副作用是 jsonb 无法保存超出 numeric 取值范围的数，以及可能会丢失浮点数的精度。
另外， json 类型会保留重复的键，而 jsonb 类型遇到重复键时只保留最后一的那个值。
jsonb 支持新的运算符：@>（左侧集合包含右侧集合）、 <@（右侧集合包含左侧集合）、?（左侧集合是否存在右侧元素）、?|（左侧集合是否存在右侧数组中的任意元素）、?&（左侧集合是否存在右侧数组中的所有元素）。
jsonb 的默认 GIN 操作符类 jsonb_ops 支持为带有 @> 、 ? 、 ?& 、 ?| 操作符的查询创建索引；非默认的 GIN 操作符类 jsonb_path_ops 只支持为带有 @> 操作符的查询创建索引。对于相同的数据，建立 jsonb_path_ops 索引所需的空间比建立 jsonb_ops 索引所需的空间要少得多，并且 jsonb_path_ops 索引在处理某些特定的查询时拥有更好的性能，当查询包含的键频繁地在数据中出现时，更是如此。 jsonb_ops 和 jsonb_path_ops GIN 索引之间的技术区别在于，前者会为数据中的每个键和值都创建独立的索引项，而后者只会为数据中的每个值创建索引项。

6 、索引类型（ btree 、 hash 、 gin 、 gist 、 sp gist ）
btree 就是最常见的 B 树，不多说。适用于范围检索，字符串前缀查找以及排序。
hash 就是常见的哈希表，也不用多说。哈希索引无法覆盖索引优化。适用于数据离散好、重复率低的字段，只能用于等式检索。
gin 反向索引。用于数组、 jsonb 、全文检索等。
gist 用于二维几何数据类型。

7 、表达式索引
就是字面的意思。用表达式作为索引值。省去再建一个字段的麻烦了。

8 、表继承
当父表结构发生变化时，子表结构也会相应改变。当从父表中 SELECT 数据时，会返回子表中的数据。可以在表名前加 ONLY 关键字来限制只返回父表数据。
父表和子表共享 serial 字段。

9 、类型转换
SQL92 标准的类型转换： CAST(expr AS typename)
PostgreSQL 风格的类型转换：
typename expr
typename(expr)
expr::typename

10 、数组类型
array[1,2,3,4]
array['a','b','c','d']
CREATE TABLE arr(f1 int[], f2 int[]);

11 、行构造器
行构造器可以用于制作存储在复合类型字段中的复合类型值，或者是传递给一个接受复合类型参数的函数。
CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
select (row(1, 'asdsad', 123.41)::myrowtype).f2;

12 、序列类型
smallserial,serial 和 bigserial 类型不是真正的类型，只是为在表中创建唯一标识做的概念上的便利。类似其它一些数据库中的 AUTO_INCREMENT 属性。
CREATE TABLE tablename (
colname SERIAL
);
等价于声明下面几个语句：
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

glasslion

2015-12-14 14:47:30 +08:00

比 MySQL 多出来的特性很多，这里就只列关键字了，具体解释可以参考 PG 文档，或者这篇 blog: [Why Postgres]( http://www.craigkerstiens.com/2012/04/30/why-postgres/)

- Datatypes
- Conditional Indexes
- Transactional DDL
- Foreign Data Wrappers
- Concurrent Index Creation
- Extensions
- Common Table Expressions
- Fast Column Addition
- Listen/Notify
- Table Inheritance
- Per Transaction sync replication
- Window functions
- NoSQL inside SQL(array, hstore, jsonb)
- Materialized View
- Procedural Languages in Python/Javascript/Perl

此外建议观看下面几个视频：
[Postgres Performance for Humans]( <amp-youtube data-videoid="MpH8W5hce9I" layout="responsive" width="480" height="270"></amp-youtube>)

[PostgreSQL when it's not your job]( <amp-youtube data-videoid="3yhfW1BDOSQ" layout="responsive" width="480" height="270"></amp-youtube>)
[PostgreSQL Proficiency for Python People]( <amp-youtube data-videoid="0uCxLCmzaG4" layout="responsive" width="480" height="270"></amp-youtube>)

DBA 的话，可以再看看 High performance PostgreSQL 这本书