ParadeDB PostgreSQL 18 pg search

Prerequisites

• An active Azure subscription, an SSH public key, and a VNet plus subnet in the target region.
• A subscription to this listing on Azure Marketplace.
• A network security group (NSG) that allows TCP 22 from your administration network. Nothing else needs to be opened, because PostgreSQL is bound to loopback in the shipped image.
• Recommended VM size: Standard_B2ms (2 vCPU, 8 GB RAM). BM25 index builds are the memory-hungry part of the workload, since Tantivy assembles segments in memory before writing them. For large corpora and high query rates, choose a memory-rich size such as Standard_E4s_v5 and a premium data disk.

Deploy the virtual machine

Create the VM from the image. Open only SSH, and only to your own network:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:paradedb-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name paradedb-1 \
  --image sac1647283583153:paradedb-ubuntu-24-04:default:latest \
  --size Standard_B2ms \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

First boot creates the database cluster and builds the demo BM25 index, so wait a minute or two before connecting. To watch it complete, run systemctl status paradedb-firstboot.service.

Confirm first boot finished

SSH in as azureuser and confirm that the first-boot unit ran to completion:

systemctl is-active paradedb-firstboot.service

ls -l /var/lib/sac/data/paradedb-firstboot.done

Once initialisation is complete, the unit reports active and the sentinel file exists.

Retrieve your per-VM credentials

On first boot, the VM initialises its own cluster and generates its own passwords and TLS certificate. Read the root-only credentials file:

sudo cat /root/paradedb-credentials.txt

The file is 0600 root:root. It contains the postgres superuser password, the searchapp application role password and the database name (searchdb). These values are unique to this VM.

Connect to the database

The quickest local connection uses peer authentication and needs no password. Run:

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

To connect as the application role over TLS with the generated password, run:

sudo sh -c 'PW=$(sed -n "s/^searchapp\.password=//p" /root/paradedb-credentials.txt); psql "host=127.0.0.1 port=5432 dbname=searchdb user=searchapp password=$PW sslmode=require" -c "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"'

The query returns t when the session is encrypted.

Verify the extensions

sudo -u postgres psql -d searchdb -c "SELECT extname, extversion FROM pg_extension WHERE extname IN ('pg_search','vector') ORDER BY extname;"

pg_search is the BM25 search engine, and vector is pgvector, which pg_search requires. The pg_search library is loaded at postmaster start. Confirm it:

sudo -u postgres psql -d searchdb -c "SHOW shared_preload_libraries;"

Run a ranked search against the demo corpus

The searchdb database ships a documents table with five thousand records and a BM25 index named documents_bm25_idx. Confirm that the index uses the bm25 access method rather than a stock PostgreSQL index type:

sudo -u postgres psql -d searchdb -c "SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname = 'documents_bm25_idx';"

Now run a search. The @@@ operator matches against the BM25 index, and paradedb.score() returns the relevance score:

sudo -u postgres psql -d searchdb -c "SELECT id, title, round(paradedb.score(id)::numeric, 4) AS score FROM documents WHERE body @@@ 'relevance ranking retrieval' ORDER BY paradedb.score(id) DESC LIMIT 10;"

The scores differ between rows and descend. That is the difference between a search and a filter. BM25 weighs how often each term appears in a document against how common that term is across the whole corpus. A document that matches all three terms strongly therefore ranks above one that mentions a single common term.

A term that appears in no document returns nothing. This confirms that the operator discriminates rather than matching everything:

sudo -u postgres psql -d searchdb -c "SELECT count(*) AS should_be_zero FROM documents WHERE body @@@ 'qwertzuiopasdfghjkl';"

Confirm the BM25 index is actually being used

A working search does not prove that the index is doing the work. Ask PostgreSQL for the query plan:

sudo -u postgres psql -d searchdb -c "EXPLAIN (FORMAT TEXT, COSTS ON) SELECT id, paradedb.score(id) FROM documents WHERE body @@@ 'relevance' ORDER BY paradedb.score(id) DESC LIMIT 10;"

The plan contains a Custom Scan (ParadeDB ...) node naming documents_bm25_idx. That node is pg_search executing the query against the Tantivy index, not PostgreSQL scanning the table.

For contrast, run the same search without BM25 and compare the plans:

sudo -u postgres psql -d searchdb -c "EXPLAIN (FORMAT TEXT, COSTS ON) SELECT id FROM documents WHERE body ILIKE '%relevance%' LIMIT 10;"

This produces a Seq Scan at a materially higher total cost, because PostgreSQL has to read every row. The gap between the two plans is the value the extension adds, and it grows with the size of the corpus.

Index your own table

Create a table, insert text, and build a BM25 index over the columns you want searchable. The index needs a key_field, a unique column that pg_search uses to identify rows:

sudo -u postgres psql -d searchdb -c "CREATE TABLE IF NOT EXISTS articles (id bigserial PRIMARY KEY, title text NOT NULL, body text NOT NULL); INSERT INTO articles (title, body) VALUES ('Indexing guide', 'how to build an inverted index for fast retrieval'), ('Backup guide', 'point in time recovery and base backup scheduling'), ('Tuning guide', 'buffer cache sizing and query planner statistics') ON CONFLICT DO NOTHING;"

sudo -u postgres psql -d searchdb -c "CREATE INDEX IF NOT EXISTS articles_bm25_idx ON articles USING bm25 (id, title, body) WITH (key_field = 'id'); ANALYZE articles;"

Search the new table:

sudo -u postgres psql -d searchdb -c "SELECT title, round(paradedb.score(id)::numeric, 4) AS score FROM articles WHERE body @@@ 'index retrieval' ORDER BY score DESC;"

Grant the application role access to anything you create for it, for example:

sudo -u postgres psql -d searchdb -c "GRANT SELECT ON articles TO searchapp;"

Vector search in the same database

Because pgvector is installed, embeddings and keyword search live side by side. Start with a minimal example:

sudo -u postgres psql -d searchdb -c "CREATE TABLE IF NOT EXISTS embeddings (id bigserial PRIMARY KEY, label text, v vector(3)); INSERT INTO embeddings (label, v) VALUES ('a', '[1,0,0]'), ('b', '[0,1,0]'), ('c', '[0.9,0.1,0]') ON CONFLICT DO NOTHING;"

sudo -u postgres psql -d searchdb -c "SELECT label, round((v <=> '[1,0,0]')::numeric, 4) AS cosine_distance FROM embeddings ORDER BY v <=> '[1,0,0]' LIMIT 3;"

You can also combine the two: filter by BM25 relevance and then re-rank by vector similarity, or the reverse. Most teams end up wanting this pattern, and here it is a single SQL statement.

Security posture

The shipped image is closed by default.

The database is not reachable from the network. listen_addresses is localhost, so the only port listening off the VM is TCP 22:

sudo ss -lnt

Confirm the setting directly with:

sudo -u postgres psql -d searchdb -c "SHOW listen_addresses;"

Every TCP path requires TLS and a password. The stock plaintext host rules are removed from pg_hba.conf and replaced with hostssl ... scram-sha-256 rules, including on loopback. List the active rules:

sudo grep -E '^(host|hostssl)' /etc/postgresql/18/main/pg_hba.conf

There are no plaintext host lines. If you later open the database to your application subnet, TLS and the per-VM password are enforced from the first packet, with nothing to remember to turn on.

SSH is locked down. Root has no login path, and password authentication is disabled. Verify the settings:

sudo sshd -T | grep -E '^(permitrootlogin|passwordauthentication|clientaliveinterval) '

Kernel module baseline. The image fences the IPsec and RxRPC modules associated with the Dirty Frag vulnerabilities (CVE-2026-43284, CVE-2026-43500). This appliance terminates no IPsec, so the fence costs nothing. Check it:

sudo modprobe -n -v esp4

The last line reads install /bin/false, which is the fence. Any insmod lines above it are esp4's own dependencies, which are not fenced. Only the entry for esp4 itself matters. Attempting sudo modprobe esp4 fails with could not insert 'esp4', so the module cannot load.

Optionally expose the database to your application subnet

The database is loopback-only by design. Opening it is a deliberate two-step change, and you should scope it to your own subnet rather than the internet.

First, change the listen address and restart PostgreSQL:

sudo sed -i "s/^listen_addresses.*/listen_addresses = '*'/" /etc/postgresql/18/main/postgresql.conf
sudo systemctl restart postgresql

Then open TCP 5432 to your own subnet in the VM's network security group:

az network nsg rule create \
  --resource-group my-rg \
  --nsg-name my-nsg \
  --name allow-postgres-from-app-subnet \
  --priority 200 \
  --source-address-prefixes 10.0.1.0/24 \
  --destination-port-ranges 5432 \
  --access Allow --protocol Tcp

Tighten the hostssl all all 0.0.0.0/0 line in /etc/postgresql/18/main/pg_hba.conf to the same CIDR at the same time. Because the hostssl and scram-sha-256 requirements are already in place, clients must present the per-VM password over TLS from the moment the port opens.

Rotating the credentials

The generated passwords are yours to change. Rotate the application role password, then update the credentials file to match:

sudo -u postgres psql -c "ALTER ROLE searchapp PASSWORD 'your-new-password'"

Maintenance

• Security updates. Unattended upgrades remain enabled, so the OS keeps patching itself. Run sudo apt-get update && sudo apt-get upgrade to apply everything immediately.
• Rebuilding an index. BM25 indexes are maintained transactionally, so ordinary inserts and updates are reflected automatically. After a very large bulk load, run REINDEX INDEX documents_bm25_idx; to compact the segments.
• Index size. Check it with SELECT pg_size_pretty(pg_relation_size('documents_bm25_idx'));. Tantivy indexes are compact but not free, so budget disk accordingly for large corpora.
• Backups. The BM25 index is part of the database, so ordinary PostgreSQL backups (pg_dump, pg_basebackup, or an Azure disk snapshot) capture it along with your data.
• Upgrades. ParadeDB ships on a weekly release train. To move to a newer pg_search, install the newer package and run ALTER EXTENSION pg_search UPDATE; in each database.

Support

SecureAnyCloud provides 24/7 support for this image. Raise a request at secureanycloud.com and include the VM size, the region and the output of sudo -u postgres psql -d searchdb -c "SELECT extname, extversion FROM pg_extension;".

Deploy on Azure

Launch ParadeDB PostgreSQL 18 with pg_search on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com