SecureAnyCloud
Deployment guide

PostgreSQL 18 with pgvectorscale

Overview

This guide covers launching and verifying PostgreSQL 18 with pgvectorscale on Ubuntu 24.04 from SecureAnyCloud on Amazon EC2. The image is one version of the SecureAnyCloud Hardened Milvus, Qdrant & Vector Databases and RAG AMIs listing on AWS Marketplace. You choose it as the software version when you subscribe and launch. The guide hub of the listing describes the other vector databases and RAG tools in the listing.

The image is a vector search database. One PostgreSQL stores high-dimensional embeddings next to your ordinary relational data and ranks rows by similarity to a query vector. Two complementary index families work 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 lets a modest instance serve a large embedding collection. pgvectorscale has no distribution package, so SecureAnyCloud 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. The database 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 you get:

  • PostgreSQL 18.6 from the apt repository of the PostgreSQL project (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 the first boot of your instance, with the vectordb database, both extensions, a vectorapp application role and the demo table
  • TLS with a certificate generated on the first boot of your instance (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. It writes them to /root/pgvectorscale-credentials.txt, which only root can read. 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 does not start until first boot has created the cluster, the certificate and the passwords. While first boot sets the passwords, the server listens on its local socket only, with no TCP port and with statement logging off. The passwords are sent on standard input and never on a command line.
  • Per-instance TLS certificate. First boot creates the private key and self-signed certificate of this instance 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 security group of the listing 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 that 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). Every PostgreSQL unit and both first-boot services are therefore 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, because 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. Login for ubuntu is key-only. Root login, password login, keyboard-interactive login and X11 forwarding are off. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

You need the following:

  • An AWS account and permission to launch EC2 instances and create security groups
  • An EC2 key pair in the Region that you launch in
  • 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 memory settings of the image (shared_buffers = 1GB, maintenance_work_mem = 512MB) are sized for this instance type. On larger instances, raise them in /etc/postgresql/18/main/conf.d/10-sac.conf. 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

The version is PostgreSQL 18 with pgvectorscale on Ubuntu 24.04. The operating system is Ubuntu 24.04 LTS, and the SSH user is ubuntu.

1

Launch the instance

Option A: AWS Marketplace console

  • Open the SecureAnyCloud Hardened Milvus, Qdrant & Vector Databases and RAG AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  • When the subscription is active, choose Launch your software.
  • 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.
  • Pick an instance type (m5.large is recommended, and the software charge is $0.04 per vCPU per hour) and your key pair.
  • Set the security group. The default group of the listing allows SSH (22) only. Restrict it to your own IP address, and do not open 5432 to the internet.
  • Choose your VPC and subnet, and leave the metadata defaults, because the image already requires IMDSv2. Then choose Launch.

Option B: AWS CLI

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

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, because the AMI already requires IMDSv2.

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. The following command shows the connection details and leaves out the two password lines. Read the passwords 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 (sac 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 in the following steps read the passwords from that file at run time, so the passwords never appear on screen. In your own sessions, you can instead put the password in ~/.pgpass (mode 0600) as 127.0.0.1:5432:vectordb:vectorapp:<VECTORAPP_PASSWORD>.

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 and 127.0.0.54 is the local DNS stub of the operating system. 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)
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. In this example, the query is the vector of row 1, 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 the first boot of your instance, 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.

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 <#>. The following 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;). When you are done with the example, remove it with DROP TABLE guide_docs;.

6

pgvector HNSW in the same database

The HNSW index of pgvector 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)

When you are done with the example, remove it with DROP TABLE guide_hnsw;.

7

Connect from your application

PostgreSQL accepts connections on localhost only. You can reach it in two ways:

  • 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-sac.conf, change listen_addresses = '127.0.0.1' to listen_addresses = '*' and restart PostgreSQL. Then add an inbound rule for 5432 to the security group of the instance from your application subnet or VPC CIDR only (never 0.0.0.0/0). 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 the certificate of this instance, /etc/postgresql/18/main/sac-server.crt) and the password of a role:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-sac.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 for each. In sudo -u postgres psql -d vectordb, run CREATE ROLE app2 LOGIN; and then \password app2.

PostgreSQL refuses a wrong password, no default postgres password exists, and PostgreSQL refuses a plain-text TCP connection before authentication:

OUT="$(PGPASSWORD=<set-at-first-boot> psql "host=127.0.0.1 dbname=vectordb user=vectorapp sslmode=require" -c "SELECT 1" 2>&1 || true)" echo "${OUT#*failed: }" OUT="$(PGPASSWORD=<set-at-first-boot> 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
8

Check the security controls

The following commands confirm these controls. The PostgreSQL unit carries the metadata block and the no-core-dump setting. PostgreSQL listens on loopback with the certificate of this instance and logs connections. Only postgres and vectorapp can log in. Every TCP rule requires TLS plus scram-sha-256. SSH runs with the settings listed in the security posture.

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/sac-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. PostgreSQL asks for the new password twice, sends only a SCRAM verifier, and never puts the password 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. The SecureAnyCloud settings are in /etc/postgresql/18/main/conf.d/10-sac.conf (listener, memory, logging) and 20-sac-tls.conf (the certificate of this instance). 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, which is binary-compatible with every PostgreSQL 18 minor release. A newer pgvectorscale arrives as a new version of this listing. After you upgrade 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. On restore, PostgreSQL rebuilds the StreamingDiskANN and HNSW indexes from the table data.
  • Logs. PostgreSQL logs to /var/log/postgresql/postgresql-18-main.log, with connections logged with the client address, and to 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/sac/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 that pgvectorscale supports. Neither is reachable in this build. RUSTSEC-2026-0235 (rkyv 0.7 archive validation with shared pointers) does not apply, because the on-disk index structures of pgvectorscale contain no shared pointers. RUSTSEC-2026-0253 (lru, informational "unsound") needs a cache key whose destructor can panic, and the cache keys of pgvectorscale are plain tuple pointers with no destructor. The image build fails if a source change would make either advisory reachable.

Support

SecureAnyCloud supports this image by email at sales@secureanycloud.com. Include the instance ID, the version (PostgreSQL 18 with pgvectorscale on Ubuntu 24.04) and a description of 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. SecureAnyCloud is not affiliated with or endorsed by these organisations.

Deploy on AWS

Launch PostgreSQL 18 with pgvectorscale on Ubuntu 24.04 on AWS with 24/7 support from SecureAnyCloud.

View on Marketplace

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com

Deploy on AWS