Databases AWS

PostgreSQL 18 with pgvectorscale on AWS User Guide

| Product: PostgreSQL 18 with pgvectorscale on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying PostgreSQL 18 with pgvectorscale on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened Milvus, Qdrant & Vector Databases and RAG AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other vector databases and RAG tools in the listing are described on the listing's guide hub.

It is a vector search database: one PostgreSQL that stores high-dimensional embeddings next to your ordinary relational data and ranks rows by similarity to a query vector, with two complementary index families in the same database:

  • pgvectorscale adds StreamingDiskANN (the diskann index type), an approximate nearest-neighbour index that keeps most of the graph on disk instead of in memory, plus statistical binary quantisation. It is what lets a modest instance serve a large embedding collection. pgvectorscale has no distribution package: cloudimg compiles it from the pinned upstream source at build time and ships only the finished extension, never the compiler.
  • pgvector provides the vector column type, the distance operators (<-> Euclidean, <=> cosine, <#> inner product) and the HNSW and IVFFlat index types.

Both extensions are enabled in a default database named vectordb, which also holds a demo items table of 2,000 vectors of 128 dimensions with a StreamingDiskANN index, so you can run a real similarity query the moment the instance boots.

What is on the image:

  • PostgreSQL 18.6 from the PostgreSQL project's apt repository (postgresql@18-main.service)
  • pgvectorscale 0.9.1 built from the upstream source at commit 9d9851ce, and pgvector 0.8.7
  • A brand-new cluster created on your instance's first boot, with the vectordb database, both extensions, a vectorapp application role and the demo table
  • TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)
  • A credential guard that runs on every boot and refuses to serve if a published or default password ever authenticates

Default user: ubuntu · Architecture: x86_64 · Root volume: 8 GiB gp3

Security posture

  • No database ships in the image. The data directory is empty in the AMI. First boot runs initdb on your instance, so no role, password, demo row or cluster identity is shared with any other instance.
  • No default password. First boot generates two 32-character passwords, one for the postgres superuser and one for the vectorapp application role, and writes them to /root/pgvectorscale-credentials.txt, readable by root only. On the instance itself you can also use sudo -u postgres psql (peer authentication over the local socket).
  • Nothing is served before first boot finishes. PostgreSQL will not start until first boot has created the cluster, the certificate and the passwords. While it sets the passwords, the server listens on its local socket only, with no TCP port and with statement logging off, and the passwords are sent on standard input, never on a command line.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate under /etc/postgresql/18/main/ and turns TLS on. No key ships in the image.
  • Localhost only. PostgreSQL listens on 127.0.0.1:5432, and the listing's security group opens SSH (22) only. Reach the database through an SSH tunnel, or open it to your VPC as described in Step 7.
  • TLS on every TCP connection. pg_hba.conf accepts TCP clients, loopback included, only over hostssl with scram-sha-256; there is no plain-text TCP rule and no trust rule.
  • Connection logging from the socket. log_connections is on: every connection is logged with the client address PostgreSQL read from the network socket, which a client cannot forge.
  • No metadata access for the database. A database user can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM as a superuser), so every PostgreSQL unit and both first-boot services are blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works. If you add backups to S3 from inside the database, give them explicit credentials; they cannot use the instance role.
  • No core dumps. PostgreSQL runs with a core-file limit of zero, so a crash cannot write process memory (which can hold credentials) to disk.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • Fails closed. If first boot cannot finish, PostgreSQL stays stopped and first boot tries again on the next boot.
  • No build toolchain. The Rust and C toolchains used to compile pgvectorscale are removed before capture.
  • SSH. Key-only login for ubuntu; root login, password and keyboard-interactive login and X11 forwarding are off. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large (2 vCPU, 8 GiB). The image's memory settings (shared_buffers = 1GB, maintenance_work_mem = 512MB) are sized for it; raise them in /etc/postgresql/18/main/conf.d/10-cloudimg.conf on larger instances. StreamingDiskANN and HNSW index builds use maintenance_work_mem. StreamingDiskANN tolerates modest memory well; for large collections and high query rates choose a memory-optimised instance and a larger gp3 volume.

Connecting to your instance

Version Operating system SSH user
PostgreSQL 18 with pgvectorscale on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened Milvus, Qdrant & Vector Databases and RAG AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose PostgreSQL 18 with pgvectorscale on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 5432 to the internet.
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name pgvectorscale \
  --description "PostgreSQL with pgvectorscale" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pgvectorscale}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. Show the connection details (the two password lines are left out here; read them with sudo cat /root/pgvectorscale-credentials.txt and keep them in your secrets manager):

sudo grep -v -e '^postgres.password=' -e '^vectorapp.password=' /root/pgvectorscale-credentials.txt | head -16

# PostgreSQL 18 with pgvectorscale 0.9.1 and pgvector 0.8.7 (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by pgvectorscale-firstboot.service. These passwords
# exist nowhere else. On this instance the postgres superuser also works with no password:
#   sudo -u postgres psql -d vectordb        (peer authentication over the local socket)
# Rotate a password (never on a command line) with:
#   sudo -u postgres psql   then   \password vectorapp   (or \password postgres)
# and update this file to match.

postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.database=vectordb
postgres.sslmode=require
vectorapp.role=vectorapp
pgvector.version=0.8.7
pgvectorscale.version=0.9.1

The commands below read the passwords from that file at run time, so they never appear on screen. In your own sessions you can instead put them in ~/.pgpass (mode 0600) as 127.0.0.1:5432:vectordb:vectorapp:<VECTORAPP_PASSWORD>.

Step 3: Confirm the database is running

psql --version
systemctl is-active postgresql@18-main.service pgvectorscale-firstboot.service pgvectorscale-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
active
active
active
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

PostgreSQL listens on 127.0.0.1:5432 only; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub. Now connect over TLS as vectorapp, and show the extensions, the StreamingDiskANN access method, the demo table and the TLS session:

export PGPASSWORD="$(sudo sed -n 's/^vectorapp.password=//p' /root/pgvectorscale-credentials.txt)"
psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=require" \
  -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;" \
  -c "SELECT amname FROM pg_am WHERE amname IN ('diskann','hnsw','ivfflat') ORDER BY 1;" \
  -c "SELECT count(*) AS items, max(vector_dims(embedding)) AS dims FROM items;" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

   extname   | extversion
-------------+------------
 plpgsql     | 1.0
 vector      | 0.8.7
 vectorscale | 0.9.1
(3 rows)

 amname
---------
 diskann
 hnsw
 ivfflat
(3 rows)

 items | dims
-------+------
  2000 |  128
(1 row)

 ssl | tls_version
-----+-------------
 t   | TLSv1.3
(1 row)

Step 4: Run a similarity search over the demo table

The demo table items holds 2,000 random 128-dimension vectors with a StreamingDiskANN index (items_embedding_diskann, cosine distance). Order by the cosine distance operator <=> to find the rows nearest to a query vector; here the query is row 1's own vector, so row 1 comes back first at distance 0:

export PGPASSWORD="$(sudo sed -n 's/^vectorapp.password=//p' /root/pgvectorscale-credentials.txt)"
psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=require" \
  -c "SELECT id, label, round((embedding <=> (SELECT embedding FROM items WHERE id = 1))::numeric, 6) AS cosine_distance FROM items ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;" \
  -c "EXPLAIN SELECT id FROM items ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;"

  id  |  label   | cosine_distance
------+----------+-----------------
    1 | doc-1    |        0.000000
  601 | doc-601  |        0.183129
  733 | doc-733  |        0.183884
 1014 | doc-1014 |        0.187218
  716 | doc-716  |        0.188109
(5 rows)

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=4.85..5.30 rows=5 width=16)
   InitPlan 1
     ->  Index Scan using items_pkey on items items_1  (cost=0.28..2.50 rows=1 width=520)
           Index Cond: (id = 1)
   ->  Index Scan using items_embedding_diskann on items  (cost=2.35..184.65 rows=2000 width=16)
         Order By: (embedding <=> (InitPlan 1).col1)
(6 rows)

The vectors are generated on your instance's first boot, so your ids and distances after the first row differ from these. Index Scan using items_embedding_diskann in the plan is the difference between a vector column and a vector database: the planner answers the nearest-neighbour query from the StreamingDiskANN index instead of sorting the whole table.

Step 5: Build a StreamingDiskANN index on your own embeddings

vectorapp can create tables in vectordb. Store your embeddings in a vector(N) column, then build a StreamingDiskANN index with USING diskann. Match the operator class to the distance operator you query with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. This example loads 20,000 random 128-dimension vectors (your application would load real embeddings from a model), builds the index and checks that the planner uses it:

export PGPASSWORD="$(sudo sed -n 's/^vectorapp.password=//p' /root/pgvectorscale-credentials.txt)"
psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=require" <<'SQL'
CREATE TABLE guide_docs (
    id        bigserial PRIMARY KEY,
    title     text NOT NULL,
    embedding vector(128) NOT NULL
);
INSERT INTO guide_docs (title, embedding)
SELECT 'doc-' || g.i, v.emb
FROM generate_series(1, 20000) AS g(i)
CROSS JOIN LATERAL (
    SELECT ('[' || string_agg(random()::text, ',') || ']')::vector(128) AS emb
    FROM generate_series(1, 128) AS d(k)
    WHERE g.i IS NOT NULL
) AS v;
CREATE INDEX guide_docs_diskann ON guide_docs USING diskann (embedding vector_cosine_ops);
ANALYZE guide_docs;
EXPLAIN SELECT id, title FROM guide_docs
ORDER BY embedding <=> (SELECT embedding FROM guide_docs WHERE id = 42) LIMIT 10;
SELECT id, title FROM guide_docs
ORDER BY embedding <=> (SELECT embedding FROM guide_docs WHERE id = 42) LIMIT 3;
SQL

CREATE TABLE
INSERT 0 20000
NOTICE:  Starting index build with num_neighbors=-1, search_list_size=100, max_alpha=1.2, storage_layout=SbqCompression.
NOTICE:  Indexed 20000 tuples
CREATE INDEX
ANALYZE
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit  (cost=17.21..18.12 rows=10 width=25)
   InitPlan 1
     ->  Index Scan using guide_docs_pkey on guide_docs guide_docs_1  (cost=0.29..2.51 rows=1 width=520)
           Index Cond: (id = 42)
   ->  Index Scan using guide_docs_diskann on guide_docs  (cost=14.70..1836.60 rows=20000 width=25)
         Order By: (embedding <=> (InitPlan 1).col1)
(6 rows)

  id   |   title
-------+-----------
    42 | doc-42
  8766 | doc-8766
 11900 | doc-11900
(3 rows)

The index build takes about a minute for 20,000 vectors on an m5.large. Two query-time settings trade accuracy for speed: SET diskann.query_search_list_size = 100; widens the search frontier and SET diskann.query_rescore = 50; rescores more candidates with the full-precision vectors. Set them per session or per role (ALTER ROLE vectorapp SET diskann.query_rescore = 100;). Remove the example when you are done with DROP TABLE guide_docs;.

Step 6: pgvector HNSW in the same database

pgvector's own HNSW index works alongside StreamingDiskANN, for example on a smaller, latency-critical table:

export PGPASSWORD="$(sudo sed -n 's/^vectorapp.password=//p' /root/pgvectorscale-credentials.txt)"
psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=require" <<'SQL'
CREATE TABLE IF NOT EXISTS guide_hnsw (id bigint PRIMARY KEY, embedding vector(3) NOT NULL);
INSERT INTO guide_hnsw VALUES (1, '[1,0,0]'), (2, '[0,1,0]'), (3, '[0.9,0.1,0]'), (4, '[0,0,1]') ON CONFLICT (id) DO NOTHING;
CREATE INDEX IF NOT EXISTS guide_hnsw_idx ON guide_hnsw USING hnsw (embedding vector_l2_ops);
SELECT id, round((embedding <-> '[1,0.05,0]')::numeric, 4) AS l2_distance
FROM guide_hnsw ORDER BY embedding <-> '[1,0.05,0]' LIMIT 2;
SQL

CREATE TABLE
INSERT 0 4
CREATE INDEX
 id | l2_distance
----+-------------
  1 |      0.0500
  3 |      0.1118
(2 rows)

Remove the example when you are done with DROP TABLE guide_hnsw;.

Step 7: Connect from your application

PostgreSQL accepts connections on localhost only. Two ways to reach it:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 as vectorapp with sslmode=require and the password from the credentials file.
  • Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-cloudimg.conf change listen_addresses = '127.0.0.1' to listen_addresses = '*', restart PostgreSQL, then add an inbound rule for 5432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0), and narrow the 0.0.0.0/0 lines in /etc/postgresql/18/main/pg_hba.conf to the same CIDR. Clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/18/main/cloudimg-server.crt) and a role's password:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-cloudimg.conf
sudo systemctl restart postgresql@18-main.service
aws ec2 authorize-security-group-ingress --group-id <instance-sg> --protocol tcp --port 5432 --cidr <your-vpc-cidr>

For more applications, create one least-privilege role each: in sudo -u postgres psql -d vectordb run CREATE ROLE app2 LOGIN; and then \password app2.

A wrong password is refused, the default postgres password does not exist, and a plain-text TCP connection is refused before authentication:

OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"
OUT="$(PGPASSWORD=postgres psql "host=127.0.0.1 dbname=vectordb user=postgres sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"
export PGPASSWORD="$(sudo sed -n 's/^vectorapp.password=//p' /root/pgvectorscale-credentials.txt)"
OUT="$(psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=disable" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "vectorapp"
FATAL:  password authentication failed for user "postgres"
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "vectorapp", database "vectordb", no encryption

Step 8: Check the security controls

The PostgreSQL unit carries the metadata block and no core dumps, PostgreSQL listens on loopback with this instance's own certificate and logs connections, only postgres and vectorapp can log in, every TCP rule is TLS plus scram-sha-256, and SSH runs with the settings listed above:

systemctl show -p IPAddressDeny -p LimitCORE postgresql@18-main.service
sudo -u postgres psql -tAc "SHOW listen_addresses;" -c "SHOW ssl_cert_file;" -c "SHOW log_connections;"
sudo -u postgres psql -tAc "SELECT rolname, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolcanlogin ORDER BY 1;"
sudo grep -vE '^[[:space:]]*(#|$)' /etc/postgresql/18/main/pg_hba.conf
sudo sshd -T | grep -E '^(permitrootlogin|passwordauthentication|kbdinteractiveauthentication|x11forwarding) '

IPAddressDeny=168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128
LimitCORE=0
127.0.0.1
/etc/postgresql/18/main/cloudimg-server.crt
on
postgres|t
vectorapp|t
local     all          all                 peer
hostssl   all          all   127.0.0.1/32  scram-sha-256
hostssl   all          all   ::1/128       scram-sha-256
hostssl   all          all   0.0.0.0/0     scram-sha-256
hostssl   all          all   ::/0          scram-sha-256
local     replication  all                 peer
permitrootlogin no
passwordauthentication no
kbdinteractiveauthentication no
x11forwarding no

Maintenance

  • Change a password. Run sudo -u postgres psql and type \password vectorapp (or \password postgres) at the prompt; it asks for the new password twice, sends only a SCRAM verifier and never puts it on a command line or in a log. Then update /root/pgvectorscale-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
  • Configuration. cloudimg's settings are in /etc/postgresql/18/main/conf.d/10-cloudimg.conf (listener, memory, logging) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@18-main.service.
  • Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 18 minor releases and pgvector updates from apt.postgresql.org and Ubuntu security updates (unattended security upgrades are on). pgvectorscale 0.9.1 is compiled into the image and stays at this version (it is binary-compatible with every PostgreSQL 18 minor release); a newer pgvectorscale arrives as a new version of this listing. After an upgrade of pgvectorscale run ALTER EXTENSION vectorscale UPDATE; in each database that uses it.
  • Backups. Take EBS snapshots of the volume or pg_dump -Fc vectordb backups regularly. StreamingDiskANN and HNSW indexes are rebuilt from the table data on restore.
  • Logs. /var/log/postgresql/postgresql-18-main.log (connections are logged with the client address) and journalctl -u postgresql@18-main.service; first boot and the credential guard log to journalctl -u pgvectorscale-firstboot.service and journalctl -u pgvectorscale-credguard.service.
  • Licences. pgvectorscale is under The PostgreSQL License (Tiger Data); its source for this exact build is github.com/timescale/pgvectorscale at tag 0.9.1. PostgreSQL and pgvector are under The PostgreSQL License. /usr/share/doc/cloudimg/pgvectorscale/ holds the licence text, ATTRIBUTION.txt, the exact Rust dependency set (Cargo.lock) and BUILD-PROVENANCE.txt with the digests of the shipped library.
  • Dependency advisories. The audited Rust dependency set of pgvectorscale 0.9.1 carries two advisories that have no fix in the versions pgvectorscale supports, and neither is reachable in this build: RUSTSEC-2026-0235 (rkyv 0.7 archive validation with shared pointers: pgvectorscale's on-disk index structures contain no shared pointers) and RUSTSEC-2026-0253 (lru, informational "unsound": needs a cache key whose destructor can panic; pgvectorscale's cache keys are plain tuple pointers with no destructor). The image build fails if a source change would make either reachable.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (PostgreSQL 18 with pgvectorscale on Ubuntu 24.04) and what you were doing.

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. pgvectorscale is a project of Tiger Data. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.