上个月我接了一个有点磨人但收获很大的任务把内部一直在用的 apiSQL 数据层整体迁移到团队已有的 PostgreSQL 实例上。apiSQL 这套东西说白了就是给一堆第三方 API 加了一层 SQL 查询外壳——把 API 返回的 JSON 数据注册成虚拟表用 SQL 的方式去筛、去聚合、去关联。在数据量小、查询不复杂的时候这套方案非常灵活很多团队都这么干。但随着接入的 API 越来越多、数据量涨到几千万行原来用 JSON 文件加内存缓存顶着的方案就扛不住了查询慢、并发一高就内存溢出、重启丢数据、没法做事务也没法把数据直接喂给 BI 看板。折腾了一周之后我决定把它搬到团队已经跑着的 PostgreSQL 上。这篇文章就把整个迁移过程整理出来包括迁移前的决策分析、数据模型怎么设计、已有 PostgreSQL 实例要做哪些检查、全量数据怎么倒、增量同步怎么做、以及我在这个过程中踩过的几个坑。如果你正在用类似的 API 查询工具或者准备把一套轻量数据服务迁到 PostgreSQL这篇内容应该能给你省不少弯路。1. 迁移决策为什么选择已有 PostgreSQL 而不是另起炉灶1.1 原来 apiSQL 方案的问题出在哪先说说我们原来的架构。apiSQL 本身不存储业务主数据它做的事情更像是数据网关 查询引擎你配置一个数据源比如某个第三方订单接口、某个内部用户服务指定请求参数映射、分页规则、响应字段路径apiSQL 就会把响应结果拉下来缓存成 JSON 结构然后对外提供 SQL 查询能力。听起来很美但有几个硬伤第一数据容易丢。缓存在内存里的数据进程一重启就没了落盘到 JSON 文件的数据写一半断电就是文件损坏。对生产环境来说这种可靠性完全不可接受。第二查询性能会随着数据量断崖式下跌。apiSQL 的实现本质是内存计算数据量在几百万行以内还不错一旦到千万级以上一次带排序带分页的全表扫描响应时间直接从几十毫秒变成几秒甚至超时。第三并发能力受限。API 网关的令牌桶、限流逻辑、内存锁决定了它很难应对高并发查询。更重要的是业务方开始要求我们把数据和内部其他系统的数据做关联分析这已经不是 apiSQL 的定位了——它擅长的是帮你查 API不是帮你做企业级数据仓库。1.2 PostgreSQL 对 apiSQL 这类场景的适配性选 PostgreSQL 不是偶然。我们对几个候选存储做了对比最终还是觉得 PG 最贴合 apiSQL 迁移过来的需求JSONB 类型是天然契合点。apiSQL 最核心的数据形态就是 JSONPostgreSQL 的 JSONB 支持 GIN 索引、支持、?这类 JSON 路径操作符复杂嵌套结构可以不用拆表就能做条件过滤这对迁移初期非常友好。事务和并发能力是质变。以前 apiSQL 的写入和查询是完全分开的没有事务可言切到 PostgreSQL 后写入、更新、删除都可以放进事务里数据一致性从尽力而为变成了有保障。生态成熟。DBeaver、Navicat、Metabase、Grafana 都能直接连 PG运维排障、数据可视化、定时备份的工具链都是现成的不用再额外开发。我们当时的判断是迁移不是把 apiSQL 丢掉而是把 apiSQL 的数据底座从临时文件 内存换成 PostgreSQLapiSQL 继续承担 API 接入和查询路由的角色但数据存储和复杂查询交给 PG。1.3 复用已有实例的取舍既然团队已经有一台跑了好几年的 PostgreSQL 实例另一个选择是新建一个独立的 PG 实例。我实际评估下来结论是除非有合规隔离要求否则优先复用已有实例。复用的好处很明显不用新买机器、不用再配一套监控告警、备份体系直接沿用现有的。但代价是需要克制——你不能在别人的生产库里乱来创建表、建索引、写数据都要提前和 DBA 沟通容量也要算清楚。我们当时做的第一件事就是拉出已有实例的磁盘使用量、CPU 峰值、活跃连接数评估再塞下一套 apiSQL 数据是否会影响核心业务。注意如果你要迁移的目标实例承载着线上核心交易建议先做一轮资源评估。最关键的三项指标磁盘剩余空间至少是预估迁移数据量的 2 倍、max_connections当前使用率避免连接数被打满、WAL 目录增长速度避免大量写入把 WAL 撑爆。2. 迁移前的评估盘点数据形态、设计表结构2.1 理清 apiSQL 里到底存了什么动手之前我先盘点了一下 apiSQL 运行时产生的数据大致分成三类一是数据源元数据——也就是 apiSQL 内部的配置信息比如数据源名称、API 地址、请求头、字段映射规则、缓存策略。这些数据量不大通常几百条但是不能丢丢了 apiSQL 就不知道去哪拉数据了。二是API 响应快照——这是大头。每天定时任务从各个第三方 API 拉回来的原始 JSON有的带分页有的嵌套很深有的字段不稳定昨天有email今天可能就没了。这类数据的特点是无序增长、结构多变、价值密度低但又是原始凭证。三是物化查询结果——业务方经常要跑固定报表apiSQL 会把频繁执行的查询结果缓存成一张临时表。这类数据时效性强过期之后意义不大迁移时可以适当丢弃。搞清楚这三类数据之后迁移策略就清晰了第一类完整迁移第二类全量迁移且要设计好 JSONB 兜底第三类只迁移最近一个周期比如最近 7 天的结果更早的重新跑一次查询就能生成没必要搬。2.2 表结构设计用一张真实表举例以我们最典型的一张订单聚合查询表为例原来 apiSQL 拉取的是电商平台的订单接口响应 JSON 大概是这样的{ order_id: ORD20240615001, user: { uid: 10086, nickname: zhang3, level: 3 }, items: [ {sku: A001, name: 机械键盘, price: 399}, {sku: B002, name: 鼠标垫, price: 19.9} ], pay: {method: alipay, amount: 418.9}, status: paid, created_at: 2024-06-15T12:30:00Z }迁到 PostgreSQL 之后我设计成一张主表加一张子表CREATE TABLE api_orders ( id BIGSERIAL PRIMARY KEY, order_id VARCHAR(64) NOT NULL UNIQUE, uid BIGINT NOT NULL, user_nickname VARCHAR(128), user_level INT, status VARCHAR(32), pay_method VARCHAR(32), pay_amount NUMERIC(12,2), item_count INT, raw_json JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), synced_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_api_orders_uid ON api_orders(uid); CREATE INDEX idx_api_orders_created_at ON api_orders(created_at); CREATE INDEX idx_api_orders_raw_json ON api_orders USING GIN (raw_json); -- 商品明细拆分到子表方便后续做 SKU 维度分析 CREATE TABLE api_order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES api_orders(id) ON DELETE CASCADE, sku VARCHAR(32), name VARCHAR(128), price NUMERIC(12,2) );这个设计有两个关键决策为什么保留raw_json字段因为第三方 API 的字段经常变顶层字段抽出来当普通列用于高频查询和索引未知的、未来可能出现的字段全部留在 JSONB 里。这样既保证了核心查询的性能又不用每次 API 加字段都改表。这就叫结构化与半结构化共存。为什么拆子表数组类型的商品明细如果直接塞在一个 JSONB 字段里查询某个 SKU 卖了多少就会很痛苦PG 的 JSONB 虽然能搞定但是性能不如拆表。拆出来的子表可以做聚合、Join、统计这也是为后续数据建设铺路。2.3 命名、约束和索引的约定数据库迁移最忌讳的就是表建成什么样全看心情。我列了几个原则表名统一加前缀api_一眼能看出来源后续清理数据也知道哪些是 apiSQL 同步的。主键优先用BIGSERIAL保留业务单号如order_id做UNIQUE约束防止同一订单被重复写入。时间字段统一用TIMESTAMPTZ不要用TIMESTAMP否则时区问题会阴魂不散。索引先建最常用的过滤字段uid、status、created_at不要一上来把所有字段都建索引写入性能会吃亏。JSONB 字段如果确实要根据内部字段过滤单独建 GIN 索引就够了。除了这些还有一个容易踩坑的地方——apiSQL迁移到 PG 后原来某些字段是字符串123现在变成BIGINT123类型转换很容易导致同步脚本报错。我的建议是凡是 API 返回的数值类字段落库前在代码里做一次显式转换不要指望数据库自动帮你 cast否则一个脏数据就让整批写入失败。3. 目标端准备已有 PostgreSQL 实例的检查与调优3.1 版本确认与扩展检查迁移前一定要先确认目标实例的 PostgreSQL 版本。不同版本对 JSONB、索引、并发控制的能力差异很大我们的实例是 PostgreSQL 16性能很好直接拿过来用。如果你手上还是 12 或更老的版本我的建议是优先把实例升级到 16 以上再做迁移尤其是 JSONB 的查询计划和并行查询能力老版本和新版本差得不是一点半点。确认版本之后还要检查必要的扩展-- 查看当前实例版本 SELECT version(); -- 确认 JSONB 相关的扩展一般内置不需要额外安装但确认一下不亏 SELECT name FROM pg_available_extensions WHERE name IN (pg_trgm, btree_gin);老实说apiSQL 迁移基本用不到外部扩展内置的 JSONB、GIN 索引已经足够。但如果你后续想对 JSONB 里的长文本做模糊搜索pg_trgm扩展就很有用它让 GIN 索引支持ILIKE查询。建议提前装上反正不影响现有业务。3.2 创建专用用户和 Schema对待已有的生产 PostgreSQL 实例最小权限原则必须贯彻。不要用postgres超级用户跑 apiSQL 的写入任务万一 SQL 写错要么删了业务表要么把连接资源耗光。我当时的做法是-- 创建专用登录角色只赋予 api 相关库的权限 CREATE ROLE apisql_user WITH LOGIN PASSWORD your-strong-password; GRANT CONNECT ON DATABASE your_db TO apisql_user; -- 用独立的 schema 隔离 apiSQL 的数据避免和现有业务表混在一起 CREATE SCHEMA apisql AUTHORIZATION apisql_user; -- 后续建表都放在 apisql 这个 schema 下路由和查询一目了然 SET search_path TO apisql;用 Schema 隔离还有个额外的好处以后如果你想把这个 schema 整体迁移到别的库直接pg_dump -n apisql打一个包就能带走不需要碰其他业务表。这对一个后来者模块来说非常实用。3.3 关键参数检查与调整apiSQL 迁移的数据写入模式是批量、高频、间歇性和交易系统的小额、高并发不太一样。如果目标实例之前主要跑偏交易型负载下面这几个参数值得重新过一遍参数推荐值说明shared_buffers物理内存的约 25%调大后热数据缓存命中率显著提升对秒级频繁查询帮助大work_mem64MB 起步排序、哈希聚合内存不调大很容易在大量 DISTINCT/ORDER BY 时落盘maintenance_work_mem512MB 或更高直接影响CREATE INDEX、VACUUM的速度max_connections已有值基础上评估apiSQL 再加几十个连接确认不超上限wal_levelreplica一般已是如果要做 PITR 备份必须保持这个级别这里我特别提醒max_connections是最容易被忽视的坑。我们第一次跑全量导入脚本时用的是多线程并发写瞬间开了 40 个连接直接把实例的活跃连接数顶到接近上限导致其他业务模块的查询开始排队。后来改成串行写入 限制脚本连接池大小问题才缓解。3.4 连接信息与客户端配置PostgreSQL 16 默认监听localhost如果你的 apiSQL 服务和数据库不在同一台机器上需要确认postgresql.conf里listen_addresses是否包含对应内网 IP。pg_hba.conf里是否有允许 apiSQL 服务所在网段访问your_db的条目。客户端连接串建议显式写sslmoderequire生产环境尽量不要用明文密码传。我们内部因为所有服务都在同一个私有网络所以pg_hba.conf用的是host all apisql_user 10.0.0.0/8 scram-sha-256配合强密码够用。如果你们有更严格的要求再考虑证书认证。4. 全量迁移落地数据导出、导入与校验4.1 从 apiSQL 导出历史数据apiSQL 本身提供数据导出接口但我们这里情况有点特殊——它导出的数据格式是 JSON Lines每一行是一个 JSON 对象正好和 PG 的 JSONB 字段对得上。导出脚本的核心逻辑不复杂难的是在大数据量下保持稳定。我当时直接用 Python 写了个脚本分批拉取import requests import json # apiSQL 提供的导出接口示例假设分页拉取 base_url http://localhost:8080/api/export/orders page 1 page_size 5000 with open(orders.jsonl, w, encodingutf-8) as f: while True: resp requests.get(base_url, params{page: page, page_size: page_size}, timeout60) data resp.json() if not data[items]: break for item in data[items]: f.write(json.dumps(item, ensure_asciiFalse) \n) page 1导出这一步要关注两个问题超时和限流。apiSQL 在导出大数据量时本身会做流式读取但如果你的网络或者 apiSQL 服务不稳定建议加上重试机制并且把单次页大小控制在 5000 条以内。我们第一次导出时写了一页 20000 条结果拉了 5 次就超时中断改成 5000 条后稳定很多。4.2 导入方式选择COPY 是最优解导出完成后导入 PostgreSQL 有好几种方式我帮你把主流方案对比一下导入方式速度适用场景缺点psql\COPY极快大批量 JSON Lines/CSV需要先预处理成 COPY 格式逐条INSERT慢小数据量几千条连接往返多效率低批量INSERT多行 VALUES中等百万行以内构造 SQL 太长容易碰参数上限使用 Pythonpsycopg3COPY极快程序化导入、带转换逻辑需要写代码我们的数据量在千万行级别逐条 INSERT 根本不现实。当时用 psycopg3 的copy_expert方式把 JSON Lines 直接流式写入临时表再通过 SQL 转换插入正式表import psycopg from psycopg import sql conn psycopg.connect(hostyour-pg-host dbnameyour_db userapisql_user password***) with conn.cursor() as cur: with open(orders.jsonl, r, encodingutf-8) as f: with cur.copy(COPY apisql.api_orders_staging (order_id, uid, user_nickname, user_level, status, pay_method, pay_amount, item_count, raw_json, created_at) FROM STDIN WITH (FORMAT CSV, DELIMITER E\t, QUOTE E\b)) as copy: while True: line f.readline() if not line: break obj json.loads(line) # 在 Python 层做类型转换避免脏数据打进来 try: uid int(obj[user][uid]) pay_amount float(obj[pay][amount]) except (KeyError, TypeError, ValueError): # 跳过脏数据单独记录日志后面人工处理 skipped_count 1 continue # raw_json 用 json.dumps 序列化COPY 时作为一列插入 copy.write_row(( obj[order_id], uid, obj.get(user, {}).get(nickname), obj.get(user, {}).get(level), obj.get(status), obj.get(pay, {}).get(method), pay_amount, len(obj.get(items, [])), json.dumps(obj, ensure_asciiFalse), obj.get(created_at) ))这种做法有几个好处COPY 是 PG 批量写入最快的方式实测几百万行能在几十秒内完成。Python 层可以做字段补齐、类型转换、脏数据过滤比直接 COPY 原始 JSONL 灵活得多。先写入 staging 表再通过INSERT INTO ... SELECT转正式表中间可以加数据清洗逻辑例如对created_at统一转成TIMESTAMPTZ。4.3 转正式表和数据校验数据导入 staging 表后用一条 SQL 完成转正式表INSERT INTO apisql.api_orders ( order_id, uid, user_nickname, user_level, status, pay_method, pay_amount, item_count, raw_json, created_at ) SELECT order_id, uid, user_nickname, user_level, status, pay_method, pay_amount, item_count, raw_json, created_at::timestamptz FROM apisql.api_orders_staging;然后做行数比对SELECT count(*) FROM apisql.api_orders; SELECT count(*) FROM apisql.api_orders_staging;如果两个数对不上优先查 staging 表里是否有created_at为空、字段类型异常的脏数据。我们第一次迁移差了 1123 行最后发现是一个第三方接口的created_at字段有 5 种不同格式Python 层导出的created_at全部原样塞进 staging转正式表时 cast 失败被跳过了。解决方式很简单Python 层统一用datetime.fromisoformat解析后改成标准 ISO 格式再写入。顺带一提校验阶段最好也随机抽验几条数据的 JSONB 内容光比行数不代表字段结构没丢失。我当时写了条 SQLSELECT order_id, raw_json-status AS status, raw_json-pay-method AS pay_method FROM apisql.api_orders WHERE order_id ORD20240615001;用来和 apiSQL 原始导出记录做交叉对比确保嵌套字段解析正确。4.4 apiSQL 配置文件切换数据落地之后最关键的一步是让 apiSQL 把查询路由切到 PostgreSQL。不同版本的 apiSQL 配置方式略有差别但核心思路都是把原来指向文件缓存/内存缓存的存储后端改成 PostgreSQL。我们当时在 apiSQL 的配置中心里加了这样一段storage: backend: postgresql postgres: host: your-pg-host port: 5432 database: your_db schema: apisql user: apisql_user password: *** pool_size: 10 connection_timeout: 10配置改完后先重启 apiSQL 服务做冒烟测试确认它建的内部表能正常读写。这里我踩过一个坑apiSQL 建内部表时用的 schema 是public而我们规划的 schema 是apisql结果它报权限不足。后来找到配置里有个schema_name参数显式指定apisql才解决。所以切换配置后第一件事不是测查询而是检查它是否正确读写在你指定的 schema 里避免它默默往 public 里建一堆表。5. 增量同步与性能验证5.1 增量同步策略定时任务与幂等设计全量迁移完了但第三方 API 每天都在产出新数据所以增量同步是常态。我们设计成这个样子apiSQL 继续按原来的调度拉取 API 数据比如每 15 分钟一次。拉回来的新数据写入 PostgreSQL此时需要做的不是盲目 INSERT而是用 UPSERT 保证幂等。PostgreSQL 的 UPSERT 是 ON CONFLICT 子句比如INSERT INTO apisql.api_orders ( order_id, uid, user_nickname, user_level, status, pay_method, pay_amount, item_count, raw_json, created_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) ON CONFLICT (order_id) DO UPDATE SET status EXCLUDED.status, pay_amount EXCLUDED.pay_amount, raw_json EXCLUDED.raw_json, synced_at now();这样同一个订单如果被 API 重复推送只会更新字段不会产生重复行。幂等是增量同步的底线否则哪天调度脚本多跑一次数据就翻倍了。增量任务本身我们也做了监控每次同步结束后写入一张api_sync_log表记录本次拉取起始时间、结束时间、成功条数、失败条数、失败原因。排查问题的时候一张日志表能省很多事。5.2 迁移前后查询性能对比不拿出数据说话迁移就没有说服力。我整理了迁移前后的典型查询对比查询场景apiSQL 内存缓存apiSQL PostgreSQL单条订单按 order_id 查询约 80ms约 3ms走唯一索引按用户查询最近 100 单约 300ms约 20ms走 uid 索引按状态聚合统计约 2s全量内存扫描约 100mspg 聚合 索引按 JSONB 内部字段过滤不支持约 150msGIN 索引并发 50 个查询同时跑经常超时稳定在 200ms 内最让我意外的是 JSONB 内部字段过滤原来apiSQL 想做查所有pay.method alipay的订单这种需求基本要靠拉全量在内存里遍历现在 PG 的 GIN 索引直接搞定。这说明 JSONB 不是鸡肋关键是建对索引。5.3 功能回归测试确保 API 查询面没断迁移到 PostgreSQL 之后apiSQL 的对外查询接口保持不变但内部数据源变成了 PG所以回归测试重点要覆盖这几个点原有 SQL 语法兼容apiSQL 支持 JSON field 查询比如WHERE payload.pay.method alipay切换到 PG 后是否还能原样解析并下推给 PG。如果 apiSQL 不做下推优化所有过滤都是全表扫描进来再过滤那性能还是烂查日志确认执行计划很关键。分页和排序功能LIMIT/OFFSET、ORDER BY created_at DESC这些高频操作是否正常。定时缓存策略原来 apiSQL 可能会定期把报表结果缓存下来迁到 PG 后缓存表是否正常写入和过期清理。错误处理PG 返回的错误码如unique_violationapiSQL 是否能转换成业务友好的报错信息。我们当时用回归脚本把原来 apiSQL 上面的 20 多个核心查询全部跑了一遍对比了返回行数和字段内容确认没有差异才正式切换线上流量。6. 迁移过程中踩过的坑与运维建议6.1 连接池耗尽、时区、索引失效这几个坑踩坑最深的当然是连接池。我们最早写同步脚本的时候图省事直接用 psycopg2 的裸连接每来一批数据就 new 一个 connection跑到一半实例连接数直接爆炸。后来改成from psycopg_pool import ConnectionPool pool ConnectionPool( conninfohostyour-pg-host dbnameyour_db userapisql_user password***, min_size5, max_size20, timeout10, )然后所有读写都从 pool 里取连接用完归还。这里关键参数是max_size不要贪大20 个连接对 apiSQL 这种中低频写入场景完全够用。第二个坑是时区。API 返回的时间字段五花八门有带Z的、有带08:00的、还有纯YYYY-MM-DD HH:mm:ss不带时区的。如果直接塞进TIMESTAMPTZ字段PostgreSQL 会按会话时区理解不带时区的字符串。我们统一在 Python 层全部转成 UTC ISO 8601 格式再入库查询时在 apiSQL 侧指定时区彻底根治。第三个坑是 JSONB 索引失效。一开始我们给raw_json建 GIN 索引但查询搜raw_json {status: paid}时执行计划居然没走索引。查了半天原因是查询里用了raw_json - status这种操作符而它不匹配 GIN 索引。JSONB 的和-走的是两套查询路径你要查询嵌套字段就用你要提取字段值就用-两者别混用。后来统一改成形式索引就正常命中了。6.2 备份与恢复策略迁移完成之后数据安全还必须补上。PostgreSQL 的备份方式很多针对 apiSQL 这种数据量我们用的是每天凌晨全量pg_dumppg_dump -h your-pg-host -U apisql_user -F c -f /backup/apisql.dump -n apisql your_db只备份 apisql 这个 schema不影响其他业务。WAL 归档可选如果需要更细粒度的恢复点可以把archive_mode打开配合wal_levelreplica最多能恢复到秒级。定期做恢复演练光备份不演练等于没备份。两个月抽一次恢复演练把 dump 文件恢复到一台临时实例上跑几条查询验证数据可用性。注意如果你授权了别人的库备份一定要先和库的实际负责人确认备份窗口和影响尽量放在业务低峰期执行。6.3 后续扩展方向迁移完成后apiSQL 的定位变成了API 接入层而 PostgreSQL 变成了真正的数据底座。基于这个架构后续可以自然生长出几个能力物化视图如果业务方反复跑同一条聚合报表直接用 PG 的物化视图把结果预计算好查询速度能再上一个量级。只读副本如果未来 apiSQL 查询压力变大PG 加一个只读副本让报表查询和在线查询分离。迁移到云数据库云厂商托管的 PostgreSQL 通常自动帮你做高可用、备份、监控如果团队不想维护自建实例可以考虑。我们的计划是把这套数据底座再接一个数据同步工具定时把 PostgreSQL 里的核心表同步给数据仓库让 apiSQL 的数据真正融入公司的数据分析体系而不是孤岛。6.4 给后来者的几条实在建议最后总结几条实际操作中得来的经验每条都有血泪成分先跑通小数据量再上全量。不要一开始就导入千万行先导 100 行验证 schema、验证 apiSQL 的连接配置再放量。保留回滚通道。迁移完不要马上删掉 apiSQL 原来的数据文件至少保留一周确认线上运行稳定再清理。监控先行。迁移前就把 PG 的关键指标连接数、查询延迟、磁盘 IO接入告警不要等出了问题才想起来看监控。文档即资产。把表结构说明、同步脚本的 cron 配置、连接参数、upsert 逻辑写在项目 README 里下次有人接手不用从零猜。就我个人体会来说apiSQL 加 PostgreSQL 的组合既保留了 API 接入层灵活、快速迭代的优势又把数据可靠性、查询性能和生态工具链全部拉满了。如果你也正在面对类似的API 数据怎么落库的困惑我的建议是不要犹豫PostgreSQL 值得你为它花两周时间。