tencentdb_ai 1.6 Auto Embedding 功能说明

SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN ('tencentdb_ai', 'pgvector', 'pgmq', 'vector');

SHOW shared_preload_libraries;

SELECT name, setting, context
FROM pg_settings
WHERE name LIKE 'tencentdb_ai.autoembedding%'
ORDER BY name;

                       name                       | setting  | context
--------------------------------------------------+----------+---------
 tencentdb_ai.autoembedding_batch_size            | 32       | sighup
 tencentdb_ai.autoembedding_database              | postgres | sighup
 tencentdb_ai.autoembedding_max_input_bytes       | 65536    | sighup
 tencentdb_ai.autoembedding_max_retry             | 5        | sighup
 tencentdb_ai.autoembedding_naptime_ms            | 5000     | sighup
 tencentdb_ai.autoembedding_retry_base_ms         | 1000     | sighup
 tencentdb_ai.autoembedding_task_launch_jitter_ms | 10       | sighup
 tencentdb_ai.autoembedding_worker                | on       | sighup
(8 rows)

-- 会自动安装 pgcrypto、vector、pgmq
CREATE EXTENSION IF NOT EXISTS tencentdb_ai CASCADE;

SELECT * FROM pgmq.list_queues();

             queue_name              | is_partitioned | is_unlogged |          created_at
-------------------------------------+----------------+-------------+-------------------------------
 tencentdb_ai_autoembedding_incr     | f              | f           | 2026-08-05 22:36:41.689961+08
 tencentdb_ai_autoembedding_backfill | f              | f           | 2026-08-05 22:36:41.689961+08
(2 rows)

-- 查看 model_list 表结构
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'tencentdb_ai' AND table_name = 'model_list'
ORDER BY ordinal_position;

-- 查看已有模型
SELECT * FROM tencentdb_ai.model_list;

-- 注册模型（最后一个参数 backend_type 默认值为 'hunyuan'，可不传）
SELECT tencentdb_ai.add_model('hunyuan-embedding', '2023-09-01', NULL, NULL);

-- 【在这里填入 SecretId】
SELECT tencentdb_ai.update_model_attr(
    'hunyuan-embedding',
    'SecretId',
    '<你的SecretId>'  -- 替换为你的 SecretId
);

-- 【在这里填入 SecretKey】
SELECT tencentdb_ai.update_model_attr(
    'hunyuan-embedding',
    'SecretKey',
    '<你的SecretKey>'     -- 替换为你的 SecretKey
);

-- 设置嵌入向量维度
UPDATE tencentdb_ai.model_list
SET embedding_dim = 1024
WHERE model_name = 'hunyuan-embedding';

-- 验证模型配置
SELECT model_name, backend_type, embedding_dim
FROM tencentdb_ai.model_list
WHERE model_name = 'hunyuan-embedding';

 add_model
-----------

(1 row)

 update_model_attr
-------------------

(1 row)

 update_model_attr
-------------------

(1 row)

UPDATE 1

    model_name     | backend_type | embedding_dim
-------------------+--------------+---------------
 hunyuan-embedding | hunyuan      |          1024
(1 row)

NOTICE:  table "kb_articles" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 3
       ?column?
-----------------------
 Test data inserted: 3
(1 row)

-- 5.1 创建增量任务
-- 自动创建 content_embedding vector(1024) 列
-- 自动创建 INSERT AFTER + UPDATE BEFORE 触发器
SELECT tencentdb_ai.add_incr_autoembedding_task(
    'public', 'kb_articles',
    ARRAY['content'],
    'hunyuan-embedding'
) AS incr_task_id;

-- 5.2 创建存量任务（回填历史数据）
SELECT tencentdb_ai.add_backfill_autoembedding_task(
    'public', 'kb_articles',
    ARRAY['content'],
    'hunyuan-embedding'
) AS backfill_task_id;

 incr_task_id
--------------
            1
(1 row)

 backfill_task_id
------------------
                1
(1 row)

\\d kb_articles

                                    Table "public.kb_articles"
      Column       |     Type     | Collation | Nullable |                 Default
-------------------+--------------+-----------+----------+-----------------------------------------
 id                | bigint       |           | not null | nextval('kb_articles_id_seq'::regclass)
 title             | text         |           |          |
 content           | text         |           |          |
 content_embedding | vector(1024) |           |          |
Indexes:
    "kb_articles_pkey" PRIMARY KEY, btree (id)
Triggers:
    tencentdb_ai_autoemb_ins_1 AFTER INSERT ON kb_articles FOR EACH ROW
        EXECUTE FUNCTION tencentdb_ai._autoembedding_enqueue_trigger(...)
    tencentdb_ai_autoemb_upd_1 BEFORE UPDATE OF content ON kb_articles FOR EACH ROW
        EXECUTE FUNCTION tencentdb_ai._autoembedding_enqueue_trigger(...)

SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'kb_articles';

SELECT task_kind, task_id, table_name, target_column, status, backfill_state, pending, failed_count
FROM tencentdb_ai.autoembedding_status
ORDER BY task_id;

 task_kind | task_id | table_name  |   target_column   | status  | backfill_state | pending | failed_count
-----------+---------+-------------+-------------------+---------+----------------+---------+--------------
 incr      |       1 | kb_articles | content_embedding | enabled |                |       0 |            0
 backfill  |       1 | kb_articles | content_embedding |         | not_started    |       0 |            0
(2 rows)

SELECT table_name, relid, status FROM tencentdb_ai.autoembedding_incr_task;

 table_name  | relid | status
-------------+-------+---------
 kb_articles | 17136 | enabled
(1 row)

-- 插入新数据（在增量任务创建之后再插入）
INSERT INTO kb_articles (title, content) VALUES
    ('嵌入模型', '嵌入模型是将文本转换为向量表示的机器学习模型，常见的嵌入模型包括BERT、GPT和混元嵌入等。');

INSERT 0 1

-- 立即查 pending（应看到 pending = 1，表示已入队）
SELECT task_kind, pending FROM tencentdb_ai.autoembedding_status;

 task_kind | pending
-----------+---------
 incr      |       1
 backfill  |       0
(2 rows)

-- 等待后台 worker 处理
SELECT pg_sleep(5);

 pg_sleep
----------

(1 row)

-- 检查向量是否生成（注意：前3行在任务创建前已存在，不会自动回填）
SELECT id, title,
       content_embedding IS NOT NULL AS has_embedding,
       CASE WHEN content_embedding IS NOT NULL
            THEN vector_dims(content_embedding) ELSE NULL END AS dims
FROM kb_articles
ORDER BY id;

 id |      title      | has_embedding | dims
----+-----------------+---------------+------
  1 | PostgreSQL 简介 | f             |
  2 | 向量数据库      | f             |
  3 | RAG 技术        | f             |
  4 | 嵌入模型        | t             |
(4 rows)

INSERT INTO kb_articles (title, content) VALUES
    ('混元大模型', '腾讯混元大模型是腾讯自研的通用大语言模型，具备强大的自然语言理解和生成能力。');

INSERT 0 1

-- 立即查 pending
SELECT task_kind, pending FROM tencentdb_ai.autoembedding_status;

 task_kind | pending
-----------+---------
 incr      |       1
 backfill  |       0
(2 rows)

SELECT pg_sleep(5);

SELECT task_kind, pending, failed_count FROM tencentdb_ai.autoembedding_status;

SELECT id, title, content_embedding IS NOT NULL AS has_embedding
FROM kb_articles ORDER BY id;

-- 错误表
SELECT * FROM tencentdb_ai.autoembedding_error ORDER BY created_at DESC LIMIT 5;

 pg_sleep
----------

(1 row)

 task_kind | pending | failed_count
-----------+---------+--------------
 incr      |       0 |            0
 backfill  |       0 |            0
(2 rows)

 id |      title      | has_embedding
----+-----------------+---------------
  1 | PostgreSQL 简介 | f
  2 | 向量数据库      | f
  3 | RAG 技术        | f
  4 | 嵌入模型        | t
  5 | 混元大模型      | t
(5 rows)

 error_id | msg_id | task_kind | task_id | row_id | error_code | error_message | detail | created_at
----------+--------+-----------+---------+--------+------------+---------------+--------+------------
(0 rows)

SELECT id, title,
       vector_dims(content_embedding) AS dims,
       length(content_embedding::text) AS text_len
FROM kb_articles
WHERE content_embedding IS NOT NULL;

 id |   title    | dims | text_len
----+------------+------+----------
  4 | 嵌入模型   | 1024 |    12727
  5 | 混元大模型 | 1024 |    12779
(2 rows)

-- 更新已有行，即使内容不变也会触发 UPDATE 触发器
UPDATE kb_articles SET content = content WHERE id IN (1,2,3);

UPDATE 3

-- 立即查 pending（应为 3）
SELECT task_kind, pending FROM tencentdb_ai.autoembedding_status;

 task_kind | pending
-----------+---------
 incr      |       3
 backfill  |       0
(2 rows)

SELECT pg_sleep(5);

-- 全量验证
SELECT id, title, content_embedding IS NOT NULL AS has_embedding
FROM kb_articles ORDER BY id;

 pg_sleep
----------

(1 row)

 id |      title      | has_embedding
----+-----------------+---------------
  1 | PostgreSQL 简介 | t
  2 | 向量数据库      | t
  3 | RAG 技术        | t
  4 | 嵌入模型        | t
  5 | 混元大模型      | t
(5 rows)

SELECT id, title,
       content_embedding <=> (
           SELECT content_embedding FROM kb_articles WHERE id = 4
       ) AS distance
FROM kb_articles
WHERE content_embedding IS NOT NULL
ORDER BY distance;

 id |   title    |      distance
----+------------+---------------------
  4 | 嵌入模型   |                   0
  5 | 混元大模型 | 0.28689392595353047
(2 rows)

SELECT id, title,
       content_embedding <=> (
           SELECT content_embedding FROM kb_articles WHERE id = 1
       ) AS distance
FROM kb_articles
WHERE content_embedding IS NOT NULL
ORDER BY distance;

 id |      title      |      distance
----+-----------------+---------------------
  1 | PostgreSQL 简介 |                   0
  2 | 向量数据库      |  0.2789466068930575
  3 | RAG 技术        |  0.3563481692751316
  4 | 嵌入模型        |  0.3840361738420558
  5 | 混元大模型      | 0.40611747180705204
(5 rows)

SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'tencentdb_ai' AND table_name = 'model_list'
ORDER BY ordinal_position;

   column_name   | data_type
-----------------+-----------
 model_name      | name
 json_path       | jsonpath
 secretid        | bytea
 secretkey       | bytea
 version         | text
 region          | text
 id_random       | integer
 key_random      | integer
 backend_type    | text
 real_model_name | name
 api_key         | bytea
 api_key_random  | integer
 embedding_dim   | integer
(13 rows)

-- ===== 文本向量模型注册模板 =====
-- 关键参数：第5个参数 p_backend_type 必须显式传 'tokenhub'（默认值是 'hunyuan'）
-- 第6个参数 real_model_name 为 TokenHub 上的实际模型名
-- 根据实际需求选择以下一个即可

-- 模型 A：轻量文本向量（1024 维，成本低，速度快）
-- 场景：中文知识库检索、FAQ 匹配、文本聚类分类
SELECT tencentdb_ai.add_model(
    'my-text-embed-0.6b',              -- 模型别名（自定义）
    NULL, NULL, NULL,                   -- version, region, json_path（不需要）
    'tokenhub',                         -- backend_type：必须显式指定为 'tokenhub'
    'kinfra-text-embedding-0.6b'        -- real_model_name
);
SELECT tencentdb_ai.update_model_attr('my-text-embed-0.6b', 'api_key', 'sk-tp-你的APIKey');
UPDATE tencentdb_ai.model_list SET embedding_dim = 1024 WHERE model_name = 'my-text-embed-0.6b';

-- 模型 B：高精度文本向量（2560 维，精度高）
-- 场景：多语言深度语义检索、跨语言匹配、高质量语义理解
SELECT tencentdb_ai.add_model(
    'my-text-embed-4b',                 -- 模型别名（自定义）
    NULL, NULL, NULL,
    'tokenhub',
    'kinfra-text-embedding-4b'
);
SELECT tencentdb_ai.update_model_attr('my-text-embed-4b', 'api_key', 'sk-tp-你的APIKey');
UPDATE tencentdb_ai.model_list SET embedding_dim = 2560 WHERE model_name = 'my-text-embed-4b';