基本概念

关系（Relation）

TABLE

元组（Tuple）

RECORD、ROW

字段 Field

Column

表空间（Tablespace）

是一个存储位置，可以在其中保存底层数据库对象的实际数据，定义一个文件系统位置，代表数据库对象（表、索引等）的文件可以存储在该文件目录下

数据段（Segment）和数据页（Page）

每个表和索引都保存在一个单独的文件中，每个文件就是一个数据段（Segment）。在默认情况下，当一个表或索引的大小超过1GB时，它会被拆分出另一个数据段。第1个数据段以PostgreSQL内部定义的filenode命名，第2个数据段则命名为filenode.1，以此类推。一个数据段内部是以数据页的形式来组织的，数据页表示硬盘中的数据块，默认大小为8KB，最大为32KB，数据页的大小是在编译时确定的。行就存储在数据页中，由于每个数据页是等价的，因此一个特定的行可以存储在任意一个数据页中

存储结构

数据库族 -> 数据库 -> schema -> 表 -> 行 -> 字段

安装

安装脚本

管理命令

pg_ctl start -D /opt/postgresql/data 
pg_ctl stop -D /opt/postgresql/data -m smart/fast/immediate 
pg_ctl restart -D /opt/postgresql/data 
pg_ctl reload -D /opt/postgresql/data 
pg_ctl status -D /opt/postgresql/data

sql

sql分类

DDL

DDL 用于定义和管理数据库中的对象结构，比如创建、修改或删除数据库、表、索引等。这些操作会改变数据库的结构，而不是数据本身。

DML

DML 用于对数据库表中的数据进行增删改操作，不改变表的结构，只操作表内的记录。

DQL

DQL 用于从数据库表中查询数据，是使用最频繁的 SQL 语言，核心命令只有 SELECT。

控制台客户端

psql –help

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
      --csv                CSV (Comma-Separated Values) table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

常用命令

# 列举数据库
\l
# 选择数据库
\c databasename
# 查看该某个库中的所有表
\d
# 查看某个库中的某个表结构
\d tablename
# 显示字符集
\encoding
# 退出psgl
\q
# 显示帮助
\?

表空间

mkdir -p /opt/postgresql/tablespace/testspace && chown -R postgres:postgres  /opt/postgresql
CREATE TABLESPACE testspace OWNER postgres LOCATION '/opt/postgresql/tablespace/testspace';
\db;
create TABLE test(a int) tablespace testspace;
select oid,datname from pg_database where datname = 'postgres';
select relname,relfilenode from pg_class where relname='test';
drop TABLESPACE testspace;

创建数据库

# 获取帮助
\h create database
# 用法
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html
# 创建数据库
CREATE database dennis WITH ENCODING='utf8' TABLESPACE=testspace OWNER=postgres;

字段说明

• name：要创建的数据库的名称
• user_name：要创建的数据库所属的用户如果没有指定，则默认属于执行该命令的用户
• template：要创建的数据库所用的模板库，默认的模板库是template1
• encoding：要创建的数据库所使用的字符集。如果没有指定，则默认使用其模板库的字符集
• lc_collate：要创建的数据库所使用的collation顺序。这会影响在ORDER BY语法中字符串类型列的顺序，也会影响text类型列的索引顺序。如果没有指定，则默认使用其模板库的collation顺序
• lc_ctype：要创建的数据库所使用的字符分类。这会影响字符的分类，如大小写和数字。如果没有指定，则默认使用其模板库的字符分类
• tablespace_name：要创建的数据库所关联的表空间。默认使用模板库对应的表空间
• allowconn：是否可以连接该数据库，默认设置为true。如果设置为false，则任何用户都不能连接该数据库
• connlimit：允许并发连接该数据库的个数。默认设置为-1，即没有限制
• istemplate：是否是模板库，默认设置为false。如果设置为true，则任何具有创建数据库权限的用户均可以用其复制新的数据库；如果设置为false，则只有超级用户和该数据库的用户可以用其复制新的数据库

创建数据表

官方文档

drop TABLE IF EXISTS company;
CREATE TABLE IF NOT EXISTS company (
    id serial PRIMARY KEY,
    title CHAR(50) UNIQUE NOT NULL,
    subtitle varchar(500) UNIQUE NOT NULL,
    age INT NOT NULL DEFAULT 0,
    address TEXT,
    salary REAL,
    money decimal,
    status double precision,
    guid uuid
);
-- \d+ company
insert into company(name,address,age,salary) values('dennis','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis1','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis2','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis3','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis4','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis5','成都','28',3.14);
delete from company where age=28;

postgresql、mysql数据类型比较

json、jsonb数据类型

二者的区别在于json写入快，读取慢，jsonb写入慢

drop table if exists json_test;
CREATE TABLE json_test (
  id serial,
  board_id float NOT NULL,
  data jsonb
);
INSERT INTO json_test VALUES (1, 1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');
INSERT INTO json_test VALUES (2, 1, '{"name": "Wash dishes", "tags": ["Clean", "Kitchen"], "finished": false}');
INSERT INTO json_test VALUES (3, 1, '{"name": "Cook lunch", "tags": ["Cook", "Kitchen", "Tacos"], "ingredients": ["Tortillas", "Guacamole"], "finished": false}');
INSERT INTO json_test VALUES (4, 1, '{"name": "Vacuum", "tags": ["Clean", "Bedroom", "Office"], "finished": false}');
INSERT INTO json_test VALUES (5, 1, '{"name": "Hang paintings", "tags": ["Improvements", "Office"], "finished": false}');
SELECT data->>'name' AS name FROM json_test;
SELECT * FROM json_test WHERE data->>'finished' = 'true';

insert into json_test select * from json_test;

vacuum full json_test

->将以JSON对象的形式返回该属性，而->>将以整数或文本

数组类型

在存储一些数据时能更节省空间

drop table if exists array_test;
CREATE TABLE array_test (
id serial primary key,
data int[]
);
INSERT INTO array_test VALUES (1,'{3,5}');
select * from array_test;
update array_test set data[0]=3434 where id=1;
select * from array_test;
update array_test set data[7]=3434 where id=1;
select * from array_test;

postgresql还支持几何类型、uuid类型、xml类型、数组类型、复合类型、范围类型、布尔类型等

uuid采用16位存储,更节省空间

select * from pg_database;
select * from pg_class;
COPY json_test TO '/opt/postgresql/test.copy';
select * from json_test;
delete from json_test;
COPY json_test FROM '/opt/postgresql/test.copy';
COPY json_test(id, name) TO '/opt/postgresql/test.copy' DELIMITER ' ';

查询正在执行的sql

SELECT procpid,START,now() - START AS lap,current_query FROM(SELECT backendid,pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query FROM(SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' ORDER BY lap DESC;

配置相关接口

select name,setting from pg_settings where category='File Locations';
select name,setting from pg_settings;
alter system set log_statement='none';
SELECT pg_reload_conf();
show log_statement;

upsert

insert into test values (1,'test',now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time;

其他

• postgre 事务号32位，约49亿
• vacuum 不会锁表，只是单纯的回收空间以被重用，被回收的空间一般情况不会被返还给操作系统，仅仅被保留在同一个表中以备重用
• vacuum full会锁表，会将表的整个内容重写到一个新的磁盘文件中，并且不包含额外的空间，这使得没有被 使用的空间被还给操作系统

索引组织表和堆表

• 堆表：存储存储，插入速度块，查询速度慢(postgresql)，索引的二级索引为堆表上面的绝对位置(页号，页内偏移)，所以不一定要有主键，索引一般用B树
• 索引组织表：有序存储，插入速度慢，查询速度快，写入时需要在特定的位置写入（mysql innodb），索引的二级索引为主键，所以是需要有主键的，索引一般用B+树

cpu 选取方法

最好的方法是通过类似top工具对当前数据库所在服务器进行监测。如果每个CPU运行的进程很少，则速度更快的CPU方案会更加适合这种类型的工作负载,单个查询一般只能使用一个核心（9.6引入了并行查询机制）

内存选取方式

• 数据量相对于系统RAM来说非常小，那么加大内存也不会提升性能。此时，用户需要使用速度更快的处理器
• 表的数据量远远大于可以为数据库分配的内存时，如数据仓库系统，这时用户需要选取更快的硬盘，而不是增加内存
• 用户频繁访问的数据量相对于内存较大时，加大内存往往能起到显著效果，一般尽可能把热数据放在内存中

pg_buffercache

-- 搜索contrib
yum search contrib
-- 安装 contrib
yum install postgresql12-contrib.x86_64
-- 查看已安装扩展 \dx
-- 查看pg_buffercache插件是否存在
select * from pg_available_extensions where name='pg_buffercache';
-- 安装插件
create extension pg_buffercache;
-- 查询
select * from pg_buffercache;
-- 查看buffer配置
SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';
-- 查看当前buffer使用情况
select d.datname, c.relname, c.relkind, count(*) as buffers from pg_class c inner join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) where 1=1 group by d.datname, c.relname, c.relkind order by d.datname,4 desc ;
-- 删除buffer
drop extension pg_buffercache;

wal和lsm

PostgreSQL实现MVCC方式

PostgreSQL如何实现MVCC (基于xmin、xmax、cmin、cmax)

常用运维sql

-- 查看数据库
select * from pg_database;
-- 查看表空间
select * from pg_tablespace;
-- 查看用户
select * from pg_user;
select * from pg_shadow;
select * from pg_roles;
-- 查看表
select * from pg_tables;
-- 查看索引
select * from pg_index;
-- 查看锁
select * from pg_locks;
-- 查看表页数,表存储空间大小为 8k*relpages
select relname,relpages,reltuples,relallvisible,reltuples/relpages as avg_record_per_page from pg_class where relpages>0 order by relpages desc;
-- 查看会话进程
select usename,application_name,client_addr,query_start,query,backend_type from pg_stat_activity;
-- 查看表大小
select pg_relation_size('json_test')/1024 as table_size;
-- 查看数据库统计信息
select * from pg_stat_database;
-- 查看表io信息 
select * from pg_statio_user_tables;
-- 查看报表统计  vacuum
select * from pg_stat_all_tables;
-- 查看索引使用情况
select * from pg_stat_all_indexes;
select * from pg_statio_user_indexes;

pg_statio_user_indexes、pg_statio_all_indexes

返回字段含义

• idx_blks_read:从磁盘中读取索引的次数
• idx_blks_hit：从内存中读取索引次数

select * from pg_statio_user_indexes;
-- 查询索引内存命中率
select relname,relname,idx_blks_read,idx_blks_hit,idx_blks_hit*100/(idx_blks_read+idx_blks_hit) as idx_hit_rate from pg_statio_user_indexes;

pg_statio_user_tables、pg_statio_all_tables

heap_blks_read：读取的磁盘块数 heap_blks_hit：缓冲区命中数 idx_blks_read：所有索引读取的磁盘块数 idx_blks_hit：所有索引缓冲区命中数 toast_blks_read：TOAST表读取的磁盘块数（如果存在） toast_blks_hit：TOAST表命中缓冲区数（如果存在） tidx_blks_read：TOAST表索引读取的磁盘块数（如果存在） tidx_blks_hit：TOAST表索引命中缓冲区数（如果存在）

select * from pg_statio_user_tables;
-- 查询索引内存命中率
select heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,heap_blks_hit*100/(heap_blks_hit+heap_blks_read) as heap_hit_rate,idx_blks_hit*100/(idx_blks_read+idx_blks_hit) as idx_hit_rate from pg_statio_user_tables;

pg_stat_user_tables、pg_stat_all_tables

seq_scan：顺序扫描次数（全表扫描） seq_tup_read：顺序扫描抓取的有live数据行的数目 idx_scan:索引扫描的次数
idx_tup_fetch:索引扫描抓取的有live数据行的数目 n_tup_ins:插入的行数
n_tup_upd:更新的行数
n_tup_del:删除的行数 n_tup_hot_upd:HOT更新的行数（即不需要单独的索引更新
n_live_tup:live行估计数 n_dead_tup:dead行估计数 last_vacuum:最后一次手动vacuum时间（不计算VACUUM FULL） last_autovacuum:最后一次autovacuum时间 last_analyze:最后一次analyze时间 last_autoanalyze:最后一次autoanalyze时间 vacuum_count:vacuum的次数（不计算VACUUM FULL） autovacuum_count:autovacuum的次数 analyze_count:analyze的次数 autoanalyze_count:autoanalyze的次数

select * from pg_stat_user_tables;
-- 查看全表扫描与索引扫描，seq_scan最好能为0
select relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch from pg_stat_user_tables;
-- 查看操作行数
select relname,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup from pg_stat_user_tables;
-- 查看vacuum和analyze
select relname,last_vacuum,last_autovacuum,vacuum_count,autovacuum_count,last_analyze,last_autoanalyze,analyze_count,autoanalyze_count from pg_stat_user_tables;