OpenAssistant 数据库巡检 SQL 速查手册基于 PostgreSQL 的消息树、任务与用户数据诊断实战【免费下载链接】Open-AssistantOpenAssistant is a chat-based assistant that understands tasks, can interact with third-party systems, and retrieve information dynamically to do so.项目地址: https://gitcode.com/gh_mirrors/op/Open-Assistant本手册是 OpenAssistantOA后端 PostgreSQL 数据库的实用 SQL 片段合集覆盖表行数统计、消息角色与语言分布、消息树Message Tree状态与规模、用户活跃度、任务耗时以及数据库连接数等核心巡检场景。阅读本文后你将能够直接连上 OA 的 Postgres 实例用十余条可直接复制执行的 SQL 快速洞察数据采集流水线的健康状况并对照后端源码理解每条查询背后涉及的字段语义与状态机设计。前置说明OA 数据库结构与本手册的定位OpenAssistant 是一个面向大众的数据采集与对话式 AI 项目其核心业务是让用户参与构建消息树的众包流程用户提出初始提示词prompt随后由志愿者分别扮演提问者prompter与助手assistant逐层回复再通过打标labeling、排序ranking等任务沉淀出高质量对话数据集。后端的所有业务数据都落在 PostgreSQL 中。本文所述的backend/sql_snippets.md正是社区成员日常巡检该库时沉淀下来的查询集合。手册中的每张表在 backend/oasst_backend/models 目录下都能找到对应的 SQLModel 定义巡检时结合源码理解字段语义会事半功倍。下表是手册中出现的主要表及其对应的模型定义文件方便对照表名说明模型文件user平台用户注意是保留字需加引号user.pytask分发给前端的各类众包任务task.pymessage对话中的单条消息提问/回答message.pymessage_tree_state消息树状态机状态message_tree_state.pymessage_reaction用户对消息的评分/反应任务结果message_reaction.pytext_labels打标任务的文本标签结果text_labels.pyjournal审计日志含时间有序 UUIDjournal.pycached_stats缓存统计结果JSONBcached_stats.py所有主键默认使用gen_random_uuid()生成 UUIDcreated_date等时间字段均为带时区的timestamp with time zone相关迁移见 2023_01_19_2153-7f0a28a156f4_switch_to_timestamp_with_tz.py因此手册中大量使用age()、current_timestamp做时间差计算是安全的。基础统计快速盘点各核心表行数数据采集项目最先关心的通常是库里到底攒了多少数据。手册给出的第一条查询用UNION一次返回八张核心表的行数-- tables row counts (select user as table, count(*) from user) union (select task, count(*) from task) union (select message_tree_state, count(*) from message_tree_state) union (select message_reaction, count(*) from message_reaction) union (select text_labels, count(*) from text_labels) union (select message, count(*) from message) union (select journal, count(*) from journal);几个值得注意的细节user是 PostgreSQL 保留字必须用双引号包裹源码中 SQLModel 也通过__tablename__ user处理见 user.py。message_reaction与text_labels是任务结果表其行数增长代表志愿者正在产生有效标注数据journal是审计日志表event_type JSONBevent_payload记录每次关键事件见 journal.py。这类统计在 OA 中实际由cached_stats表缓存并提供给前端展示模型见 cached_stats.pyname为主键stats为 JSONB后台定时任务刷新见 scheduled_tasks.py。直接查表是获取真值的途径而cached_stats适合面向用户的高频读取。消息维度角色分布、语言分布与干净数据过滤按角色统计仅人工生成-- only human by role select role, count(*) from message where not deleted and review_result and not synthetic group by role;这里体现了 OA 消息数据的三个关键过滤维度deleted逻辑删除标记模型定义见 message.py默认false删除采用软删。review_result消息是否通过质量审查Boolean可空见同文件 L61。在 OA 的流程中初始提示词与回复都要经过打标任务的质量把关review_result true才意味着消息达到入库质量标准。synthetic是否为模型生成的合成数据见 message.py。not synthetic即只保留人工数据。role字段约束为^prompter|assistant$两个取值对应对话中提问者/助手两类角色。这一查询常用于评估纯人工数据的规模是数据看板的核心指标。按语言分布含合成数据-- language distribution of messages (incl. synthetic) select lang, count(*), synthetic from message where not deleted and review_result group by lang, synthetic;lang字段存 ISO 639-1 语言代码迁移见 2022_12_28_1824-ef0b52902560_added_lang_column_for_iso_639_1_codes.py默认值为en见 message.py。本查询不区分人工/合成可用来观察整体语料构成判断是否需要为某些语言补充数据采集任务。仅人工消息按语言分布-- only human generated messages by lang select lang, count(*) from message where not deleted and review_result and not synthetic group by lang;这是前两条的组合只统计通过审查、未删除、且非合成的人工消息按语言聚合。OA 训练数据集的黄金语料主要来自这一子集语言覆盖度直接关系到多语言模型的最终效果。消息树维度理解状态机与树规模message_tree_state是 OA 数据流水线中最核心的状态表其状态机定义在 message_tree_state.py 的State枚举中状态值含义initial_prompt_review树仅有根节点正在审查初始提示词质量growing正在收集人机演示对话可继续挂载新消息ranking消息数量达标开始发放排序任务ready_for_scoring排序数据齐备等待评分算法计算聚合分数ready_for_export评分完成可导出为数据集scoring_failed评分算法执行异常aborted_low_grade差评过多树被中止halted_by_moderator版主手动中止backlog_ranking导入的外部树待激活排序当前未激活prompt_lottery_waiting初始提示词通过垃圾过滤等待被抽签选中进入生长其中VALID_STATES与TERMINAL_STATES两组常量见 message_tree_state.py界定了状态的流转合法性。下面几条手册 SQL 均围绕该状态机展开。消息树总数与状态分布-- total count of message trees select count(*) from message_tree_state;-- message tree counts by state select state, count(*) from message_tree_state group by state;第二条是运营看板的晴雨表growing数量代表正在生产的树ready_for_export代表可导出量aborted_low_grade比例过高则说明提示词质量或任务分配需要关注。等待抽签的初始提示词按语言-- count of waiting initial prompts by language select m.lang, count(*) from message_tree_state mts join message m on mts.message_tree_id m.id where mts.state prompt_lottery_waiting group by m.lang;这条查询通过message_tree_id message.id关联到树的根消息从而拿到语言信息。prompt_lottery_waiting状态背后的调度逻辑在 tree_manager.py 的_prompt_lottery()中系统按语言统计等待队列并受配置项max_prompt_lottery_waiting默认 250见 config.py约束——某语言等待数超过上限时就不再放行新的提示词进入队列。因此该查询可用于判断某语言是否爆仓。可导出/生长中树的语言分布-- message trees by lang in ready_for_export or growing state select m.lang, mts.state, count(*) from message_tree_state mts join message m on mts.message_tree_id m.id where mts.state in (ready_for_export, growing) group by mts.state, m.lang order by lang, state;ready_for_export与growing是数据流水线两端最关键的中间态前者直接决定可导出的数据集规模后者代表在途产能。注意message_tree_state表本身也带有lang字段见 message_tree_state.py迁移见 2023_02_26_0052-9db92d504f64_add_lang_to_message_tree_state.py但手册选择 joinmessage取根消息的语言二者在正常情况下应一致。按规模挑选生长中的树指定语言-- select message tree counts select mts.message_tree_id, count(m.id), max(m.depth), count(m.id) filter (where m.roleprompter) as prompter, count(m.id) filter (where m.roleassistant) as assistant from message_tree_state mts join message m on mts.message_tree_id m.message_tree_id where mts.stategrowing and not m.deleted and m.review_resulttrue and m.langen and mts.active group by mts.message_tree_id order by count(m.id) desc;这条查询展示了FILTER子句的用法——在同一聚合中按role分别统计提问者与助手消息数。depth字段在 message.py 中定义表示消息在树中的深度根节点为 0。active标记message_tree_state.py表示该树当前是否参与调度。用途举例找出英语语料中体量最大的生长中树判断是否存在个别树过度生长、质量稀释的风险。Top 100 最大消息树-- show top 100 largest trees select mts.message_tree_id, mts.goal_tree_size, mts.state, count(m.id) as message_count from message_tree_state mts join message m on mts.message_tree_id m.message_tree_id where not m.deleted and m.review_resulttrue group by mts.message_tree_id, mts.state order by count(m.id) desc limit 100;goal_tree_size是每棵树的目标消息规模在树创建时由后端根据配置决定见 tree_manager.py 中树的创建逻辑。将message_count与goal_tree_size对比即可快速发现超编的树。limit 100保证大表下查询开销可控。活跃树当前规模 vs 目标规模-- active trees, current goal_size select mts.message_tree_id, mts.state, mts.goal_tree_size, count(m.id) AS tree_size, max(m.depth) AS max_depth from message_tree_state mts join message m ON mts.message_tree_id m.message_tree_id WHERE mts.active and not m.deleted and m.review_result group by mts.message_tree_id, mts.goal_tree_size;tree_size当前消息数与goal_tree_size目标同列展示是最直观的距满员还差多少查询max_depth反映树的纵深可配合goal_tree_size判断树的形态是否健康例如大量树深度过浅可能意味着分支不足。按状态聚合树数、消息总数、最大/平均规模-- count max, mean message counts per tree for a given language with t(message_tree_id, tree_size, state) as (select mts.message_tree_id, count(m.id), mts.state from message_tree_state mts join message m on mts.message_tree_id m.message_tree_id where not m.deleted and m.review_resulttrue and m.lang en group by mts.message_tree_id) select state, count(t.*) as trees, sum(t.tree_size) as total_msgs, max(t.tree_size), avg(t.tree_size) from t group by t.state;使用 CTE公用表表达式先把每条消息树聚合为(tree_id, tree_size, state)三元组再按state做二次聚合得到各状态下的树数、消息总量、最大/平均规模。这是手册中信息密度最高的一条统计查询适合生成语料健康度报告。用户维度ToS 接受、活跃度与在线人数接受服务条款的用户数-- count users that accepted tos select count(*) from user where tos_acceptance_date is not null;tos_acceptance_date字段由迁移 2023_02_01_0022-55361f323d12_add_tos_acceptance_date_to_user.py 引入模型定义见 user.py。只有接受过 ToS 的用户才能参与任务因此该指标是有效用户池的下限估计。最近活跃用户列表两种写法-- last 25 active users select u.id, u.username, u.auth_method, u.display_name, u.last_activity_date, age(current_timestamp, last_activity_date) from user u WHERE u.last_activity_date is not null order by u.last_activity_date desc limit 25; select id, display_name, username, auth_method, last_activity_date from user where age(last_activity_date) interval 1 minutes order by last_activity_date desc limit 25;last_activity_date在用户每次心跳/完成任务时刷新见 user.py。第一条按时间倒序取最近 25 人并用age()显示多久前活跃第二条用age(last_activity_date) interval 1 minutes直接筛出最近 1 分钟内活跃的用户适合运营即时监控。注意age()接受两个参数时计算的是两者之差的时间间隔单参数则相对当前时间。最近 5 分钟活跃用户数-- count active users in last 5 mins select count(*) from user u where age(current_timestamp, last_activity_date) interval 5 mins;这是当前在线人数的实用近似是评估众包平台实时热度的核心指标。手册特意将其单独列出说明社区运营常用其监测任务供给是否充足。总消息数人工 合成-- total count of non-deleted messages (human synth) select count(*) from message where deletedfalse and review_resulttrue;与前面的按角色查询互补不区分synthetic统计通过审查的全部有效消息包括模型合成数据。OA 的合成数据由 tree_manager.py 等模块生成用于扩充语料。任务维度各任务类型的平均完成耗时-- average time between task creation and completion (select t.payload#{payload, type} as type, count(*), avg(r.created_date-t.created_date) from task t join message_reaction r on t.id task_id where t.done and not t.skipped group by t.payload#{payload, type}) union (select t.payload#{payload, type} as type, count(*), avg(l.created_date-t.created_date) from task t join text_labels l on t.id l.task_id where t.done and not t.skipped group by t.payload#{payload, type}) union ( select t.payload#{payload, type} as type, count(*), avg(m.created_date-t.created_date) from task t join message m on t.id m.task_id where t.done and not t.skipped group by t.payload#{payload, type});这条查询是手册中最复杂的一条用UNION合并三类任务结果的耗时评分/反应类message_reaction如RankInitialPromptsTask、RankConversationRepliesTask等打标类text_labels如LabelInitialPromptTask、LabelPrompterReplyTask、LabelAssistantReplyTask等消息生成类message如InitialPromptTask、PrompterReplyTask、AssistantReplyTask、SummarizeStoryTask等。任务类型的完整清单见 protocol.py。两个关键实现细节payload#{payload, type}task.payload是 JSONB 容器#运算符取出路径payload.type的文本值作为任务类型名。payload_type字段task.py也存类型字符串但手册选择直接从 JSON 内提取二者可互证。avg(r.created_date - t.created_date)两个timestamp with time zone相减得到intervalavg会返回平均时长可用于识别哪种任务最耗时、最容易流失用户。t.done and not t.skipped只统计完成且未跳过的任务skipped/skip_reason字段由迁移 2023_02_01_2146-9e7ec4a9e3f2_add_skip_bool_skip_reason_to_task.py 引入。连接管理数据库连接余量监控-- from https://dba.stackexchange.com/questions/161760/number-of-active-connections-and-remaining-connections select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1, (select setting::int res_for_super from pg_settings where name$$superuser_reserved_connections$$) t2, (select setting::int max_conn from pg_settings where name$$max_connections$$) t3;这条查询利用 PostgreSQL 系统视图/系统表pg_stat_activity与pg_settings计算max_connmax_connections配置上限used当前已用连接数res_for_super为超级用户保留的连接数superuser_reserved_connectionsres_for_normal普通用户可用连接余量max_conn - used - res_for_super。注意$$是 PostgreSQL 的美元引号字符串定界符等价于普通字符串字面量用于避免与单引号冲突。当res_for_normal趋近于 0 时普通后端连接将无法建立此时需要排查连接泄漏或调大max_connections。OA 后端使用 SQLAlchemy 连接池访问该库见 database.py生产环境还会配合 PgBouncer 等连接代理见 redis.conf 旁的部署配置与 ansible 中的部署文档这条查询有助于判断是否需要调整连接池参数。实战从数据到决策的巡检套路将上述片段组合起来可以形成一套完整的巡检流程总量摸底跑基础统计的 UNION 查询确认各表规模是否与预期相符、增长是否正常。流水线健康检查跑消息树状态分布观察growing/ready_for_export的占比若aborted_low_grade异常攀升结合prompt_lottery_waiting按语言查询判断是提示词质量问题还是某语言任务分配失衡。社区热度评估用最近 5 分钟活跃用户数与各任务平均耗时判断任务供给与转化效率耗时过长的任务类型可能需要调整任务配置或引导策略。语料质量审计用按角色/按语言查询确认人工数据与合成数据的构成为数据集导出相关导出工具见 export.py 与 tree_export.py提供依据。基础设施兜底定期检查连接余量避免连接耗尽导致后端不可用。需要说明的是上述查询面向人工巡检场景为保障生产库性能高频看板类指标 OA 实际会写入cached_statsJSONB 缓存供前端读取针对超大数据集的导出则使用游标与批量处理见 tree_export.py不应直接用本文的聚合 SQL 替代。在执行开销较大的聚合查询时建议在副本库或低峰期进行并为message_tree_id、lang、deleted、review_result等高频过滤字段建立合适索引message_tree_state表已有ix_message_tree_state__lang__state联合索引见 message_tree_state.py。以上所有 SQL 均来自仓库文档 sql_snippets.md结合 models 目录下的模型定义、tree_manager.py 的状态调度逻辑以及 alembic 迁移历史即可完整还原每条查询背后的业务语义直接复制到 psql 即可使用。【免费下载链接】Open-AssistantOpenAssistant is a chat-based assistant that understands tasks, can interact with third-party systems, and retrieve information dynamically to do so.项目地址: https://gitcode.com/gh_mirrors/op/Open-Assistant创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 OpenAssistant 数据库巡检 SQL 速查手册：基于 PostgreSQL 的消息树、任务与用户数据诊断实战
OpenAssistant 数据库巡检 SQL 速查手册基于 PostgreSQL 的消息树、任务与用户数据诊断实战【免费下载链接】Open-AssistantOpenAssistant is a chat-based assistant that understands tasks, can interact with third-party systems, and retrieve information dynamically to do so.项目地址: https://gitcode.com/gh_mirrors/op/Open-Assistant本手册是 OpenAssistantOA后端 PostgreSQL 数据库的实用 SQL 片段合集覆盖表行数统计、消息角色与语言分布、消息树Message Tree状态与规模、用户活跃度、任务耗时以及数据库连接数等核心巡检场景。阅读本文后你将能够直接连上 OA 的 Postgres 实例用十余条可直接复制执行的 SQL 快速洞察数据采集流水线的健康状况并对照后端源码理解每条查询背后涉及的字段语义与状态机设计。前置说明OA 数据库结构与本手册的定位OpenAssistant 是一个面向大众的数据采集与对话式 AI 项目其核心业务是让用户参与构建消息树的众包流程用户提出初始提示词prompt随后由志愿者分别扮演提问者prompter与助手assistant逐层回复再通过打标labeling、排序ranking等任务沉淀出高质量对话数据集。后端的所有业务数据都落在 PostgreSQL 中。本文所述的backend/sql_snippets.md正是社区成员日常巡检该库时沉淀下来的查询集合。手册中的每张表在 backend/oasst_backend/models 目录下都能找到对应的 SQLModel 定义巡检时结合源码理解字段语义会事半功倍。下表是手册中出现的主要表及其对应的模型定义文件方便对照表名说明模型文件user平台用户注意是保留字需加引号user.pytask分发给前端的各类众包任务task.pymessage对话中的单条消息提问/回答message.pymessage_tree_state消息树状态机状态message_tree_state.pymessage_reaction用户对消息的评分/反应任务结果message_reaction.pytext_labels打标任务的文本标签结果text_labels.pyjournal审计日志含时间有序 UUIDjournal.pycached_stats缓存统计结果JSONBcached_stats.py所有主键默认使用gen_random_uuid()生成 UUIDcreated_date等时间字段均为带时区的timestamp with time zone相关迁移见 2023_01_19_2153-7f0a28a156f4_switch_to_timestamp_with_tz.py因此手册中大量使用age()、current_timestamp做时间差计算是安全的。基础统计快速盘点各核心表行数数据采集项目最先关心的通常是库里到底攒了多少数据。手册给出的第一条查询用UNION一次返回八张核心表的行数-- tables row counts (select user as table, count(*) from user) union (select task, count(*) from task) union (select message_tree_state, count(*) from message_tree_state) union (select message_reaction, count(*) from message_reaction) union (select text_labels, count(*) from text_labels) union (select message, count(*) from message) union (select journal, count(*) from journal);几个值得注意的细节user是 PostgreSQL 保留字必须用双引号包裹源码中 SQLModel 也通过__tablename__ user处理见 user.py。message_reaction与text_labels是任务结果表其行数增长代表志愿者正在产生有效标注数据journal是审计日志表event_type JSONBevent_payload记录每次关键事件见 journal.py。这类统计在 OA 中实际由cached_stats表缓存并提供给前端展示模型见 cached_stats.pyname为主键stats为 JSONB后台定时任务刷新见 scheduled_tasks.py。直接查表是获取真值的途径而cached_stats适合面向用户的高频读取。消息维度角色分布、语言分布与干净数据过滤按角色统计仅人工生成-- only human by role select role, count(*) from message where not deleted and review_result and not synthetic group by role;这里体现了 OA 消息数据的三个关键过滤维度deleted逻辑删除标记模型定义见 message.py默认false删除采用软删。review_result消息是否通过质量审查Boolean可空见同文件 L61。在 OA 的流程中初始提示词与回复都要经过打标任务的质量把关review_result true才意味着消息达到入库质量标准。synthetic是否为模型生成的合成数据见 message.py。not synthetic即只保留人工数据。role字段约束为^prompter|assistant$两个取值对应对话中提问者/助手两类角色。这一查询常用于评估纯人工数据的规模是数据看板的核心指标。按语言分布含合成数据-- language distribution of messages (incl. synthetic) select lang, count(*), synthetic from message where not deleted and review_result group by lang, synthetic;lang字段存 ISO 639-1 语言代码迁移见 2022_12_28_1824-ef0b52902560_added_lang_column_for_iso_639_1_codes.py默认值为en见 message.py。本查询不区分人工/合成可用来观察整体语料构成判断是否需要为某些语言补充数据采集任务。仅人工消息按语言分布-- only human generated messages by lang select lang, count(*) from message where not deleted and review_result and not synthetic group by lang;这是前两条的组合只统计通过审查、未删除、且非合成的人工消息按语言聚合。OA 训练数据集的黄金语料主要来自这一子集语言覆盖度直接关系到多语言模型的最终效果。消息树维度理解状态机与树规模message_tree_state是 OA 数据流水线中最核心的状态表其状态机定义在 message_tree_state.py 的State枚举中状态值含义initial_prompt_review树仅有根节点正在审查初始提示词质量growing正在收集人机演示对话可继续挂载新消息ranking消息数量达标开始发放排序任务ready_for_scoring排序数据齐备等待评分算法计算聚合分数ready_for_export评分完成可导出为数据集scoring_failed评分算法执行异常aborted_low_grade差评过多树被中止halted_by_moderator版主手动中止backlog_ranking导入的外部树待激活排序当前未激活prompt_lottery_waiting初始提示词通过垃圾过滤等待被抽签选中进入生长其中VALID_STATES与TERMINAL_STATES两组常量见 message_tree_state.py界定了状态的流转合法性。下面几条手册 SQL 均围绕该状态机展开。消息树总数与状态分布-- total count of message trees select count(*) from message_tree_state;-- message tree counts by state select state, count(*) from message_tree_state group by state;第二条是运营看板的晴雨表growing数量代表正在生产的树ready_for_export代表可导出量aborted_low_grade比例过高则说明提示词质量或任务分配需要关注。等待抽签的初始提示词按语言-- count of waiting initial prompts by language select m.lang, count(*) from message_tree_state mts join message m on mts.message_tree_id m.id where mts.state prompt_lottery_waiting group by m.lang;这条查询通过message_tree_id message.id关联到树的根消息从而拿到语言信息。prompt_lottery_waiting状态背后的调度逻辑在 tree_manager.py 的_prompt_lottery()中系统按语言统计等待队列并受配置项max_prompt_lottery_waiting默认 250见 config.py约束——某语言等待数超过上限时就不再放行新的提示词进入队列。因此该查询可用于判断某语言是否爆仓。可导出/生长中树的语言分布-- message trees by lang in ready_for_export or growing state select m.lang, mts.state, count(*) from message_tree_state mts join message m on mts.message_tree_id m.id where mts.state in (ready_for_export, growing) group by mts.state, m.lang order by lang, state;ready_for_export与growing是数据流水线两端最关键的中间态前者直接决定可导出的数据集规模后者代表在途产能。注意message_tree_state表本身也带有lang字段见 message_tree_state.py迁移见 2023_02_26_0052-9db92d504f64_add_lang_to_message_tree_state.py但手册选择 joinmessage取根消息的语言二者在正常情况下应一致。按规模挑选生长中的树指定语言-- select message tree counts select mts.message_tree_id, count(m.id), max(m.depth), count(m.id) filter (where m.roleprompter) as prompter, count(m.id) filter (where m.roleassistant) as assistant from message_tree_state mts join message m on mts.message_tree_id m.message_tree_id where mts.stategrowing and not m.deleted and m.review_resulttrue and m.langen and mts.active group by mts.message_tree_id order by count(m.id) desc;这条查询展示了FILTER子句的用法——在同一聚合中按role分别统计提问者与助手消息数。depth字段在 message.py 中定义表示消息在树中的深度根节点为 0。active标记message_tree_state.py表示该树当前是否参与调度。用途举例找出英语语料中体量最大的生长中树判断是否存在个别树过度生长、质量稀释的风险。Top 100 最大消息树-- show top 100 largest trees select mts.message_tree_id, mts.goal_tree_size, mts.state, count(m.id) as message_count from message_tree_state mts join message m on mts.message_tree_id m.message_tree_id where not m.deleted and m.review_resulttrue group by mts.message_tree_id, mts.state order by count(m.id) desc limit 100;goal_tree_size是每棵树的目标消息规模在树创建时由后端根据配置决定见 tree_manager.py 中树的创建逻辑。将message_count与goal_tree_size对比即可快速发现超编的树。limit 100保证大表下查询开销可控。活跃树当前规模 vs 目标规模-- active trees, current goal_size select mts.message_tree_id, mts.state, mts.goal_tree_size, count(m.id) AS tree_size, max(m.depth) AS max_depth from message_tree_state mts join message m ON mts.message_tree_id m.message_tree_id WHERE mts.active and not m.deleted and m.review_result group by mts.message_tree_id, mts.goal_tree_size;tree_size当前消息数与goal_tree_size目标同列展示是最直观的距满员还差多少查询max_depth反映树的纵深可配合goal_tree_size判断树的形态是否健康例如大量树深度过浅可能意味着分支不足。按状态聚合树数、消息总数、最大/平均规模-- count max, mean message counts per tree for a given language with t(message_tree_id, tree_size, state) as (select mts.message_tree_id, count(m.id), mts.state from message_tree_state mts join message m on mts.message_tree_id m.message_tree_id where not m.deleted and m.review_resulttrue and m.lang en group by mts.message_tree_id) select state, count(t.*) as trees, sum(t.tree_size) as total_msgs, max(t.tree_size), avg(t.tree_size) from t group by t.state;使用 CTE公用表表达式先把每条消息树聚合为(tree_id, tree_size, state)三元组再按state做二次聚合得到各状态下的树数、消息总量、最大/平均规模。这是手册中信息密度最高的一条统计查询适合生成语料健康度报告。用户维度ToS 接受、活跃度与在线人数接受服务条款的用户数-- count users that accepted tos select count(*) from user where tos_acceptance_date is not null;tos_acceptance_date字段由迁移 2023_02_01_0022-55361f323d12_add_tos_acceptance_date_to_user.py 引入模型定义见 user.py。只有接受过 ToS 的用户才能参与任务因此该指标是有效用户池的下限估计。最近活跃用户列表两种写法-- last 25 active users select u.id, u.username, u.auth_method, u.display_name, u.last_activity_date, age(current_timestamp, last_activity_date) from user u WHERE u.last_activity_date is not null order by u.last_activity_date desc limit 25; select id, display_name, username, auth_method, last_activity_date from user where age(last_activity_date) interval 1 minutes order by last_activity_date desc limit 25;last_activity_date在用户每次心跳/完成任务时刷新见 user.py。第一条按时间倒序取最近 25 人并用age()显示多久前活跃第二条用age(last_activity_date) interval 1 minutes直接筛出最近 1 分钟内活跃的用户适合运营即时监控。注意age()接受两个参数时计算的是两者之差的时间间隔单参数则相对当前时间。最近 5 分钟活跃用户数-- count active users in last 5 mins select count(*) from user u where age(current_timestamp, last_activity_date) interval 5 mins;这是当前在线人数的实用近似是评估众包平台实时热度的核心指标。手册特意将其单独列出说明社区运营常用其监测任务供给是否充足。总消息数人工 合成-- total count of non-deleted messages (human synth) select count(*) from message where deletedfalse and review_resulttrue;与前面的按角色查询互补不区分synthetic统计通过审查的全部有效消息包括模型合成数据。OA 的合成数据由 tree_manager.py 等模块生成用于扩充语料。任务维度各任务类型的平均完成耗时-- average time between task creation and completion (select t.payload#{payload, type} as type, count(*), avg(r.created_date-t.created_date) from task t join message_reaction r on t.id task_id where t.done and not t.skipped group by t.payload#{payload, type}) union (select t.payload#{payload, type} as type, count(*), avg(l.created_date-t.created_date) from task t join text_labels l on t.id l.task_id where t.done and not t.skipped group by t.payload#{payload, type}) union ( select t.payload#{payload, type} as type, count(*), avg(m.created_date-t.created_date) from task t join message m on t.id m.task_id where t.done and not t.skipped group by t.payload#{payload, type});这条查询是手册中最复杂的一条用UNION合并三类任务结果的耗时评分/反应类message_reaction如RankInitialPromptsTask、RankConversationRepliesTask等打标类text_labels如LabelInitialPromptTask、LabelPrompterReplyTask、LabelAssistantReplyTask等消息生成类message如InitialPromptTask、PrompterReplyTask、AssistantReplyTask、SummarizeStoryTask等。任务类型的完整清单见 protocol.py。两个关键实现细节payload#{payload, type}task.payload是 JSONB 容器#运算符取出路径payload.type的文本值作为任务类型名。payload_type字段task.py也存类型字符串但手册选择直接从 JSON 内提取二者可互证。avg(r.created_date - t.created_date)两个timestamp with time zone相减得到intervalavg会返回平均时长可用于识别哪种任务最耗时、最容易流失用户。t.done and not t.skipped只统计完成且未跳过的任务skipped/skip_reason字段由迁移 2023_02_01_2146-9e7ec4a9e3f2_add_skip_bool_skip_reason_to_task.py 引入。连接管理数据库连接余量监控-- from https://dba.stackexchange.com/questions/161760/number-of-active-connections-and-remaining-connections select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1, (select setting::int res_for_super from pg_settings where name$$superuser_reserved_connections$$) t2, (select setting::int max_conn from pg_settings where name$$max_connections$$) t3;这条查询利用 PostgreSQL 系统视图/系统表pg_stat_activity与pg_settings计算max_connmax_connections配置上限used当前已用连接数res_for_super为超级用户保留的连接数superuser_reserved_connectionsres_for_normal普通用户可用连接余量max_conn - used - res_for_super。注意$$是 PostgreSQL 的美元引号字符串定界符等价于普通字符串字面量用于避免与单引号冲突。当res_for_normal趋近于 0 时普通后端连接将无法建立此时需要排查连接泄漏或调大max_connections。OA 后端使用 SQLAlchemy 连接池访问该库见 database.py生产环境还会配合 PgBouncer 等连接代理见 redis.conf 旁的部署配置与 ansible 中的部署文档这条查询有助于判断是否需要调整连接池参数。实战从数据到决策的巡检套路将上述片段组合起来可以形成一套完整的巡检流程总量摸底跑基础统计的 UNION 查询确认各表规模是否与预期相符、增长是否正常。流水线健康检查跑消息树状态分布观察growing/ready_for_export的占比若aborted_low_grade异常攀升结合prompt_lottery_waiting按语言查询判断是提示词质量问题还是某语言任务分配失衡。社区热度评估用最近 5 分钟活跃用户数与各任务平均耗时判断任务供给与转化效率耗时过长的任务类型可能需要调整任务配置或引导策略。语料质量审计用按角色/按语言查询确认人工数据与合成数据的构成为数据集导出相关导出工具见 export.py 与 tree_export.py提供依据。基础设施兜底定期检查连接余量避免连接耗尽导致后端不可用。需要说明的是上述查询面向人工巡检场景为保障生产库性能高频看板类指标 OA 实际会写入cached_statsJSONB 缓存供前端读取针对超大数据集的导出则使用游标与批量处理见 tree_export.py不应直接用本文的聚合 SQL 替代。在执行开销较大的聚合查询时建议在副本库或低峰期进行并为message_tree_id、lang、deleted、review_result等高频过滤字段建立合适索引message_tree_state表已有ix_message_tree_state__lang__state联合索引见 message_tree_state.py。以上所有 SQL 均来自仓库文档 sql_snippets.md结合 models 目录下的模型定义、tree_manager.py 的状态调度逻辑以及 alembic 迁移历史即可完整还原每条查询背后的业务语义直接复制到 psql 即可使用。【免费下载链接】Open-AssistantOpenAssistant is a chat-based assistant that understands tasks, can interact with third-party systems, and retrieve information dynamically to do so.项目地址: https://gitcode.com/gh_mirrors/op/Open-Assistant创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考