AnalyticDB for PostgreSQL Long-term Memory Advanced Features Guide

Set an expiration date

Pass an expiration_date in the meta parameter of adbpg_llm_memory.add() to automatically expire a memory at a specified date. Expired memories are excluded from all subsequent retrievals.

-- This memory expires on 2025-11-30 and will not be recalled on or after 2025-12-01.
SELECT adbpg_llm_memory.add($$
[
  {"role": "user", "content": "I will travel to Beijing this weekend"}
]
$$, 'test_u', null, null, $${"expiration_date": "2025-11-30"}$$, null, null);

Update memory content

Use adbpg_llm_memory.update() to replace the content of an existing memory by ID. This is useful when the LLM-extracted content does not accurately reflect what you want stored.

Version requirement: 7.2.1.9 and later

SELECT adbpg_llm_memory.update(
  'b55a108f-f073-4d48-87ec-2ffc18603e3d',
  'likes to drink coffee'
);

Parameters:

Import memories without LLM extraction

Set infer => 'false' in adbpg_llm_memory.add() to store memory content directly, skipping the large language model (LLM) extraction step. This is useful when your content has already been processed externally.

Version requirement: 7.2.1.10 and later

SELECT adbpg_llm_memory.add($$
[
  {"role": "user", "content": "I will travel to Beijing this weekend"}
]
$$, 'test_u', infer => 'false');

Customize the fact extraction prompt

Set custom_fact_extraction_prompt in adbpg_llm_memory.config() to control how facts are extracted from conversations.

Prompt structure guidelines:

1. State the allowed fact types explicitly.

2. Include short examples that match the style of your production messages.

3. Show both a populated output and an empty output ({"facts": []}).

4. Remind the model to return JSON with only the facts key.

SELECT adbpg_llm_memory.config(
$$
{
  "llm": {
    "provider": "qwen",
    "config": {
      "model": "qwen3-32b",
      "qwen_base_url": "https://dashscope.aliyuncs.com/compatible-mode/v1",
      "api_key": "sk-xxxxxxx"
    }
  },
  "embedder": {
    "provider": "openai",
    "config": {
      "model": "text-embedding-v4",
      "api_key": "sk-xxxxxx",
      "embedding_dims": "1536",
      "openai_base_url": "https://dashscope.aliyuncs.com/compatible-mode/v1"
    }
  },
  "vector_store": {
    "provider": "adbpg",
    "config": {
      "user": "username",
      "dbname": "postgres",
      "hnsw": "True",
      "port": "xx"
      "embedding_model_dims": "1536"
    }
  },
  "custom_fact_extraction_prompt": "xxxx"
}
$$
);

Filter memories by structured conditions

Pass a filter JSON object to adbpg_llm_memory.search() to narrow results by specific fields. Filters support compound logic using AND, OR, and NOT.

Entity fields:

Time fields:

Content fields:

Example 1: Filter by agent and time range

SELECT adbpg_llm_memory.search(
  'Recommend a place for a weekend trip',
  'test_u',
  null,
  null,
  $$
  {
    "AND": [
      {"created_at": {"gte": "2025-07-29", "lte": "2025-07-30"}},
      {"agent_id": "travel1"}
    ]
  }
  $$
);

Example 2: Filter by a metadata field

-- The categories field is set in the meta parameter when calling adbpg_llm_memory.add().
SELECT adbpg_llm_memory.search(
  'what do you know about me?',
  'test_u',
  null,
  null,
  $$
  {
    "AND": [
      {"categories": {"contains": "food"}}
    ]
  }
  $$
);

Limit the number of results

Set the limits parameter to control how many memories search() returns. The default is 10.

Version requirement: 7.2.1.7 and later

-- Return only the 5 most relevant memories.
SELECT adbpg_llm_memory.search(
  'Recommend a place for a weekend trip',
  'test_u',
  null,
  null,
  $$
  {
    "AND": [
      {"created_at": {"gte": "2025-07-29", "lte": "2025-07-30"}},
      {"agent_id": "travel1"}
    ]
  }
  $$,
  5
);

Set a similarity threshold

Set the threshold parameter (a FLOAT between 0.0 and 1.0) to exclude memories below a minimum similarity score. Only memories with a score above the threshold are returned.

Version requirement: 7.2.1.9 and later

-- Return only memories with a similarity score above 0.4.
SELECT adbpg_llm_memory.search(
  query => 'what do you know about me?',
  user_id => 'test_u',
  threshold => 0.4
);

Enable reranking

Add a reranker section to adbpg_llm_memory.config() to apply a reranking model as a secondary sort after vector retrieval. Reranking improves result relevance, with the reranking score returned in the rerank_score field.

Version requirement: 7.2.1.9 and later

Supported models: Qwen series rerank models (see Text Rerank API)

Reranking adds latency to each search request. Test the latency impact in your environment before enabling it in production, especially for user-facing, real-time applications.

-- When configuring long-term memory, add information about the Rerank model. Currently, only Qwen series Rerank models are supported.
SELECT adbpg_llm_memory.config(
$$
{
  "llm": {
    "provider": "qwen",
    "config": {
      "model": "qwen3-32b",
      "qwen_base_url": "https://dashscope.aliyuncs.com/compatible-mode/v1",
      "api_key": "sk-xxxxxxx"
    }
  },
  "embedder": {
    "provider": "openai",
    "config": {
      "model": "text-embedding-v3",
      "api_key": "sk-xxxxxx",
      "embedding_dims": "1536",
      "openai_base_url": "https://dashscope.aliyuncs.com/compatible-mode/v1"
    }
  },
  "vector_store": {
    "provider": "adbpg",
    "config": {
      "user": "username",
      "dbname": "postgres",
      "hnsw": "True",
      "embedding_model_dims": "1536"
    }
  },
  "reranker": {
        "provider": "qwen",
        "config": {
            "model": "qwen3-rerank",
            "api_key": "sk-xxxx",
            "top_k": 2    -- Returns the top 2 results.
        }
  }
}
$$
);

top_k controls the maximum number of reranked results returned. In the example above, at most 2 results are returned.

Track memory operation history

Enable history tracking to record all memory operations — reads, writes, and deletes — for auditing and debugging purposes.

Version requirement: 7.2.1.9 and later

Set the trace field in adbpg_llm_memory.config() to one of the following values:

Enable history tracking:

-- Configure whether to record history operations.
SELECT adbpg_llm_memory.config(
$$
{
  "llm": {
    "provider": "qwen",
    "config": {
      "model": "qwen3-32b",
      "qwen_base_url": "https://dashscope.aliyuncs.com/compatible-mode/v1",
      "api_key": "sk-xxxxxxx"
    }
  },
  "embedder": {
    "provider": "openai",
    "config": {
      "model": "text-embedding-v3",
      "api_key": "sk-xxxxxx",
      "embedding_dims": "1536",
      "openai_base_url": "https://dashscope.aliyuncs.com/compatible-mode/v1"
    }
  },
  "vector_store": {
    "provider": "adbpg",
    "config": {
      "user": "username",
      "dbname": "postgres",
      "hnsw": "True",
      "embedding_model_dims": "1536"
    }
  },
  "trace": "all"
$$
);
```

-- View the history operations of a memory. The parameter is the memory ID.
SELECT adbpg_llm_memory.get_history('b55a108f-f073-4d48-87ec-2ffc18603e3d');

-- Delete the history of a specific memory. The parameter is the memory ID.
SELECT adbpg_llm_memory.delete_history('b55a108f-f073-4d48-87ec-2ffc18603e3d');

-- Delete all memory history.
SELECT adbpg_llm_memory.delete_all_history();

-- Get the disk size occupied by memory history operations.
SELECT adbpg_llm_memory.history_size();

Manage history records:

-- Retrieve the operation history for a specific memory.
SELECT adbpg_llm_memory.get_history('b55a108f-f073-4d48-87ec-2ffc18603e3d');

-- Delete the history for a specific memory.
SELECT adbpg_llm_memory.delete_history('b55a108f-f073-4d48-87ec-2ffc18603e3d');

-- Delete all history records.
SELECT adbpg_llm_memory.delete_all_history();

-- Check disk space used by history records.
SELECT adbpg_llm_memory.history_size();

Check memory disk usage

Call adbpg_llm_memory.memory_size() to get the total disk space used by all long-term memory data.

Version requirement: 7.2.1.9 and later

SELECT adbpg_llm_memory.memory_size();