For AI agents: The documentation index is at https://docs.digitalocean.com/llms.txt. Markdown versions of pages use the same URL with index.html.md in place of the HTML page (for example, append index.html.md to the directory path instead of opening the HTML document).

Create a PostgreSQL vector database, connect from a trusted source, enable pgvector, add sample embeddings, and run a similarity search in about 15 minutes.

Before you begin, you need:

• A PostgreSQL vector database. Provisioning can take several minutes.
• A trusted source added to the cluster, such as your current IP address.
• The cluster’s host, port, username, password, and database name from the cluster’s connection details.
• A terminal on the trusted source with psql installed.
• Precomputed embeddings from your application or embedding pipeline. PostgreSQL stores embeddings, but doesn’t generate them.

For more information about creating PostgreSQL database clusters, see Create a PostgreSQL Cluster.

Export Connection Details on a Trusted Source

After you create your PostgreSQL vector database and secure the cluster, export the connection details as environment variables:

1. From your vector database’s Network Access tab, choose a trusted source to open a terminal session. For example, if you added your current IP address as a trusted source, open the terminal from the same computer and network using that IP address.

2. In the terminal, export the connection details as environment variables:

   export PGHOST="<your-cluster-host>"
   export PGPORT="<your-cluster-port>"
   export PGUSER="<your-cluster-username>"
   export PGPASSWORD="<your-cluster-password>"
   export PGDATABASE="<your-database-name>"
   export PGSSLMODE="require"

   Replace <your-cluster-host>, <your-cluster-port>, <your-cluster-username>, <your-cluster-password>, and <your-database-name> with the values from the cluster’s connection details.

3. Verify the connection:

   psql -c "SELECT version();"

   If successful, the command returns the PostgreSQL version.

Run a Cosine Similarity Query

PostgreSQL vector databases support vector storage and similarity search through the pgvector extension. After the cluster is active, you can enable the extension, create a table with a vector column, insert embeddings, and run similarity queries.

This example creates a small table with 4-dimensional vectors, adds sample documents, and searches for the closest matches to a coffee-related query vector:

1. Using the trusted source, open a terminal session. For example, if you added your current IP address as a trusted source, open the terminal from the same computer and network using that IP address.

2. Then, connect to the cluster with psql:

   psql

3. Enable the vector extension:

   CREATE EXTENSION IF NOT EXISTS vector;

   Note

   The project is named “pgvector”, but the registered extension name is vector. CREATE EXTENSION pgvector; fails. Always use CREATE EXTENSION vector;.

4. Create a table that stores 4-dimensional vectors:

   Note

   Use the dimension required by your embedding model, such as 384, 768, 1024, or 1536 for production use.

   CREATE TABLE articles (
       id bigserial PRIMARY KEY,
       title text NOT NULL,
       body text NOT NULL,
       embedding vector(4)
   );

   In this table, embedding vector(4) defines a vector column with four dimensions. The dimension must match the embedding model.

5. Then, add four sample documents with precomputed embeddings:

   INSERT INTO articles (title, body, embedding) VALUES
       (
           'Coffee brewing basics',
           'Pour-over, espresso, and cold brew compared.',
           '[0.91, 0.10, 0.05, 0.02]'
       ),
       (
           'Best espresso machines',
           'A buyer guide for home espresso setups.',
           '[0.88, 0.15, 0.07, 0.04]'
       ),
       (
           'Intro to deep learning',
           'Neural networks, backpropagation, activations.',
           '[0.05, 0.92, 0.18, 0.10]'
       ),
       (
           'Hiking trails near Denver',
           'Five scenic day hikes within an hour of the city.',
           '[0.12, 0.08, 0.90, 0.22]'
       );

6. Then, search for the two documents closest to a coffee-related query vector:

   SELECT
       id,
       title,
       body,
       1 - (embedding <=> '[0.90, 0.12, 0.06, 0.03]') AS cosine_similarity
   FROM articles
   ORDER BY embedding <=> '[0.90, 0.12, 0.06, 0.03]'
   LIMIT 2;

   The response returns the two coffee-related articles as the highest-ranked results.

Run the Same Query with Python

To run the same query with Python:

1. First, install psycopg:

   pip install "psycopg[binary]>=3.1"

2. Then, create a Python file with the following code:

   import os
   import psycopg
   
   conninfo = (
       f"host={os.environ['PGHOST']} "
       f"port={os.environ.get('PGPORT', '25060')} "
       f"user={os.environ['PGUSER']} "
       f"password={os.environ['PGPASSWORD']} "
       f"dbname={os.environ['PGDATABASE']} "
       f"sslmode={os.environ.get('PGSSLMODE', 'require')}"
   )
   
   query_vector = "[0.90, 0.12, 0.06, 0.03]"
   
   with psycopg.connect(conninfo) as conn:
       with conn.cursor() as cur:
           cur.execute(
               """
               SELECT
                   id,
                   title,
                   1 - (embedding <=> %s::vector) AS cosine_similarity
               FROM articles
               ORDER BY embedding <=> %s::vector
               LIMIT 2;
               """,
               (query_vector, query_vector),
           )
   
           for row in cur.fetchall():
               print(row)

   Fill in the PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, and PGSSLMODE environment variables you exported earlier.

3. Save the file as query-postgresql.py, and then run it:

   python3 query-postgresql.py

   The output lists the closest matching article titles and their cosine similarity scores.