Forem: Rafael Rodrigues

Building Robust Task Queues and Event‑Driven Workflows in NestJS with Event Bus, Redis, and Bull

Rafael Rodrigues — Wed, 30 Jul 2025 19:26:38 +0000

This article shows how to implement a message‑driven workflow in NestJS using its built‑in Event Bus together with Redis‑backed Bull queues. As a concrete use case, we’ll send a verification email whenever a new user registers. By using events, we decouple user registration from email delivery, making our system more modular, easier to test, and resilient to email service failures.

To follow along, you should have at least a basic understanding of how NestJS and its module system work.

Architecture overview

We glue together three pieces:

NestJS Event Bus (CqrsModule) for in‑process notifications.
Bull queues (via @nestjs/bull) backed by Redis for reliable background jobs.
MailerService for actual email delivery.

In this example, all components share the same Redis instance so there’s no need to operate multiple brokers.

First, register the queue as a module:

@Global()
@Module({
  imports: [
    BullModule.forRootAsync({
      imports: [AppConfigModule],
      inject: [AppEnvConfigService],
      useFactory: (config: AppEnvConfigService) => ({
        redis: { host: config.redisHost, port: config.redisPort },
      }),
    }),
    BullModule.registerQueue({ name: MAIL_PROCESS_TOKEN }),
    MailModule,
  ],
  providers: [
    { provide: EmailGateway, useClass: MailQueueService },
    MailProcessor,
  ],
  exports: [EmailGateway, BullModule],
})
export class QueueModule {}

This sets up a Redis-backed queue named mail and binds the custom EmailGateway interface to the MailQueueService implementation. These components can be adapted to suit your specific use cases. For testing purposes, this article demonstrates how to configure a local SMTP server for email delivery.

As for the Redis module, the configuration shown here is based on my setup, but you can adjust it to match your own requirements.

Define the event:

export class UserRegistered {
  constructor(
    public readonly userId: number,
    public readonly email: string,
    public readonly username: string,
    public readonly verificationCode: string,
  ) {}
}

We publish this event any time a new user is registered. We could have any other event here, such as to send the welcome email after users verify their account, trigger a referral bonus process, log the registration activity for analytics, etc.

Publish the event within the service/use-case layer:

@Injectable()
export class UserRegistrationService {
  constructor(private readonly userService: UserService) {}

  async signUp(registerDto: RegisterDto): Promise<MessageDto> {
    await this.userService.create(registerDto);
    return { message: 'Account created successfully' };
  }

  // any other related service here
}

Whenever you call signUp, an event is emitted. Later we’ll subscribe to it.

Enqueue jobs via an interface, here we use our interface EmailGateway:

@Injectable()
export class MailQueueService implements EmailGateway {
  constructor(
    @InjectQueue(MAIL_PROCESS_TOKEN) private readonly mailQueue: Queue,
  ) {}

  async enqueueVerification(
    to: string,
    username: string,
    code: string,
  ): Promise<void> {
    await this.mailQueue.add(
      SEND_VERIFICATION_PROCESS_TOKEN,
      { to, username, code },
      { attempts: 5, backoff: { type: 'fixed', delay: 1000 } },
    );
  }

   // any other related service here
}

enqueueVerification schedules the verification job with retries and fixed backoff.

The .add method supports various configuration options, choose the ones that best suit your use case. See the official documentation for full details.

Define the process jobs

@Processor(MAIL_PROCESS_TOKEN)
export class MailProcessor {
  constructor(private readonly mailService: MailService) {}

  @Process(SEND_VERIFICATION_PROCESS_TOKEN)
  async handleSendVerification(job: Job<{
    to: string; username: string; code: string;
  }>) {
    const { to, username, code } = job.data;
    await this.mailService.sendVerificationEmail(to, username, code);
  }

  // others related processing methods
}

The processor listens for incoming queue jobs and invokes the MailService for each task. Defining the correct token for each process is essential, as it determines how Bull maps jobs to their respective processors.

MailService implementation

@Injectable()
export class MailService {
  constructor(private readonly mailer: MailerService) {}

  async sendVerificationEmail(
    to: string, username: string, code: string
  ): Promise<void> {
    const confirmationUrl = `https://yourapp.com/verify?code=${code}`;
    await this.mailer.sendMail({
      to,
      subject: 'Confirm your email',
      template: 'verification',
      context: { username, confirmationUrl },
    });
  }
}

Actual email dispatch is handled here. Any failure bubbles up and can be retried by Bull.

Wire up the event handler:

@EventsHandler(UserRegistered)
export class SendVerificationEvent implements IEventHandler<UserRegistered> {
  constructor(private readonly mailQueue: MailQueueService) {}

  handle(event: UserRegistered) {
    // retry if fail to send email
    return this.mailQueue.enqueueVerification(
      event.email,
      event.username,
      event.verificationCode,
    );
  }
}

This handler listens for the UserRegistered event and delegates the email-sending task to the queue by calling enqueueVerification. It helps separate domain logic from infrastructure concerns and allows failed attempts to be retried automatically.

Invoke the event in your service

Finally, you need to publish the UserRegistered event at the point where a new user is created or when you issue a fresh verification code. In your user‑registration service, inject the NestJS EventBus and call publish(...)

@Injectable()
export class UserService {
  constructor(
    private readonly eventBus: EventBus,
    private readonly userTokenService: UserTokenService,
    // … other injections
  ) {}

  async create(dto: CreateUserDto): Promise<User> {
    const result = await this.dataSource.transaction(async (manager) => {
      // atomic logic to handle rollbacks
      // e.g., create user, generate token, save to DB
    });

    const { user, verificationCode } = result;

    // Publish event to trigger email delivery
    this.eventBus.publish(
      new UserRegistered(user.id!, user.email, user.username, verificationCode),
    );

    return user;
  }
}

After this call, the UserRegistered event flows through the NestJS Event Bus, your SendVerificationEvent handler enqueues the Bull job, and MailProcessor delivers the verification email in the background.

Example flow

The client calls POST /user, triggering a controller or resolver that invokes userService.create(...).
UserRegistered is published.
The SendVerificationEvent handler receives the event and enqueues a Bull job via MailQueueService.
The MailProcessor picks up the job and calls MailService.sendVerificationEmail(...).
If email delivery fails, Bull retries the job up to 5 times with a 1-second fixed delay (as configured).

Benefits

Zero‑config wiring thanks to Nest’s decorators (@OnEvent, @Processor, @InjectQueue).
Single infrastructure piece (Redis) powers both event transport and job persistence.
Built‑in retry logic, back‑off, and dead‑letter handling.
Easy horizontal scaling: add more workers or API nodes without changing code.
Simple monitoring via Bull UI tools for pending/failed jobs.

Bull also provides a web-based UI to inspect and manage queues. In this example, it is accessible at http://localhost:3010/queues/.

Through this panel, you can view job statuses, retry failed jobs, inspect logs, and examine queue parameters.

To set it up, add the following code to your main.ts (or index.ts):

export function registerBullBoard(
  app: INestApplication,
  mountPath: string,
  queueTokens: string[],
  env: AppEnvConfigService,
): void {
  const serverAdapter: ExpressAdapter = new ExpressAdapter();
  serverAdapter.setBasePath(mountPath);

  const adapters: BullAdapter[] = queueTokens.map((token) => {
    const q = app.get<Queue>(getQueueToken(token));
    return new BullAdapter(q);
  });

  createBullBoard({ queues: adapters, serverAdapter });
  app.use(
    mountPath,
    basicAuth({
      users: { [env.bullUser]: env.bullPassword },
      challenge: true,
    }),
    serverAdapter.getRouter(),
  );
}

async function bootstrap(): Promise<void> {
  const app: INestApplication = await NestFactory.create(AppModule);

  const env: AppEnvConfigService = app.get(AppEnvConfigService);

  registerBullBoard(app, '/queues', [MAIL_PROCESS_TOKEN], env);

  await app.listen(env.apiPort ?? 3010);
}

void bootstrap();

The basicAuth middleware is optional. You can remove it if you prefer to access the Bull UI without authentication during local development.

Besides Bull and the NestJS Event Bus, you could use messaging brokers like Kafka or RabbitMQ. The key question is: When should you choose a full-featured broker instead?

Use a dedicated broker when your system requires cross-language communication, strict message ordering, or persistent event storage. For most web applications and background job processing, the combination of the NestJS Event Bus and Bull provides reliable and low-overhead message handling.

Terraform + AWS Free Tier, From Zero to Test Deployment: A 20‑Minute Playground

Rafael Rodrigues — Wed, 30 Jul 2025 18:58:08 +0000

Introduction

On July 15, 2025 AWS simplified its Free Tier into a credit-based model, offering $200 in credits to new accounts, making it easier than ever to spin up and experiment with real infrastructure at minimal cost. This example setup is intended for testing only and is not recommended for running production workloads. It is especially useful for developers who are new to AWS and Terraform and want a quick environment to start learning. In this configuration:

Networking and access control are defined as public and SSH is enabled on all IPs.
IAM roles use broad permissions rather than least-privilege best practices.
RDS sizing and high availability are minimal.
Terraform state is assumed to be stored locally without remote locking.
Cost and load testing should be performed before handling real traffic.

For teams and individual developers looking to validate system architectures before committing to production budgets, this new Free Tier provides a perfect playground. In this article, we explore how to leverage Terraform to provision a fully functional AWS environment, including VPC networking, EKS (Kubernetes) compute, RDS databases, ElastiCache (Redis) clusters, and logging, capable of handling a modest load of around 5,000 users. You’ll learn why Terraform is the ideal tool for repeatable, versioned infrastructure, see best-practice patterns for each component, and discover how this baseline can evolve to support millions of users down the road.

Using AWS Free Tier with Terraform

By pairing Terraform’s infrastructure‑as‑code with AWS’s $200 credits, you can:

Prototype rapidly: Launch an entire VPC, cluster, database and cache in minutes. With this setup, it typically takes around 20 to 30 minutes. This timing is determined by AWS provisioning and cannot be sped up from your end.
Stay change‑aware: Preview exactly which resources will be created, modified or destroyed with terraform plan before you apply.
Iterate safely: Tear down and rebuild environments without manual error, ensuring every change is tracked in Git.

To get started, install the AWS CLI and Terraform, configure your AWS credentials (grant admin role for all services).

Why Terraform?

Idempotency: Apply the same configuration repeatedly with predictable results.
Modularity: Break your architecture into reusable modules (VPC, EKS, RDS, ElastiCache) to simplify management.
Version control: Keep your entire environment in Git; peer‑review changes before they touch actual infrastructure.
Planning: Use terraform plan to catch unintended changes before resources spin up (and burn credits).

To get up and running in minutes, copy the Terraform files below into your working directory, then run the Terraform commands shown in the final of this article.

Infrastructure Components & Best Practices

1. VPC & Networking

File: main.tf (VPC, subnets, Internet/NAT gateways, route tables)
Best Practices:
- Tag every resource with Project and Environment.
- Split public/private subnets across at least two AZs for resilience.
- Enable DNS hostnames/support on your VPC for service discovery.

2. EKS Cluster

File: eks.tf (using terraform-aws-modules/eks/aws)
Explanation: Leverages a battle‑tested module to provision control plane and managed node groups.
Best Practices:
- Lock node groups to your home IP initially, then tighten security over time.
- Use auto‑scaling (min_size, max_size) to accommodate load spikes.
- Inject IAM roles and OIDC provider for fine‑grained pod permissions.

3. RDS PostgreSQL

File: rds.tf
Explanation: A single db.t3.micro instance with encrypted storage, automated backups, and enhanced monitoring.
Best Practices:
- Enable performance_insights for query tuning.
- Keep skip_final_snapshot = true only in non‑production environments; enable snapshot retention in production.
- Group DB subnets and parameter settings in dedicated resources for clarity.

4. ElastiCache Redis (Optional - only if your API has Redis)

File: elasticache.tf
Explanation: A single‑node Redis cluster (cache.t3.micro) with LRU eviction and key‑space notifications.
Best Practices:
- Use encryption in transit and at rest.
- Ship slow‑log and engine logs to CloudWatch for visibility.
- Define a subnet group spanning private subnets to isolate cache traffic.

5. Application & Security Groups

File: app.tf
Explanation: Security groups opening only necessary ports (app port 3010, SSH for maintenance).
Best Practices:
- Favor specific CIDR blocks over “0.0.0.0/0” where possible.
- Group common ingress/egress rules into reusable modules.

6. Logging & Monitoring

File: elasticache.tf (CloudWatch log group defined alongside the ElastiCache resources)
Explanation: Captures ElastiCache slow‑log and engine‑log for performance analysis with a 7-day retention
Best Practices:
- Align retention settings with compliance requirements
- Forward logs to a centralized monitoring account as your organization grows
- Bind a CloudWatch log group for each major service in its respective Terraform file (for example, add log groups in rds.tf, app.tf and eks.tf) to ensure comprehensive observability

7. Variables Definition

File: variables.tf
Explanation: Declares all input variables used by your Terraform configurations. Each variable includes a description, type, default value (where appropriate), and a sensitive flag for secrets. This file ensures Terraform knows what inputs to expect and provides IDE support, type checking, and documentation.
Best Practices:
- Group variables by component (for example VPC, RDS, ElastiCache, EKS)
- Provide sensible defaults for common values and override via .tfvars files
- Mark secrets (such as database passwords) as sensitive = true to avoid accidental output
- Add validation blocks to enforce constraints (for example minimum password length)
- Keep descriptions concise and clear so teammates understand each variable’s purpose
- Avoid hard‑coding environment‑specific settings; instead inject them through .tfvars, explained below.

Terraform Variables (`.tfvars`)

Terraform uses a variables file to inject environment‑specific values without changing your .tf files. Create a file named terraform.tfvars alongside your configuration and include the following settings:

aws_region                  = "us-east-1"
project_name                = "project-name"
environment                 = "development"
vpc_cidr                    = "10.0.0.0/16"
availability_zones          = ["us-east-1a", "us-east-1b"]
public_subnets              = ["10.0.1.0/24", "10.0.2.0/24"]
private_subnets             = ["10.0.11.0/24", "10.0.12.0/24"]
allowed_ssh_cidr            = ["0.0.0.0/0"]
db_instance_class           = "db.t3.micro"
db_allocated_storage        = 20
db_max_allocated_storage    = 100
db_name                     = "db-name"
db_username                 = "db-username"
db_password                 = "your-secure-password-here"
db_backup_retention_period  = 7
cache_node_type             = "cache.t3.micro"
eks_cluster_name            = "name-example-eks"

NOTE: Adjust these values as needed for your environment. The name of the file should be exactly terraform.tfvars.

Using a .tfvars file lets you:

Keep secret or environment‑specific values out of your versioned .tf files.
Swap between development, staging and production settings by passing a different .tfvars file.
Simplify automation by referencing a single variables file.

Ignoring Terraform Artifacts

You don’t want to add sensitive or unnecessary Terraform files to your GitHub repository. Add the following entries to your .gitignore, adjusting the paths to match your Terraform folder (for example /src/infrastructure/cloud/terraform):

# Terraform directories and state
/src/infrastructure/cloud/terraform/.terraform
/src/infrastructure/cloud/terraform/.terraform.lock.hcl
/src/infrastructure/cloud/terraform/.terraform.tfstate.lock.info

# Terraform variable and state files
/src/infrastructure/cloud/terraform/terraform.tfvars
/src/infrastructure/cloud/terraform/terraform.tfstate
/src/infrastructure/cloud/terraform/terraform.tfstate*

# Terraform plan files
/src/infrastructure/cloud/terraform/tfplan

Applying and Managing Terraform

To provision this environment, inside your terraform folder, run:

terraform init , followed by terraform plan -out=tfplan and after terraform apply tfplan , Terraform will then begin provisioning the AWS resources defined in your configuration.

Others useful Terraform commands:

terraform fmt: keeps configuration style consistent
terraform validate: checks for syntax errors and missing variables
terraform destroy: destroys all resources managed by your Terraform state
terraform untaint <resource_address>: marks a resource as tainted so it will be replaced on the next apply. Example: terraform untaint aws_db_instance.main
terraform apply -replace="<resource_address>": forces replacement of only the specified resource. Example: terraform apply -replace="aws_elasticache_replication_group.main”
terraform destroy -target=<resource_address>: destroys only the specified resource without affecting others. Example: terraform destroy -target=aws_security_group.app
Full list here.

Conclusion & Scaling to Millions

This Terraform‑driven setup, running on AWS’s new Free Tier credits gives you a turnkey environment capable of supporting roughly 5,000 moderate daily users. To scale toward millions of users, you can:

EKS: Introduce horizontal pod autoscaling, cluster autoscaler, and multiple node groups (including spot instances).
RDS: Migrate from a single t3.micro to Amazon Aurora with read replicas for read‑heavy workloads.
ElastiCache: Enable cluster‑mode with sharding and multiple replicas for high availability and throughput.
Global Reach: Add multi‑region deployments with Route 53 latency routing and database cross‑region replicas.
CI/CD & Observability: Integrate Terraform Cloud or GitHub Actions for automated drift detection, and plug in Prometheus/Grafana for real‑time metrics.

By treating your AWS environment as code, you maintain the agility to evolve your architecture seamlessly, and with AWS Free Tier credits still available for your initial experiments, there’s no better time to start.

Quick note: If you don't want to burn your credits, you can run terraform destroy after testing this setup.

You can view my entire Terraform folder for reference here.

MailHog: a Free, Containerized SMTP Server for Local Development

Rafael Rodrigues — Wed, 09 Jul 2025 12:54:11 +0000

Are you still relying on limited third‑party SMTP services to test your email features? Hosted services often impose monthly sending caps, throttle rates, and require external network access. Self‑hosting MailHog in Docker eliminates these constraints, offers complete control over your local email pipeline, and reduces exposure to external dependencies. Unlike freemium platforms such as Mailtrap or SendGrid’s free tier, MailHog runs entirely on your machine at zero cost and without registration.

Using MailHog with Docker

MailHog is distributed as a lightweight Docker image. You can isolate one container per application to prevent mixing email logs across projects. By default, MailHog stores messages in memory, so every restart clears your inbox. To persist emails across restarts, configure the MH_STORAGE and MH_MAILDIR_PATH environment variables in your Docker Compose file and mount a volume:

 mailhog:
    image: mailhog/mailhog
    container_name: mailhog
    restart: unless-stopped
    environment:
      MH_STORAGE: maildir                  # switch from in-memory to maildir on disk :contentReference[oaicite:0]{index=0}
      MH_MAILDIR_PATH: /mailhog/data       # where on the container to store the maildir
    volumes:
      - ./mailhog_data:/mailhog/data
    ports:
      - '1025:1025'  # SMTP
      - '8025:8025'  # Web UI

This creates a mailhog_data folder at your project root. Add it to .gitignore to avoid committing email logs.

If you prefer a single Docker command instead of Compose:

docker run -d \
  --name mailhog \
  --restart unless-stopped \
  -e MH_STORAGE=maildir \
  -e MH_MAILDIR_PATH=/mailhog/data \
  -v "$(pwd)/mailhog_data:/mailhog/data" \
  -p 1025:1025 \
  -p 8025:8025 \
  mailhog/mailhog

Access it using http://localhost:8025/

Simulating Failures with Jim

MailHog includes an optional chaos‑testing feature called Jim. Jim helps you test how your application handles intermittent SMTP issues: delayed deliveries, random drops, temporary network errors, and more. With Jim enabled you can verify retry logic, error handling, and user notifications under real‑world failure scenarios.

Integrating MailHog with NestJS

Below is an example of configuring Nest’s MailerModule to use MailHog. No authentication is required by default. Adjust config.smtpMailHost and related variables to your environment variables.

import { Module } from '@nestjs/common';
import { MailerModule } from '@nestjs-modules/mailer';
import { HandlebarsAdapter } from '@nestjs-modules/mailer/dist/adapters/handlebars.adapter';
import { join } from 'path';

import { AppConfigModule } from '../config/app-config.module';
import { AppEnvConfigService } from '../config/environment-variables/app-env.config';
import { MailService } from 'src/application/mail/mail.service';

export function getTemplateDir(): string {
  const srcDir = join(
    process.cwd(),
    'src',
    'infrastructure',
    'mail',
    'templates',
  );
  const distDir = join(__dirname, 'templates');
  return process.env.NODE_ENV === 'production' ? distDir : srcDir;
}

@Module({
  imports: [
    MailerModule.forRootAsync({
      imports: [AppConfigModule],
      inject: [AppEnvConfigService],
      useFactory: (config: AppEnvConfigService) => ({
        transport: {
          host: config.smtpMailHost,
          port: config.smtpMailPort,
          secure: config.smtpMailSecure ?? false, // optional TLS toggle
          auth: config.smtpMailUser
            ? {
                // if you need auth:
                user: config.smtpMailUser,
                pass: config.smtpMailPassword,
              }
            : undefined,
        },
        defaults: {
          from: config.smtpMailFrom,
        },
        template: {
          dir: getTemplateDir(),
          adapter: new HandlebarsAdapter(),
          options: { strict: true },
        },
      }),
    }),
  ],
  providers: [MailService],
  exports: [MailService],
})
export class MailModule {}

Testing MailHog Setup

For a complete example of health checks and automated tests for your MailHog setup, check them out here in this repository.

How about you? Have you tried MailHog or similar containerized SMTP? Share your experience and any tips in the comments!

How to Optimize Search Queries in Large Databases with PostgreSQL and GIN Indexes

Rafael Rodrigues — Sat, 12 Apr 2025 22:01:07 +0000

In this article, I’m going to show you an easy and safe way to speed up search queries in large database sets using PostgreSQL. To demonstrate the process, I’ll use a common use case where you want to search through a database to find records that match a specific term.

Repository link: https://github.com/venuziano/high-performance-search-large-set-database

Let’s consider the following scenario as an example:

Library admins want to search for books in a database that match a specific title or category.
Their database contains over 2 million book records, with each book associated with 10 distinct categories (resulting in 20 million book-category relations).
There are 100 categories in total.

To start, we can use a simple endpoint, which returns a list of books with their categories. Here’s the documentation in Swagger:

This endpoint has pagination, sort and filter functionalities. I used the URL below in Postman, but it will also work in Swagger, Curl and other API tools:
/book/get/all?filter=romance&limit=50&page=1&order=ASC&sort=updated_at

Next, we need to generate the database schema and the dataset. To do so, we can use migrations that execute SQL queries and procedures to save time. If you have a look at the folder migrations in the repository, it contains five migration files: the first file creates the book, category and book_categories database tables; the second, third and fourth files create the procedures that insert data into the database; and the last file creates the database indexes. The book data (name, author, publisher, etc.) and category names are randomly generated from a predefined list in the migration files. Check them for more details if you would like to understand what’s happening in the background.

Now, for testing purposes, let’s use the search terms “special book”, “category” and “romance”. When searching for “special book”, the endpoint returns 7 results among 20 millions records within 250 ms:

Searching for “category”, the endpoint returns 1 result within 800 ms:

Finally, when searching for “romance”, the endpoint returns around 1.2 million results within 3.5 seconds:

From the results, we can see that the search performance was good, despite the large amount of records in the database. This is because, instead of using built-in ORM methods and multi-table JOINs in the SELECT query, I used the following raw SELECT query to fetch data:

`
   SELECT
     *,
     (
       SELECT jsonb_agg(jsonb_build_object('category', to_jsonb(c.*)))
       FROM book_categories bc
       JOIN category c ON c.id = bc.category_id
       WHERE bc.book_id = b.id
     ) AS "bookCategories"
   FROM (
     SELECT
       *
     FROM book b
     WHERE to_tsvector('english', coalesce(b.name, '') || ' ' || coalesce(b.author, '') || ' ' || coalesce(b.publisher, ''))
           @@ to_tsquery('english', $1)
     UNION
     SELECT
       *               
     FROM book b
     WHERE EXISTS (
       SELECT 1
       FROM book_categories bc
       JOIN category c ON c.id = bc.category_id
       WHERE bc.book_id = b.id
         AND to_tsvector('english', c.name) @@ to_tsquery('english', $1)
     )
   ) b
   ORDER BY b.${sortField} ${sortOrder}
   LIMIT $2 OFFSET $3;
 `

As there's pagination, I did the same for the COUNT query.

For comparison, I created three other services using TypeORM’s built-in methods, such as .leftJoinAndSelect(), .createQueryBuilder() and others, one of the 'tradicional' way of using TypeORM. You can check them here. The difference is significant: when searching for ‘romance’ (the same query I showed above), the endpoint took around 49 seconds to load.

Below I show the key to achieving such high performance, although results may vary depending on your machine hardware and how many fields in the database need to be scanned with the inputted search term.

Key takeaways:

In my example, I used the jsonb_agg aggregate function to fetch the category data in a JSON format: We use the aggregate function to group related data into one field, reducing the number of JOINs and simplifying how nested data is returned to the application.
The SELECT query also uses to_tsvector to convert text data into a searchable format using GIN indexes. This speeds up full-text searches by allowing the database to quickly filter out non-matching rows instead of scanning each row with pattern matching.
The UNION operator combines two search conditions: one that directly searches the book’s own text fields and another that searches within its related categories. UNION inherently removes duplicates, so this optimizes each subquery to ensure that you only retrieve relevant book data.

Given these points, we can see that this is an efficient query because the database handles complex data transformations—for example, using the jsonb_agg aggregate function—, which leverages full-text search optimizations and combines results efficiently using the UNION operator. (If you were to use regular JOINs and indexes without the listed key points, SELECT and COUNT queries would take more than 15 seconds to fetch records.) Moreover, this approach can be used for other use cases as well, such as to search for customer information and their software licenses, specific items in a store, stock names in a user’s trading history, etc.—just customize the query above according to your needs. That said, in order to avoid SQL injection, remember to always use query parameters and sanitize user input values.

I encourage you to test this repository on your own. You can clone the repository, set up an .env file and execute the command docker compose up --build -d (refer to the Readme file for more details if necessary). The first initialization may take a few minutes due to the procedure that inserts the 20 million records into the book_categories table. This is what your Docker logs should look like when all the procedures have been executed:

On a related note, have you ever needed to implement pagination, sort and filter functionalities using a large database set? If yes, was your implementation efficient?