WAL-G PostgreSQL 物理备份
WAL-G 是 PostgreSQL 的物理备份工具支持增量备份和即时恢复。WAL 归档持续保护。压缩加密。本文配置 WAL-G 备份到 S3。
PostgreSQL 中 ORDER BY 使用 CASE 自定义排序
ORDER BY CASE status WHEN active THEN 1 WHEN pending THEN 2 END 按自定义顺序排序而不按字母或数字顺序。适合状态和优先级排序。
PostgreSQL 中的 COPY 命令快速导入导出
COPY table FROM file WITH CSV HEADER 从 CSV 文件快速导入比 INSERT 快几十倍。COPY table TO file 导出。适合大数据量的导入导出。
PostgreSQL 中的 pg_dump 和 pg_restore 备份恢复
pg_dump db > backup.sql 导出 SQL 格式备份。pg_restore -d db backup.dump 恢复。--format=custom 自定义格式支持并行恢复。
PostgreSQL 中的 SERIAL 和 IDENTITY 自增
SERIAL 是 PostgreSQL 传统的自增方式。IDENTITY 是 SQL 标准自增语法支持 GENERATED ALWAYS 和 BY DEFAULT。新项目推荐用 IDENTITY。
PostgreSQL 中的 EXPLAIN 输出解读
Seq Scan 全表扫描Index Scan 索引扫描。rows 预计行数actual time 实际耗时。代价从小到大选择最优执行计划。本文解读 EXPLAIN 输出各字段含义。
PostgreSQL 中 LATERAL JOIN 横向关联
LATERAL 允许子查询引用前面表的字段。FROM table1, LATERAL (SELECT * FROM table2 WHERE id = table1.id) 实现行关联的子查询。
PostgreSQL 中 PREPARE 和 EXECUTE 预编译语句
PREPARE plan_name AS sql 预编译 SQL 语句EXECUTE plan_name(values) 执行。减少 SQL 解析和优化耗时适合高频执行的相同查询。
PostgreSQL 中的 VIEW 视图和 MATERIALIZED VIEW
普通 VIEW 每次查询都重新执行底层 SQL。MATERIALIZED VIEW 将结果物化到磁盘需要 REFRESH 更新。适合计算量大的报表查询。
PostgreSQL 中 DISTINCT ON 的用法
DISTINCT ON (column) 对指定列去重保留 ORDER BY 排序的第一行。比 GROUP BY 更灵活不用聚合函数。本文用每个用户最新订单的例子展示。
PostgreSQL 中 UPSERT 的 ON CONFLICT
INSERT INTO ... ON CONFLICT (id) DO UPDATE SET ... 实现有则更新无则插入。DO NOTHING 跳过冲突行。本文展示 UPSERT 的完整语法。
PostgreSQL 中的 GENERATED 列自动计算
GENERATED ALWAYS AS (表达式) STORED 创建自动计算列值不需要手动更新。适合价格乘以数量等于总价这类场景。本文展示 GENERATED 列的创建和使用。
PostgreSQL 递归查询 WITH RECURSIVE
WITH RECURSIVE 实现递归查询可以处理树形和层级结构的数据。递归 CTE 包含一个基准查询和一个递归查询。本文用组织架构树展示递归查询的写法。
PostgreSQL 的 RETURNING 子句返回操作后的数据
PostgreSQL 的 INSERT/UPDATE/DELETE 后加 RETURNING * 直接返回操作后的行数据不需要再查一次 SELECT。本文展示 RETURNING 的多种实际场景。
PostgreSQL 中窗口函数 row_number rank dense_rank
窗口函数在不改变行数的情况下为每行计算排名。row_number 给唯一序号rank 并列跳过dense_rank 并列不跳过。本文用成绩排名示例对比三者的输出。
PostgreSQL jsonb 字段查询操作符
PostgreSQL 的 jsonb 类型用 -> 返回 JSON 对象->> 返回文本#> 按路径访问嵌套字段。了解这些操作符可以高效查询 JSON 数据。本文对比各操作符的返回值类型。
PostgreSQL 中时间区间查询的 BETWEEN 边界问题
WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31 不包括 1 月 31 日 23:59:59 之后的数据。用 >= AND < 下一天的写法可以避免边界漏数据。本文用实际数据对比两种写法的差异。
PostgreSQL NULL 不等于 NULL
PostgreSQL NULL 不等于 NULL是开发中经常遇到的问题。本文从实际场景出发分析原因并给出可操作的解决方案适合正在排查这个问题的开发者参考。
PostgreSQL 中的 EXPLAIN ANALYZE 执行了真的 SQL
EXPLAIN ANALYZE 会真实执行 SQL 语句不只是估算对 INSERT/UPDATE/DELETE 会真正修改数据。如果在生产库上执行可能会造成数据变更。只想看执行计划不加 ANALYZE。本文区分 EXPLAIN 和 EXPLAIN ANALYZE 的行为差异。
PostgreSQL 入门到精通
全面学习 PostgreSQL，包括复杂查询、窗口函数、JSON 支持、事务隔离与性能调优。
Supabase 开源 Firebase 替代方案
使用 Supabase 构建应用后端，结合 PostgreSQL、自动生成的 RESTful/GraphQL API 和实时订阅，实现安全的数据管理。