PostgreSQL

PostgreSQL是一个开源并且功能强大，支持复杂查询和扩展的对象与关系型数据库。数据库的相关推荐开源查看 博客

安装

// linux 系统安装
sudo apt update
sudo apt install postgresql postgresql-contrib

// windows 本地安装直接下载 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
// 或者直接下载最新版本 https://get.enterprisedb.com/postgresql/postgresql-18.0-1-windows-x64.exe
// windows 在安装的时候要求设置密码

// 启动 psql -U postgres -d postgres 报：'psql' 不是内部或外部命令，也不是可运行的程序
或批处理文件。// 将 window 安装的 postgressql 的 bin 路径添加到系统的环境变量中去

简易使用

// 登录默认账户
sudo -i -u postgres
// 设置账户密码  window 不需要
psql
\password postgres
// 输入两次密码

// 设置远程登录
postgres=# SHOW config file;SHOW hba file;
config file
/etc/postgresqI/< 数据库版本号>/main/postgresql.conf(1 row)
hba file
/etc/postgresq1/< 数据库版本号>/main/pg_hba.conf(1 row)
// exit 退出 postgres 环境 修改上面文件内容
nano /etc/postgresqI/< 数据库版本号>/main/postgresql.conf
// 找到 listen_addresses ="localhost" 修改
listen_addresses ="*"
nano /etc/postgresq1/< 数据库版本号>/main/pg_hba.conf
// 找到 # IPv4 local host all connections: 
// 修改下面的 host all 127.0.0.1/32  scram-sha-256
host all 0.0.0.0/0  scram-sha-256
// 重启数据库
systemctl restart postgresql
// 防火墙开启 5432 端口提供外部访问

远程登录

window 安装的时候默认带了 pgAdmin 4 的可视化软件，若是多种数据库连接推荐使用 Dbeaver 可视化工具，支持多种数据库链接，下载连接

通过两个可视化工具可以看出，数据库里比 MySQL 的多包裹了一层结构

// window 本地 cmd 登录
psql -h localhost -U postgres -p 5432 -d postgres
localhost // 若是远程的可以使用 ip 地址，ipv6 没试过
-U postgres  // 表示登录的账户
-p 5432 // 表示使用到的端口号

// 登录后切换数据库
\c blog;  // 表示切换到 blog 数据库 相当于 MySQL 的 use blog;
\l; // 表示查看所有数据库信息
\l+;// 表示查看所有数据库详细信息
SHOW port; // 表示查看数据库当前端口号

// 创建数据库
CREATE DATABASE company_db 
    OWNER admin_user 
    ENCODING 'UTF8' 
    LC_COLLATE 'en_US.UTF-8' 
    LC_CTYPE 'en_US.UTF-8';

常用 SQL 操作

PostgreSQL 和 MySQL 的 SQL 除了在数据类型的差异语句基本一致，PostgreSQL 支持更多的数据类型超百种

// 支持 ip 地址、mac 地址等网段类型
CREATE TABLE net_work (
    id SERIAL PRIMARY key,
    segment CIDR  NOT NULL, // CIDR  ip 网段类型，添加非网段类型报错
    description TEXT
);
INSERT INTO net_work (segment, description) values ('192.168.1.0/24','本地局域网');
// >> 表示包含关系
select * FRoM net_work WHERE segment >> '192.168.1.128/26'::CIDR;

// 创建复合数据格式
-- 定义类型
CREATE type employee As(
    name VARCHAR,
    age INT,
    skillS TEXT[]
);
CREATE table employees (
    id SERIAL PRIMARY KEY,
    employee employee
)
INSERT into employees (employee) values (ROW('Tech Shrimp',30,ARRAY['Excel','English'])::employee
);

// inherits (表名) 表示继承表的数据格式
CREATE TABLE developers (programming_language TEXT[]
)inherits (employees);

INSERT into developers (employee, programming_language) values (ROW('Rick',30,ARRAY['Excel','combat Magi'])::employee,
    ARRAY['Python','Java']
);

PostgreSQL

PostgreSQL 的索引都是二级索引，数据不存储到树里面而是存储到一个叫堆表的单独空间中。索引的指针都是指向堆表的指针，主键索引和二级索引与 MySQL 的基本一致，索引和数据是分离的，插入数据的时候就是插入到堆表末尾，避免了随机写入带来数据页的分裂。

倒排索引

PostgreSQL 为  GIN  索引实现了倒排索引的结构（也称 GIN  索引），解决的问题是： 如何高效地在复合数据类型（数组、JSON 文档）中查找包含特定元素或键值的记录。

-- 创建一个包含标签的表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

-- 创建 GIN 索引来加速数组查询
CREATE INDEX idx_articles_tags_gin ON articles USING GIN (tags);

-- 查询包含特定标签的文章
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'index'];

-- 创建一个存储用户配置的表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

-- 创建 GIN 索引来加速 JSONB 查询
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);

-- 查询包含特定键值的记录
SELECT * FROM users WHERE profile @> '{"country":"China"}';
-- 查询某个键是否存在
SELECT * FROM users WHERE profile ? 'email';

使用场景

• 标签系统：如上例，文章、商品打标。
• 半结构化数据：如用户属性、设备元数据、动态表单数据，这些数据存储在  JSONB  字段中，查询模式不固定。
• 全文搜索：tsvector  类型也使用  GIN  索引，这是实现全文检索的基础。

• 优点：查询速度快，支持复杂的包含、存在性查询。
• 缺点 ：索引体积通常比 B-Tree 大； 更新、插入、删除操作相对较慢，因为需要更新多个倒排条目。不适合写多读少的场景。

GIST 索引

GiST  是“通用搜索树”的缩写。它不是一种单一的索引，而是一个 索引框架，允许实现多种不同的搜索策略。

GiST  的核心思想是将数据空间划分为重叠的区域。它非常适合索引无法被简单排序的数据，比如几何图形、范围、文本（全文搜索的另一种实现）和树形结构。

可以把  GiST  理解为一本书的 章节目录 ，而  B-Tree  是书后的 精确术语索引。

• B-Tree  告诉你术语的确切位置。
• GiST  告诉你“关于数据库的章节大概在 50-70 页”，你需要翻到那个范围再仔细找。

// 几何数据类型
-- 存储地理坐标点
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    coord GEOMETRY(Point, 4326)
);

-- 创建 GiST 索引
CREATE INDEX idx_locations_coord_gist ON locations USING GiST (coord);

-- 查找某个点附近的所有地点（按距离排序）SELECT *, ST_Distance(coord, ST_SetSRID(ST_MakePoint(116.39, 39.9), 4326)) as distance
FROM locations
ORDER BY coord <-> ST_SetSRID(ST_MakePoint(116.39, 39.9), 4326)
LIMIT 10;
// 范围类型
-- 存储会议室预定时间
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT,
    period TSRANGE -- 时间范围类型
);

-- 创建 GiST 索引
CREATE INDEX idx_bookings_period_gist ON bookings USING GiST (period);

-- 查找与特定时间范围有重叠的预定（避免冲突）SELECT * FROM bookings WHERE period && '[2023-10-27 14:00, 2023-10-27 16:00)'::tsrange;

非常适合几何图形、地理信息、网络 IP 信息、时间范围、距离远近查询等场景

• 优点：极其通用，支持自定义数据类型和操作符。对于特定查询（如  KNN），性能卓越。
• 缺点 ： 精度是“有损”的。它先过滤出一个可能的结果集（候选集），然后需要回表进行精确检查。这意味着其性能不如  B-Tree  在等值查询上那样绝对。

部分索引

部分索引不是一种新的数据结构，而是  B-Tree、GiST、GIN 等索引的“修饰器”。它只对表中满足特定条件的一部分行建立索引。

用空间和维护成本换性能。通过只索引你真正关心的数据子集，来达到大幅减小索引体积、提升查询性能（因为索引树更小，遍历更快）、减少索引维护开销

// 索引“热”数据
-- 一个订单表，大部分订单是已完成状态，我们只关心查询未完成的订单
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,
    status TEXT, -- 'pending', 'shipped', 'completed', 'cancelled'
    created_at TIMESTAMP
);

-- 只为未完成的订单创建索引
CREATE INDEX idx_orders_pending ON orders (user_id, created_at) WHERE status NOT IN ('completed', 'cancelled');

-- 这个查询会非常高效地使用部分索引
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

// 索引非空值
-- 一个可选的手机号字段，很多记录是 NULL，但我们只查询有手机号的记录
CREATE INDEX idx_users_mobile_on_not_null ON users (mobile) WHERE mobile IS NOT NULL;

// 实现软删除的高效查询
-- 使用软删除的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 只为未删除的产品创建索引
CREATE INDEX idx_products_active_name ON products (name) WHERE NOT is_deleted;

-- 查询活跃产品时，这个索引会生效
SELECT * FROM products WHERE name LIKE 'Apple%' AND NOT is_deleted;

使用场景

• 查询具有局部性：你的应用绝大部分查询只针对数据的一个子集（如未完成订单、活跃用户、最新数据）
• 处理稀疏数据：某个字段大部分值是 NULL 或一个默认值，你只关心非默认值
• 实现高效约束：可以创建  UNIQUE  部分索引来实现条件唯一约束

• 优点：精准打击，对特定查询模式性能提升巨大，节省存储和 I/O
• 缺点：索引只对匹配  WHERE  条件的查询有效。如果查询条件发生变化，可能需要创建多个部分索引

表达式索引

表达式索引允许你 对一个函数或表达式的计算结果建立索引，而不是直接对列本身。

索引的键不再是原始的列值，而是你指定的表达式计算后的值。当查询条件使用了相同的表达式时，PostgreSQL 就可以使用这个索引来快速定位，而无需为每一行计算表达式

// 大小写不敏感查询
-- 我们经常按用户名查询，但希望不区分大小写
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT,
    email TEXT
);

-- 对用户名的小写形式创建索引
CREATE INDEX idx_users_username_lower ON users (LOWER(username));

-- 现在这个查询可以使用索引
SELECT * FROM users WHERE LOWER(username) = LOWER('JohnDoe');

// 日期处理
-- 我们经常按日期（不关心时间）查询订单
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));

-- 查询某天的所有订单
SELECT * FROM orders WHERE DATE(created_at) = '2023-10-27';

// 从 JSONB 中提取字段
-- 为 JSONB 中的常用查询字段创建表达式索引
CREATE INDEX idx_users_country ON users ((profile ->> 'country'));

-- 查询可以使用索引
SELECT * FROM users WHERE profile ->> 'country' = 'China';

使用场景

• 规范化查询输入：如上例，将输入统一为小写、去除空格等，使查询可以走索引
• 基于日期的报告：如果你经常按年、月、日进行聚合查询，可以创建相应的表达式索引
• JSONB 字段的快速访问：将  JSONB  中需要高效查询的字段通过表达式索引“物化”出来

• 优点：将计算开销从查询时转移到插入 / 更新时，极大提升查询性能。实现了对函数调用的索引支持
• 缺点 ：索引维护成本高，每次插入 / 更新都需要计算表达式。索引 只对完全匹配的表达式有效。例如，索引  LOWER(username)  对  WHERE username = 'foo'  是无效的

数据一致性

• 支持可延迟约束：比如在用户表中执行姓名张三的改为李四，但是表中没有张三的，可以在后面添加事务创建姓名为张三的人，然后同时提交事务是被允许的
• 支持事务回滚：与 MySQL 的事务回滚不同，MySQL 是在事务失败的时候回滚，postgreSql 支持事务后回滚，例如创建了一张表，可以在创建表后回滚将表删除。

定时任务

使用 `SELECT cron.schedule` 数据库的定时任务功能进行定时任务的操作，使用 cron 时间表达式定义时间，然后执行操作数据库 sql 语句

安装插件

sudo apt-get -y install postgresql-< 数据库版本号 >-cron
// 修改配置文件
nano /etc/postgresql/< 数据库版本号 >/main/postgresql.conf
# Add settings for extensions here
shared_preload_libraries ='pg'
croncron.database_name ='postgres'

nano /etc/postgresq1/< 数据库版本号 >/main/pg_hba.conf
// 在  IPv4 前添加一行，trust 表示本地连接的时候不要密码验证
host all al1 127.0.0.1/32 trust
# IPv4 local connections:

// 重启数据库 
systemctl restart postgresql

// postgresql 中执行
CREATE EXTENSION pg_cron;
SELECT cron.schedule(
    'archive documents', // 定时任务名称
    '0 3 * * *', // 执行时间
    $$
    INSERT INTo documents archive SELECT * FROM documents;
    DELETE FRoM documents;
    $$
);

轻量缓存数据库

替代 redis 作为高速数据库（当然不能完全替代，毕竟 redis 的内存读取和操作的高效数据库）

-- UNLOGGED: 不写入 WAL 日志，写速度提高 5 倍，代价是断电或者数据库表里崩溃，数据库就全部丢失
CREATE UNLOGGED TABLE cache_data(
    id SERIAL PRIMARY KEY,
    content TEXT,
    expires time TIMESTAMp not null
);
// 结合定时任务可以配置比较高效的数据库
// 修改配置 设置内存大小
nano /etc/postgresql/< 数据库版本号 >/main/postgresql.conf
# -Memory-
shared buffers =128MB  # min 128kB

SELECT cron.schedule(
    'clean cache data',
    '* * * * * *', // 每分钟执行一次
    $$ DELETE FROM cache_data WHERE expires_time <= NOW();$$ 
    // 执行删除旧数据              
);

GitHub 插件https://github.com/dhamaniasad/awesome-postgres?tab=readme-ov-file 介绍了 postgresql 多种插件，可以按需安装使用

PostgreSQL 与 Mysql

特性	PostgreSQL	Mysql
数据类型	极其丰富，支持原生数组、JSON、JSONB、HSTORE（键值对）、几何图形、网络地址、XML 等	丰富，支持标准类型和 JSON
性能	读写混合操作、复杂查询  场景下表现卓越，尤其在高并发下	读密集型  操作非常快，简单查询性能极高
并发控制	多版本并发控制（MVCC），支持更高级的隔离级别，数据一致性更强	多版本并发控制（MVCC），在某些隔离级别下可能发生幻读
数据一致性	非常严格，支持强大的外键、约束、检查等	良好，但历史上对某些约束（如 CHECK 约束）支持较弱
扩展性	极强，支持用多种语言（如 C、Python、JavaScript 等）编写存储过程、函数和扩展	良好，主要通过存储过程和函数，但扩展生态不如 PostgreSQL
全文搜索	优秀，内置支持，功能强大，可配置性高，将内容字段转为向量数据提供索引查询（默认不支持中文分词检索的，需要安装插件）	良好，全表扫描，性能极低，基于第三方搜索引擎（如 Elasticsearch）的集成更常见
地理空间支持	通过  PostGIS  扩展提供 行业领先 的 GIS 功能	通过  MySQL Spatial  提供基本 GIS 功能
ACID 事务	完全支持	完全支持（InnoDB 存储引擎）
场景	– 应用涉及 复杂的数据模型和关系 。 – 使用 高级 SQL 功能 （如窗口函数、递归查询） – 处理 地理空间数据 （必须选 PostGIS） – 处理大量的 半结构化数据 （JSON 文档），并需要高性能查询 – 看重 数据的绝对一致性和完整性 – 希望数据库有极强的 可扩展性和定制能力 典型应用场景：地理信息系统（GIS）、金融系统、数据分析平台、科学研究、复杂的企业级应用。	– 标准的  Web 应用 ，主要是 读操作 ，数据结构相对简单。 – 开发速度、易用性和社区资源 – 需要一个 经过无数大规模互联网公司验证 的、成熟的、有大量运维经验的解决方案 – 使用那些与 MySQL 深度集成的流行 Web 框架（例如 Laravel、Ruby on Rails 等） – 团队对 MySQL 更熟悉。 典型应用场景：博客、内容管理系统（CMS）、电子商务网站、高流量的 Web 2.0 网站。
AI 知识库	使用 PGvector 等插件支持数据库的向量模型库	不支持