SQLite 自托管数据库运维指南：零成本高性能查询与自动备份策略

为什么在 VPS 上用 SQLite？

大多数自托管教程都在推 PostgreSQL 或 MySQL，但如果你只是运行一个个人博客、笔记应用、API 服务或者小型内部工具，SQLite 往往是更好的选择：

零配置：无需安装服务、无需维护用户权限、无需处理连接池
零成本：单文件存储，内存占用几乎为 0，不需要 512MB+ 的额外 RAM
易备份：整个数据库就是一个 .db 文件，cp 就是备份
性能出色：对于读多写少、并发低的场景，SQLite 的读写延迟远低于网络型数据库

适合 SQLite 的典型自托管场景：

Home Assistant（本地智能家居）
Ghost / WordPress（静态化后）
Miniflux / FreshRSS（RSS 阅读器）
Plausible Analytics（隐私分析）
各类小型 REST API 服务

不适合的场景： 高并发写入（>100 QPS）、多节点主从复制、复杂事务要求严格的金融系统。

核心架构：SQLite 工作原理

┌─────────────────────────────────────────────────────────────┐
│                    SQLite 自托管架构                         │
│                                                             │
│  ┌──────────┐    ┌──────────────┐    ┌─────────────────┐   │
│  │  App     │───▶│  WAL 文件     │───▶│  主数据文件       │   │
│  │  进程     │    │  (writes)    │    │  (main.db)      │   │
│  └──────────┘    └──────────────┘    └─────────────────┘   │
│       ▲                                      │              │
│       │           ┌──────────────────────────┘              │
│       │           │                                         │
│  ┌────▼────┐  ┌───▼──────────┐   ┌─────────────────────┐   │
│  │ 备份     │  │  只读查询     │──▶│  快照备份 (pg_dump)  │   │
│  │ 脚本     │  │  (VACUUM    │   │  (rsync / cron)      │   │
│  │ (cron)   │  │   / PRAGMA)  │   └─────────────────────┘   │
│  └─────────┘  └──────────────┘                             │
│                                                             │
│  关键特性：                                                   │
│  • WAL 模式：读写不互斥，并发提升 5-10x                      │
│  • 单文件架构：迁移/备份/版本管理极简                        │
│  • 无锁读：大量并发读不影响写操作                             │
└─────────────────────────────────────────────────────────────┘

第一步：WAL 模式开启（性能关键）

默认情况下 SQLite 使用 DELETE 日志模式，读写互斥——写入时所有查询阻塞。开启 WAL（Write-Ahead Logging）后，读写操作不再互相阻塞，性能提升显著。

2.1 创建数据库时启用 WAL

-- 创建数据库并立即启用 WAL
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;      -- 平衡性能与安全
PRAGMA cache_size = -64000;       -- 64MB 缓存
PRAGMA temp_store = MEMORY;       -- 临时表存内存

2.2 已有数据库开启 WAL

-- 在线切换（无需停机）
PRAGMA journal_mode = WAL;

-- 验证
PRAGMA journal_mode;  -- 返回 'wal' 即成功

2.3 Docker 部署时一键配置

services:
  myapp:
    image: your-app:latest
    environment:
      DATABASE_URL: "sqlite:///data/app.db?journal_mode=WAL&synchronous=NORMAL"
    volumes:
      - ./data:/data          # 持久化目录
    restart: unless-stopped

注意：WAL 模式会产生三个文件：main.db、main.db-wal、main.db-shm。备份时需要同时拷贝这三个文件。

第二步：并发控制与锁优化

SQLite 的写操作是数据库级别的排他锁，这意味着同一时刻只能有一个写入者。对于自托管场景这通常不是问题，但了解以下技巧很有用：

3.1 连接池配置

# Python + SQLAlchemy 示例
from sqlalchemy import create_engine
engine = create_engine(
    "sqlite:///./data/app.db",
    connect_args={
        "check_same_thread": False,  # 多线程安全
        "timeout": 30,               # 30秒超时
    },
    pool_size=5,                   # 限制连接数
    max_overflow=10,
)

3.2 批量写入加速

-- ❌ 低效：逐条插入
INSERT INTO events (user_id, action, timestamp) VALUES (1, 'login', NOW());
INSERT INTO events (user_id, action, timestamp) VALUES (2, 'login', NOW());
-- ...

-- ✅ 高效：事务包裹批量插入
BEGIN;
INSERT INTO events (user_id, action, timestamp) VALUES (1, 'login', NOW());
INSERT INTO events (user_id, action, timestamp) VALUES (2, 'login', NOW());
-- ... 1000 条 ...
COMMIT;

3.3 读写分离策略

对于读多写少的场景（如统计面板），可以在备份后立即打开只读副本：

# 定时快照（每天凌晨 3 点）
cp main.db main.db.bak
# 只读查询使用快照
sqlite3 main.db.bak "SELECT COUNT(*) FROM events WHERE date = '2026-09-14';"

第三步：自动备份策略

SQLite 的单文件特性让备份变得极其简单，但必须注意一致性——直接 cp 一个正在写入的数据库可能导致文件损坏。

4.1 使用 SQLITE 内置备份 API（推荐）

#!/bin/bash
# backup.sh — 安全的 SQLite 在线备份
DB_PATH="./data/app.db"
BACKUP_DIR="./backups"
DATE=$(date +%Y%m%d_%H%M%S)

# 使用 sqlite3 的热备份功能（不锁表）
sqlite3 "$DB_PATH" ".backup '$BACKUP_DIR/app_$DATE.db'"

# 压缩备份
gzip "$BACKUP_DIR/app_$DATE.db"

# 清理 30 天前的备份
find "$BACKUP_DIR" -name "app_*.db.gz" -mtime +30 -delete

echo "[$(date)] Backup completed: app_$DATE.db.gz"

4.2 cron 定时备份

# crontab -e
# 每天凌晨 3 点备份
0 3 * * * /opt/scripts/backup.sh >> /var/log/sqlite-backup.log 2>&1
# 每周日完整校验
0 4 * * 0 sqlite3 /opt/app/data/app.db "PRAGMA integrity_check;"

4.3 多版本保留策略

# backup-config.yaml
retention:
  daily:   7    # 保留最近 7 天的每日备份
  weekly:  4    # 保留最近 4 周的周备份
  monthly: 12   # 保留最近 12 个月的月备份
storage:
  local:   /opt/backups/sqlite
  remote:  s3://your-bucket/sqlite-backups/  # S3 / R2 对象存储

# 推送到 S3/R2（配合 restic 或 rclone）
rclone copy ./backups/ remote:sqlite-backups/ --progress

第四步：查询性能优化

5.1 索引策略

-- 为高频查询字段添加索引
CREATE INDEX idx_events_user_time ON events(user_id, created_at);
CREATE INDEX idx_sessions_token ON sessions(token) WHERE token IS NOT NULL;

-- 查看索引使用情况
PRAGMA index_list('events');
EXPLAIN QUERY PLAN SELECT * FROM events WHERE user_id = 42;
-- 应显示 "SEARCH TABLE events USING INDEX idx_events_user_time"

5.2 VACUUM 与数据库瘦身

SQLite 删除数据后不会自动释放空间，需要定期 VACUUM：

-- 查看当前数据库大小
SELECT page_count * page_size AS size_bytes 
FROM pragma_page_count(), pragma_page_size();

-- 释放未使用空间（会加锁，建议在低峰期执行）
VACUUM;

-- 在线 VACUUM（不锁表，但较慢）
-- 使用 sqlite-vacuum-ext 扩展或复制到新数据库

5.3 常用优化 PRAGMA

-- 性能调优合集
PRAGMA journal_mode = WAL;              -- 开启 WAL
PRAGMA synchronous = NORMAL;            -- 平衡性能与安全（2=安全, 0=最快）
PRAGMA cache_size = -64000;             -- 64MB 缓存
PRAGMA temp_store = MEMORY;             -- 临时对象存内存
PRAGMA mmap_size = 268435456;           -- 256MB 内存映射
PRAGMA page_size = 4096;                -- 标准页大小
PRAGMA automatic_index = ON;            -- 自动创建临时索引
PRAGMA query_only = OFF;                -- 允许写入（默认）

-- 监控
PRAGMA locking_mode;                     -- 当前锁模式
PRAGMA wal_checkpoint(RESTART);         -- 手动检查点

5.4 慢查询分析

-- 开启查询计划日志
.timer on
.explain on

-- 执行可疑查询并观察
SELECT * FROM events WHERE created_at > '2026-01-01' ORDER BY created_at DESC LIMIT 100;

-- 关闭
.explain off
.timer off

第五步：监控与健康检查

6.1 关键指标

指标	命令	健康阈值
数据库大小	`ls -lh *.db`	< 10GB（过大需分区）
连接数	`PRAGMA database_list;`	活跃连接 < 20
锁等待	`PRAGMA busy_timeout;`	timeout < 5s
完整性	`PRAGMA integrity_check;`	返回 `ok`
WAL 状态	`PRAGMA wal_checkpoint(PASSIVE);`	frame < 1000

6.2 自动化健康检查脚本

#!/bin/bash
# health-check.sh
DB="./data/app.db"
WARN_THRESHOLD=1073741824  # 1GB

# 大小检查
SIZE=$(stat -c%s "$DB")
if [ "$SIZE" -gt "$WARN_THRESHOLD" ]; then
    echo "WARN: Database size ${SIZE} bytes exceeds 1GB threshold"
fi

# 完整性检查
RESULT=$(sqlite3 "$DB" "PRAGMA integrity_check;")
if [ "$RESULT" != "ok" ]; then
    echo "ERROR: Integrity check failed: $RESULT"
    exit 1
fi

# WAL 检查点
sqlite3 "$DB" "PRAGMA wal_checkpoint(TRUNCATE);"

echo "OK: All checks passed at $(date)"

6.3 告警集成

# 配合 Uptime Kuma 或自建监控
- type: http
  name: "SQLite Health Check"
  url: "https://monitor.yourdomain.com/api/push/sqlite-health"
  interval: 300  # 5分钟
  method: POST
  headers:
    Content-Type: application/json
  body: |
    {"status":"{{now}}","db_size":{{db_size}},"integrity":"{{integrity}}"}

第六步：迁移与升级

7.1 SQLite 版本升级

# 检查当前版本
sqlite3 --version

# 备份后再升级
cp app.db app.db.bak.$(date +%s)

# 升级 SQLite 二进制后，用新的 sqlite3 打开旧数据库
# SQLite 向后兼容，新版本可读取旧版本创建的数据库
sqlite3 app.db "PRAGMA integrity_check;"

7.2 从 PostgreSQL/MySQL 迁移到 SQLite

-- PostgreSQL → SQLite 迁移步骤
-- 1. 导出结构（去掉 PG 特有语法）
pg_dump -s -d mydb > schema.sql

-- 2. 转换数据类型
-- serial → INTEGER PRIMARY KEY
-- text → TEXT
-- boolean → INTEGER
-- timestamp without time zone → DATETIME

-- 3. 导出数据
pg_dump -t events -d mydb --data-only > data.sql

-- 4. 在 SQLite 中重建并导入
sqlite3 app.db < schema.sql
sqlite3 app.db ".import data.sql events"

性能对比：SQLite vs PostgreSQL

场景	SQLite	PostgreSQL	胜出
单用户博客	✅ 轻量无开销	❌ 需 200MB+ RAM	SQLite
10 并发以内	✅ 足够	✅ 也足够	平手
百万级行查询	✅ 索引后 < 50ms	✅ 更优	看索引
高并发写入	❌ 锁瓶颈	✅ 行级锁	PostgreSQL
备份恢复	✅ 单文件拷贝	❌ 需 pg_dump	SQLite
部署复杂度	✅ 零配置	❌ 需维护服务	SQLite

结论：对于绝大多数自托管个人项目（日活 < 1000，并发 < 10），SQLite 是更经济、更简单的选择。只有当你明确遇到并发瓶颈时，才需要考虑迁移到 PostgreSQL。

结语

SQLite 不是一个"退而求其次"的选择，而是一个经过时间验证的、适合自托管场景的优秀数据库引擎。配合 WAL 模式、合理的索引策略和自动化备份，它能在极低资源消耗下提供可靠的存储服务。

核心要点回顾：

必须开启 WAL 模式 — 这是性能提升的关键
批量写入包裹在事务中 — 避免每条 INSERT 单独写盘
用 PRAGMA integrity_check 定期校验 — 预防数据损坏
备份三个 WAL 文件 — 保证恢复一致性
超过 10GB 或高并发时考虑迁移 PostgreSQL — 知道何时该换工具

让你的自托管应用在零额外成本下稳定运行吧！