# rds-text

Part of **RDS**

# ApsaraDB RDS Text and Vector Search

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Enable RUM Extension | Synchronous | Activate the RUM extension for advanced full-text search with positional and timestamp support. |
| Run Chinese Full-Text Search | Synchronous | Use the pg_jieba extension to perform Chinese language segmentation and full-text search. |
| Perform Vector Search | Synchronous | Execute vector similarity searches using the PASE extension with IVFFlat or HNSW algorithms. |
| Calculate String Similarity | Synchronous | Compute string similarity using Soundex, Levenshtein, Metaphone, or Double Metaphone via fuzzystrmatch. |
| Generate Text Embedding | Synchronous | Generate vector embeddings from text using external models via the rds_embedding extension. |
| Create Vector Index | Synchronous | Create a compressed RabitQ index on vector columns for efficient similarity search. |

## API Calling Patterns

### Authentication
Most operations require **no authentication** beyond standard PostgreSQL database credentials, as they are executed as SQL commands inside the RDS instance.

However, for **text embedding generation** using `rds_embedding`, an external API key is required:
- Use the `Authorization: Bearer <API-KEY>` header when registering the model.
- Store your DashScope API key securely; it is passed during model registration and embedding calls.
- Set the environment variable `DASHSCOPE_API_KEY` if managing keys outside SQL.

> **Recommendation**: Use database user permissions for extension management (CREATE/DROP EXTENSION), and secure API keys via parameterized SQL or secrets management for embedding calls.

### Service Endpoint
All operations are performed **inside the PostgreSQL database** via SQL. There is no external REST endpoint for most functions.

For **text embedding**, the `rds_embedding` extension calls external DashScope endpoints:
- China region: `https://dashscope.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding`
- International region: `https://dashscope-intl.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding`

Common regions for RDS instances include `cn-hangzhou`, `cn-shanghai`, and `cn-beijing`.

### Synchronous SQL Execution
All capabilities are invoked via standard SQL statements executed in the RDS PostgreSQL instance:
1. Connect to your RDS instance using a PostgreSQL client (e.g., psql, JDBC, psycopg2).
2. Enable the required extension using `CREATE EXTENSION <extension_name>;`.
3. Use provided functions (e.g., `to_tsvector`, `levenshtein`, `rds_embedding.get_embedding_by_model`) in SELECT/INSERT statements.
4. For vector search, create an index first, then query using custom operators (`<#>`, `<?>`).

No polling or async handling is needed—responses are immediate.

## Parameter Reference

### Perform Vector Search (PASE)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| clustering_type | integer | Yes | — | 0 or 1 | Clustering method: 0 = external, 1 = internal |
| distance_type | integer | No | 0 | 0 or 1 | Distance metric: 0 = Euclidean, 1 = dot product |
| dimension / dim | integer | Yes | — | 8–512 | Vector dimension (max 512) |
| base64_encoded | integer | No | 0 | 0 or 1 | Input format: 0 = float4[], 1 = Base64 string |
| clustering_params | string | Yes | — | format: "ratio,k" | For internal clustering: sample ratio and k centroids |
| base_nb_num | integer | Yes | — | 16–128 | Neighbors per node (HNSW) |
| ef_build | integer | Yes | — | 40–400 | Heap size during index build |
| ef_search | integer | Yes | — | 10–400 | Heap size during search |

### Calculate String Similarity (fuzzystrmatch)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| source | text | Yes | — | max 255 chars | Source string |
| target | text | Yes | — | max 255 chars | Target string |
| ins_cost | int | No | 1 | — | Insertion cost (Levenshtein) |
| del_cost | int | No | 1 | — | Deletion cost |
| sub_cost | int | No | 1 | — | Substitution cost |
| max_d | int | No | — | — | Max distance threshold |
| max_output_length | int | No | 100 | — | Max Metaphone output length |

### Generate Text Embedding (rds_embedding)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| mname | text | Yes | — | — | Model name (e.g., 'text-embedding-v3') |
| murl | text | Yes | — | — | Model endpoint URL |
| mauth_header_template | text | Yes | — | — | Auth header template with API key |
| mbody_template | text | Yes | — | — | JSON body with `%s` placeholder for input text |
| membedding_path | text | Yes | — | — | JSON path to extract embedding array |
| api-key | text | Yes | — | — | DashScope API key |
| texts | text | Yes | — | — | Input text to embed |

### Create Vector Index (RabitQ)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| lists | integer | No | — | positive integer | Number of IVF centroids |
| ivf_rabitq.epsilon | float | No | 1.9 | [0.1, 4.0] | Error margin coefficient (higher = better recall) |
| ivf_rabitq.topk | integer | No | 10 | [1, 32768] | Vectors to rerank exactly |
| ivf_rabitq.max_rerank_scan_tuples | integer | No | 5000 | [1, INT_MAX] | Max vectors scanned for reranking |

## Code Examples

### Enable RUM Extension - SQL - all

```sql
CREATE EXTENSION rum;
```

### Chinese Full-Text Search with Custom Dictionary - SQL - all

```sql
CREATE EXTENSION pg_jieba;
INSERT INTO jieba_user_dict VALUES ('阿里云');
SELECT jieba_load_user_dict(0);
SELECT * FROM to_tsvector('jiebacfg', 'zth是阿里云的一个研发工程师');
```

### Vector Search with PASE (IVFFlat) - SQL - all

```sql
CREATE EXTENSION pase;
CREATE INDEX ivfflat_idx ON items USING pase_ivfflat(embedding) 
WITH (clustering_type = 1, distance_type = 0, dimension = 256, clustering_params = '10,100');
SELECT id, embedding <#> '1,1,1'::pase AS distance 
FROM items 
ORDER BY embedding <#> '1,1,1:10:0'::pase ASC 
LIMIT 10;
```

### String Similarity Matching - SQL - all

```sql
CREATE EXTENSION fuzzystrmatch;
SELECT levenshtein('GUMBO', 'GAMBOL');
SELECT metaphone('GUMBO', 4);
SELECT soundex('Anne'), difference('Anne', 'Andrew');
```

### Register and Use Text Embedding Model - SQL - china

```sql
SELECT rds_embedding.add_model(
    'text-embedding-v3',
    'https://dashscope.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding',
    'Authorization: Bearer sk-****',
    '{"input":{"texts":["%s"]},"model":"text-embedding-v3","parameters":{"text_type":"query"}}',
    '->''output''->''embeddings''->0->>''embedding'''
);

INSERT INTO documents (content, embedding)
VALUES (
    'Windy high sky, apes cry sadly',
    rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', 'Windy high sky, apes cry sadly')::real[]
);
```

### Create RabitQ Vector Index - SQL - all

```sql
-- Ensure pgvector >= 0.8.0.2
ALTER EXTENSION vector UPDATE TO "0.8.0.2";
CREATE INDEX ON items USING ivfflat (embedding rabitq_vector_cosine_ops) WITH (lists=1000);
```

### Call Embedding API Directly via curl - bash - international

```bash
curl --location 'https://dashscope-intl.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding' \
--header 'Authorization: Bearer <API-KEY>' \
--header 'Content-Type: application/json' \
--data '{
    "model": "text-embedding-v3",
    "input": {
        "texts": [
            "Windy high sky, apes cry sadly"
        ]
    },
    "parameters": {
        "text_type": "query"
    }
}'
```

## Response Format

### Generate Text Embedding Success Response

```json
{
  "output": {
    "embeddings": [
      {
        "embedding": [0.123456789, -0.987654321, ...]
      }
    ]
  }
}
```

**Key Fields**:
- `output.embeddings[].embedding` — The generated vector as a list of floats

### Perform Vector Search Success Response

```text
id | distance
---|---
1 | 1.732
2 | 1.414
3 | 1.000
```

**Key Fields**:
- `id` — Identifier of the matched record
- `distance` — Computed similarity distance (lower = more similar)

### Calculate String Similarity Output Examples

```text
SELECT soundex('hello world!');  -- Result: H463
SELECT levenshtein('GUMBO', 'GAMBOL');  -- Result: 2
SELECT metaphone('GUMBO', 4);  -- Result: GMB
```

**Key Fields**:
- `soundex_code` — Phonetic hash (4-char code)
- `levenshtein_distance` — Edit distance between strings
- `metaphone_code` — Phonetically simplified string
- `dmetaphone_primary` / `dmetaphone_secondary` — Primary and alternate phonetic codes

## Error Handling

| Error Code | Description | Recommended Action |
|-----------|-------------|-------------------|
| 403 | Access denied. Only privileged accounts can create or drop extensions. | Use a superuser or account with CREATE privilege on the database. |
| 404 | The jieba_user_dict table or jieba_load_user_dict() function does not exist. | Update the minor engine version to 20220730 or later and reinstall pg_jieba. |
| 400 | Invalid request parameter. Check that all required parameters are provided and within valid ranges. | Validate parameter values against constraints (e.g., dimension ≤ 512). |
| 500 | Internal server error during index creation or query execution. | Retry after a short delay; if persistent, contact Alibaba Cloud support. |
| 42P01 | Undefined table error — occurs if the table referenced in the query does not exist. | Verify table name and schema; ensure table is created before querying. |
| 42804 | Cannot cast type error — occurs when trying to cast incompatible data types. | Ensure input types match function expectations (e.g., text for string functions). |
| 22001 | String data right truncation — input exceeds 255 characters. | Truncate input strings to ≤255 characters. |
| 401 | Unauthorized access. Verify that the API key is correct. | Check DashScope API key format and permissions; regenerate if expired. |

## Environment Requirements

- **PostgreSQL Version**: 
  - RUM: PostgreSQL 10+ (14/15 require minor version ≥20221030)
  - pg_jieba: PostgreSQL 10.0+, minor version ≥20241030 for PG17
  - PASE: PostgreSQL 11–16
  - rds_embedding: PostgreSQL 14+ (PG17 requires minor version ≥20241030)
  - RabitQ: PostgreSQL 14+, minor kernel version ≥20260330, pgvector ≥0.8.0.2
- **Extensions**: Must be enabled via `CREATE EXTENSION` before use.
- **Network**: For `rds_embedding`, configure a NAT Gateway to allow outbound HTTPS to DashScope.
- **Permissions**: Database user must have privileges to create extensions and tables.

## FAQ

Q: Do I need to install anything outside the database to use these features?
A: No—all functionality is provided via PostgreSQL extensions. Just run `CREATE EXTENSION` in your RDS instance. For `rds_embedding`, you need a DashScope API key and NAT Gateway for internet access.

Q: Can I use these extensions with read-only replicas?
A: Extensions must be enabled on the primary instance. Once enabled, functions like `to_tsvector` or `levenshtein` can be used on replicas, but DDL (e.g., `CREATE INDEX`) must run on the primary.

Q: Why is my vector search slow?
A: Ensure you’ve created an index (PASE or RabitQ). Without an index, searches perform a full scan. Also, tune `ef_search` (PASE) or `ivf_rabitq.topk` (RabitQ) for speed/recall trade-offs.

Q: How do I update an extension like pg_jieba or pgvector?
A: Use `ALTER EXTENSION <name> UPDATE;`. Check the documentation for required minor engine versions before updating.

Q: Are Chinese full-text search results case-sensitive?
A: No—`pg_jieba` performs segmentation based on dictionary and context, not case. However, ensure your text is properly encoded (UTF-8).

## Pricing & Billing

### Billing Model
- **RUM, pg_jieba, fuzzystrmatch, PASE, RabitQ**: Billed as part of RDS instance hourly cost. No per-request fees.
- **rds_embedding**: Billed per token (input + output) when calling external DashScope models.

### Price Reference

| Tier | Input Price | Output Price |
|------|------------|-------------|
| text-embedding-v3 | 0.0001 /tokens | 0.0001 /tokens |
| PASE vector search | 0.0001 / | 0.0001 / |
| fuzzystrmatch | 0.0001 / | 0.0001 / |

### Free Tier
- **rds_embedding**: 1 million tokens free per month
- **PASE vector search**: 10,000 free queries per month

### Usage Limits
- **rds_embedding**: 100 QPS
- **fuzzystrmatch**: Input strings ≤255 characters
- **PASE**: Vector dimension ≤512

### Billing Notes
- Extension usage (RUM, pg_jieba, etc.) incurs no additional cost beyond RDS instance fees.
- `rds_embedding` charges apply only when calling external models; storing/querying vectors in RDS is free.
- Index storage (PASE, RabitQ) counts toward RDS disk usage but may reduce costs via compression (RabitQ).