一、为什么从 0 搭建银行 IT 圈里用户画像系统是最常见的核心数据层。我做了 8 年 Java 后端亲眼见过MySQL EAV 模型——灵活但查询性能差MongoDB 文档型——灵活但 ACID 弱Oracle JSON——贵 不算国产PostgreSQL JSONB Citus——✅ 灵活 ACID 强 信创合规这次从 0 搭建一个 demo——不依赖任何公司项目纯个人开源GitHub 上有完整代码。二、技术选型5 个对比维度2.1 选 PostgreSQL JSONB Citus 的 5 个理由维度MySQLMongoDBPostgreSQL JSONB Citus灵活 schema❌ 严格 schema✅ 文档嵌套✅JSONB 字段零停机加字段❌ ALTER TABLE 锁表✅ 直接写字段✅JSONB 合并ACID 事务✅ 强⚠️ 4.0 才支持✅强水平扩展⚠️ 分库分表复杂✅ 分片集群✅Citus 分片信创合规⚠️ MySQL 不算❌ 国外 SSPL✅国产衍生2.2 PostgreSQL vs MySQL 的关键差异维度MySQL 8PostgreSQL 16JSON 支持JSON 函数有限✅JSONB 二进制存储 GIN 索引复杂查询简单 SQL✅CTE / 窗口函数 / 复杂 join扩展有限✅Citus / pgvector / PostGIS性能千万级50ms25msGIN 索引国产衍生❌ OceanBase 衍生✅openGauss / GaussDB 都基于 PG2.3 Citus vs 原生分库分表维度ShardingSphereCitus部署方式应用层数据库层应用改造成本⚠️ 中等要改 SQL✅几乎为零透明分片跨 shard JOIN⚠️ 复杂✅Coordinator 自动下推学习曲线⚠️ 中等✅平缓标准 PG 语法国产衍生❌ 无✅PG 同源业务价值从技术到生意的桥梁写在前面的为什么这篇值得读完——技术指标的 200ms / 1000 万数据归根结底是要让业务方敢用、愿用、天天用。角色痛点本方案带来的变化运营想做上海 25-30 岁金融理财潜在客户活动IT 排期 2 周才能出名单5 分钟出活动名单活动上线周期从 2 周 → 1 天销售不知道客户最近关注什么产品盲目推销3 秒看到客户 30 天浏览 / 关注 / 行为推荐精准度 30%产品经理想看新功能上线后用户留存变化每次让 IT 写 SQL桑基图自助分析本 demo 第九章留存曲线 1 分钟出图合规《个人信息保护法》/ 银保监数据脱敏要求严格敏感字段脱敏 角色权限分离合规审计一次过创业公司 CTO想自建画像系统但没人力从头开发Docker 一键部署5 分钟跑通1 个后端能扛 1000 万用户桑基图 业务价值最直观的展示窗口第九章会演示怎么从 JSONB 数组 → 业务可读的留存漏斗老板/产品/运营都能看懂不只是 DBA 和开发的玩具三、数据模型设计3.1 核心表user_profileCREATE TABLE user_profile ( user_id BIGINT PRIMARY KEY, base_info JSONB NOT NULL, -- 基础属性 tags JSONB NOT NULL DEFAULT {}, -- 200 维度标签 behavior_timeline JSONB NOT NULL DEFAULT [], -- 行为时间线 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() );3.2 关键设计设计 1JSONB 字段分类base_info基础属性年龄/性别/城市/VIP 等级tags业务标签200 维度behavior_timeline行为时间线最近 100 条设计 2分片键选 user_id90% 查询都带 user_id →查询本地化user_id 是 BIGINT 自增 →分布均匀避免跨 shard 事务设计 3关键索引-- GIN 索引JSONB 通用查询 CREATE INDEX idx_tags_gin ON user_profile USING GIN (tags); -- 路径表达式索引高频字段比 GIN 快 3-5x CREATE INDEX idx_city ON user_profile ((base_info-city)); CREATE INDEX idx_age ON user_profile (((base_info-age)::int)); -- 时间索引 CREATE INDEX idx_updated_at ON user_profile (updated_at DESC);3.3 性能数据索引策略标签 JSONB 包含查耗时提升无索引2000ms全表扫描1xGIN 索引45ms44x路径表达式索引15ms133x四、Spring Boot 工程化4.1 项目结构pg-user-profile-demo/ ├── pom.xml # Maven 配置 ├── docker-compose.yml # PG Citus 部署 ├── src/main/java/com/demo/pgprofile/ │ ├── PgUserProfileDemoApplication.java │ ├── controller/UserProfileController.java # REST API │ ├── service/UserProfileService.java # 业务逻辑 │ ├── repository/UserProfileRepository.java # 数据访问 │ ├── entity/UserProfile.java # JPA 实体 │ ├── dto/UserProfileDTO.java # DTO │ └── config/JsonbConfig.java # JSON 配置 └── src/main/resources/ ├── application.yml ├── schema.sql # 建表 SQL └── data.sql # 测试数据4.2 核心代码JSONB 字段映射Entity Table(name user_profile) public class UserProfile { Id private Long userId; // JSONB ↔ JsonNode用 Hypersistence Utils Type(JsonBinaryType.class) Column(columnDefinition jsonb) private JsonNode baseInfo; Type(JsonBinaryType.class) Column(columnDefinition jsonb) private JsonNode tags; // ... createdAt / updatedAt }关键Type(JsonBinaryType.class)columnDefinition jsonb让 Hibernate 正确处理 JSONB。4.3 核心代码5 个高频查询Repository public interface UserProfileRepository extends JpaRepositoryUserProfile, Long { // Q1: 按城市 年龄 标签查 Query(value SELECT * FROM user_profile WHERE (base_info-age)::int BETWEEN :minAge AND :maxAge AND base_info-city :city AND tags CAST(:tagFilter AS jsonb) LIMIT :limit , nativeQuery true) ListUserProfile findByCityAndTag( Param(minAge) int minAge, Param(maxAge) int maxAge, Param(city) String city, Param(tagFilter) String tagFilter, Param(limit) int limit); // Q2: 按多个标签查 Query(value SELECT * FROM user_profile WHERE tags CAST(:tagsFilter AS jsonb) LIMIT :limit , nativeQuery true) ListUserProfile findByTags(Param(tagsFilter) String tagsFilter, Param(limit) int limit); // Q3: 按城市聚合 Query(value SELECT base_info-city AS city, COUNT(*) AS user_count, AVG((tags-金融理财)::int) AS avg_finance_score FROM user_profile WHERE (tags-金融理财)::int 0 GROUP BY base_info-city ORDER BY user_count DESC LIMIT :limit , nativeQuery true) ListObject[] aggregateByCity(Param(limit) int limit); // Q4: 标签增量合并关键零停机 Query(value UPDATE user_profile SET tags tags || CAST(:newTag AS jsonb) WHERE user_id :userId , nativeQuery true) int mergeTag(Param(userId) Long userId, Param(newTag) String newTag); }4.4 REST API 设计RestController RequestMapping(/profiles) public class UserProfileController { PostMapping // 创建 public UserProfileDTO create(RequestBody UserProfileDTO dto); GetMapping(/{userId}) // 按 ID 查 public UserProfileDTO findByUserId(PathVariable Long userId); GetMapping(/search) // 城市 年龄 标签 public ListUserProfileDTO search( RequestParam String city, RequestParam int minAge, RequestParam int maxAge, RequestParam String tagName, RequestParam int minScore, RequestParam int limit); GetMapping(/aggregate/city) // 城市聚合 public ListObject[] aggregateByCity(RequestParam int limit); PostMapping(/{userId}/tags) // 标签合并 public MapString, Object mergeTag( PathVariable Long userId, RequestBody JsonNode tagUpdate); }五、关键设计零停机加字段5.1 传统 MySQL 的痛点-- 加新标签传统 MySQL 写法 ALTER TABLE user_profile ADD COLUMN vip_level INT; -- 锁表 30 秒-几分钟 -- 1000 万行表 → 业务停服5.2 PostgreSQL JSONB 的优雅方案-- 加新标签JSONB 合并写法 UPDATE user_profile SET tags tags || {vip_level: 5}::jsonb WHERE user_id 123; -- 1000 万行表 → 0 锁表毫秒级 -- 业务零停机5.3 业务层封装java Transactional public int mergeTag(Long userId, String tagName, int score) { String newTag { tagName : score };int updated repository.mergeTag(userId, newTag); log.info(Merged tag: userId{}, tagName{}, score{}, updated{}, userId, tagName, score, updated); return updated;}**业务价值** - ✅ **加新业务标签零停机**——200 维度随时加 - ✅ **不需要 DDL**——业务上线速度提升 **7 倍** - ✅ **不需要锁表**——金融业务连续性 100% --- ## 六、Citus 分片集群部署 ### 6.1 分布式表1 行 SQL sql -- 1. 创建协调节点 4 worker 节点已通过 docker-compose 启动 -- 2. 关键一行分片表 SELECT create_distributed_table(user_profile, user_id);6.2 分片规则hash(user_id) % 4 0 → Shard 1user_id 0-250w hash(user_id) % 4 1 → Shard 2user_id 250-500w hash(user_id) % 4 2 → Shard 3user_id 500-750w hash(user_id) % 4 3 → Shard 4user_id 750w-1亿6.3 性能对比数据量单机 P994 分片 P99提升100 万5ms3ms1.7x500 万20ms8ms2.5x1000 万80ms25ms3.2x1 亿不推荐120ms—七、Docker 一键部署5 分钟跑通7.1 docker-compose.ymlversion: 3.8 services: pg-citus: image: citusdata/citus:12.1 environment: POSTGRES_PASSWORD: postgres ports: - 5432:5432 volumes: - ./src/main/resources/schema.sql:/docker-entrypoint-initdb.d/01-schema.sql - ./src/main/resources/data.sql:/docker-entrypoint-initdb.d/02-data.sql app: build: . depends_on: - pg-citus ports: - 8080:80807.2 启动步骤# 1. 启动 PG Citus docker-compose up -d pg-citus # 2. 跑 Spring Boot ./mvnw spring-boot:run # 3. 测试 curl http://localhost:8080/api/profiles/15 分钟跑通。八、关键设计总结8.1 5 个关键决策1.PostgreSQL JSONB Citus——金融级 灵活 schema 信创合规2.JSONB 字段分类——base_info / tags / behavior_timeline3.GIN 路径表达式索引——查询性能 44x 提升4.JSONB 合并实现零停机加字段——业务上线 7 倍提速5.Citus 4 分片横向扩展——1 亿数据 P99 200ms8.2 3 个踩过的坑1.❌ 没用 GIN 索引 →2000ms 全表扫描2.❌ 没用路径表达式索引 →GIN 索引还是慢3.❌ 直接用 MySQL →没有信创合规 没有 JSONB8.3 给 Java 后端的 3 条建议1.✅学 PostgreSQL 必学 JSONB——比 MongoDB 更通用2.✅学 Citus 分片——单机扛不住就要分片3.✅懂信创合规——2027 央国企 100% 国产化是硬要求九、用户留存分析实战JSONB 数组 → 桑基图真实运营场景产品经理说看一下 7 月用户留存曲线——传统 MySQL 要查 behavior_log 表 复杂 JOIN 多 GROUP BYJSONB 方案一个 SQL 搞定 一个前端页面可视化。9.1 业务背景每个用户的behavior_timelineJSONB 数组里存了最近 5-12 条行为事件[ {event: login, timestamp: 2026-07-01T09:23:11Z}, {event: view, timestamp: 2026-07-01T11:45:33Z}, {event: click, timestamp: 2026-07-02T14:08:55Z}, {event: purchase, timestamp: 2026-07-05T20:12:09Z} ]要做Day 1 活跃用户 → 第 N 天还活跃的留存率。9.2 核心 SQLJSONB 数组拆解 留存率-- 第一步把 JSONB 数组展平成行 WITH daily_active AS ( SELECT user_id, DATE((elem-timestamp)::timestamptz) as active_date FROM user_profile CROSS JOIN LATERAL jsonb_array_elements(behavior_timeline) as elem WHERE (elem-timestamp) IS NOT NULL AND (elem-timestamp) ! ), -- 第二步找 Day 1 活跃用户集合 day1_users AS ( SELECT DISTINCT user_id FROM daily_active WHERE active_date :day1Date ), -- 第三步每个 Day 的留存数Day 1 活跃 ∩ 当天活跃 retention AS ( SELECT d.active_date, COUNT(DISTINCT d.user_id) as retained_users FROM daily_active d INNER JOIN day1_users d1 ON d.user_id d1.user_id WHERE d.active_date BETWEEN :day1Date AND :endDate GROUP BY d.active_date ORDER BY d.active_date ) SELECT :day1Date::text as day_label, retained_users FROM retention UNION ALL SELECT Day || EXTRACT(DAY FROM (active_date - :day1Date::date))::text as day_label, retained_users FROM retention WHERE active_date :day1Date;3 个关键技术点1.CROSS JOIN LATERAL jsonb_array_elements(behavior_timeline)—— 把每行的 JSONB 数组展平成多行等价于 UNNEST 但保持 JSONB 原生操作2.(elem-timestamp)::timestamptz——-拿 JSON 字符串::timestamptz转 PG 时间类型3.EXTRACT(DAY FROM (active_date - :day1Date::date))—— 计算 Day N 标签9.3 后端 Service转 ECharts 格式public MapString, Object retentionSankey(String day1Date, String endDate) { ListObject[] rows repository.retentionSankey(day1Date, endDate); ListString dayLabels new ArrayList(); ListLong retainedCounts new ArrayList(); for (Object[] row : rows) { dayLabels.add((String) row[0]); retainedCounts.add(((Number) row[1]).longValue()); } // nodes: 每个 day 一个节点 ListMapString, Object nodes dayLabels.stream() .map(label - Map.of(name, label)) .toList(); // links: Day N → Day N1value Day N1 留存数 ListMapString, Object links new ArrayList(); for (int i 0; i dayLabels.size() - 1; i) { links.add(Map.of( source, dayLabels.get(i), target, dayLabels.get(i 1), value, retainedCounts.get(i 1) )); } MapString, Object result new LinkedHashMap(); result.put(nodes, nodes); result.put(links, links); result.put(summary, Map.of( day1_users, retainedCounts.get(0), last_day_retained, retainedCounts.get(retainedCounts.size() - 1), retention_rate, String.format(%.2f%%, retainedCounts.get(retainedCounts.size() - 1) * 100.0 / retainedCounts.get(0)) )); return result; }9.4 前端 ECharts 桑基图CDN 5 分钟集成script srchttps://cdn.jsdelivr.net/npm/echarts5.4.3/dist/echarts.min.js/script div idsankey-chart stylewidth:100%;height:600px;/div script const chart echarts.init(document.getElementById(sankey-chart)); fetch(/api/profiles/retention/sankey?day12026-07-01end2026-07-30) .then(r r.json()) .then(data { chart.setOption({ series: [{ type: sankey, data: data.nodes, links: data.links, lineStyle: { color: gradient, curveness: 0.5 } }] }); }); /script配 levels 按层数渐变绿→红Day 1 绿Day 30 红流失路径一眼可见。9.5 效果展示启动 demo 后访问 http://localhost:8080/sankey.html指标卡Day 1 活跃 10000 → 末日留存 ~1000 → 留存率 ~10%桑基图典型漏斗形从 Day 1 100% 逐渐缩窄到 Day 30 10%日活柱状图每日活跃用户分布9.6 这套方案的 3 个生产价值1.0 表 JOIN—— 不需要单独的 behavior_log 表全部在主表的 JSONB 数组里2.1 个 SQL 查留存—— 传统 MySQL 需要 behavior_log user_active 多层 GROUP BYPG 一个 CTE 搞定3.可视化门槛低—— 后端只负责转 ECharts 格式前端 50 行代码渲染真实生产中这套方案可以用在任何用户行为 时间维度分析场景留存、活跃、转化、复购。十、灵活查询实战公共模板 条件构造器真实痛点运营想做上海 25-30 岁金融理财潜在客户活动传统方式要 IT 排期 2 周写 SQL本方案让业务方 5 分钟自助出名单——这是 SaaS 时代真正的速度优势。10.1 为什么需要公共查询模板反面案例很多 SaaS 的死法-- 业务方想查上海金融理财高分用户 -- 传统方式找 IT 写 SQL SELECT * FROM user_profile WHERE base_info-city 上海 AND (tags-金融理财)::int 70; -- 周期: 2-3 天 (提需求 → 排期 → 写代码 → 提测 → 上线)正面方案公共模板 条件构造器1. 业务方打开 /template-search.html 2. 选模板金融营销·高潜客户挖掘 3. 选城市上海金融理财分70 4. 点执行查询 → 3 秒出 500 人名单 → 一键导出 CSV \ **周期: 5 分钟**业务方完全自助。 ### 10.2 核心设计4 层抽象 | 层级 | 作用 | 实现 | |---|---|---| | **L1 模板定义** | 预设 5-10 个金融营销场景 | QueryTemplate.java | | **L2 字段白名单** | 防 SQL 注入只允许模板定义的字段 | TemplateService.buildSpec() 强校验 | | **L3 Specification 拼装** | 动态拼查询JPA 标准 | JpaSpecificationExecutorUserProfile | | **L4 前端动态渲染** | 模板字段 → 表单 → 调 API | template-search.html | ### 10.3 5 个预设金融营销模板// TemplateService.java 5 个模板 1. marketing-high-potential 金融营销·高潜客户挖掘 2. risk-control 风控·异常行为识别 3. churn-prediction 运营·流失风险预测 4. sales-followup 销售·重点跟进客户 5. ab-test-grouping 产品经理·A/B 测试分组每个模板包含可筛字段白名单 类型 操作符输出字段返回什么给业务方业务示例找上海 25-40 岁金融理财分≥70 推基金活动10.4 核心代码Specification 动态拼查询// TemplateService.buildSpec() —— 业务方填的 filters → JPA Specification public SpecificationUserProfile buildSpec(QueryTemplate template, MapString, Object filters) { // 1. 白名单校验 ListString allowedFields template.getFilterableFields().stream() .map(QueryTemplate.FilterField::getField) .toList(); return (root, query, cb) - { ListPredicate predicates new ArrayList(); for (Map.EntryString, Object entry : filters.entrySet()) { String field entry.getKey(); Object value entry.getValue(); // 安全检查 1只允许白名单字段 if (!allowedFields.contains(field)) { throw new IllegalArgumentException(字段不在白名单: field); } if (value null) continue; predicates.add(buildPredicate(root, cb, field, value)); } return cb.and(predicates.toArray(new Predicate[0])); }; }JSONB 字段安全查询最关键的一步private Predicate buildPredicate(RootUserProfile root, CriteriaBuilder cb, String field, Object value) { if (field.startsWith(tags.)) { String jsonKey field.substring(tags..length()); // 用原生 SQL 表达式jsonb_extract_path_text(tags, 金融理财) ExpressionString jsonValue cb.function( jsonb_extract_path_text, String.class, root.get(tags), cb.literal(jsonKey) ); // 数字比较CAST AS INTEGER if (value instanceof Number n) { return cb.greaterThanOrEqualTo( cb.function(CAST, Integer.class, jsonValue, cb.literal(AS INTEGER)), n.intValue() ); } return cb.equal(jsonValue, value.toString()); } // ... }10.5 端点设计// TemplateQueryController.java GetMapping(/api/templates) public ListQueryTemplate listTemplates() { ... } // 列所有模板 GetMapping(/api/templates/{id}) public QueryTemplate getTemplate(PathVariable String id) { ... } // 查模板详情 PostMapping(/api/templates/{id}/search) public PageUserProfileDTO search( PathVariable String id, RequestBody MapString, Object filters, // 业务方填的条件 PageableDefault(size 100) Pageable pageable ) { ... } // 动态查询10.6 前端动态表单 实时结果打开 http://localhost:8080/template-search.html第 1 步下拉选模板5 个金融营销场景第 2 步自动渲染条件表单enum / range / bool第 3 步填条件 → 调/api/templates/{id}/search第 4 步3 秒出结果 一键导出 CSV10.7 这套方案的 3 个生产价值1.业务自助— 运营 / 销售 / 产品经理不用每次找 IT 排期5 分钟出名单2.安全边界— 字段白名单 操作符限制不开放任意 SQL防注入3.可视化输出— 业务方一眼看懂不需要懂 SQL 也能用真实生产中这套方案可以用在任何业务方自助分析场景金融营销、客户分层、A/B 测试、运营召回。GitHub 仓库https://github.com/SwordBob/pg-user-profile-demo