PostgreSQL Awesome

扩展插件功能

1. 数据功能 - 数据类型、操作类型、索引类型
2. 服务功能 - 集群、cron、存储引擎
3. 查询能力 - 图、流、GIS
4. 集成功能 - Redis、ES
5. 语言功能 - JS、Lua、Java

---

• neondatabase/neon
  • Apache-2.0, Rust
  • Serverless Postgres
• orioledb/orioledb
  • storage engine
  • slide
  • HN author akorotkov
• Vonng/pigsty
  • Apache-2.0
  • 国产
  • Battery-Included Distribution for PostgreSQL
• MaterializeInc/materialize
  • 实时增量固化查询
• Superbase
• PostgresApp/PostgresApp
  • macOS 应用
• PipelineDB - 停止
• DB to API
  • Postgrest
    • REST API for any Postgres database
  • pRest
    • Golang
    • GET
      • query
        • _page, _page_size, _select=FIELD, _count=FIELD_NAME, _count_first=true, _renderer=xml, _distinct=true, _order=-FIELD, _groupby=FIELD, _FIELD=VALUE
        • _join
      • aggregation - SUM, AVG, MAX, MIN, STDDEV, VARIANCE
        • sum:FIELD
      • op - $eq
        • gt,gte,lt,lte,ne,in,nin,null,notnull,true,nottrue,false,notfalse,like,ilike,nilike,ltreelanc, ltreerdesc,ltreematch,ltreematchtxt
  • supabase
  • Hasura

Extension

• TimescaleDB
• apache/incubator-age
  • Graph + Realtime
  • openCypher
  • 受 AgensGraph 启发
• arkhipov/temporal_tables
  • Temporal - 记录数据变化
  • 将数据修改归档到历史表
  • 暂不支持 PG13
• zombodb/zombodb
  • 集成 ES 搜索和分析能力
• ankane/pgvector
  • 向量搜索
• pgaudit/pgaudit
• supabase/supa_audit
  • Generic Table Auditing
  • 纯 SQL
  • Postgres Auditing in 150 lines of SQL
• wasmerio/wasmer-postgres
• pgq/pgq
  • Queue for PostgreSQL
• knizhnik/imcs
  • In-Memory Columnar Store
  • OLAP
• heterodb/pg-strom
  • accelerate mostly batch and analytics workloads with utilization of GPU and NVME-SSD
• tembo-io/pgmq
  • PostgreSQL, Rust
  • lightweight message queue

PL

• plv8/plv8
• pllua/pllua
• tada/pljava

参考

• Implementing Incremental View Maintenance for PostgreSQL
• HN
• Waiting for PostgreSQL 14 – Improvements for handling large number of connections
• Audit
  • https://eager.io/blog/audit-postgres/
  • How Postgres Audit Tables Saved Us From Taking Down Production

Internal

• https://postgrespro.com/community/books/internals
  • https://news.ycombinator.com/item?id=32088316
• http://www.interdb.jp/pg/index.html
• https://github.com/vishesh92/pg-primer

工具

• https://pgmodeler.io/
• ankane
  • ankane/pghero
  • ankane/dexter
    • automatic indexer
  • ankane/pgslice
• keithf4/pg_bloat_check
• WAL
  • wal-g/wal-g
    • Apache-2.0, Golang
    • vs wal-e
      • LZ4, LZMA, Brotli
      • 多核处理
    • Introducing WAL-G by Citus: Faster Disaster Recovery for Postgres
  • wal-e/wal-e
    • BSD-3, Python
    • 不再维护
• Web
  • https://github.com/commandprompt/pgmanage

Scale

• yandex/odyssey
• pgbouncer
• postgresml/pgcat
  • MIT, Rust
  • sharding, load balancing, failover
• pg-sharding/spqr
  • Stateless Postgres Query Router
• awslabs/pgbouncer-rr-patch
• agroal/pgagroal
  • connection pool
• Replication, Clustering, and Connection Pooling

Sync

• ankane/pgsync
  • Ruby
  • sync table
• toluaina/pgsync
  • to elasticsearch
• eulerto/wal2json
• jackc/pglogrepl
  • PostgreSQL logical replication library for Go

Scale Stories

• Herding elephants: Lessons learned from sharding Postgres at Notion
  • HN
  • The data model behind Notion's flexibility
• Database “sharding” came from UO?
• PostgreSQL at Scale: Database Schema Changes Without Downtime
• Why you don’t want to shard.
• Transaction ID Wraparound in Postgres
• Re-architecting Slack’s Workspace Preferences: How to Move to an EAV Model to Support Scalability
• PostgreSQL at Scale: Database Schema Changes Without Downtime
  • HN
• retool How we upgraded our 4TB Postgres database
  • HN
• The growing pains of database architecture
  • AWS 最大实例 r5.24xlarge
  • 96C 768GiB 25Gbps
  • 加了 PgBouncer
  • 水平扩容

FDW

• tembo-io/prometheus_fdw

FAQ

HA vs Horizontal Scale

• HA
  • 可以是 master-master 方式
  • 可以是 master-slave 方式
  • 但每个节点是完整数据
  • 强调可用
• HS
  • 每个节点数据不是完整的 - sharding
  • 目前只有 citus 提供这样的能力
  • 强调 partation
  • 增加节点