PostgreSQL

v16.x

SQL

官方网站 GitHub

概述

PostgreSQL 是世界上最先进的开源关系数据库。它以强大的功能、优秀的性能和高度的标准兼容性著称，是生产环境的首选数据库。

为什么选择 PostgreSQL？

• 功能最全：JSON、全文搜索、GIS、数组、范围类型等
• 性能优秀：复杂查询优化能力强
• 标准兼容：最接近 SQL 标准的数据库
• 扩展性强：支持自定义类型、函数、索引
• 开源免费：无商业限制，社区活跃

技术特性

特性	说明
数据类型	JSON/JSONB、数组、UUID、范围、几何等
索引类型	B-tree、Hash、GiST、GIN、BRIN
全文搜索	内置支持，无需额外扩展
事务隔离	四种级别全支持
复制方式	流复制、逻辑复制
最大表大小	32 TB
最大行数	无限制

数据库选型对比

特性	PostgreSQL	MySQL	SQLite
JSON 支持	✅ JSONB 性能优秀	✅ 基础支持	✅ 基础支持
全文搜索	✅ 内置	❌ 需扩展	❌ 需扩展
数组类型	✅ 原生支持	❌ 不支持	❌ 不支持
事务支持	✅ 完整 ACID	✅ 完整 ACID	✅ 完整 ACID
复杂查询	✅ 优秀	⚠️ 一般	⚠️ 有限
部署复杂度	⚠️ 需独立服务	⚠️ 需独立服务	✅ 零配置
适用场景	生产环境首选	传统 Web 应用	开发/小型项目

选型建议

• 开发环境：SQLite 最方便，零配置
• 生产环境：PostgreSQL 功能最强大
• 已有 MySQL 经验：MySQL 也可以

本地安装

macOS

bash

        $
        brew install postgresql@16
      

bash

        $
        brew services start postgresql@16
      

Ubuntu/Debian

bash

        $
        sudo apt install postgresql postgresql-contrib
      

bash

        $
        sudo systemctl start postgresql
      

Windows

推荐使用 PostgreSQL 官方安装包。

Docker（推荐）

bash

        $
        docker run -d --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:16
      

连接配置

连接字符串格式

postgresql://[用户名]:[密码]@[主机]:[端口]/[数据库名]?schema=[schema]

各框架配置示例

Prisma (Node.js)

.env

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/mydb?schema=public"

Spring Boot (Java/Kotlin)

application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: postgres
    driver-class-name: org.postgresql.Driver

FastAPI (Python)

database.py

SQLALCHEMY_DATABASE_URL = "postgresql://postgres:postgres@localhost:5432/mydb"

常用命令

进入 PostgreSQL 命令行

bash

        $
        psql -U postgres
      

常用 SQL 命令

常用命令

-- 列出所有数据库
\l

-- 连接数据库
\c mydb

-- 列出所有表
\dt

-- 查看表结构
\d users

-- 创建数据库
CREATE DATABASE mydb;

-- 创建用户
CREATE USER myuser WITH PASSWORD 'mypassword';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

PostgreSQL 特色功能

JSONB 存储

JSONB 示例

-- 创建包含 JSONB 的表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  metadata JSONB
);

-- 插入数据
INSERT INTO products (name, metadata) VALUES 
('iPhone', '{"color": "black", "storage": 256}');

-- 查询 JSONB 字段
SELECT * FROM products WHERE metadata->>'color' = 'black';

-- JSONB 索引（提升查询性能）
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

数组类型

数组示例

-- 创建包含数组的表
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  tags TEXT[]
);

-- 插入数据
INSERT INTO articles (title, tags) VALUES 
('Vue 3 入门', ARRAY['vue', 'frontend', 'javascript']);

-- 查询包含特定标签的文章
SELECT * FROM articles WHERE 'vue' = ANY(tags);

全文搜索

全文搜索示例

-- 创建全文搜索索引
CREATE INDEX idx_articles_search ON articles 
USING GIN (to_tsvector('chinese', title || ' ' || content));

-- 全文搜索查询
SELECT * FROM articles 
WHERE to_tsvector('chinese', title || ' ' || content) @@ to_tsquery('Vue & 入门');

生产环境建议

生产环境配置

1. 连接池：使用 PgBouncer 管理连接
2. 备份策略：定期 pg_dump 或使用 WAL 归档
3. 监控：使用 pg_stat_statements 监控慢查询
4. 索引优化：定期 ANALYZE 和 VACUUM
5. SSL 连接：生产环境强制 SSL

相关资源

PostgreSQL 官方文档

https://www.postgresql.org/docs/

→

PostgreSQL 中文社区

http://www.postgres.cn/

→

pgAdmin 管理工具

https://www.pgadmin.org/

→