先交代一个背景我在一个数据量不算夸张、但又足够让“导出CSV到服务器上训练”变得难受的团队里完整摸索了一轮“数据库内机器学习”的落地路径。整个项目从最开始的数据出库、在Python环境里折腾数据清洗和特征工程到后面把数据和训练全部留在数据库里最终让模型作为数据库对象直接“入库”并对外提供预测中间踩了很多坑。如果你也正被“数据出库再入库”这个流程反复折磨或者你想知道怎么在数据库里把机器学习这件事真正跑通这篇实践记录值得花几分钟看完。即便你用的不是PostgreSQL里面关于特征固化、模型注册、推理函数封装和权限安全的思路一样可以抄作业。1. 库内机器学习到底治了谁的病1.1 传统“数据出库再入库”模式的成本与痛点先说最常见的场景。业务库是PostgreSQL或者MySQL每天有几百上千万行行为数据落进来。原来的建模流程通常是写SQL把需要的数据查出来导出成CSV传到一台带GPU或者大内存的机器上用pandas清洗用sklearn或者LightGBM训练模型文件保存成pickle然后推理阶段再写一个Python服务定时从数据库拉特征调用模型最后把预测结果写回库。这套流程看起来没毛病但真正维护过的人都知道问题一堆。首先数据在导出环节就开始变形字符编码、时间字段格式、数值精度每一个都可能让pandas读出来的DataFrame和数据库里看到的不一样。更麻烦的是训练用的特征SQL和推理时拉特征的SQL往往是两套代码今天改一个特征口径明天忘了同步模型上线之后预测结果就开始悄悄漂移等发现的时候已经跑了很久。其次是性能和安全。数据量一上来一次全量导出就是好几GB的CSV数据库IO被打满网络传输又慢。业务敏感数据每次都要拷贝出去合规审计压力也大有些场景业务方直接不给明细数据出库的权限整个建模流程卡在第一步。1.2 从“出库建模”到“库内建模”核心思路转变所谓库内机器学习核心思路很朴素训练数据不要拉出去直接在数据库里完成特征计算和模型训练训练好的模型也不要散落成一个文件而是序列化后存进数据库的模型注册表推理阶段更不要跨服务调来调去直接通过SQL函数调用模型给一个用户ID或者一行特征返回一个预测结果。我把这个思路拆解成三个原则一是特征逻辑只能有一份训练和推理都用同一个数据库视图从源头消灭特征口径不一致的问题二是模型必须入库所有模型版本、训练时间、评估指标、上线状态都记录在模型注册表里三是推理即查询预测函数就是一个SQL函数业务系统直接SELECT调用不需要额外部署模型服务。这个转变带来的好处有多直接特征口径统一之后模型上线前的联调时间从几天压缩到几小时。数据不出域之后安全团队那边基本不用再走审批流程。数据库本身承担了模型文件的管理回滚和切换版本就是一条UPDATE语句的事情。1.3 什么场景适合、什么场景不适合库内机器学习不要一听“库内机器学习”就觉得能取代所有机器学习工程体系它有清晰的边界。适合的场景表格型数据、中小数据量训练样本几十万到几千万行特征几十到几百个、使用sklearn系列模型或者树模型、预测结果不需要毫秒级响应但也不要跨服务来回传数据。比如用户流失预警、风控评分卡初筛、库存预测、设备故障分类这类任务在库内完成是绰绰有余的。不适合的场景深度学习或者大模型训练动辄几十GB的特征和分布式训练数据库根本扛不住特征量极大且训练样本上百亿行的场景那属于大数据平台和分布式训练框架的领域线上QPS要求每秒几千次的实时推理也别硬塞进数据库PL/Python的全局解释器锁会把你卡死。2. 技术选型为什么我最终锁定PostgreSQL PL/Python2.1 为什么不是SQL Server、Oracle或ClickHouse做技术选型的时候我对比了很多方案。SQL Server有Machine Learning Services官方支持R和Python通过sp_execute_external_script就能在存储过程里跑Python集成度很高。但问题是它对操作系统和版本要求比较严格我所在的团队当时主要跑在开源栈上不希望在SQL Server上绑定整个机器学习链路。Oracle有内建的DBMS_DATA_MINING包不需要写Python直接调用算法包。这一点很吸引人但算法灵活性有限想换一个Oracle没内置的算法就麻烦了。ClickHouse虽然很快但内置机器学习算法极少基本只有简单的线性回归之类的老古董复杂模型直接劝退。大厂的云数仓比如BigQuery ML、Snowflake ML做得确实很漂亮训练和预测都能通过SQL完成但我们是私有化部署要求数据不能往云上放这条路也断了。最后只剩下PostgreSQL PL/Python这条路走起来最顺畅原因很简单PostgreSQL的PL/Python插件允许我在数据库函数里直接写Python代码pandas、scikit-learn、joblib全部能用数据库本身支持BYTEA类型存二进制模型函数权限控制也还算成熟。2.2 主流方案对比一览库内ML到底有哪几条路如果你准备在自己的项目里尝试先对主流方案有个整体印象会很有帮助。方案优势短板适合场景PostgreSQL PL/Python通用性强算法自定义自由度最高SQL与Python边界清晰并发受GIL限制需控制推理QPS中小规模表格建模私有化部署SQL Server ML Services官方深度集成Python/R代码直接跑在存储过程里跨平台受限版本绑定重已经重度使用SQL Server的团队Oracle Data Mining内建大量算法零Python依赖算法扩展性差非Oracle环境无法迁移纯Oracle存量业务简单分类聚类ClickHouse极速查询个别回归算法内建算法覆盖太少复杂模型无力在ClickHouse里做数值型简单预测MADOlibApache老牌库内ML库算法丰富社区维护活跃度一般新算法跟进慢想完全不用Python的场景云数仓MLBigQuery ML/Snowflake MLSQL化程度高免运维强绑定云环境本地化困难数据已上云、商业合规允许的场景我的建议是不要只看算法列表重点看三件事训练和推理的SQL化程度高不高能不能支持自定义算法离线训练和在线推理的特征口径要怎么保持一致。最后这一点才是最影响长期维护的。2.3 环境准备与基础配置清单选定了方案之后环境准备其实比想象中简单。我用的是PostgreSQL 13以上版本让它自带plpython3u插件。注意不是plpythonu那是Python 2时代的东西我见过有人装错版本卡了半天。数据库服务器上需要安装的Python包包括pandas、numpy、scikit-learn、joblib。这个看似简单坑在于数据库服务运行时用的Python环境和你自己本地开发环境的Python不是同一个很多人直接在本地pip install了然后把函数部署到数据库里一执行就报ModuleNotFoundError。创建插件和确认版本可以用两条命令搞定CREATE EXTENSION IF NOT EXISTS plpython3u;# 在数据库服务器上确认Python路径和版本 python3 -V python3 -m pip list | grep -E pandas|scikit-learn|joblib千万不要跳过第二步。版本不一致会在模型反序列化时产生各种妖蛾子后面踩坑实录里我会专门讲。3. 库内训练全流程实现特征、训练、注册一条龙3.1 先把特征逻辑固化成一个视图训练和推理共用的关键我在项目里用一个“用户流失预测”的场景来做验证。特征来自用户信息表和订单行为表目标变量是用户未来30天是否流失。如果按传统方式我会写两套SQL一套在训练阶段跑另一套在推理阶段用时间一长就会长出两条不同的分支。库内ML的关键做法是把特征逻辑固化成一个数据库视图训练和推理都只查这一个视图。这样特征口径永远只有一份训练时模型看到的数据分布和推理时线上实时计算的特征天然就是一致的。CREATE VIEW feature_user_behavior AS SELECT u.user_id, date_part(day, now() - u.reg_time)::int AS reg_days, date_part(day, now() - u.last_login_time)::int AS inactive_days, COALESCE(o.order_cnt_30d, 0) AS order_cnt_30d, COALESCE(o.order_amt_30d, 0) AS order_amt_30d, CASE WHEN u.last_login_time now() - interval 30 days THEN 1 ELSE 0 END AS is_lost FROM user_info u LEFT JOIN ( SELECT user_id, count(*) AS order_cnt_30d, sum(order_amount) AS order_amt_30d FROM order_log WHERE order_time now() - interval 30 days GROUP BY user_id ) o ON o.user_id u.user_id;实际业务里特征比这个复杂得多有各种时间窗口聚合、漏斗转化、金额分位数统计但核心思想是一样的把所有特征一次性全部算好作为模型训练的输入。用视图还有一个好处底层表数据更新后视图结果自动跟着变重训模型非常方便。3.2 模型注册表设计模型入库的“元数据底座”模型训练完了不能直接扔到某个表里就不管需要设计一张模型注册表记录每个模型的元信息。这张表是整个“模型入库”流程的地基。我当时的表结构长这样字段类型说明model_idserial模型唯一IDmodel_nametext模型名称比如user_churn_modelversionint模型版本号model_typetext模型类型比如logistic_regression、xgbfeature_view_nametext训练用的特征视图名target_columntext目标变量列名model_objectbytea序列化后的模型二进制model_metricsjsonb准确率、AUC、混淆矩阵等指标statustextactive、candidate、archivetrained_attimestamptz训练完成时间feature_windowtext训练数据对应的特征时间范围status字段非常关键它实现了模型上线和回滚的语义。新模型训练完先标成candidate评估指标达标后更新为active历史模型自动变成archive。推理函数永远只取active且最新version的模型。建表语句大概长这样CREATE TABLE model_registry ( model_id serial PRIMARY KEY, model_name text NOT NULL, version int NOT NULL, model_type text, feature_view_name text, target_column text, model_object bytea, model_metrics jsonb, status text DEFAULT candidate, trained_at timestamptz DEFAULT now(), feature_window text, UNIQUE (model_name, version) );这张表支持了后续的一切模型上线、回滚、对比、审计。业务方问你“这个模型是什么时候训练的、用了哪些特征、效果指标是多少”一条SQL直接查出来不用再去翻训练脚本日志。3.3 训练函数实现从数据表到模型对象训练这一步我通过PL/Python写了一个训练函数。函数做的事情很简单从特征视图读数据转成pandas DataFrame做训练集和验证集划分训练模型评估指标最后把模型对象序列化写进模型注册表。CREATE OR REPLACE FUNCTION train_model(p_model_name text) RETURNS integer LANGUAGE plpython3u AS $$ import pandas as pd import joblib import io from sklearn.ensemble import GradientBoostingClassifier from sklearn.model_selection import train_test_split # 读取特征视图 rv plpy.execute(SELECT * FROM feature_user_behavior) # 转成DataFrame df pd.DataFrame(rv) # 类型处理plpy返回的numeric可能带Decimal包装 df[reg_days] df[reg_days].astype(float) df[inactive_days] df[inactive_days].astype(float) df[order_cnt_30d] df[order_cnt_30d].astype(float) df[order_amt_30d] df[order_amt_30d].astype(float) target df[is_lost].astype(int) features df.drop(columns[user_id, is_lost]) X_train, X_val, y_train, y_val train_test_split( features, target, test_size0.2, random_state42, stratifytarget ) model GradientBoostingClassifier(random_state42) model.fit(X_train, y_train) from sklearn.metrics import roc_auc_score y_pred model.predict_proba(X_val)[:, 1] auc roc_auc_score(y_val, y_pred) # 序列化模型 buf io.BytesIO() joblib.dump(model, buf) model_bytes buf.getvalue() # 写入模型注册表 version plpy.execute( SELECT coalesce(max(version), 0) 1 AS v FROM model_registry WHERE model_name p_model_name )[0][v] plan plpy.prepare( INSERT INTO model_registry (model_name, version, model_type, feature_view_name, target_column, model_object, model_metrics, status, feature_window) VALUES ($1, $2, $3, $4, $5, $6, $7, candidate, $8), [text, int, text, text, text, bytea, jsonb, text] ) plpy.execute(plan, [ p_model_name, version, gbt, feature_user_behavior, is_lost, model_bytes, {auc: round(auc, 4), train_rows: len(X_train), val_rows: len(X_val)}, 2024-01-01 至 2024-12-31 ]) return version $$;这里有一个细节值得展开说plpy.execute返回的结果对象不是纯Python原生类型数值字段可能是Decimal直接拿去做模型训练会报类型错误。我在代码里显式做了astype(float)处理。还有写入bytea字段时不能直接把Python的bytes塞进plpy.prepare的参数PostgreSQL的PL/Python会识别bytes类型并序列化成bytea这块在PostgreSQL 11以上的版本比较正常低版本最好先测试一下。3.4 训练细节采样、随机种子与内存控制在库里训练最怕的一件事就是把整张几百GB的表全部读进内存。虽然我建议先用中小数据量验证但你不控制好数据量数据库被搞挂是分分钟的事。我当时的做法是在训练函数里先用LIMIT或者TABLESAMPLE SYSTEM采样一部分数据做快速验证确认特征和代码没问题之后再对全量数据训练。不要一开始就闷头全量跑那种情况下出问题很难排查是特征SQL写错了还是模型参数不对都混在一起。-- 先用1%的抽样数据快速验证 SELECT * FROM feature_user_behavior TABLESAMPLE SYSTEM (1);采样这块还要注意类别不平衡问题如果正负样本差距特别大纯随机抽样可能把少数类抽没了。用train_test_split的时候一定要加stratifytarget我上面的代码里已经带上了。随机种子要固定。模型训练和SQL查询不一样SQL是确定性操作同一个查询返回相同结果但机器学习模型如果不固定random_state每次训练出来的模型都会有差异。固定好random_state重训过程才能复现排错的时候也方便。4. 推理落地把模型变成库里的一个SQL函数4.1 实时预测函数的实现与缓存优化训练完成只是项目的一半真正让模型“入库”产生价值的是推理阶段。我把预测能力封装成一个SQL函数业务系统调用这个函数就能拿到预测结果。CREATE OR REPLACE FUNCTION predict_user_churn(p_user_id int) RETURNS float LANGUAGE plpython3u AS $$ import joblib import io import pandas as pd # 获取active模型 if churn_model not in GD: rv plpy.execute( SELECT model_object FROM model_registry WHERE model_name user_churn_model AND status active ORDER BY version DESC LIMIT 1 ) if not rv: plpy.error(active model not found) model_bytes rv[0][model_object] # 如果是bytea类型PL/Python会自动转成memoryview需要转bytes if isinstance(model_bytes, memoryview): model_bytes model_bytes.tobytes() GD[churn_model] joblib.load(io.BytesIO(model_bytes)) model GD[churn_model] # 取用户特征 rv plpy.execute( SELECT reg_days, inactive_days, order_cnt_30d, order_amt_30d FROM feature_user_behavior WHERE user_id $1, [p_user_id] ) if not rv: return None row rv[0] features [[ float(row[reg_days]), float(row[inactive_days]), float(row[order_cnt_30d]), float(row[order_amt_30d]) ]] prob model.predict_proba(features)[0][1] return float(prob) $$;这儿我用了GD字典做模型缓存。GD是PL/Python提供的会话级全局字典同一个数据库会话内多次调用预测函数时模型对象只加载一次否则每次调用都要从bytea反序列化一次性能差得很明显。4.2 批量打分与任务调度单条预测适合在线接口但很多时候我们需要跑全量用户打分比如每天凌晨算一遍所有用户的流失概率写入结果表供下游使用。这时候如果写一个循环逐行调用predict_user_churn速度会非常感人因为函数调用和SQL执行都有固定开销。更高效的做法是写一个批量函数一次读取多行数据在Python内部循环预测然后一次性写回结果表。或者更简单一点直接用一条INSERT INTO ... SELECT让PostgreSQL优化器去跑INSERT INTO user_churn_daily (user_id, predict_score, score_date) SELECT user_id, predict_user_churn(user_id), current_date FROM user_info WHERE user_id IS NOT NULL;几十万用户这条SQL能撑住。再大的量比如千万级以上就要考虑分批跑避免事务太长。调度方面我用dolphinscheduler这类工具把“刷新特征数据 → 跑批量预测 → 写入结果表”串成一个定时工作流每天凌晨自动跑。这个思路和你用什么数据库引擎无关调度框架只是把整个流程自动化而已。4.3 模型上线、回滚与版本管理模型入库之后上线和回滚变得非常简单。新模型训练完状态是candidate。我手动查看model_metrics字段里的AUC跟线上active版本对比一下决定是否切换。切换就是一条UPDATE-- 先把老模型下线 UPDATE model_registry SET status archive WHERE model_name user_churn_model AND status active; -- 把新模型置为active UPDATE model_registry SET status active WHERE model_name user_churn_model AND version 5;如果上线后发现预测分布有问题回滚就是把上面两条SQL反过来跑一次。整个过程不需要重新部署服务不需要回滚代码数据库里改一条状态就行。还有一点值得注意active模型只保留一个但是如果想保留多个候选模型进行A/B对比可以把状态字段扩展成多个取值比如champion、challenger。我现在的做法是简化成candidate和active两态够用就行避免过度设计。5. 踩坑实录序列化、权限与性能相关的五个高频问题5.1 pickle模型与BYTEA字段的坑模型序列化成bytea存进数据库思路很直接但有两个隐蔽的问题。第一如果模型文件过大比如一个随机森林有几千棵树bytea字段会导致表迅速膨胀备份和恢复时间变长。第二PL/Python写入bytea时低版本PostgreSQL可能遇到bytes和memoryview类型转换的坑。我的规避方案很土但很好用先在Python里把模型二进制转成base64字符串存入text字段读取时再base64解码。虽然存储多占用一点空间但避免了格式兼容问题跨库迁移也更安全因为base64就是纯文本。import base64 model_b64 base64.b64encode(model_bytes).decode(ascii)读取的时候反过来model_bytes base64.b64decode(model_b64) model joblib.load(io.BytesIO(model_bytes))如果坚持用bytea至少确保PostgreSQL 12以上版本并且在测试环境跑一遍完整的写入和读取流程。5.2 PL/Python环境与数据库环境隔离的坑数据库服务器上的Python环境和你的开发机几乎肯定不一样。最常见的问题在开发机上pip install了scikit-learn然后PL/Python函数里import sklearn直接报ModuleNotFoundError。因为PL/Python用的是PostgreSQL内置的Python解释器不是你shell下输入python3打开的那个解释器两者可能路径不同、包版本不同。解决办法是直接往数据库服务进程使用的Python环境装包。我当时的做法是确认PostgreSQL是哪个Python编译的# 在数据库服务器上查看plpython3u对应的Python路径 pg_config --pythonversion然后找到对应的Python解释器路径去pip。另一个麻烦是多个Python版本共存时容易装错我的建议是尽量保持服务器只有一套Python环境或者至少确保数据库配置的Python路径和安装包的解释器路径完全一致。还有更隐蔽的坑scikit-learn版本不一致导致模型反序列化失败。训练时用1.2.0推理时环境是1.1.2joblib.load经常会报ModuleNotFoundError或者AttributeError。解决方式就是环境版本全部固定训练和推理都在同一个数据库实例上完成不要跨环境训完再灌进来。5.3 推理性能与PL/Python全局锁PL/Python是全解释器执行的Python的全局解释器锁GIL意味着同一时间只有一个Python函数在执行并发一高就明显瓶颈。我在压测实时预测函数时发现单连接连续调用的延迟还算能接受但并发20个连接同时调用predict_user_churn整个数据库的Python函数都在排队CPU占用率上去了但吞吐量上不去。这个问题的解法有几个一是控制并发让业务方通过连接池限制同时进入的请求数二是把大批量计算放到批量函数里一次调用处理多行数据比逐行调用函数高效得多三是如果确实需要高并发实时预测就老老实实把模型部署成独立服务不要硬塞在数据库里。库内ML解决的是中小规模和批处理场景不是高并发场景。5.4 特征不一致问题与漂移监控库内ML已经把特征口径统一到视图中了但还是可能遇到数据分布漂移的问题。比如模型是用2024年上半年的数据训练的下半年产品功能改版用户行为模式发生变化特征分布跟训练时有了明显差异模型效果就会衰减。我的实践是写一个简单的漂移检测脚本定期统计线上特征和训练基线特征的分布差异用PSIPopulation Stability Index或者简单的均值方差对比来判断。超过阈值就在模型管理群里告警提醒重新训练。SQL实现也很简单按特征分桶统计频率然后和对应用户训练数据的频率做对比。这里的核心原则是模型入库不代表一劳永逸监控和重训机制必须配套建起来。5.5 权限与安全不要让任何会话覆盖模型对象PL/Python的威力很大它能执行任意Python代码权限等级基本上等同于数据库超级用户。这意味着一旦某个数据库账号能创建PL/Python函数他就有能力在数据库服务器上执行系统命令这是巨大的安全风险。所以权限设计必须严格。我的做法是只有管理员账号能操作模型注册表的写操作应用账号只允许调用预测函数和读取结果表plpython3u语言本身只对受控的管理员角色开放普通业务账号没有创建PL/Python函数的权限。模型注册表的写入只能通过训练函数完成不要让应用账号直接对model_registry表执行INSERT或UPDATE否则一个误操作就能把线上模型覆盖掉。另外要提醒pickle反序列化本身也是安全风险恶意构造的pickle文件可以在反序列化时执行任意代码。所以模型对象只能从可信的模型注册表读取绝对不要接受外部上传的序列化文件。6. 最后说几句真实的体会整个项目从开始改造到稳定运行我最深的体会不是“数据库内跑模型有多快”而是“特征口径不再分叉”这件事带来的维护成本下降有多明显。以前团队里每个人对特征定义都有自己的理解训练一套、上线一套线上效果和离线评估永远对不上库里外来回导数据还特别耗时间。现在所有特征逻辑收敛到一个视图里模型训练和预测共用同一套口径问题定位清晰了很多。踩过几次坑之后我现在的原则很简单能用一条SQL梳理清楚的问题就不要先导出数据。当数据量或者模型复杂度确实撑不住库内方案的时候再老老实实把数据带出来只是这时候你会很清楚自己在做什么而不是无脑把全库的东西往外导。如果你也想尝试这条路我的建议是从一个小模型、中等数据量开始先把特征视图、模型注册表、权限隔离这三件事做完再考虑模型精度和性能优化这些基础不打牢后面全是债。 PostgreSQL库内机器学习实践：从数据出库到模型入库
先交代一个背景我在一个数据量不算夸张、但又足够让“导出CSV到服务器上训练”变得难受的团队里完整摸索了一轮“数据库内机器学习”的落地路径。整个项目从最开始的数据出库、在Python环境里折腾数据清洗和特征工程到后面把数据和训练全部留在数据库里最终让模型作为数据库对象直接“入库”并对外提供预测中间踩了很多坑。如果你也正被“数据出库再入库”这个流程反复折磨或者你想知道怎么在数据库里把机器学习这件事真正跑通这篇实践记录值得花几分钟看完。即便你用的不是PostgreSQL里面关于特征固化、模型注册、推理函数封装和权限安全的思路一样可以抄作业。1. 库内机器学习到底治了谁的病1.1 传统“数据出库再入库”模式的成本与痛点先说最常见的场景。业务库是PostgreSQL或者MySQL每天有几百上千万行行为数据落进来。原来的建模流程通常是写SQL把需要的数据查出来导出成CSV传到一台带GPU或者大内存的机器上用pandas清洗用sklearn或者LightGBM训练模型文件保存成pickle然后推理阶段再写一个Python服务定时从数据库拉特征调用模型最后把预测结果写回库。这套流程看起来没毛病但真正维护过的人都知道问题一堆。首先数据在导出环节就开始变形字符编码、时间字段格式、数值精度每一个都可能让pandas读出来的DataFrame和数据库里看到的不一样。更麻烦的是训练用的特征SQL和推理时拉特征的SQL往往是两套代码今天改一个特征口径明天忘了同步模型上线之后预测结果就开始悄悄漂移等发现的时候已经跑了很久。其次是性能和安全。数据量一上来一次全量导出就是好几GB的CSV数据库IO被打满网络传输又慢。业务敏感数据每次都要拷贝出去合规审计压力也大有些场景业务方直接不给明细数据出库的权限整个建模流程卡在第一步。1.2 从“出库建模”到“库内建模”核心思路转变所谓库内机器学习核心思路很朴素训练数据不要拉出去直接在数据库里完成特征计算和模型训练训练好的模型也不要散落成一个文件而是序列化后存进数据库的模型注册表推理阶段更不要跨服务调来调去直接通过SQL函数调用模型给一个用户ID或者一行特征返回一个预测结果。我把这个思路拆解成三个原则一是特征逻辑只能有一份训练和推理都用同一个数据库视图从源头消灭特征口径不一致的问题二是模型必须入库所有模型版本、训练时间、评估指标、上线状态都记录在模型注册表里三是推理即查询预测函数就是一个SQL函数业务系统直接SELECT调用不需要额外部署模型服务。这个转变带来的好处有多直接特征口径统一之后模型上线前的联调时间从几天压缩到几小时。数据不出域之后安全团队那边基本不用再走审批流程。数据库本身承担了模型文件的管理回滚和切换版本就是一条UPDATE语句的事情。1.3 什么场景适合、什么场景不适合库内机器学习不要一听“库内机器学习”就觉得能取代所有机器学习工程体系它有清晰的边界。适合的场景表格型数据、中小数据量训练样本几十万到几千万行特征几十到几百个、使用sklearn系列模型或者树模型、预测结果不需要毫秒级响应但也不要跨服务来回传数据。比如用户流失预警、风控评分卡初筛、库存预测、设备故障分类这类任务在库内完成是绰绰有余的。不适合的场景深度学习或者大模型训练动辄几十GB的特征和分布式训练数据库根本扛不住特征量极大且训练样本上百亿行的场景那属于大数据平台和分布式训练框架的领域线上QPS要求每秒几千次的实时推理也别硬塞进数据库PL/Python的全局解释器锁会把你卡死。2. 技术选型为什么我最终锁定PostgreSQL PL/Python2.1 为什么不是SQL Server、Oracle或ClickHouse做技术选型的时候我对比了很多方案。SQL Server有Machine Learning Services官方支持R和Python通过sp_execute_external_script就能在存储过程里跑Python集成度很高。但问题是它对操作系统和版本要求比较严格我所在的团队当时主要跑在开源栈上不希望在SQL Server上绑定整个机器学习链路。Oracle有内建的DBMS_DATA_MINING包不需要写Python直接调用算法包。这一点很吸引人但算法灵活性有限想换一个Oracle没内置的算法就麻烦了。ClickHouse虽然很快但内置机器学习算法极少基本只有简单的线性回归之类的老古董复杂模型直接劝退。大厂的云数仓比如BigQuery ML、Snowflake ML做得确实很漂亮训练和预测都能通过SQL完成但我们是私有化部署要求数据不能往云上放这条路也断了。最后只剩下PostgreSQL PL/Python这条路走起来最顺畅原因很简单PostgreSQL的PL/Python插件允许我在数据库函数里直接写Python代码pandas、scikit-learn、joblib全部能用数据库本身支持BYTEA类型存二进制模型函数权限控制也还算成熟。2.2 主流方案对比一览库内ML到底有哪几条路如果你准备在自己的项目里尝试先对主流方案有个整体印象会很有帮助。方案优势短板适合场景PostgreSQL PL/Python通用性强算法自定义自由度最高SQL与Python边界清晰并发受GIL限制需控制推理QPS中小规模表格建模私有化部署SQL Server ML Services官方深度集成Python/R代码直接跑在存储过程里跨平台受限版本绑定重已经重度使用SQL Server的团队Oracle Data Mining内建大量算法零Python依赖算法扩展性差非Oracle环境无法迁移纯Oracle存量业务简单分类聚类ClickHouse极速查询个别回归算法内建算法覆盖太少复杂模型无力在ClickHouse里做数值型简单预测MADOlibApache老牌库内ML库算法丰富社区维护活跃度一般新算法跟进慢想完全不用Python的场景云数仓MLBigQuery ML/Snowflake MLSQL化程度高免运维强绑定云环境本地化困难数据已上云、商业合规允许的场景我的建议是不要只看算法列表重点看三件事训练和推理的SQL化程度高不高能不能支持自定义算法离线训练和在线推理的特征口径要怎么保持一致。最后这一点才是最影响长期维护的。2.3 环境准备与基础配置清单选定了方案之后环境准备其实比想象中简单。我用的是PostgreSQL 13以上版本让它自带plpython3u插件。注意不是plpythonu那是Python 2时代的东西我见过有人装错版本卡了半天。数据库服务器上需要安装的Python包包括pandas、numpy、scikit-learn、joblib。这个看似简单坑在于数据库服务运行时用的Python环境和你自己本地开发环境的Python不是同一个很多人直接在本地pip install了然后把函数部署到数据库里一执行就报ModuleNotFoundError。创建插件和确认版本可以用两条命令搞定CREATE EXTENSION IF NOT EXISTS plpython3u;# 在数据库服务器上确认Python路径和版本 python3 -V python3 -m pip list | grep -E pandas|scikit-learn|joblib千万不要跳过第二步。版本不一致会在模型反序列化时产生各种妖蛾子后面踩坑实录里我会专门讲。3. 库内训练全流程实现特征、训练、注册一条龙3.1 先把特征逻辑固化成一个视图训练和推理共用的关键我在项目里用一个“用户流失预测”的场景来做验证。特征来自用户信息表和订单行为表目标变量是用户未来30天是否流失。如果按传统方式我会写两套SQL一套在训练阶段跑另一套在推理阶段用时间一长就会长出两条不同的分支。库内ML的关键做法是把特征逻辑固化成一个数据库视图训练和推理都只查这一个视图。这样特征口径永远只有一份训练时模型看到的数据分布和推理时线上实时计算的特征天然就是一致的。CREATE VIEW feature_user_behavior AS SELECT u.user_id, date_part(day, now() - u.reg_time)::int AS reg_days, date_part(day, now() - u.last_login_time)::int AS inactive_days, COALESCE(o.order_cnt_30d, 0) AS order_cnt_30d, COALESCE(o.order_amt_30d, 0) AS order_amt_30d, CASE WHEN u.last_login_time now() - interval 30 days THEN 1 ELSE 0 END AS is_lost FROM user_info u LEFT JOIN ( SELECT user_id, count(*) AS order_cnt_30d, sum(order_amount) AS order_amt_30d FROM order_log WHERE order_time now() - interval 30 days GROUP BY user_id ) o ON o.user_id u.user_id;实际业务里特征比这个复杂得多有各种时间窗口聚合、漏斗转化、金额分位数统计但核心思想是一样的把所有特征一次性全部算好作为模型训练的输入。用视图还有一个好处底层表数据更新后视图结果自动跟着变重训模型非常方便。3.2 模型注册表设计模型入库的“元数据底座”模型训练完了不能直接扔到某个表里就不管需要设计一张模型注册表记录每个模型的元信息。这张表是整个“模型入库”流程的地基。我当时的表结构长这样字段类型说明model_idserial模型唯一IDmodel_nametext模型名称比如user_churn_modelversionint模型版本号model_typetext模型类型比如logistic_regression、xgbfeature_view_nametext训练用的特征视图名target_columntext目标变量列名model_objectbytea序列化后的模型二进制model_metricsjsonb准确率、AUC、混淆矩阵等指标statustextactive、candidate、archivetrained_attimestamptz训练完成时间feature_windowtext训练数据对应的特征时间范围status字段非常关键它实现了模型上线和回滚的语义。新模型训练完先标成candidate评估指标达标后更新为active历史模型自动变成archive。推理函数永远只取active且最新version的模型。建表语句大概长这样CREATE TABLE model_registry ( model_id serial PRIMARY KEY, model_name text NOT NULL, version int NOT NULL, model_type text, feature_view_name text, target_column text, model_object bytea, model_metrics jsonb, status text DEFAULT candidate, trained_at timestamptz DEFAULT now(), feature_window text, UNIQUE (model_name, version) );这张表支持了后续的一切模型上线、回滚、对比、审计。业务方问你“这个模型是什么时候训练的、用了哪些特征、效果指标是多少”一条SQL直接查出来不用再去翻训练脚本日志。3.3 训练函数实现从数据表到模型对象训练这一步我通过PL/Python写了一个训练函数。函数做的事情很简单从特征视图读数据转成pandas DataFrame做训练集和验证集划分训练模型评估指标最后把模型对象序列化写进模型注册表。CREATE OR REPLACE FUNCTION train_model(p_model_name text) RETURNS integer LANGUAGE plpython3u AS $$ import pandas as pd import joblib import io from sklearn.ensemble import GradientBoostingClassifier from sklearn.model_selection import train_test_split # 读取特征视图 rv plpy.execute(SELECT * FROM feature_user_behavior) # 转成DataFrame df pd.DataFrame(rv) # 类型处理plpy返回的numeric可能带Decimal包装 df[reg_days] df[reg_days].astype(float) df[inactive_days] df[inactive_days].astype(float) df[order_cnt_30d] df[order_cnt_30d].astype(float) df[order_amt_30d] df[order_amt_30d].astype(float) target df[is_lost].astype(int) features df.drop(columns[user_id, is_lost]) X_train, X_val, y_train, y_val train_test_split( features, target, test_size0.2, random_state42, stratifytarget ) model GradientBoostingClassifier(random_state42) model.fit(X_train, y_train) from sklearn.metrics import roc_auc_score y_pred model.predict_proba(X_val)[:, 1] auc roc_auc_score(y_val, y_pred) # 序列化模型 buf io.BytesIO() joblib.dump(model, buf) model_bytes buf.getvalue() # 写入模型注册表 version plpy.execute( SELECT coalesce(max(version), 0) 1 AS v FROM model_registry WHERE model_name p_model_name )[0][v] plan plpy.prepare( INSERT INTO model_registry (model_name, version, model_type, feature_view_name, target_column, model_object, model_metrics, status, feature_window) VALUES ($1, $2, $3, $4, $5, $6, $7, candidate, $8), [text, int, text, text, text, bytea, jsonb, text] ) plpy.execute(plan, [ p_model_name, version, gbt, feature_user_behavior, is_lost, model_bytes, {auc: round(auc, 4), train_rows: len(X_train), val_rows: len(X_val)}, 2024-01-01 至 2024-12-31 ]) return version $$;这里有一个细节值得展开说plpy.execute返回的结果对象不是纯Python原生类型数值字段可能是Decimal直接拿去做模型训练会报类型错误。我在代码里显式做了astype(float)处理。还有写入bytea字段时不能直接把Python的bytes塞进plpy.prepare的参数PostgreSQL的PL/Python会识别bytes类型并序列化成bytea这块在PostgreSQL 11以上的版本比较正常低版本最好先测试一下。3.4 训练细节采样、随机种子与内存控制在库里训练最怕的一件事就是把整张几百GB的表全部读进内存。虽然我建议先用中小数据量验证但你不控制好数据量数据库被搞挂是分分钟的事。我当时的做法是在训练函数里先用LIMIT或者TABLESAMPLE SYSTEM采样一部分数据做快速验证确认特征和代码没问题之后再对全量数据训练。不要一开始就闷头全量跑那种情况下出问题很难排查是特征SQL写错了还是模型参数不对都混在一起。-- 先用1%的抽样数据快速验证 SELECT * FROM feature_user_behavior TABLESAMPLE SYSTEM (1);采样这块还要注意类别不平衡问题如果正负样本差距特别大纯随机抽样可能把少数类抽没了。用train_test_split的时候一定要加stratifytarget我上面的代码里已经带上了。随机种子要固定。模型训练和SQL查询不一样SQL是确定性操作同一个查询返回相同结果但机器学习模型如果不固定random_state每次训练出来的模型都会有差异。固定好random_state重训过程才能复现排错的时候也方便。4. 推理落地把模型变成库里的一个SQL函数4.1 实时预测函数的实现与缓存优化训练完成只是项目的一半真正让模型“入库”产生价值的是推理阶段。我把预测能力封装成一个SQL函数业务系统调用这个函数就能拿到预测结果。CREATE OR REPLACE FUNCTION predict_user_churn(p_user_id int) RETURNS float LANGUAGE plpython3u AS $$ import joblib import io import pandas as pd # 获取active模型 if churn_model not in GD: rv plpy.execute( SELECT model_object FROM model_registry WHERE model_name user_churn_model AND status active ORDER BY version DESC LIMIT 1 ) if not rv: plpy.error(active model not found) model_bytes rv[0][model_object] # 如果是bytea类型PL/Python会自动转成memoryview需要转bytes if isinstance(model_bytes, memoryview): model_bytes model_bytes.tobytes() GD[churn_model] joblib.load(io.BytesIO(model_bytes)) model GD[churn_model] # 取用户特征 rv plpy.execute( SELECT reg_days, inactive_days, order_cnt_30d, order_amt_30d FROM feature_user_behavior WHERE user_id $1, [p_user_id] ) if not rv: return None row rv[0] features [[ float(row[reg_days]), float(row[inactive_days]), float(row[order_cnt_30d]), float(row[order_amt_30d]) ]] prob model.predict_proba(features)[0][1] return float(prob) $$;这儿我用了GD字典做模型缓存。GD是PL/Python提供的会话级全局字典同一个数据库会话内多次调用预测函数时模型对象只加载一次否则每次调用都要从bytea反序列化一次性能差得很明显。4.2 批量打分与任务调度单条预测适合在线接口但很多时候我们需要跑全量用户打分比如每天凌晨算一遍所有用户的流失概率写入结果表供下游使用。这时候如果写一个循环逐行调用predict_user_churn速度会非常感人因为函数调用和SQL执行都有固定开销。更高效的做法是写一个批量函数一次读取多行数据在Python内部循环预测然后一次性写回结果表。或者更简单一点直接用一条INSERT INTO ... SELECT让PostgreSQL优化器去跑INSERT INTO user_churn_daily (user_id, predict_score, score_date) SELECT user_id, predict_user_churn(user_id), current_date FROM user_info WHERE user_id IS NOT NULL;几十万用户这条SQL能撑住。再大的量比如千万级以上就要考虑分批跑避免事务太长。调度方面我用dolphinscheduler这类工具把“刷新特征数据 → 跑批量预测 → 写入结果表”串成一个定时工作流每天凌晨自动跑。这个思路和你用什么数据库引擎无关调度框架只是把整个流程自动化而已。4.3 模型上线、回滚与版本管理模型入库之后上线和回滚变得非常简单。新模型训练完状态是candidate。我手动查看model_metrics字段里的AUC跟线上active版本对比一下决定是否切换。切换就是一条UPDATE-- 先把老模型下线 UPDATE model_registry SET status archive WHERE model_name user_churn_model AND status active; -- 把新模型置为active UPDATE model_registry SET status active WHERE model_name user_churn_model AND version 5;如果上线后发现预测分布有问题回滚就是把上面两条SQL反过来跑一次。整个过程不需要重新部署服务不需要回滚代码数据库里改一条状态就行。还有一点值得注意active模型只保留一个但是如果想保留多个候选模型进行A/B对比可以把状态字段扩展成多个取值比如champion、challenger。我现在的做法是简化成candidate和active两态够用就行避免过度设计。5. 踩坑实录序列化、权限与性能相关的五个高频问题5.1 pickle模型与BYTEA字段的坑模型序列化成bytea存进数据库思路很直接但有两个隐蔽的问题。第一如果模型文件过大比如一个随机森林有几千棵树bytea字段会导致表迅速膨胀备份和恢复时间变长。第二PL/Python写入bytea时低版本PostgreSQL可能遇到bytes和memoryview类型转换的坑。我的规避方案很土但很好用先在Python里把模型二进制转成base64字符串存入text字段读取时再base64解码。虽然存储多占用一点空间但避免了格式兼容问题跨库迁移也更安全因为base64就是纯文本。import base64 model_b64 base64.b64encode(model_bytes).decode(ascii)读取的时候反过来model_bytes base64.b64decode(model_b64) model joblib.load(io.BytesIO(model_bytes))如果坚持用bytea至少确保PostgreSQL 12以上版本并且在测试环境跑一遍完整的写入和读取流程。5.2 PL/Python环境与数据库环境隔离的坑数据库服务器上的Python环境和你的开发机几乎肯定不一样。最常见的问题在开发机上pip install了scikit-learn然后PL/Python函数里import sklearn直接报ModuleNotFoundError。因为PL/Python用的是PostgreSQL内置的Python解释器不是你shell下输入python3打开的那个解释器两者可能路径不同、包版本不同。解决办法是直接往数据库服务进程使用的Python环境装包。我当时的做法是确认PostgreSQL是哪个Python编译的# 在数据库服务器上查看plpython3u对应的Python路径 pg_config --pythonversion然后找到对应的Python解释器路径去pip。另一个麻烦是多个Python版本共存时容易装错我的建议是尽量保持服务器只有一套Python环境或者至少确保数据库配置的Python路径和安装包的解释器路径完全一致。还有更隐蔽的坑scikit-learn版本不一致导致模型反序列化失败。训练时用1.2.0推理时环境是1.1.2joblib.load经常会报ModuleNotFoundError或者AttributeError。解决方式就是环境版本全部固定训练和推理都在同一个数据库实例上完成不要跨环境训完再灌进来。5.3 推理性能与PL/Python全局锁PL/Python是全解释器执行的Python的全局解释器锁GIL意味着同一时间只有一个Python函数在执行并发一高就明显瓶颈。我在压测实时预测函数时发现单连接连续调用的延迟还算能接受但并发20个连接同时调用predict_user_churn整个数据库的Python函数都在排队CPU占用率上去了但吞吐量上不去。这个问题的解法有几个一是控制并发让业务方通过连接池限制同时进入的请求数二是把大批量计算放到批量函数里一次调用处理多行数据比逐行调用函数高效得多三是如果确实需要高并发实时预测就老老实实把模型部署成独立服务不要硬塞在数据库里。库内ML解决的是中小规模和批处理场景不是高并发场景。5.4 特征不一致问题与漂移监控库内ML已经把特征口径统一到视图中了但还是可能遇到数据分布漂移的问题。比如模型是用2024年上半年的数据训练的下半年产品功能改版用户行为模式发生变化特征分布跟训练时有了明显差异模型效果就会衰减。我的实践是写一个简单的漂移检测脚本定期统计线上特征和训练基线特征的分布差异用PSIPopulation Stability Index或者简单的均值方差对比来判断。超过阈值就在模型管理群里告警提醒重新训练。SQL实现也很简单按特征分桶统计频率然后和对应用户训练数据的频率做对比。这里的核心原则是模型入库不代表一劳永逸监控和重训机制必须配套建起来。5.5 权限与安全不要让任何会话覆盖模型对象PL/Python的威力很大它能执行任意Python代码权限等级基本上等同于数据库超级用户。这意味着一旦某个数据库账号能创建PL/Python函数他就有能力在数据库服务器上执行系统命令这是巨大的安全风险。所以权限设计必须严格。我的做法是只有管理员账号能操作模型注册表的写操作应用账号只允许调用预测函数和读取结果表plpython3u语言本身只对受控的管理员角色开放普通业务账号没有创建PL/Python函数的权限。模型注册表的写入只能通过训练函数完成不要让应用账号直接对model_registry表执行INSERT或UPDATE否则一个误操作就能把线上模型覆盖掉。另外要提醒pickle反序列化本身也是安全风险恶意构造的pickle文件可以在反序列化时执行任意代码。所以模型对象只能从可信的模型注册表读取绝对不要接受外部上传的序列化文件。6. 最后说几句真实的体会整个项目从开始改造到稳定运行我最深的体会不是“数据库内跑模型有多快”而是“特征口径不再分叉”这件事带来的维护成本下降有多明显。以前团队里每个人对特征定义都有自己的理解训练一套、上线一套线上效果和离线评估永远对不上库里外来回导数据还特别耗时间。现在所有特征逻辑收敛到一个视图里模型训练和预测共用同一套口径问题定位清晰了很多。踩过几次坑之后我现在的原则很简单能用一条SQL梳理清楚的问题就不要先导出数据。当数据量或者模型复杂度确实撑不住库内方案的时候再老老实实把数据带出来只是这时候你会很清楚自己在做什么而不是无脑把全库的东西往外导。如果你也想尝试这条路我的建议是从一个小模型、中等数据量开始先把特征视图、模型注册表、权限隔离这三件事做完再考虑模型精度和性能优化这些基础不打牢后面全是债。