PostgreSQL pgvector on AWS User Guide

Overview

This guide covers the deployment and use of PostgreSQL with pgvector on AWS using cloudimg's pre configured Amazon Machine Image (AMI). It pairs the proven PostgreSQL 17 relational database with pgvector, the open source extension that adds a native vector data type, distance operators and approximate nearest neighbour indexes. The result is a single appliance that stores high dimensional embeddings next to ordinary relational data and ranks rows by similarity to a query vector, the storage layer behind semantic search and retrieval augmented generation (RAG), all on CPU with no GPU required.

PostgreSQL 17 and the matching postgresql-17-pgvector package are installed from the official PostgreSQL PGDG repository. The extension is pre enabled (CREATE EXTENSION vector) in a default database named vectordb, which also ships a small demo items table with a vector(3) column and an HNSW index so you can run a nearest neighbour query the moment the instance boots. The cluster data directory lives on a dedicated, independently resizable EBS volume mounted at /var/lib/postgresql, separate from the operating system disk.

Security by design, no baked credential. The postgres superuser role is password less by construction: a fresh install leaves it with no password, and the captured image ships that way, so there is no default or blank password to discover. On first boot each instance generates a unique password for the postgres role and a unique self signed TLS server certificate, writes them to the root only file /root/postgresql-pgvector-credentials.txt, then enables TLS. Remote clients are accepted only over TLS (hostssl with scram-sha-256); on box administration stays password less through the local unix socket. Until first boot mints the certificate, the database is not reachable off box at all.

What is included:

• PostgreSQL 17 from the official PGDG repository, running under systemd as postgresql.service

• The pgvector extension pre enabled in a default database vectordb, with a demo items table and an HNSW index

• A per instance postgres password and a per instance TLS certificate generated on first boot, written to a root only credentials file

• pg_hba.conf configured so remote clients connect only over TLS with the per instance password, while local access stays password less via the unix socket

• A dedicated EBS data volume for the database at /var/lib/postgresql, plus unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• An AWS account subscribed to this product in AWS Marketplace

• An EC2 key pair in your target region for SSH access

• A security group allowing inbound TCP 22 (administration) from your IP and TCP 5432 (the PostgreSQL wire protocol) from your application network. In production, restrict 5432 to your application subnet.

Recommended instance type: m5.large (2 vCPU, 8 GB RAM) for development and light workloads. For larger vector collections and higher query rates, choose a memory rich type such as r5.large or larger so more of the index fits in RAM.

Connecting to your instance

Step 1 - Launch from the AWS Marketplace console

1. Open the product page in AWS Marketplace and choose Continue to Subscribe, then Continue to Configuration.
2. Select the PostgreSQL 17 + pgvector on Ubuntu 24.04 delivery option and your region, then Continue to Launch.
3. Choose your instance type, VPC and subnet, key pair and the security group described above, then launch.

Step 2 - Launch from the AWS CLI

aws ec2 run-instances \
  --image-id ami-xxxxxxxxxxxxxxxxx \
  --instance-type m5.large \
  --key-name your-key \
  --security-group-ids sg-xxxxxxxx \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pgvector}]'

Step 3 - Connect to your instance

ssh -i your-key.pem ubuntu@<instance-public-ip>

Step 4 - Retrieve your per instance credentials

On the first boot the instance generates its own postgres password and TLS certificate. SSH in and read the root only credentials file:

sudo cat /root/postgresql-pgvector-credentials.txt

The file is mode 0600, owned by root, and contains the per instance password plus a ready to paste TLS connection string. Nothing is baked into the image, every deployed instance has its own password and certificate.

Step 5 - Confirm the service is healthy

Check that PostgreSQL is active and listening, that TLS is on, and that the pgvector extension is enabled in the default database:

sudo systemctl is-active postgresql.service
sudo ss -tlnp | grep 5432
sudo -u postgres psql -tAc 'SHOW ssl;'
sudo -u postgres psql -d vectordb -c "SELECT extname, extversion FROM pg_extension WHERE extname='vector';"

You should see the service active, PostgreSQL listening on 5432, ssl reported as on, and the vector extension with its version.

Step 6 - Connect to the database

On the instance itself you can connect as the postgres superuser with no password, through the local unix socket:

sudo -u postgres psql -d vectordb -c "SELECT version();"

From a remote client, connect over TLS with the per instance password from the credentials file (replace <public-ip> with your instance's address and <POSTGRES_PASSWORD> with the value from the file):

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<public-ip> port=5432 dbname=vectordb user=postgres sslmode=require" -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

The pg_stat_ssl view confirms the connection is encrypted (ssl is t) and reports the negotiated TLS version.

Step 7 - Run a similarity search

The vectordb database ships a demo items table with three dimensional embeddings and an HNSW index, so you can run a nearest neighbour query immediately. The <-> operator is Euclidean (L2) distance; <=> is cosine distance and <#> is inner product. This query ranks the seeded rows by distance to the query vector [1,1,1]:

sudo -u postgres psql -d vectordb -c "SELECT label, round((embedding <-> '[1,1,1]')::numeric, 4) AS distance FROM items ORDER BY embedding <-> '[1,1,1]' LIMIT 3;"

The near-origin row is returned first at distance 0, ahead of mid and far, a nearest neighbour ranking by similarity to the query vector, in plain SQL.

Store your own embeddings

To store your own vectors, create a table with a vector column of your embedding dimension, insert your embeddings, build an approximate nearest neighbour index, and order by distance to a query vector. Real embeddings are typically hundreds or thousands of dimensions; this example uses three for clarity:

sudo -u postgres psql -d vectordb <<'SQL'
CREATE TABLE documents (id bigserial PRIMARY KEY, title text, embedding vector(3));
INSERT INTO documents (title, embedding) VALUES
  ('red apple',   '[0.9, 0.1, 0.1]'),
  ('green apple', '[0.8, 0.2, 0.1]'),
  ('blue sky',    '[0.1, 0.1, 0.9]');
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops);
SELECT title, round((embedding <-> '[0.85, 0.15, 0.1]')::numeric, 4) AS distance
FROM documents ORDER BY embedding <-> '[0.85, 0.15, 0.1]' LIMIT 3;
SQL

Swap vector(3) for your model's dimension (for example vector(1536) for many text embedding models) and generate the embeddings with your preferred model or an AWS service such as Amazon Bedrock.

Security posture

• No baked credential. The postgres role ships password less; the per instance password is generated only on first boot and stored in /root/postgresql-pgvector-credentials.txt (mode 0600, root only).

• TLS for every remote connection. pg_hba.conf permits remote clients only via hostssl ... scram-sha-256. Each instance mints its own self signed certificate on first boot, so no certificate is shared between deployments.

• Local access stays password less through the unix socket for on box administration (sudo -u postgres psql).

• Network. Expose only TCP 22 and TCP 5432 in your security group, and restrict 5432 to your application subnet. The security group is the first layer, TLS and the per instance password the second.

To rotate the password later, run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'" and update the credentials file to match.

Operations

Manage the service through systemd and inspect logs with journalctl:

sudo systemctl status postgresql.service --no-pager
sudo journalctl -u postgresql@17-main.service --no-pager | tail -20

Take a logical backup of a database with pg_dump, for example sudo -u postgres pg_dump vectordb > vectordb.sql. The data directory lives under /var/lib/postgresql/17/main on the dedicated EBS data volume, which you can resize independently of the OS disk.

Support

Every cloudimg image includes 24/7 support. If you have any questions about this PostgreSQL with pgvector image, contact us at support@cloudimg.co.uk.