说到数据库,很多人第一反应是MySQL,但PostgreSQL凭借强大的功能和严谨的设计,越来越受青睐——尤其是需要复杂查询、地理数据(PostGIS)、或向量检索(pgvector)的场景。本文带你入门PostgreSQL并做基础优化。
一、PostgreSQL vs MySQL,怎么选?
- MySQL:生态最大、教程最多、Web建站(尤其WordPress)默认选择,读多写少场景成熟;
- PostgreSQL:功能更强大、标准更严谨,擅长复杂查询、事务、JSON、地理数据、扩展生态(如向量检索pgvector,参考博客RAG篇)。
简单建站选MySQL够用;需要复杂数据处理、高级特性、或做AI/地理类应用,PostgreSQL更合适。两者都是优秀的开源数据库。
二、安装PostgreSQL
apt install postgresql postgresql-contrib -y
systemctl enable --now postgresql
# 切换到postgres用户进入命令行
sudo -u postgres psql三、基础操作
-- 创建数据库和用户
CREATE DATABASE mydb;
CREATE USER myuser WITH PASSWORD '强密码';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
-- 常用命令(psql内)
\l -- 列出数据库
\c mydb -- 连接数据库
\dt -- 列出表
\du -- 列出用户
\q -- 退出四、核心参数调优
默认配置很保守,现代服务器需调整(编辑 postgresql.conf,以8G内存专用库为例):
shared_buffers = 2GB # 最重要,设为内存的25%左右
effective_cache_size = 6GB # 告知优化器可用缓存,设为内存的50%-75%
work_mem = 64MB # 每个查询操作的内存
maintenance_work_mem = 512MB # 维护操作(建索引等)的内存
max_connections = 100 # 按并发调整shared_buffers 是PostgreSQL的核心缓存,相当于MySQL的buffer pool,设置得当能让热数据驻留内存、大幅减少磁盘访问。
五、索引与慢查询优化
和所有数据库一样,索引是性能关键:
-- 开启慢查询日志(postgresql.conf)
log_min_duration_statement = 1000 -- 记录超过1秒的查询
-- 分析查询执行计划
EXPLAIN ANALYZE SELECT ... ;
-- 创建索引
CREATE INDEX idx_name ON table_name(column);用 EXPLAIN ANALYZE 看查询是否走了索引、扫描了多少行(原理与MySQL优化相通,参考博客MySQL优化篇)。PostgreSQL还支持部分索引、表达式索引等高级索引类型,复杂场景优化空间更大。
六、硬件与部署建议
PostgreSQL同样是随机IO密集型负载,硬件建议与MySQL一致(参考博客数据库服务器配置篇):
七、安全与备份
- 不监听公网:配置 pg_hba.conf 和 listen_addresses,只允许本机或内网/白名单访问(参考防火墙篇);
- 强密码 + 最小权限:业务用户只授予需要的权限;
- 定期备份:用 pg_dump 导出,配合异地备份(参考rclone篇)。
结论
PostgreSQL是功能强大的开源数据库,适合复杂查询和高级场景。优化核心:shared_buffers调到内存25%、补对索引、揪慢查询、NVMe+物理独享。选MySQL还是PostgreSQL看业务——简单建站MySQL,复杂/AI/地理应用PostgreSQL。想跑通部署,在一台VPS上装个PostgreSQL练习本文操作,核心库再上独享资源。
