Databases Azure

ParadeDB PostgreSQL 18 with pg_search BM25 Full Text Search on Ubuntu 24.04 on Azure User Guide

| Product: ParadeDB PostgreSQL 18 with pg_search on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of ParadeDB PostgreSQL 18 with pg_search on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

ParadeDB's pg_search extension puts a real search engine inside PostgreSQL. It builds a BM25 index, backed by the Tantivy search library, over the text columns you choose. Searching is then an ordinary SQL query with the @@@ operator, and paradedb.score() gives you the BM25 relevance score you can sort and filter on. Because the index lives in the same database as your data, there is no separate search cluster to run and no synchronisation pipeline to keep correct.

What this replaces, and what it does not. This image is the right tool when your corpus fits comfortably in one PostgreSQL and you want search to be a query against data you already store transactionally, with no second datastore to operate. It is honestly not a drop in replacement for a multi node Elasticsearch or OpenSearch deployment. Specifically, this is one PostgreSQL instance and therefore has:

  • no sharding across nodes and no distributed query execution
  • no separate analyzer and plugin ecosystem of the kind Elasticsearch offers
  • no Kibana style search UI — this is a database endpoint, and you query it with SQL
  • no BM25 reads from a physical standby. This is worth stating plainly because it is an upstream product boundary rather than a limit of the image: pg_search is open core, and serving BM25 searches from a read replica needs write ahead log integration that upstream ships only in ParadeDB Enterprise, a separate commercial product. The extension you get here is ParadeDB Community, which is wholly AGPL v3 licensed and contains no licence key, activation check or trial timer of any kind — it simply raises a clear error if you ask a standby to answer a BM25 query. Ordinary PostgreSQL streaming replication still works; it is search on the replica that does not.

If you need a search tier that scales horizontally and independently of your database, or you need to serve searches from read replicas, you want a dedicated search cluster or ParadeDB Enterprise. If you have been running a search cluster purely to add relevance ranking to data that already lives in one Postgres, this removes it.

pgvector is included and is required. The pg_search extension declares requires = 'vector', so pgvector is installed and enabled alongside it. That means the same database gives you BM25 keyword relevance and vector similarity search over embeddings, in one place.

Every VM gets its own database. The captured image contains the PostgreSQL binaries and both extensions but no database cluster at all. On first boot each VM runs its own initdb, so no two deployments share a cluster identity, a certificate or a password. There is no default, blank or shared credential at any point, not even for a moment.

What is included:

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

  • pg_search 0.25.9 and pgvector, both pre enabled in the default searchdb database, with a demo documents table of five thousand text records and a real BM25 index so you can run a ranked search the moment the VM boots

  • A per VM database cluster, a per VM TLS certificate and two per VM passwords (the postgres superuser and the searchapp application role) generated on first boot into a root only credentials file

  • Loopback only networking by default: the only port reachable from the network is TCP 22

  • pg_hba.conf requiring TLS and scram-sha-256 on every TCP connection, so opening the database later is a single safe step

  • Unattended security upgrades left enabled so the appliance keeps receiving patches

Licensing. ParadeDB's pg_search is distributed under the GNU Affero General Public License v3. The cloudimg build verifies this on every run: it pins the upstream package by SHA256, opens it before installing, and checks the licence shipped inside the package is the verbatim AGPL v3 by digest. PostgreSQL itself is under the PostgreSQL License and pgvector under a permissive licence.

Prerequisites

  • Active Azure subscription, an SSH public key, and a VNet plus subnet in the target region

  • Subscription to this listing on Azure Marketplace

  • A Network Security Group allowing TCP 22 from your administration network. Nothing else needs to be opened: PostgreSQL is bound to loopback in the shipped image.

Recommended virtual machine 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, opening only SSH to your own network:

az vm create \
  --resource-group my-rg \
  --name paradedb-1 \
  --image <this-marketplace-image> \
  --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 give it a minute or two before connecting. You can watch it complete with systemctl status paradedb-firstboot.service.

Confirm first boot finished

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

systemctl is-active paradedb-firstboot.service

ls -l /var/lib/cloudimg/paradedb-firstboot.done

The unit reports active and the sentinel file exists once initialisation is complete.

Retrieve your per VM credentials

On the 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 and 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:

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

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

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();"'

A result of t confirms 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:

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 the index really 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;"

Note that 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, so a document that matches all three terms strongly ranks above one that mentions a single common term.

A term that appears in no document returns nothing, which confirms the operator is discriminating 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 search that works is not proof that the index is doing the work. Ask PostgreSQL for the 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, express the same intent without BM25 and compare:

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 those 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 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 it:

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:

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. 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;"

Combining the two — filtering by BM25 relevance and then re ranking by vector similarity, or the reverse — is the pattern most teams end up wanting, and it is a single SQL statement here.

Security posture

The shipped image is deliberately closed.

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

sudo ss -lnt

Confirm the setting directly:

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:

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

There are no plaintext host lines. This matters because it means that if you later open the database to your application subnet, TLS and the per VM password are enforced from the first packet rather than being something you have to remember to turn on.

SSH is locked down. Root has no login path and password authentication is disabled:

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:

sudo modprobe -n -v esp4

This resolves to install /bin/false, meaning 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 while you are there. 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 and 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. sudo apt-get update && sudo apt-get upgrade applies everything immediately.

  • Rebuilding an index — BM25 indexes are maintained transactionally, so ordinary inserts and updates are reflected automatically. After a very large bulk load, REINDEX INDEX documents_bm25_idx; compacts the segments.

  • Index size — check with SELECT pg_size_pretty(pg_relation_size('documents_bm25_idx'));. Tantivy indexes are compact but not free; 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

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