Forem: Rajeshwar R

Building Advanced Search with PostgreSQL: pg_search on AWS

Rajeshwar R — Sun, 09 Nov 2025 07:44:46 +0000

Introduction

Ever needed powerful search capabilities in your application but dreaded the complexity of managing Elasticsearch or OpenSearch? You're not alone. While these dedicated search engines are powerful, they introduce significant operational overhead: another cluster to deploy, ETL pipelines to maintain, and constant synchronization headaches.

What if you could get search engine capabilities directly inside PostgreSQL? That's exactly what pg_search offers. In this post, I'll walk you through what pg_search is, why it can't run on Amazon RDS, and how we architected a solution using EC2 that keeps everything in PostgreSQL while delivering modern search features.

What is pg_search?

pg_search is a PostgreSQL extension from ParadeDB that transforms your database into a full-featured search engine. Instead of basic substring matching with LIKE or ILIKE, you get:

Full-text search with BM25 ranking (the algorithm behind modern search engines)
Vector search for semantic similarity using embeddings
Hybrid search that combines text relevance and vector similarity
All using standard SQL queries

Here's a simple example:

SELECT id, title, 
       bm25_rank((title, body), 'configure postgres search') AS score
FROM articles
ORDER BY score DESC
LIMIT 10;

This returns ranked results like a real search engine, not just rows that contain your keywords.

Why pg_search Instead of Traditional Search Solutions?

You might reach for pg_search when:

Plain Postgres queries aren't enough. Basic LIKE queries don't provide relevance ranking, and scaling text search across multiple columns becomes difficult.

You want to avoid separate infrastructure. Solutions like Elasticsearch require:

Additional clusters to deploy and monitor
ETL pipelines to sync data from Postgres
Managing consistency between two systems
Extra latency from network hops

You prefer the Postgres ecosystem. With pg_search, everything stays in Postgres. Your backups, permissions, transactions, and tooling all work the same way. No new stack to learn.

The RDS Challenge

Here's the catch: Amazon RDS doesn't support pg_search.

RDS only allows a pre-approved list of extensions. Since RDS is a managed service, AWS controls what can be loaded into the PostgreSQL process. If you try:

CREATE EXTENSION pg_search;

You'll get:

ERROR: extension "pg_search" is not available

This limitation is by design. To use pg_search, you need a self-managed PostgreSQL instance where you control the installation.

Our Solution: RDS + EC2 Architecture

Since we can't install pg_search on RDS, we use a hybrid approach:

Amazon RDS remains our primary database for all application writes and transactions
EC2 PostgreSQL runs as a search replica with pg_search installed
Data flows from RDS to EC2 using logical replication
Applications query RDS for normal operations and EC2 for search

Here's the architecture:

          ┌─────────────────────────────┐
          │         Application         │
          │  (API / backend / service)  │
          └────────────┬────────────────┘
                       │
                 Write / Read 
                       │
          ┌────────────▼──────────────┐
          │   Amazon RDS PostgreSQL   │
          │        (Primary DB)       │
          └────────────┬──────────────┘
                       │
                Logical Replication
                       │
          ┌────────────▼──────────────┐
          │        Amazon EC2         │
          │  PostgreSQL + pg_search   │
          │    (Search / Analytics)   │
          └────────────▲──────────────┘
                       │
                Search Queries
                       │
          ┌────────────┴──────────────┐
          │         Application       │
          └───────────────────────────┘

Everything stays inside your private AWS VPC with no external services.

Setting Up pg_search on EC2

Step 1: Provision Your EC2 Instance

Launch an EC2 instance (t3.medium or larger) in the same VPC as your RDS instance. Place it in a private subnet and attach sufficient storage (100+ GB gp3 EBS).

Configure security groups to allow:

EC2 → RDS communication on port 5432
Your admin access via SSH

Step 2: Install PostgreSQL with pg_search

We'll use Docker for a reproducible setup. Create a Dockerfile:

FROM postgres:16

# Install build dependencies
RUN apt-get update && apt-get install -y \
    build-essential git curl pkg-config libssl-dev \
    libclang-dev clang postgresql-server-dev-16 \
    libicu-dev wget ca-certificates

# Install Rust toolchain
ENV CARGO_HOME=/usr/local/cargo
ENV PATH=$CARGO_HOME/bin:$PATH

RUN curl https://sh.rustup.rs -sSf | sh -s -- -y && \
    . "$CARGO_HOME/env" && \
    cargo install cargo-pgrx --version 0.15.0 --locked && \
    cargo pgrx init --pg16=$(which pg_config)

# Install pgvector (optional, for vector search)
RUN wget https://github.com/pgvector/pgvector/archive/refs/tags/v0.5.1.tar.gz && \
    tar -xvzf v0.5.1.tar.gz && \
    cd pgvector-0.5.1 && make && make install && \
    cd .. && rm -rf pgvector-0.5.1 v0.5.1.tar.gz

# Build and install pg_search
RUN git clone https://github.com/paradedb/paradedb.git && \
    cd paradedb/pg_search && \
    cargo pgrx install --release && \
    cd ../.. && rm -rf paradedb

Create a docker-compose.yml:

version: '3.8'

services:
  postgres:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: pgsearch-ec2
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: your_strong_password
    ports:
      - "5432:5432"
    volumes:
      - /mnt/pgdata:/var/lib/postgresql/data
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
      - "-c"
      - "max_replication_slots=10"
      - "-c"
      - "max_wal_senders=10"
      - "-c"
      - "shared_preload_libraries=pg_search"

Start the container:

docker compose up -d

Step 3: Enable the Extensions

Connect to your EC2 PostgreSQL instance:

psql -h localhost -U postgres

Create your database and enable extensions:

CREATE DATABASE app_db;
\c app_db

CREATE EXTENSION pg_search;
CREATE EXTENSION vector;  -- if using vector search

Step 4: Create Search Indexes

Now you can create powerful search indexes on your tables:

-- BM25 full-text search index
CREATE INDEX documents_search_idx
ON documents
USING bm25 (title, body)
WITH (key_field = 'id');

-- Vector similarity index (for semantic search)
CREATE INDEX documents_embedding_idx
ON documents
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

Syncing Data from RDS to EC2

Installing pg_search is only half the solution. We need continuous data replication from RDS to EC2.

Initial Data Load

First, take a snapshot of your RDS data:

# Dump from RDS
pg_dump -h your-rds-endpoint.amazonaws.com \
        -U your_user -d app_db \
        -Fc -f app_db_dump.backup

# Restore to EC2
psql -h localhost -U postgres -c "CREATE DATABASE app_db;"
pg_restore -h localhost -U postgres \
           -d app_db app_db_dump.backup

Configure RDS for Logical Replication

In your RDS parameter group, set:

rds.logical_replication = 1
max_replication_slots = 50 (or more, depending on your needs)
max_wal_senders = 10
max_slot_wal_keep_size = 2048 (MB — safety net to avoid WAL filling storage)

Apply changes and reboot your RDS instance.

Create a replication user on RDS:

CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE app_db TO repl_user;
GRANT USAGE ON SCHEMA public TO repl_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;

Create a publication on RDS:

-- Replicate all tables
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- Or specific tables only
-- CREATE PUBLICATION app_pub FOR TABLE documents, users;

Set Up Subscription on EC2

On your EC2 PostgreSQL instance:

CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=your-rds-endpoint.amazonaws.com port=5432 
              user=repl_user password=secure_password dbname=app_db'
  PUBLICATION app_pub
  WITH (
    slot_name = app_slot,
    create_slot = true,
    copy_data = false,  -- data already loaded via pg_restore
    enabled = true
  );

Verify Replication

Check replication status on EC2:

SELECT subname, pid, latest_end_time,
       now() - latest_end_time AS delay
FROM pg_stat_subscription;

Test with a simple insert on RDS:

INSERT INTO documents (id, title, body)
VALUES (gen_random_uuid(), 'Test', 'Replication check');

The row should appear on EC2 within seconds.

Running Search Queries

Now your application can run powerful search queries against the EC2 instance:

Full-text search with ranking:

SELECT id, title,
       bm25_rank((title, body), 'postgres configuration') AS score
FROM documents
ORDER BY score DESC
LIMIT 10;

Vector similarity search:

SELECT id, title,
       1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

Your application connects to RDS for writes and normal reads, and to EC2 for search operations.

Keeping New Tables in Sync

By default:

If you used FOR ALL TABLES in your publication, new tables will be included on the publisher side, but…

The subscriber needs to be refreshed to “see” them:

ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION WITH (copy_data = true);

You can automate this with a cron job on EC2:

*/15 * * * * psql -h localhost -U postgres -d app_db \ -c "ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION WITH (copy_data = true);"

This ensures new tables created on RDS are picked up and synced to EC2 regularly.

Pros and Cons

Advantages

✅ Advanced search features – BM25 ranking, vector search, and hybrid search capabilities

✅ Postgres-native – Use SQL and standard Postgres tooling instead of learning new systems

✅ RDS stays primary – Keep RDS for transactions, backups, and managed infrastructure

✅ Private network – Everything stays inside your VPC with no external dependencies

✅ Flexible extensions – Install any Postgres extension on EC2, not limited by RDS restrictions

Challenges

❌ Additional infrastructure – You now manage a self-hosted Postgres instance

❌ Replication complexity – Setting up and maintaining logical replication requires care

❌ Near real-time search – Search data lags RDS by a few seconds (acceptable for most use cases)

❌ Dual endpoints – Application needs to connect to two different databases

❌ Higher costs – Extra EC2 instance and storage expenses

Conclusion

We chose this architecture because it gives us powerful search capabilities without the operational complexity of maintaining a separate search infrastructure. While managing an EC2 Postgres instance adds some overhead, it's significantly simpler than running Elasticsearch clusters and ETL pipelines.

For teams already comfortable with PostgreSQL, this approach feels natural. Everything stays in the Postgres ecosystem, uses familiar SQL, and remains inside your private network. The trade-off of managing one additional Postgres node is worthwhile for the search capabilities you gain.

If you're considering this architecture, start small: set up a test EC2 instance, replicate a subset of tables, and validate that the search performance meets your needs before committing to production deployment.

Have questions about this setup? Feel free to reach out or leave a comment below.

A Step-by-Step Guide to Deploying an Application on AWS App Runner with GitHub Action workflow

Rajeshwar R — Wed, 31 May 2023 17:11:07 +0000

Privacy-Protect

Share passwords and sensitive files over email or store them in insecure locations like cloud drives using nothing more than desktop or mobile web browsers like chrome and safari.

No special software. No need to create an account. It's free, open-source, keeps your private data a secret, and leave you alone.

App Runner

AWS App Runner is a fully managed container application service that lets you build, deploy, and run containerized web application and API services without prior infrastructure or container experience.

Cons of App Runner

Lack of EFS Mount Support:

The absence of support for mounting the Elastic File System (EFS). Instead, AWS offers an alternative solution with DynamoDB for handling data storage. The lack of EFS mount support might be a challenge for certain use cases:Limited File Storage Flexibility, Potential Performance Impact, Additional Configuration Complexity. However, by leveraging AWS's alternative storage solution, DynamoDB, developers can work around this limitation and continue to build scalable applications.

Go through this Architecture diagram.

᛫ Create a IAM role

This role is used by AWS App runner to access AWS ECR docker images.

The following are the step-by-step instructions to create a service role and associating AWSAppRunnerServicePolicyForECRAccess policy.

step 1
Create a role named with app-runner-service-role



{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "build.apprunner.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

step 2

Attach the AWSAppRunnerServicePolicyForECRAccess existing policy to the role app-runner-service-role.

Here the steps to deploy Privacy-Protect application in App Runner using GitHub Action

᛫ Create a AWS ECR private repostiory

᛫ Clone the repository



git clone https://github.com/r4jeshwar/privacy-protect.git
cd privacy-protect

᛫ Migrate from Server deployment(Vercel-@sveltejs/adapter-vercel) to static site generator(@sveltejs/adapter-static)

First, Install npm i -D @sveltejs/adapter-static

Go to the svelte.config.js file delete the existing content and paste the below content in svelte.config.js file.



import adapter from "@sveltejs/adapter-static";
import { vitePreprocess } from "@sveltejs/kit/vite";
import { mdsvex } from "mdsvex";
import { resolve } from "path";

/** @type {import('@sveltejs/kit').Config} */
export default {
  extensions: [".md", ".svelte"],
  kit: {
    adapter: adapter({
       pages: 'build',
       assets: 'build',
      fallback: null,
      precompress: false,
      strict: true
    }),
    alias: {
      $components: resolve("src/components"),
      $icons: resolve("src/assets/icons"),
    },
    csp: {
      directives: {
        "base-uri": ["none"],
        "default-src": ["self"],
        "frame-ancestors": ["none"],
        "img-src": ["self", "data:"],
        "object-src": ["none"],
        // See https://github.com/sveltejs/svelte/issues/6662
        "style-src": ["self", "unsafe-inline"],
        "upgrade-insecure-requests": true,
        "worker-src": ["none"],
      },
      mode: "auto",
    },
  },
  preprocess: [
    mdsvex({
      extensions: [".md"],
      layout: {
        blog: "src/routes/blog/post.svelte",
      },
    }),
    vitePreprocess(),
  ],
};

Note: This changes is to deploy in various infrastructure and to dockerize in small size. If you need to deploy in vercel ignore this.

Why we need to switch out the adapter from vercel to static ?

Adapter-Static allows deploying Vite.js application to various static hosting providers.It also simplifies the deployment process by eliminating the need for serverless infrastructure and configuration specific to Vercel.

Overall, migrating from Adapter-Vercel to Adapter-Static empowers you with greater flexibility, simplicity.

᛫ Dockerfile for privacy-protect to deploy in App Runner



FROM node:18-alpine as build

WORKDIR /app

COPY . .

RUN npm i
RUN npm run build


FROM nginx:stable-alpine

COPY --from=build /app/build/ /usr/share/nginx/html

Push the changes to your repository to be picked up by the GH workflow we'll be setting up next

᛫ Configure the GitHub Action secrets

Go to your project repository, Go to settings in the security section click the Secrets and variables drop down and click Actions. Configure the variables.

AWS_ACCESS_KEY_ID is your AWS user access key.
AWS_SECRET_ACCESS_KEY is your AWS user secret key.
AWS_REGION is the region of AWS services where you creating.
ROLE_ARN is your IAM role ARN which you have created before which you named app-runner-service-role

᛫ Configure the new workflow in GitHub Action

Click set up a workflow by yourself

Next, on the file editor(main.yml), populate the below YAML file



name: Deploy to App Runner - Image based # Name of the workflow

on:

  push:

    branches: [ main ] # Trigger workflow on git push to main branch

  workflow_dispatch: # Allow manual invocation of the workflow

jobs:


  deploy:

    runs-on: ubuntu-latest

steps:      
  - name: Checkout
    uses: actions/checkout@v2
    with:
      persist-credentials: false

  - name: Configure AWS credentials
    id: aws-credentials
    uses: aws-actions/configure-aws-credentials@v1
    with:
      aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
      aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
      aws-region: ${{ secrets.AWS_REGION }}

  - name: Login to Amazon ECR
    id: login-ecr
    uses: aws-actions/amazon-ecr-login@v1        

  - name: Build, tag, and push image to Amazon ECR
    id: build-image
    env:
      ECR_REGISTRY: ${{ steps.login-ecr.outputs.registry }}
      ECR_REPOSITORY: privacy-protect
      IMAGE_TAG: ${{ github.sha }}
    run: |
      docker build -t $ECR_REGISTRY/$ECR_REPOSITORY:$IMAGE_TAG .
      docker push $ECR_REGISTRY/$ECR_REPOSITORY:$IMAGE_TAG
      echo "::set-output name=image::$ECR_REGISTRY/$ECR_REPOSITORY:$IMAGE_TAG"  

  - name: Deploy to App Runner
    id: deploy-apprunner
    uses: awslabs/amazon-app-runner-deploy@main        
    with:
      service: privacy-protect-app-runner
      image: ${{ steps.build-image.outputs.image }}          
      access-role-arn: ${{ secrets.ROLE_ARN }}       
      region: ${{ secrets.AWS_REGION }}
      cpu : 1
      memory : 2
      port: 80
      wait-for-service-stability: true

  - name: App Runner ID
    run: echo "App runner ID ${{ steps.deploy-apprunner.outputs.service-id }}"

᛫ GitHub Action runs successfully after a few minutes

You can see the AWS App Runner dashboard your application is up and running successfully.

How to Deploy a TimeVault Application on Cloud Run with Cloud Build on GCP

Rajeshwar R — Tue, 23 May 2023 07:00:32 +0000

Timevault

A deadman's switch to encrypt your vulnerability reports or other compromising data to be decryptable at a set time in the future. Uses tlock-js and is powered by drand. Messages encrypted with timevault are also compatible with the go tlock library.

Cloudrun
Cloud Run is a managed compute platform that lets you run containers directly on top of Google's scalable infrastructure. You can deploy code written in any programming language on Cloud Run if you can build a container image from it. In fact, building container images is optional.

Structure of Timevault

Step 1:

Create a repository as in GCP Cloud Source Repositories name timevault-cloudrun. And clone the repository to your local.

Step 2:
Clone the timevault repository

 git clone https://github.com/r4jeshwar/timevault.git
 cd  timevault

Step 3:

Mirror the Github (timevault) repository to GCP cloud source repository

Go the GCP cloud source repository service and click Add repository and click connect external repository next click continue

Select your project ID and Select Github in Git provider, next choose your needed repository and click connect selected repository

Finally, Your Github repository will connect to your GCP cloud source repository

Step 4:
Using this Multi-stage Dockerfile we can able to deploy it on Cloudrun

FROM node:18-alpine AS build

WORKDIR /app

COPY package*.json ./
COPY ./src ./src

RUN npm i
RUN npm run build

FROM nginx:stable-alpine

COPY --from=build /app/dist/ /usr/share/nginx/html/

Step 5:
Write the cloudbuild.yaml to deploy it on Cloudrun. Here the cloudbuild.yaml file

 steps:
 # Build the container image
 - name: 'gcr.io/cloud-builders/docker'
   args: ['build', '-t', 'gcr.io/$_PROJECT_ID/$_IMAGE_NAME:$COMMIT_SHA', '.']
 # Push the container image to Container Registry
 - name: 'gcr.io/cloud-builders/docker'
   args: ['push', 'gcr.io/$_PROJECT_ID/$_IMAGE_NAME:$COMMIT_SHA']
 # Deploy container image to Cloud Run
 - name: 'gcr.io/google.com/cloudsdktool/cloud-sdk'
   entrypoint: gcloud
   args:
   - 'run'
   - 'deploy'
   - '$_IMAGE_NAME'
   - '--platform=managed'
   - '--image'
   - 'gcr.io/$_PROJECT_ID/$_IMAGE_NAME:$COMMIT_SHA'
   - '--region'
   - '$_REGION'
   - '--allow-unauthenticated'
   - '--port'
   - '80'  
 images:
 - 'gcr.io/$_PROJECT_ID/$_IMAGE_NAME:$COMMIT_SHA'

 substitutions:
   _IMAGE_NAME: timevault
   _REGION: <YOUR_REGION>
   _PROJECT_ID: <YOUR_PROJECT_ID>

YOUR_REGION is the region of the Cloud Run service you are deploying.
YOUR_PROJECT_ID is your Google Cloud project ID where your image is stored.

Step 6:

Go to Cloud Build and create a trigger.

Step 7:

Trigger the Cloud Build. By below steps:
Cloud Build --> Triggers --> click RUN --> click RUN TRIGGER

Deployed on Cloud Run. Copy the Cloud Run URL and expose it on web browser

Adding custom domain in Cloud run

When you deploy a service to Cloud run, you are provided with a default domain to access the service. However, you can use your own domain or subdomain instead of default one.

Step 1:
Go to Cloud run service, Click MANAGE CUSTOM DOMAINS

Step 2:

Go to the domain mapping, Click ADD MAPPING

Step 3:

In the add mapping form, select the service which you map to and select the verify the new domain, next enter your domain. As shown in the below. Click the CONTINUE

It will take some time to verify your domain, Click REFRESH to check verification is done.

After the verification is done, enter the subdomain to map to the service you have selected, then click CONTINUE

You will get the DNS record for your custom domain that maps to your Cloud run service. You need to add this DNS record to your domain provider

Finally, when you add the DNS record successfully, you can use your custom domain to access your deployed Cloud run service.