ApsaraDB RDS:Build an AI application with RDS for PostgreSQL and Dify

Step 1: Create an RDS for PostgreSQL instance

1. Quickly create an RDS for PostgreSQL instance.

2. Create an account and a database for the RDS for PostgreSQL instance.

   Note the following:

   • When you create the account, set Account Type to Privileged Account.

   • When you create the database, set Authorized Account to the privileged account that you created.

3. Enable a public endpoint for the RDS for PostgreSQL instance. Enable or disable a public endpoint.

   Add the public IP address of your ECS instance to the whitelist of the RDS for PostgreSQL instance. Configure an IP address whitelist.

4. Enable the vector extension for the target database. Manage extensions.

Step 2: Deploy Dify

1. Create an ECS instance. Create an instance by using the wizard.

   • A CPU-based ECS instance supports online LLMs.

   • A GPU-accelerated ECS instance supports both online and locally deployed LLMs.

   Important

   • This article uses Alibaba Cloud Linux 3 as an example.

   • If you use a GPU-accelerated ECS instance, install the GPU driver when you configure the image. This allows you to deploy LLMs with Ollama.

2. Install Docker on the ECS instance.

3. (Optional) If you are using a GPU-accelerated ECS instance, run the following command to install the container-toolkit component.

   Install the container-toolkit component

   curl -s -L https://nvidia.github.io/nvidia-container-runtime/centos8/nvidia-container-runtime.repo | \
   sudo tee /etc/yum.repos.d/nvidia-container-runtime.repo
   sudo yum install -y nvidia-container-toolkit
   # Restart Docker.
   sudo systemctl restart docker

4. Clone the Dify source code.

   git clone https://github.com/langgenius/dify.git

   To install a specific version, use the branch parameter. Available versions: dify. Example for v1.0.0:

   git clone https://github.com/langgenius/dify.git --branch 1.0.0

   Note

   If Git is not installed, run sudo yum install git -y to install it.

5. Configure environment variables to set RDS for PostgreSQL as the default database and vector store.

   1. Set RDS for PostgreSQL as the default database.

      export DB_USERNAME=testdbuser
      export DB_PASSWORD=dbPassword
      export DB_HOST=pgm-****.pg.rds.aliyuncs.com
      export DB_PORT=5432
      export DB_DATABASE=testdb01

      Replace these values with your actual settings.

      Parameter	Description
      DB_USERNAME	The privileged account of the RDS for PostgreSQL instance.
      DB_PASSWORD	The password of the privileged account for the RDS for PostgreSQL instance.
      DB_HOST	The public endpoint of the RDS for PostgreSQL instance.
      DB_PORT	The public port of the RDS for PostgreSQL instance. The default value is 5432.
      DB_DATABASE	The name of the database in the RDS for PostgreSQL instance.

   2. Set RDS for PostgreSQL as the default vector database.

      export VECTOR_STORE=pgvector
      export PGVECTOR_HOST=pgm-****.pg.rds.aliyuncs.com
      export PGVECTOR_PORT=5432
      export PGVECTOR_USERNAME=testdbuser
      export PGVECTOR_PASSWORD=dbPassword
      export PGVECTOR_DATABASE=testdb01

      Replace these values with your actual settings.

      Parameter	Description
      VECTOR_STORE	Specifies the vector store. Use pgvector for the PostgreSQL extension.
      PGVECTOR_USERNAME	The privileged account of the RDS for PostgreSQL instance.
      PGVECTOR_PASSWORD	The password of the privileged account for the RDS for PostgreSQL instance.
      PGVECTOR_HOST	The public endpoint of the RDS for PostgreSQL instance.
      PGVECTOR_PORT	The public port of the RDS for PostgreSQL instance. The default value is 5432.
      PGVECTOR_DATABASE	The name of the database in the RDS for PostgreSQL instance.

   Alternatively, configure these settings in the .env file.

   Configure RDS for PostgreSQL using .env

   1. Copy .env.example to .env. This overwrites the existing file.

      cd /root/dify/docker
      cp .env.example .env

      Note

      After you obtain the Dify source code, the .env.example and .env files are automatically included in the /root/dify/docker directory.

   2. Edit .env with your RDS for PostgreSQL instance details.

      # Default database configuration
      DB_USERNAME=testdbuser
      DB_PASSWORD=dbPassword
      DB_HOST=pgm-****.pg.rds.aliyuncs.com
      DB_PORT=5432
      DB_DATABASE=testdb01
      # Default vector database configuration
      VECTOR_STORE=pgvector
      PGVECTOR_HOST=pgm-****.pg.rds.aliyuncs.com
      PGVECTOR_PORT=5432
      PGVECTOR_USER=testdbuser
      PGVECTOR_PASSWORD=dbPassword
      PGVECTOR_DATABASE=testdb01

6. (Optional) To save resources, disable the default database and Weaviate containers by editing the .env and docker-compose.yaml files.

   • Edit the .env file

     1. (Optional) Copy .env.example to .env.

        Important

        Skip this step if you already copied the file when configuring the database.

        cd /root/dify/docker
        cp .env.example .env

     2. Comment out the following line in .env.

        #COMPOSE_PROFILES=${VECTOR_STORE:-weaviate}

   • Edit the docker-compose.yaml file

     1. Back up docker-compose.yaml.

        cd /root/dify/docker
        cp docker-compose.yaml docker-compose.yaml.bak

     2. Edit docker-compose.yaml to disable the default database container.

        • In the API service section, comment out - db under depends_on:.

          # API service
          api:
            ......
            depends_on:
              #- db          
              - redis  

        • In the worker service section, comment out - db under depends_on:.

          # worker service
          # The Celery worker for processing the queue.
          worker:
            ......
            depends_on:
              #- db
              - redis

        • In the The postgres database section, add the profiles: -pg configuration.

          # The postgres database.
          db:
            image: postgres:15-alpine
            profiles:
              - pg 

        • In The Weaviate vector store section, comment out - '' under profiles:.

          # The Weaviate vector store.    
          weaviate:
             image: semitechnologies/weaviate:1.19.0
             profiles:
               #- ''
               - weaviate

7. Start Dify.

   cd /root/dify/docker
   docker compose -f docker-compose.yaml up -d

Step 3: Access the Dify service

1. Open http://<public_IP_address_of_your_ECS_instance>/install in a browser to start the Dify installation.

   Note

   If the page fails to load, refresh it. Dify may still be initializing.

2. Follow the instructions to set up an administrator account (email, username, and password).

Step 4: Add and configure an AI model

This example uses Qwen.

1. Log on to Dify.

2. Click your username > Settings.

3. On the Settings page, choose Model Providers > Qwen > (Set).

4. On the Settings page for Qwen, click the link to obtain an API key from Alibaba Cloud Model Studio.

5. Paste your API key and click Save.

On a GPU-accelerated ECS instance, you can deploy the Qwen LLM locally.

Step 5: Create a knowledge base

A knowledge base enables the Q&A application to answer domain-specific questions accurately.

Prerequisites

Prepare corpus files for the knowledge base. Supported formats: TXT, MARKDOWN, MDX, PDF, HTML, XLSX, XLS, DOCX, CSV, MD, and HTM. Maximum file size: 15 MB.

Procedure

1. Click Knowledge > Create Knowledge > Import from Text > Select File > Next to upload your files.

   Supported formats: TXT, MARKDOWN, MDX, PDF, HTML, XLSX, XLS, DOCX, CSV, MD, and HTM. Maximum file size: 15 MB.

2. After you click Next, follow the on-screen instructions for text chunking and cleaning.

   Keep the default settings. The knowledge base automatically chunks, cleans, and indexes the documents.

Verify the knowledge base and index

Verify the knowledge base contents and index in RDS for PostgreSQL.

1. Connect to an RDS for PostgreSQL instance used by Dify.

2. Query the knowledge base ID.

   SELECT * FROM datasets;

3. Derive the table name from the dataset ID: replace hyphens (-) with underscores (_), add prefix embedding_vector_index_ and suffix _nod. Example query:

   SELECT * FROM embedding_vector_index_6b169753_****_****_be66_9bddc44a4848_nod;

4. Confirm the knowledge base index.

   Dify creates an HNSW index by default to accelerate pgvector similarity searches. The search query looks like:

   SELECT 
       meta, 
       text, 
       embedding <=> $1 AS distance 
   FROM 
       embedding_vector_index_6b169753_****_****_be66_9bddc44a4848_nod 
   ORDER BY 
       distance 
   LIMIT 
       $2;

   Verify that the index parameters meet your recall requirements. The relationship between m, ef_construction, and recall is documented in pgvector performance test (based on HNSW index).

   SELECT *
   FROM pg_indexes
   WHERE tablename = 'embedding_vector_index_6b169753_****_****_be66_9bddc44a4848_nod';

   If no index exists or the default parameters do not meet your recall requirements, create one manually.

   1. (Optional) Drop the existing index.

      DROP INDEX IF EXISTS embedding_vector_index_6b169753_****_****_be66_9bddc44a4848_nod;

   2. Create the index.

      CREATE INDEX ON embedding_vector_index_6b169753_****_****_be66_9bddc44a4848_nod 
      USING hnsw (embedding vector_cosine_ops)
      WITH (m = '16', ef_construction = '100');

   Note

   embedding_vector_index_6b169753_****_****_be66_9bddc44a4848_nod is an example. Replace it with your actual table name.

Step 6: Create an intelligent Q&A application

This example uses the Question Classifier + Knowledge + Chatbot template.

1. Click Studio > Create from Template.

2. Find the Question Classifier + Knowledge + Chatbot template and click Use this template.

3. Configure the application name and icon, and then click Create.

4. On the Studio page, click the card for the new application to open the application orchestration page.

5. Configure the application workflow: delete the Answer module, keep one Knowledge Retrieval module, and change the AI model for the Question Classifier and LLM modules to Qwen.

   When you configure the Qwen model, set the Top P value to less than 1.

   After you select the qwen-vl-max-0809 model, click the settings icon next to the model name to open the parameter configuration panel, and set Top P to 0.8.

6. Edit the Question Classifier module to define question categories. For a PostgreSQL knowledge base:

   • PostgreSQL-related questions: route to the knowledge base and Qwen for a summarized answer.

   • Other questions: route directly to Qwen.

7. Add the PostgreSQL knowledge base to the Knowledge Retrieval module.

   In the Knowledge Retrieval node, set Query Variable to sys.query, select pg for the knowledge base, and set the retrieval mode to High-quality · Vector Retrieval.

8. Click Preview to test the application, then click Publish to deploy.