# PostgreSQL Field Guide

- [PostgreSQL 教程与生产实践指南](/docs): 面向初学者、工程团队和 AI Agent 的 PostgreSQL 18 双语教程、运维与安全实践

- **PostgreSQL 19 专题**
- [PostgreSQL 19：新功能、发布时间与 18 升级 19 指南](/docs/postgresql-19): PostgreSQL 19 Beta 2 新功能、发布时间与 PostgreSQL 18 升级 19 清单，覆盖兼容性、pg_upgrade、扩展、回滚与生产风险

- **新人路径**
- [从这里开始](/docs/start-here): 新人用 90 分钟建立 PostgreSQL 的正确心智模型
- [5 分钟快速开始](/docs/quickstart): 用 Docker 启动 PostgreSQL 18，连接、建表并验证持久化

- **安装与连接**
- 安装与连接
  - [PostgreSQL 安装与连接](/docs/setup): 按 Ubuntu、macOS、Windows 或容器选择可验证的 PostgreSQL 18 安装路径
  - [PostgreSQL Linux 软件包、版本与 PGDG 仓库](/docs/setup/linux-packages): 按 Linux 发行版核对 PostgreSQL 18、PostgreSQL 19、客户端与扩展软件包，理解默认仓库、PGDG、版本号和安全回溯
  - [Ubuntu 安装 PostgreSQL 18](/docs/setup/ubuntu): 使用 Ubuntu 自带包或 PostgreSQL 官方 Apt 仓库安装并验证 PostgreSQL
  - [macOS 安装 PostgreSQL 18](/docs/setup/macos): 比较 Homebrew、Postgres.app 和图形安装器并验证本机 PostgreSQL 服务
  - [Windows 安装 PostgreSQL 18](/docs/setup/windows): 使用 PostgreSQL 官方推荐的 Windows 安装入口配置服务、端口和 psql
  - [psql 连接 PostgreSQL 与 SSL 配置](/docs/setup/psql-connection): 正确使用连接 URI、环境变量、pgpass、超时和 TLS 验证连接 PostgreSQL
- 核心知识
  - [数据建模与约束](/docs/core/data-modeling): 用类型、键和约束表达业务事实，让错误数据无法落库
  - [查询工具箱](/docs/core/queries): 从过滤与 JOIN 到 CTE、窗口函数和安全参数化
  - [事务、MVCC 与并发](/docs/core/transactions): 理解 PostgreSQL 的快照、隔离级别、行锁和整事务重试
  - [PostgreSQL MVCC 与快照可见性](/docs/core/mvcc-snapshots): 理解行版本、语句快照、长事务和 vacuum 之间的关系
  - [PostgreSQL 锁等待与死锁排查](/docs/core/locks-deadlocks): 识别阻塞链、避免死锁并正确处理 lock timeout 和 SQLSTATE 40P01
  - [索引与 EXPLAIN](/docs/core/indexes-explain): 用执行计划、真实耗时与缓冲区访问验证索引是否有效
  - [JSONB、全文与语义检索](/docs/core/jsonb-search): 选择关系列、JSONB、全文检索与 pgvector 的边界

- **AI / Agent 参考**
- AI / Agent 参考
  - [AI / Agent 文档入口](/docs/ai): 让模型在明确 schema、权限、成本和失败语义下使用 PostgreSQL
  - [上下文契约](/docs/ai/context-contract): 用一份小而稳定的契约替代把整个数据库定义塞进 prompt
  - [安全 SQL 护栏](/docs/ai/safe-sql): 只读优先、最小权限、超时、行数限制与写操作审批
  - [Text-to-SQL 生产模式](/docs/ai/text-to-sql): 把自然语言查询变成受约束、可解释、可拒绝的数据库操作
  - [Schema 检索与文档生成](/docs/ai/schema-retrieval): 从 PostgreSQL 系统目录生成紧凑、可追踪的模型上下文
  - [PostgreSQL RAG 管道](/docs/ai/rag-pipeline): 用关系过滤、全文与 pgvector 组合可审计的混合检索
  - [PostgreSQL 安装 pgvector](/docs/ai/pgvector-setup): 在 Docker、Ubuntu 和云 PostgreSQL 中安装、启用并验证 pgvector 扩展
  - [pgvector 生产最佳实践](/docs/ai/vector-production): 用 exact 基线、过滤后召回和可重建索引管理向量检索
  - [数据库 Agent 评估](/docs/ai/agent-evals): 用结果断言、安全断言和计划回归替代“看起来能用”

- **云 PostgreSQL**
- 云 PostgreSQL
  - [云 PostgreSQL 入口](/docs/cloud): 先按兼容边界和责任模型分类，再比较价格与功能
  - [免费 PostgreSQL 云数据库选型](/docs/cloud/free-postgresql): 对比 Supabase、Neon、Aiven、Nhost、Prisma Postgres、Koyeb 与 Render 的免费 PostgreSQL 额度、暂停、备份和生产限制
  - [云 PostgreSQL 服务版图](/docs/cloud/service-map): 用官方能力边界理解主流托管与开发者平台
  - [云 PG 生产选型清单](/docs/cloud/production-checklist): 用可验证问题和演练替代功能表打勾

- **生产运维**
- 生产运维
  - [生产运维入口](/docs/operations): 从目标、可恢复性和可观测性出发管理 PostgreSQL
  - [PostgreSQL 生产工具栈与高可用](/docs/operations/production-stack): 从连接池、备份恢复、监控和故障域出发设计 PostgreSQL 生产架构，并判断 PgBouncer、pgBackRest、Patroni、CloudNativePG 与 Pigsty 何时值得采用
  - [PostgreSQL 监控：SQL、指标与日志](/docs/operations/monitoring-logging): 建立 PostgreSQL SQL、指标和日志三层可观测性，覆盖 pg_stat_statements、JSON 日志、postgres_exporter、Prometheus、Grafana 与 pgBadger
  - [PostgreSQL 安全迁移与零停机 Schema 变更](/docs/operations/safe-migrations): 使用 expand-and-contract、lock_timeout、Squawk、Testcontainers 和 pgTAP 降低 PostgreSQL DDL 锁表、回填与跨版本升级风险
  - [PostgreSQL autovacuum 与表膨胀](/docs/operations/autovacuum-bloat): 监控 dead tuples、冻结风险和 vacuum 进度并安全调整高写入表
  - [备份、恢复与 PITR](/docs/operations/backup-recovery): 按恢复目标选择逻辑或物理备份，并用演练证明可恢复
  - [安全基线](/docs/operations/security): 用网络、认证、角色、对象权限和行级策略形成多层边界
  - [复制、故障切换与升级](/docs/operations/replication-upgrades): 区分物理和逻辑复制，用可回退清单完成版本切换

- **现场速查**
- 现场速查
  - [PostgreSQL 现场速查](/docs/reference): 连接、psql、对象、会话、锁与容量的高频命令
  - [PostgreSQL 索引与存储访问方法](/docs/reference/index-access-methods): 解释 PostgreSQL heap table access method 与 B-tree、Hash、GIN、GiST、SP-GiST、BRIN、Bloom、HNSW 和 IVFFlat 的选择边界
  - [PostgreSQL 扩展与开源生态选型指南](/docs/reference/extensions-ecosystem): 按向量、GIS、时间序列、搜索、分析、维护与脱敏场景选择 PostgreSQL 扩展，并控制版本升级、许可证、备份和云兼容风险
  - [PostgreSQL 血缘、分支与兼容数据库](/docs/reference/postgresql-compatible-databases): 区分 Supabase、Neon、YugabyteDB、CockroachDB、Cloudberry、IvorySQL、Materialize、Gel 与 FerretDB 的 PostgreSQL 血缘和兼容边界
  - [SQLSTATE 错误速查](/docs/reference/errors): 用稳定的五字符状态码诊断约束、事务、权限、资源和连接问题
  - [PostgreSQL 连接错误排查](/docs/reference/connection-errors): 按 DNS、网络、TLS、认证、database 和连接数顺序诊断常见连接失败
  - [版本与支持策略](/docs/reference/version-policy): PostgreSQL major/minor 语义、支持周期与 2026 年版本选择
  - [编辑、事实核对与更正政策](/docs/reference/editorial-policy): 说明本站如何选择来源、验证 SQL、标记版本和维护 AI 辅助内容

# PostgreSQL Field Guide

- [PostgreSQL tutorial and production guide](/en/docs): A PostgreSQL 18 tutorial and operations reference for learners, engineering teams, and AI agents

- **PostgreSQL 19 topic**
- [PostgreSQL 19 release and upgrade guide](/en/docs/postgresql-19): PostgreSQL 19 Beta 2 features, release timing, and an 18-to-19 upgrade checklist for pg_upgrade, extensions, compatibility, rollback, and production

- **Learning path**
- [Start here](/en/docs/start-here): Build the right PostgreSQL mental model in 90 minutes
- [5-minute quickstart](/en/docs/quickstart): Run PostgreSQL 18 with Docker, connect, create a table, and verify persistence

- **Install and connect**
- Install and connect
  - [Install and connect to PostgreSQL](/en/docs/setup): Choose a verifiable PostgreSQL 18 setup for Ubuntu, macOS, Windows, or containers
  - [PostgreSQL Linux packages, versions, and PGDG](/en/docs/setup/linux-packages): Verify PostgreSQL 18, PostgreSQL 19, client, and extension packages by Linux distribution, with repository, version, and backport boundaries
  - [Install PostgreSQL 18 on Ubuntu](/en/docs/setup/ubuntu): Install and verify PostgreSQL from Ubuntu packages or the official PostgreSQL Apt repository
  - [Install PostgreSQL 18 on macOS](/en/docs/setup/macos): Compare Homebrew, Postgres.app, and the graphical installer, then verify the local server
  - [Install PostgreSQL 18 on Windows](/en/docs/setup/windows): Configure the service, port, and psql with the Windows installer linked by PostgreSQL
  - [Connect to PostgreSQL with psql and SSL](/en/docs/setup/psql-connection): Use connection URIs, environment variables, pgpass, timeouts, and TLS verification correctly
- Core knowledge
  - [Data modeling and constraints](/en/docs/core/data-modeling): Express business facts with types, keys, and constraints so invalid data cannot land
  - [Query toolbox](/en/docs/core/queries): Filters, joins, CTEs, windows, pagination, and safe parameters
  - [Transactions, MVCC, and concurrency](/en/docs/core/transactions): PostgreSQL snapshots, isolation levels, row locks, and whole-transaction retries
  - [PostgreSQL MVCC and snapshot visibility](/en/docs/core/mvcc-snapshots): Understand row versions, statement snapshots, long transactions, and vacuum interaction
  - [Diagnose PostgreSQL lock waits and deadlocks](/en/docs/core/locks-deadlocks): Trace blockers, prevent deadlocks, and handle lock timeout and SQLSTATE 40P01 correctly
  - [Indexes and EXPLAIN](/en/docs/core/indexes-explain): Verify index value with plans, actual timing, and buffer access
  - [JSONB, full-text, and semantic retrieval](/en/docs/core/jsonb-search): Boundaries between relational columns, JSONB, built-in search, and pgvector

- **AI / Agent reference**
- AI / Agent reference
  - [AI / agent reference](/en/docs/ai): Let models use PostgreSQL under explicit schema, privilege, cost, and failure contracts
  - [Context contract](/en/docs/ai/context-contract): Replace a full-schema prompt dump with a small, stable, task-scoped contract
  - [Safe SQL guardrails](/en/docs/ai/safe-sql): Read-only defaults, least privilege, timeouts, row bounds, and write approval
  - [Text-to-SQL production pattern](/en/docs/ai/text-to-sql): Turn natural-language questions into bounded, explainable, and rejectable database operations
  - [Schema retrieval and documentation](/en/docs/ai/schema-retrieval): Generate compact, traceable model context from PostgreSQL catalogs
  - [PostgreSQL RAG pipeline](/en/docs/ai/rag-pipeline): Combine relational filters, full text, and pgvector into auditable hybrid retrieval
  - [Install pgvector for PostgreSQL](/en/docs/ai/pgvector-setup): Install, enable, and verify pgvector with Docker, Ubuntu, or managed cloud PostgreSQL
  - [pgvector production practices](/en/docs/ai/vector-production): Operate vector retrieval with exact baselines, filtered recall, and rebuildable indexes
  - [Database agent evaluation](/en/docs/ai/agent-evals): Replace “looks useful” with result, safety, and plan regression assertions

- **Cloud PostgreSQL**
- Cloud PostgreSQL
  - [Cloud PostgreSQL entry point](/en/docs/cloud): Classify compatibility and ownership before comparing price and features
  - [Free PostgreSQL cloud database guide](/en/docs/cloud/free-postgresql): Compare free PostgreSQL limits, sleep, backup, and production boundaries across Supabase, Neon, Aiven, Nhost, Prisma Postgres, Koyeb, and Render
  - [Cloud PostgreSQL service map](/en/docs/cloud/service-map): Understand mainstream managed and developer platforms through verified capability boundaries
  - [Cloud PG production checklist](/en/docs/cloud/production-checklist): Replace feature checkboxes with verifiable questions and drills

- **Production operations**
- Production operations
  - [Production operations](/en/docs/operations): Operate PostgreSQL from explicit objectives, recoverability, and observability
  - [PostgreSQL production stack and HA](/en/docs/operations/production-stack): Choose PgBouncer, pgBackRest, Patroni, CloudNativePG, and Pigsty from recovery, pooling, observability, and failure-domain requirements
  - [PostgreSQL monitoring and logs](/en/docs/operations/monitoring-logging): Build query, metric, and log observability for PostgreSQL with pg_stat_statements, JSON logs, postgres_exporter, Prometheus, Grafana, and pgBadger
  - [Safe PostgreSQL schema migrations](/en/docs/operations/safe-migrations): Reduce PostgreSQL DDL lock, backfill, and major-version risk with expand-and-contract, lock_timeout, Squawk, Testcontainers, and pgTAP
  - [PostgreSQL autovacuum and table bloat](/en/docs/operations/autovacuum-bloat): Monitor dead tuples, freeze risk, and vacuum progress, then tune high-write tables safely
  - [Backup, recovery, and PITR](/en/docs/operations/backup-recovery): Choose logical or physical backups from recovery objectives and prove them with drills
  - [Security baseline](/en/docs/operations/security): Layer network, authentication, roles, object privileges, and row policies
  - [Replication, failover, and upgrades](/en/docs/operations/replication-upgrades): Distinguish physical and logical replication and cut over with a reversible checklist

- **Field reference**
- Field reference
  - [PostgreSQL field reference](/en/docs/reference): Frequent connection, psql, object, session, lock, and capacity commands
  - [PostgreSQL index access methods](/en/docs/reference/index-access-methods): Choose among heap tables, B-tree, Hash, GIN, GiST, SP-GiST, BRIN, Bloom, HNSW, and IVFFlat with clear operator and extension boundaries
  - [PostgreSQL extension selection guide](/en/docs/reference/extensions-ecosystem): Choose PostgreSQL extensions for vector, GIS, time series, search, analytics, maintenance, and masking with explicit upgrade and cloud boundaries
  - [PostgreSQL compatible database guide](/en/docs/reference/postgresql-compatible-databases): Classify Supabase, Neon, YugabyteDB, CockroachDB, Cloudberry, IvorySQL, Materialize, Gel, and FerretDB by PostgreSQL lineage and compatibility
  - [SQLSTATE error fieldbook](/en/docs/reference/errors): Diagnose constraints, transactions, privilege, resources, and connections with stable five-character codes
  - [Troubleshoot PostgreSQL connection errors](/en/docs/reference/connection-errors): Diagnose common failures in DNS, network, TLS, authentication, database, and connection capacity order
  - [Version and support policy](/en/docs/reference/version-policy): PostgreSQL major/minor semantics, lifecycle, and choosing a version in 2026
  - [Editorial and verification policy](/en/docs/reference/editorial-policy): How this site selects sources, tests SQL, labels versions, and maintains AI-assisted content