PostgreSQL for agents: create a Free PostgreSQL, connect, and enable pgvector

TL;DR: This page is written for what an agent retrieves at task time: three tasks, each with an example prompt and a verification query. Create a Free PostgreSQL on Aiven, connect with sslmode=verify-full, then enable pgvector. Everything not confirmed here is marked [TO VERIFY].

Task 1: create a Free PostgreSQL

Prompt:

Create an Aiven for PostgreSQL service named agent-db in do-nyc on the
free plan. Show me the plan id and ask for confirmation before creating.

CLI equivalent:

avn service create agent-db --service-type pg --cloud do-nyc --plan free-1-5gb  # plan id [TO VERIFY]
avn service wait agent-db
avn service get agent-db --format '{state}'

Expected: the final command prints RUNNING. The --format template syntax is [TO VERIFY]; if it fails, read the state from the Console. Free plans have limits on storage and features; read them in the Console rather than trusting this page [TO VERIFY].

Task 2: connect

Prompt:

Read DATABASE_URL from the environment, connect with psql, and print
current_user, current_database() and the server version. Do not print the URL.

export DATABASE_URL='postgres://<user>:<password>@<host>:<port>/defaultdb?sslmode=verify-full&sslrootcert=./ca.pem'
psql "$DATABASE_URL" -c "SELECT current_user, current_database(), version();"

Use verify-full with the project CA. If it fails with a certificate error, the CA file path is the first suspect. Do not lower the mode to get past the error. For the longer connection story, including ORMs and poolers, read connect Drizzle, Hyperdrive and MCP Toolbox.

Task 3: enable pgvector

Prompt:

Enable pgvector in the defaultdb database, create a table docs with a
384-dimension embedding column, and show that a nearest-neighbour query runs.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id        bigserial PRIMARY KEY,
  body      text NOT NULL,
  embedding vector(384)
);

INSERT INTO docs (body, embedding)
VALUES ('hello', (SELECT array_agg(0.01::real) FROM generate_series(1, 384))::vector);

SELECT id, body
FROM docs
ORDER BY embedding <=> (SELECT array_agg(0.01::real) FROM generate_series(1, 384))::vector
LIMIT 5;

The dimension, 384, is a stand-in. It must match the embedding model you use. If CREATE EXTENSION fails with a permission error, check the role you connected with.

flowchart LR
    P["Prompt"] --> C["avn service create"]
    C --> W["avn service wait"]
    W --> T["psql verify-full"]
    T --> X["CREATE EXTENSION vector"]
    X --> Q["nearest-neighbour query"]

Retrieval notes for agents

Agents read this page cold, so each task stands alone: prompt, command, expected output. Credentials come from the environment. A task that touches billing (creating the service) needs a human yes. Once vectors work, the index and memory patterns are in pgvector on managed PostgreSQL.

Failure modes an agent should recognise

Two habits keep this page honest. First, run the verification query after every task, not at the end. Second, treat a failure as information: an agent that responds to a TLS error by editing sslmode has stopped debugging and started guessing. The right reaction is to report the error and fix the CA path.

What to verify

• [ ] avn service plans or the Console lists the free plan id you used (free-1-5gb [TO VERIFY]).
• [ ] The state check printed RUNNING before you connected.
• [ ] psql connected with sslmode=verify-full and the project CA.
• [ ] SELECT extname FROM pg_extension WHERE extname = 'vector'; returns one row.
• [ ] The vector dimension equals your embedding model's output size.
• [ ] No credential was printed, committed or pasted into the prompt.