【 PG 入门系列 】PostgreSQL的对象管理（九）

PostgreSQL 入门系列博文讲解（B站同步介绍视频：https://space.bilibili.com/282421760）
支持一下，关注一波微信公众号：【 钥道不止 】

1. 字段定义

定义缺省值和自增列

# PostgreSQL提供三种序列伪类型SMALLSERIAL, SERIAL, BIGSERIAL
CREATE TABLE testtable (id SERIAL,txtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,name VARCHAR(64));
# 使用pg_get_serial_sequence('table_name','column_name')函数获得serial列的序列名称
select pg_get_serial_sequence('testtable','id');

1.1. 关于自增列

• MySQL直接一个AUTO_INCREMENT
• Oracle设置自增主键分三步走：建表设置主键（primary key）、建序列、建触发器
• PG直接一个SERIAL，serial列不会隐式在列上创建索引或是该列为主键列。可以使用PRIMARY KEY关键字增加相应约束

2. 索引

2.1. 索引的简介

• 索引是一种快速定位到数据行(tuple)的访问方法
• PG具有多种索引访问接口
• 索引是牺牲DML操作的性能来提高查询性能的
• 大批量数据装载之前可以考虑删除索引，完成装载后再重建索引

2.2. 重建索引

# 查看testtable表上索引定义
select * from pg_indexes where tablename = 'testtable';

# 方法一：会堵住写操作
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name
# 方法二（推荐）：并行创建一个相同定义的索引后再删除旧索引
CREATE INDEX CONCURRENTLY testtable_id_idx_new ON testtable(id);
DROP INDEX testtab1_id_idx1;

2.3. 索引访问接口

• B-Tree索引：最常用，适合等值、范围检索，可用于优化排序
• Hash索引：字段值很长的等值检索
• GIN索引：多值类型，如数组、JSON、多列的任意组合等
• RUM索引：多字段任意组合，多值查询、全文检索、相似查询
• GiST和SP-GiST索引：空间数据和范围类型
• Brin索引：大范围的连续查询
• Bloom索引：多列任意组合查询
• Zombodb索引：提供了ElasticSearch的接口
• 表达式索引：支持查询表达式
• 条件索引

# 当前支持的索引接口类型
\d pg_am
select * from pg_am;

3. 函数

#时间函数
select now();
select current_timestamp;
select clock_timestamp();

select current_date;
select current_time;
select localtime;

4. 表分区

表分区（Table Partitioning）是指在特定场景下，把逻辑上的一个大表分裂成多个更小的物理分片，以获得性能的提升。

• 数据量大的表可以考虑使用分区技术
• 分区表有助于在数据检索时减少扫描的数据量提高性能
• 支持范围分区、列表分区和哈希分区
• 可以使用pg_pathman插件加速和增强功能
  https://gitee.com/mirrors/pg_pathman/

4.1. range分区

# 创建范围分区表
CREATE TABLE ptab_order_range(
orderno bigint,name varchar(32),accno varchar(18),ordertime timestamp without time zone,city varchar(128))
PARTITION BY RANGE(ordertime);
# 给范围分区表创建分区（时间左闭右开）
CREATE TABLE ptab_order_p2022 PARTITION OF ptab_order_range 
FOR VALUES FROM (MINVALUE) TO ('2022-01-01 00:00:00');
CREATE TABLE ptab_order_p202201 PARTITION OF ptab_order_range 
FOR VALUES FROM ('2022-01-01 00:00:00') TO ('2022-02-01 00:00:00');
CREATE TABLE ptab_order_p202202 PARTITION OF ptab_order_range 
FOR VALUES FROM ('2022-02-01 00:00:00') TO ('2022-03-01 00:00:00');

4.2. list分区

# 创建列表分区表
CREATE TABLE ptab_order_list (
orderno bigint,name varchar(32),accno varchar(18),ordertime timestamp without time zone,city varchar(128))
PARTITION BY LIST (city);
# 列表分区表创建分区
CREATE TABLE ptab_order_beijing PARTITION OF ptab_order_list 
FOR VALUES IN ('Beijing');
CREATE TABLE ptab_order_guangzhou PARTITION OF ptab_order_list 
FOR VALUES IN ('Shanghai');
CREATE TABLE ptab_order_shanghai PARTITION OF ptab_order_list 
FOR VALUES IN ('Guangzhou');

4.3. hash分区

# 创建列表分区表
CREATE TABLE ptab_order_hash (
orderno bigint,name varchar(32),accno varchar(18),ordertime timestamp without time zone,city varchar(128))
PARTITION BY HASH (orderno);
# 给列表分区表创建分区
CREATE TABLE ptab_order_beijing PARTITION OF ptab_order_hash 
FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE ptab_order_guangzhou PARTITION OF ptab_order_hash 
FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE ptab_order_shanghai PARTITION OF ptab_order_hash 
FOR VALUES WITH (MODULUS 3, REMAINDER 2);