DEV Community: Poojan

Scaling PostgreSQL in Production: Indexing, RPC Functions, Views, and Query Optimization

Poojan — Fri, 05 Jun 2026 00:00:00 +0000

As applications grow, database performance becomes one of the biggest factors affecting user experience. Features can be implemented quickly, but poorly optimized queries, inefficient data access patterns, and increasing data volume eventually create bottlenecks.

Recently, I worked on optimizing a multi-tenant PostgreSQL system built on Supabase. The system consisted of:

99 database tables
Nearly 1 million records in the largest table
More than 120,000 database requests per day
Complex reporting and dashboard requirements
Transactional workloads with strict consistency requirements

While the application continued to function correctly, several reporting and aggregation queries had started taking multiple seconds to execute. As the dataset grew, it became clear that improvements were needed to keep the system responsive and maintainable.

This article covers the techniques that produced the biggest impact.

Understanding the Bottlenecks

The application served multiple tenants from a shared database. Most operations were tenant-scoped and involved data spread across several related tables.

The primary performance challenges were:

Slow reporting queries
Repeated aggregation logic
Increasing dashboard generation time
Large result sets being fetched unnecessarily
Excessive application-side processing
Growing query complexity

Rather than attempting a complete redesign, the focus was on improving the existing architecture through targeted database optimizations.

Composite Indexes Instead of More Indexes

One of the first discoveries was that several frequently executed queries were filtering on multiple columns simultaneously.

A common mistake is creating separate indexes for every column and expecting PostgreSQL to combine them efficiently via index merging. In practice, composite indexes often provide significantly better results when they match actual query patterns because they allow Postgres to perform a single index scan to satisfy multiple filters.

For example, instead of indexing columns independently:

-- ❌ Suboptimal: Individual index on each column
CREATE INDEX idx_events_tenant_id ON events (tenant_id);
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_date ON events (event_date);

we created a single composite index tailored to our access patterns:

-- ✅ Optimal: Single composite index matching query filters
CREATE INDEX idx_events_tenant_user_date ON events (tenant_id, user_id, event_date);

Similarly, transaction-related queries benefited from composite indexes structured like:

CREATE INDEX idx_transactions_composite 
ON transactions (tenant_id, transaction_type, created_at, status_id);

and scheduling-related operations used patterns such as:

CREATE INDEX idx_schedules_composite 
ON schedules (tenant_id, category_id, week_number, day_of_week);

The most important lesson was that index order matters.

The leading columns in the index should closely match how the query filters data. Since most queries were tenant-scoped, the tenant identifier typically appeared first. If a query filters on tenant_id and user_id but not event_date, the composite index is still utilized. However, a query filtering only on event_date would not benefit from this index.

After reviewing query patterns and introducing composite indexes where appropriate, reporting workloads showed noticeable improvements and several slow queries were eliminated entirely.

Moving Business Logic into Supabase RPC Functions

Initially, many operations required multiple database round trips.

The application would:

Fetch data
Perform calculations
Execute additional queries
Aggregate results
Return the final response

This approach worked well during the early stages of development but became increasingly expensive as datasets grew and the latency of consecutive queries compounded.

To reduce overhead, complex operations were moved into Supabase RPC functions (PostgreSQL PL/pgSQL database functions).

Instead of multiple client-server interactions:

Application
 ├─ Query A (Fetch tenant orders)
 ├─ Query B (Fetch menu metrics)
 ├─ Query C (Insert audit log)
 └─ Processing (Aggregate in Node.js)

the flow became:

Application
 └─ RPC Function (get_tenant_dashboard_metrics)
      ├─ Validation
      ├─ Aggregation
      ├─ Calculations
      └─ Result

Here is a simplified example of how we defined the database function in Postgres:

CREATE OR REPLACE FUNCTION get_tenant_summary(p_tenant_id UUID)
RETURNS TABLE (
  total_revenue NUMERIC,
  completed_orders BIGINT
) LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COALESCE(SUM(amount), 0) AS total_revenue,
    COUNT(id) FILTER (WHERE status = 'completed') AS completed_orders
  FROM transactions
  WHERE tenant_id = p_tenant_id;
END;
$$;

We then call this function in a single, fast client-side request:

const { data, error } = await supabase
  .rpc('get_tenant_summary', { p_tenant_id: tenantId });

This reduced network round trips, centralized business logic, and improved execution speed. The biggest performance gains were observed in reporting-related workloads where response times dropped from several seconds to millisecond-level execution.

Simplifying Data Access with Database Views

As the application evolved, certain joins and aggregations began appearing repeatedly throughout the codebase. The same datasets were being reconstructed in multiple places.

To solve this, database views were introduced.

For example, to build a consolidated daily activity summary, we defined a Postgres View:

CREATE OR REPLACE VIEW daily_activity_summary AS
SELECT 
  tenant_id,
  DATE_TRUNC('day', created_at) AS activity_date,
  COUNT(id) AS total_events,
  COUNT(DISTINCT user_id) AS active_users
FROM events
GROUP BY tenant_id, DATE_TRUNC('day', created_at);

Instead of repeatedly joining numerous tables across different services, the application can now query the view directly, using client-side filters:

const { data, error } = await supabase
  .from('daily_activity_summary')
  .select('activity_date, total_events, active_users')
  .eq('tenant_id', tenantId)
  .order('activity_date', { ascending: false });

Benefits included:

Reduced query duplication
Cleaner application code
Easier reporting development
Improved maintainability
Faster onboarding for developers

Views effectively became reusable building blocks for reporting and dashboard generation.

Eliminating Unnecessary Data Retrieval

During optimization, several endpoints were found to be retrieving significantly more data than required.

Avoiding `SELECT *`

Instead of:

SELECT *
FROM transactions;

queries were modified to fetch only the required columns:

SELECT
  id,
  status,
  created_at
FROM transactions;

While simple, this optimization reduced network transfer and improved query efficiency across multiple endpoints.

Introducing Pagination

Some endpoints attempted to return entire datasets at once. As records increased, response sizes grew unnecessarily.

Pagination was introduced across reporting and administrative interfaces to ensure only the required records were fetched.

Instead of returning thousands of rows:

SELECT *
FROM transactions;

queries were limited appropriately:

SELECT *
FROM transactions
LIMIT 50 OFFSET 0;

This significantly reduced response sizes and improved perceived application performance.

Removing Unnecessary Sorting

Several queries performed sorting operations (ORDER BY) that were not actually required by the business logic or layout. Removing unnecessary sorting clauses reduced CPU execution time and lowered database workload, especially on larger datasets.

Optimizing Aggregations

Reporting systems often become slow because aggregation logic is repeatedly recalculated.

By simplifying query logic, moving heavy aggregations to periodic background rollups (or materialized views), and querying via optimized RPC functions, we reduced database complexity and significantly improved dashboard loading speeds.

Adopting UUIDv7 for New Tables

As the system evolved, UUIDv7 was adopted for newly created tables.

Traditional UUIDv4 values are completely random. While they provide excellent uniqueness guarantees, they introduce index fragmentation in B-Tree structures. When a database inserts random values, it forces the B-Tree index to split pages in random locations to fit the keys, lowering write speeds and caching efficiency.

UUIDv7 solves this by introducing millisecond-level time ordering while maintaining globally unique identifiers.

-- Conceptual structure of UUIDv7:
-- [48-bit Timestamp] [4-bit Version] [12-bit Rand/Seq] [2-bit Variant] [62-bit Random]

Benefits include:

Better insertion locality: Since keys are sequential, Postgres inserts new rows at the end of the B-Tree index structure, minimizing page splits.
More predictable index growth: Better utilization of memory-mapped buffer cache.
Improved long-term scalability: Higher write throughput on index pages.
Retained uniqueness guarantees: No risk of collisions across tenants.

To implement this on new tables, we define the column default using a generator:

CREATE TABLE new_tenant_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  tenant_id UUID NOT NULL,
  log_message TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

The migration strategy was intentionally conservative: existing tables remained unchanged while newly created tables adopted UUIDv7 moving forward. Since no formal benchmarking was performed on the UUID transition, no specific performance improvements are claimed. However, UUIDv7 aligns well with modern PostgreSQL best practices for large-scale systems.

Key Lessons Learned

Several lessons became clear throughout the optimization process.

1. Indexes Are Not a Magic Solution

Adding indexes blindly often increases write overhead while providing little benefit. Indexes should be designed around real query patterns.

2. Composite Indexes Usually Matter More Than Individual Indexes

Understanding how queries filter data is more important than increasing the total number of indexes.

3. Database-Side Processing Can Be Extremely Effective

For aggregation-heavy workloads, RPC functions reduced round trips and simplified application logic.

4. Views Improve Maintainability

Complex reporting logic becomes significantly easier to manage when reusable views are introduced.

5. Pagination Should Be Introduced Early

Large datasets eventually become expensive. Pagination prevents future performance problems.

6. Small Optimizations Compound

Removing unnecessary sorting, reducing selected columns, optimizing aggregations, and improving indexes may seem minor individually. Together, they can transform the performance characteristics of a system.

Final Thoughts

Database optimization is rarely about finding a single breakthrough improvement. In this case, the largest gains came from systematically addressing multiple bottlenecks:

Composite indexing
RPC-based processing
Database views
Query optimization
Aggregation improvements
Pagination strategies
UUIDv7 adoption

The result was a more maintainable system, faster reporting workflows, and significantly improved responsiveness under production workloads. Most importantly, these improvements were achieved without redesigning the entire architecture. Instead, they came from understanding how the database was being used and optimizing the paths that mattered most.

Building an Atomic Delivery Dispatcher with PostgreSQL Row-Level Locking

Poojan — Tue, 02 Jun 2026 00:00:00 +0000

Building a logistics and last-mile delivery dispatcher involves more than calculating routes or sending SMS updates. In the real world, you are managing scarce physical resources (riders) operating under strict constraints (weight/capacity limits) in real-time.

When multiple orders enter the system at peak hours, a logistics backend must guarantee that:

Riders are never assigned orders exceeding their physical carrying capacity.
A rider is never double-booked onto multiple concurrent delivery batches.
Orders move through a strict, deterministic state progression.

Below is an engineering walkthrough on how we built a concurrency-safe delivery dispatch engine in TypeScript using PostgreSQL row-level locking.

1. Concurrency Control: Locking with `SELECT ... FOR UPDATE`

When assigning a batch of orders to a rider, the system must check the rider's active load to ensure the additions do not exceed their maximum capacity limit.

In a standard Node.js environment, querying the database to calculate current load, checking the limits in application memory, and then issuing an UPDATE leaves a gap for race conditions. If two different order managers attempt to assign batches to the same rider simultaneously, both operations might read the rider's load as "available" before either write completes, resulting in over-allocation.

To solve this, we use PostgreSQL row-level locking (SELECT ... FOR UPDATE) inside an explicit transaction block.

import { PoolClient } from 'pg';

interface RiderAssignmentResult {
  success: boolean;
  message: string;
}

async function assignRiderToBatch(
  client: PoolClient,
  batchId: string,
  riderId: string
): Promise<RiderAssignmentResult> {
  try {
    // Start transaction boundary
    await client.query('BEGIN');

    // 1. Lock the rider row for modification
    const riderRes = await client.query(
      `SELECT rider_id, availability_status, max_capacity, type 
       FROM delivery_riders 
       WHERE rider_id = $1 
       FOR UPDATE`,
      [riderId]
    );

    if (riderRes.rowCount === 0) {
      throw new Error('RIDER_NOT_FOUND');
    }

    const rider = riderRes.rows[0];

    if (rider.availability_status !== 'AVAILABLE') {
      throw new Error('RIDER_UNAVAILABLE');
    }

    // 2. Fetch order count in the proposed batch
    const batchRes = await client.query(
      `SELECT count(*) as order_count, status FROM delivery_batches WHERE batch_id = $1`,
      [batchId]
    );

    const batch = batchRes.rows[0];
    const incomingOrderCount = parseInt(batch.order_count, 10);

    if (batch.status !== 'CREATED') {
      throw new Error('INVALID_BATCH_STATUS');
    }

    // 3. Calculate rider's current active order load
    const activeLoadRes = await client.query(
      `SELECT COALESCE(SUM(order_count), 0) as current_load 
       FROM delivery_batches 
       WHERE assigned_rider_id = $1 AND status IN ('ASSIGNED', 'PICKED', 'OUT_FOR_DELIVERY')`,
      [riderId]
    );

    const currentLoad = parseInt(activeLoadRes.rows[0].current_load, 10);

    // 4. Validate capacity constraints
    if (currentLoad + incomingOrderCount > rider.max_capacity) {
      throw new Error('CAPACITY_EXCEEDED');
    }

    // 5. Update batch and assign rider
    await client.query(
      `UPDATE delivery_batches 
       SET assigned_rider_id = $1, status = 'ASSIGNED', assigned_at = NOW() 
       WHERE batch_id = $2`,
      [riderId, batchId]
    );

    // 6. Log assignment audit log
    await client.query(
      `INSERT INTO rider_assignment_history (batch_id, new_rider_id, reason) 
       VALUES ($1, $2, $3)`,
      [batchId, riderId, 'System Auto-Dispatch']
    );

    await client.query('COMMIT');
    return { success: true, message: 'Rider assigned successfully' };

  } catch (error: any) {
    await client.query('ROLLBACK');
    return { success: false, message: error.message || 'Transaction failed' };
  }
}

The Locking Mechanics

When the transaction runs SELECT ... FOR UPDATE on the target rider, Postgres places an exclusive write lock on that specific row. Any other concurrent transaction attempting to read or update that same rider row is forced to queue until the first transaction calls COMMIT or ROLLBACK. This guarantees that capacity limits are computed against deterministic, sequential state.

2. Enforcing State Transitions via State Machines

A delivery batch must follow a strict path to prevent "orphaned" orders or invalid logic (like marked delivered before they are picked up).

The lifecycle follows this state map:

    [CREATED]
        │
        ▼ (Rider Assigned)
   [ASSIGNED]
        │
        ▼ (Rider Picked Up Order)
    [PICKED]
        │
        ▼ (Transit Started)
[OUT_FOR_DELIVERY]
   ┌────┴────────────────────────┐
   ▼ (Successful Delivery) ▼ (Delivery Failed)
[COMPLETED] [FAILED]

To enforce this, we build a transition map in TypeScript that acts as our guardrail:

type BatchStatus = 'CREATED' | 'ASSIGNED' | 'PICKED' | 'OUT_FOR_DELIVERY' | 'COMPLETED' | 'FAILED' | 'CANCELLED';

const VALID_TRANSITIONS: Record<BatchStatus, BatchStatus[]> = {
  CREATED: ['ASSIGNED', 'CANCELLED'],
  ASSIGNED: ['PICKED', 'CREATED', 'CANCELLED'], // Can reassign back to CREATED
  PICKED: ['OUT_FOR_DELIVERY', 'FAILED'],
  OUT_FOR_DELIVERY: ['COMPLETED', 'FAILED'],
  COMPLETED: [], // Terminal State
  FAILED: [], // Terminal State
  CANCELLED: [] // Terminal State
};

function validateStateTransition(current: BatchStatus, next: BatchStatus): boolean {
  const allowed = VALID_TRANSITIONS[current];
  return allowed ? allowed.includes(next) : false;
}

By verifying transitions before executing any database changes, the engine blocks invalid states at the API boundaries. For example, if a client tries to patch the status of a CREATED batch to OUT_FOR_DELIVERY, the application throws a 400 Bad Request error immediately.

3. Storing Rich Tracking Data with JSONB Proof of Delivery

When a driver completes a delivery, the app captures rich proof of delivery (PoD): photo URLs (uploaded to S3), coordinate telemetry, customer signatures, notes, and timestamps.

Instead of writing a rigid relational schema with child tables for tracking metadata, we opted for a Postgres JSONB column on our batch order mapping table.

CREATE TABLE delivery_batch_orders (
    batch_order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id UUID REFERENCES delivery_batches(batch_id),
    order_id UUID REFERENCES orders(order_id),
    sequence INTEGER NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',

    -- JSONB document holding unstructured proof of delivery
    proof_of_delivery JSONB NULL,

    completed_at TIMESTAMP WITH TIME ZONE
);

Why JSONB instead of structured relational columns?

Dynamic Metadata: Different delivery types require different proofs. A corporate drop-off might require a text note ("Left with receptionist"), while a residential drop-off requires an S3 photo link and GPS bounds. JSONB handles this schema variance without migrations.
Reduced Join Overhead: When fetching delivery details for the administration portal, the application reads a single row without executing expensive relational joins to retrieve metadata.
Indexing Support: Postgres allows indexing JSONB fields using GIN (Generalized Inverted Index) keys. If we need to search for deliveries verified within specific coordinates, we can index the JSONB path directly:

CREATE INDEX idx_pod_coordinates ON delivery_batch_orders USING GIN ((proof_of_delivery->'coordinates'));

4. Real-Time Telemetry Updates

Delivery is highly interactive. Customers, outlet merchants, and platform dispatchers need to see location changes and status updates instantly.

To achieve this, we link our database transactions directly with a real-time event broker (Socket.IO):

import { Server } from 'socket.io';

interface DeliveryEventPayload {
  batchId: string;
  status: BatchStatus;
  outletId: string;
  riderId?: string;
  updatedAt: string;
}

class DeliveryNotificationService {
  private io: Server;

  constructor(io: Server) {
    this.io = io;
  }

  public broadcastBatchStatus(payload: DeliveryEventPayload): void {
    // 1. Notify the merchant outlet dashboard
    this.io.to(`outlet:${payload.outletId}`).emit('delivery_batch_updated', payload);

    // 2. Notify the specific rider's device
    if (payload.riderId) {
      this.io.to(`rider:${payload.riderId}`).emit('my_batch_updated', payload);
    }

    // 3. Notify the customer room tracking this order
    this.io.to(`batch:${payload.batchId}`).emit('tracking_updated', {
      status: payload.status,
      timestamp: payload.updatedAt
    });
  }
}

This pub/sub routing model ensures that the instant database row updates commit successfully, the web socket layer broadcasts the changes to the respective clients in sub-100ms.

Summary of Dispatcher Core Patterns

Serialize Resource Allocation: Use row locks (FOR UPDATE) inside isolated database transactions to prevent capacity overloading.
Defensive State Logic: Define a clear, immutable transitions map to enforce linear lifecycle paths on orders.
Leverage Document Fields for Audits: Use JSONB for storing flexible metadata profiles, keeping reads fast and schema migrations minimal.
Link DB Events to Websockets: Keep clients synchronized by coupling transactional success hooks directly to your real-time notification brokers.

Designing a Multi-Tenant Subsidy Engine with Concurrency-Safe Budget Tracking

Poojan — Tue, 02 Jun 2026 00:00:00 +0000

Expanding a single-tenant business application to support B2B corporate models brings massive complexity to your data access layer. You are no longer just handling independent user checkouts; you are now enforcing complex corporate compliance rules, isolating merchant visibility, managing shared program budgets, and tracking subsidy ledgers.

As a backend engineer, I recently architected a B2B corporate allowance engine. Below is a deep dive into the core engineering decisions, database design, conflict resolution algorithms, and concurrency patterns required to build a high-integrity multi-tenant corporate wallet.

1. Multi-Tenant Relational Isolation

In a multi-tenant B2B platform, security starts at the database schema. Corporate organizations contract with the platform to allow their employees to buy meals from a selected pool of merchants.

To enforce this, we designed a two-tiered outlet mapping structure:

Platform Level: The platform admin assigns a master pool of merchants to the organization.
Office Level: The corporate admin maps specific merchants from that pool to individual physical offices.

This is enforced at the database level using composite foreign keys:

-- 1. Master Pool assigned to the organization
CREATE TABLE corporate_org_outlets (
    organization_id UUID NOT NULL,
    outlet_id UUID NOT NULL,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (organization_id, outlet_id)
);

-- 2. Office level assignment mapping to specific offices
CREATE TABLE corporate_office_outlets (
    office_outlet_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    office_id UUID NOT NULL,
    organization_id UUID NOT NULL,
    outlet_id UUID NOT NULL,
    is_visible_in_app BOOLEAN DEFAULT TRUE,

    -- Enforce that the office outlet MUST exist in the organization's master pool
    FOREIGN KEY (organization_id, outlet_id) 
        REFERENCES corporate_org_outlets (organization_id, outlet_id)
        ON DELETE CASCADE
);

The Architectural Benefit

By referencing a composite key (organization_id, outlet_id) rather than just a single outlet_id, PostgreSQL prevents a corporate admin from accidentally (or maliciously) displaying an unauthorized merchant to their employees. Data integrity is guaranteed at the database engine level, minimizing reliance on application-layer checks.

2. Resolving Subsidy Conflicts: The "Maximum Rule"

When an employee checks out, multiple subsidy programs might apply to their order. For example:

A Corporate Allowance (e.g., 50% subsidy up to $10, eligible for the entire office).
A Merchant-Specific Subsidy (e.g., a flat $5 discount sponsored for a specific team).

Allowing these programs to stack cumulatively (e.g., applying both) leads to extreme financial leakage where orders can become free or negative-cost.

To solve this, we implemented the Maximum Rule : evaluate all eligible programs, calculate the absolute discount for each independently, and apply only the program that grants the user the highest discount.

Here is a simplified version of the logic executing in the order validation lifecycle:

interface SubsidyProgram {
  programId: string;
  type: 'PERCENTAGE' | 'FIXED' | 'PERCENTAGE_CAPPED';
  value: number;
  maxCap?: number;
}

function calculateDiscount(orderAmount: number, program: SubsidyProgram): number {
  switch (program.type) {
    case 'PERCENTAGE':
      return orderAmount * (program.value / 100);

    case 'PERCENTAGE_CAPPED':
      const percentageDiscount = orderAmount * (program.value / 100);
      return Math.min(percentageDiscount, program.maxCap || 0);

    case 'FIXED':
      return Math.min(program.value, orderAmount);

    default:
      return 0;
  }
}

function getBestEligibleSubsidy(
  orderAmount: number, 
  eligiblePrograms: SubsidyProgram[]
): { programId: string; discountAmount: number } | null {
  if (eligiblePrograms.length === 0) return null;

  return eligiblePrograms.reduce((best, current) => {
    const currentDiscount = calculateDiscount(orderAmount, current);

    if (!best || currentDiscount > best.discountAmount) {
      return { programId: current.programId, discountAmount: currentDiscount };
    }
    return best;
  }, null as { programId: string; discountAmount: number } | null);
}

Why Reduce?

The Array.prototype.reduce pattern executes in linear O(N) time complexity. Because the list of eligible programs for a single employee is typically small (under 10), this keeps calculation overhead virtually non-existent while guaranteeing deterministic, leak-proof checkouts.

3. Concurrency-Safe Budget Exhaustion

In corporate subsidy programs, organizations set strict limits (e.g., a total monthly budget of $5,000). Once the budget is exhausted, the program must immediately disable itself, and employees must pay full price.

In a high-concurrency environment where dozens of users check out at the exact same second, a standard Select-then-Update application-layer loop is highly vulnerable to race conditions:

1. User A Reads: budget_used = 4995. Limit = 5000. (Eligible!)
2. User B Reads: budget_used = 4995. Limit = 5000. (Eligible!)
3. User A Updates: budget_used = 4995 + 10 = 5005. (Overdraft!)
4. User B Updates: budget_used = 5005 + 10 = 5015. (Double Overdraft!)

The Solution: Database-Level Atomic Updates

Instead of validating budget state inside Node.js, we offloaded the validation to an atomic transaction block with conditional update locks in PostgreSQL.

We write a single, atomic SQL statement that increments the budget and checks the boundary condition in one step:

-- Atomic Check-and-Apply Query
UPDATE corporate_subsidies 
SET budget_used = budget_used + $1
WHERE subsidy_id = $2 
  AND is_active = TRUE
  AND (budget_used + $1) <= total_budget
RETURNING subsidy_id, budget_used;

How the Application Handles It

If the update affects exactly 0 rows, the database tells our application that the budget limit has been reached or the program is inactive. Node.js can catch this state and fail the transaction gracefully, prompting the user that the subsidy is no longer available:

async function applySubsidyTransaction(client, subsidyId, discountAmount) {
  const query = `
    UPDATE corporate_subsidies 
    SET budget_used = budget_used + $1
    WHERE subsidy_id = $2 
      AND is_active = TRUE
      AND (budget_used + $1) <= total_budget
    RETURNING subsidy_id, budget_used;
  `;

  const result = await client.query(query, [discountAmount, subsidyId]);

  if (result.rowCount === 0) {
    throw new Error('SUBSIDY_BUDGET_EXHAUSTED');
  }

  return result.rows[0];
}

By leveraging PostgreSQL's row locks on update execution, database transactions are serialized cleanly. We completely eliminate race conditions and budget overruns without introducing the latency or complexity of distributed lock managers like Redis Redlock.

4. Audit Trail Integrity: The Subsidy Ledger

Subsidies are financial transactions. In corporate systems, admins can edit or delete subsidy programs at any time. If you only store references to program IDs, generating historical GST reports, settlement invoices, or user ledgers will yield incorrect values if program parameters have changed.

To solve this, we built a Subsidy Ledger containing full denormalized snapshots:

CREATE TABLE corporate_subsidy_ledger (
    ledger_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(order_id),
    employee_id UUID NOT NULL,
    subsidy_program_id UUID NOT NULL,
    applied_amount NUMERIC(10, 2) NOT NULL,

    -- Snapshot data for historical reporting
    program_snapshot JSONB NOT NULL, 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Whenever an order is processed:

We calculate the discount using the active program state.
We record the calculation details and save a stringified snapshot of the parameters (the percentage, cap, limits, and type) directly inside the JSONB column.
This creates a permanent, immutable ledger entry. Even if an admin updates the program's parameters or deletes the program entirely next month, historical reports will remain 100% accurate and audit-safe.

Summary of Architectural Best Practices

When building financial or B2B entitlement layers:

Push Integrity to the Database: Enforce relational pools via composite foreign keys and composite constraints.
De-stack Early: Implement a deterministic resolution algorithm like the Maximum Rule to avoid cumulative discounts.
Enforce Atomic Bounds: Never validate business thresholds in application code. Let SQL updates handle calculations and assertions simultaneously.
Log Immutable State: Store point-in-time configuration snapshots alongside transactional tables to maintain audit compliance.

How I Cut AWS Costs by 60% by Fixing Infrastructure Inefficiencies

Poojan — Sun, 01 Mar 2026 00:00:00 +0000

When I became the sole backend and infra owner at MealPe, the AWS bill was higher than it needed to be. Not because of over-provisioning - but because of accumulated inefficiencies nobody had cleaned up.

I didn't redesign the entire system. Instead, I analyzed the bill, audited the runtime patterns, and fixed what was actually broken. Here's exactly how we accomplished a 60% reduction in monthly cloud spend while actually improving LCP and request latencies.

The Audit: Where Was the Money Going?

Before making any changes, I analyzed our AWS Billing console line-by-line. The monthly distribution revealed that compute overhead and log retention were dramatically out of proportion with our active user base:

Service Category	Monthly Cost (Before)	Monthly Cost (After)	% Reduction	Primary Culprit
EC2 Compute	$620	$280	54%	Blocked event loop, static serving CPU overhead
S3 Storage & Transfer	$380	$90	76%	Indefinite log storage, massive image uploads
CloudWatch / I/O	$250	$110	56%	Verbose application request/response dumps
Total	$1,250	$480	61%	Overall Savings: $770/month

1. Fixing Excessive Logging Inefficiencies

Our application log files were growing at an unsustainable rate. The Node.js application was generating verbose logs for every inbound request—including full request and response bodies, heavy SQL trace logs, and absolute stack dumps on trivial input errors.

// ❌ Deprecated verbose pattern that flooded CloudWatch and S3
app.use((req, res, next) => {
  logger.info({
    url: req.url,
    headers: req.headers,
    body: req.body, // Large raw JSON buffers
    timestamp: new Date()
  });
  next();
});

These logs were being written to disk and continuously shipped to Amazon S3.

The Fix

I audited our log configuration and implemented Pino for structured JSON logging. I restricted verbose request/response logging only to non-production environments and defined clear log levels for our production cluster:

DEBUG/INFO: Used only for crucial system boot details and structured API transaction summaries (method, path, status, duration).
WARN/ERROR: Reserved for true exception states, database connection alerts, and 500-level codes.

This change alone decreased log volume from ~15GB/day to under 800MB/day , dramatically dropping both write I/O costs and file shipping overhead.

2. Setting Up Automated S3 Lifecycle Policies

S3 storage is inexpensive until files accumulate over months without rules. We were storing every daily server log, PDF bill copy, and redundant image backup indefinitely. There was no cleanup process in place.

The Fix

I configured strict S3 Lifecycle Rules on our logging and static resource buckets:

Daily Server Logs: Transition to S3 Standard-IA (Infrequent Access) after 14 days, move to S3 Glacier Flexible Retrieval after 30 days, and permanently expire after 90 days.
Temporary File Uploads: Automatically expire from the /tmp/ bucket prefix after 7 days.

Implementing these automated lifecycle states trimmed down our total active S3 storage footprint by over 70% in the first billing cycle.

3. Offloading Static Asset Serving from Node.js to NGINX

This was the most impactful architectural refinement. The Node.js backend process was serving static frontend assets directly. Whenever a client loaded our web app, the request for a Javascript chunk, CSS bundle, SVG logo, or favicon went straight through our Express application.

Because Node.js is single-threaded, serving static file assets blocks the event loop from doing actual API computations, leading to high CPU usage and queuing lag.

The NGINX Solution

I configured NGINX as a reverse proxy in front of Node.js. NGINX is built from the ground up to serve static files with extremely low memory and CPU overhead. By handling static requests at the NGINX layer, we bypass the Node.js event loop completely:

                         ┌──────────────────────┐
   Web Client ───────▶ │ NGINX Reverse Proxy │
                         └──────────┬───────────┘
                    /static/* │ /api/*
              (CSS, JS, images) │ (REST endpoints)
                    ┌───────────────┴───────────────┐
                    ▼ ▼
        ┌─────────────────────┐ ┌────────────────────────┐
        │ Local Static Files │ │ Node.js / PM2 Cluster │
        └─────────────────────┘ └────────────┬───────────┘
                                                      ▼
                                          ┌────────────────────────┐
                                          │ PostgreSQL Database │
                                          └────────────────────────┘

Configured NGINX Routing Block

# NGINX config: serve static files directly, proxy only API requests
server {
    listen 443 ssl http2;
    server_name app.mealpe.in;

    # Static assets served directly by NGINX - never blocks Node.js loop
    location /static/ {
        root /var/www/mealpe-frontend;
        expires 30d;
        add_header Cache-Control "public, no-transform, immutable";
        access_log off; # Turn off access logging for static assets to reduce disk I/O
    }

    # API requests proxied to the PM2 Node.js server cluster
    location /api/ {
        proxy_pass http://127.0.0.1:3000;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

Offloading this work to NGINX decreased our average EC2 instance memory consumption by 35% and CPU usage by 40%. This allowed us to safely downsize our EC2 compute tier, reducing our monthly server cost significantly.

The Verdict

Optimizing infrastructure isn't always about moving to serverless or re-architecting your entire system. Usually, the highest return on investment comes from cleaning up operational inefficiencies:

Logging: Keep production logs actionable, structured, and short.
Storage: Enforce S3 lifecycle rules from day one.
Separation of Concerns: Let NGINX serve your files, and let Node.js compute your API logic.

Scaling a Food-Tech SaaS from 1K to 20K Users

Poojan — Thu, 01 Jan 2026 00:00:00 +0000

When I took over the backend at MealPe, the platform had around 1,000 registered users across a handful of client sites. A year later, it's at 20,000+ users processing 50,000 meals per month. This is what that scaling journey actually looked like - not the clean version, but the real one.

The Early Cracks

At 1,000 users, most things "worked." But as we onboarded more institutional clients, cracks started showing.

API response times crept up. Database queries that ran fine with 10,000 rows started timing out at 200,000. Concurrent meal orders during our intense lunch rush (11:30 AM – 1:00 PM) caused request queuing and database locks that slowed the entire site to a crawl.

1. Database-First Optimization

Almost every scaling problem traced back to the database. When I audited the system, I focused on three immediate interventions:

Indexing Strategy Overhaul: I audited every query using EXPLAIN ANALYZE and added composite indexes for the most common access patterns (such as combining venue_id with order_status and delivery_time). This alone dropped average query time from 800ms to under 50ms for the order listing endpoints.
Schema Normalization: Some tables had been designed with flexibility in mind (utilizing JSON columns for dynamically defined fields). I migrated these to proper relational columns with appropriate types and foreign key constraints, which dramatically cut down CPU evaluation costs in query filters.
Connection Pooling: We were creating new database connections per request, creating heavy TCP handshake overhead. Implementing proper connection pooling using pg-pool reduced connection overhead and kept connection saturation safely below thresholds.

2. API Architecture Refinement

The original API had grown organically - lots of endpoints doing too many things. I refactored our core router with a focus on:

Pagination Everywhere: Enforced strict query pagination using limit/offset and cursors so that list views never try to render thousands of rows in a single DOM draw.
Response Shaping: Different clients needed different data structures. Instead of sending full database models, I implemented lightweight view DTOs to shrink our network payload size.
Caching Hot Data: Venue menus change once a week, not once a second. Adding a cache layer in front of dynamic menus eliminated thousands of database reads per hour.

3. The Lunch Rush Problem

Institutional cafeterias have extremely predictable and highly congested usage patterns. Unlike standard food delivery, 80% of all meal orders come in during a tightly bounded 90-minute lunch window. This meant our servers needed to handle peak loads that were 15x the daily average.

To survive these spikes without scaling our cloud costs infinitely:

Decoupling Non-Critical Work: I offloaded tasks like push notifications, admin email alerts, and analytic log compilations into a background queue processed after peak hours.
Pre-Computing Availability: Instead of scanning ordering records to determine inventory limits on every checkout, we pre-computed meal item quotas every hour, converting an O(N) scan into an O(1) cache read.

Monitoring That Actually Matters

I set up monitoring dashboards that tracked metrics predictive of failures rather than just CPU alerts:

API p95 Latency: Warns us when p95 responses exceed 200ms.
Connection Pool Saturation: Alerts us when available database pools drop below 15%.
Job Queue Depth: Helps us spot background processing lag.

What I'd Do Differently Next Time

Plan the Indexing Early: Invest in database indexes from day one rather than retrofitting them under pressure.
Bake in Caching First: Integrate cache and pagination middlewares into the router framework rather than adding them as custom interventions.
Realistic Load Testing: Build synthetic load tests modeling the concentrated lunch rush early in staging.

Node.js Performance Optimization: From Slow APIs to Scalable Systems

Poojan — Mon, 01 Dec 2025 00:00:00 +0000

Node.js is incredibly fast out of the box because of its non-blocking I/O model. However, as your transaction volume grows, minor engineering shortcuts turn into devastating performance bottlenecks.

At MealPe, as the sole backend engineer, I faced the challenge of optimizing our backend to scale from a few dozen concurrent transactions to thousands. Here is a practical, evidence-based guide to identifying Node.js performance bottlenecks and resolving them using production-proven patterns.

Performance Benchmarks: The Impact of Optimization

To demonstrate the concrete impact of these optimizations, I ran load tests using Autocannon on a single t3.medium EC2 instance. The test simulated 100 concurrent users making continuous request rounds to a dynamic database endpoint over 10 seconds:

Stage of Optimization	Avg Latency (p95)	Throughput (Req/Sec)	CPU Utilization	Error Rate
Baseline (Single thread, no cache)	420 ms	240 req/sec	95% (bottleneck)	4.2%
Step 1: PM2 Clustered (4 Workers)	110 ms	880 req/sec	42% (distributed)	0.0%
Step 2: Redis Caching Layer Active	18 ms	4,200 req/sec	15% (idle compute)	0.0%

1. Keep the Event Loop Clear (Non-Blocking IO)

Node.js executes JavaScript code in a single-threaded loop. If you block that thread with CPU-intensive tasks—such as image resizing, parsing massive JSON files, or executing synchronous cryptos—every other user request is forced to wait in a queue.

// ❌ Deprecated synchronous blocking pattern
app.post('/api/report', (req, res) => {
  const data = fs.readFileSync('./large-report.csv', 'utf8'); // Blocks event loop!
  const processed = heavySyncParsing(data); // Blocks event loop!
  res.json(processed);
});

The Fix

Always use asynchronous, non-blocking file and network operations. If you absolutely must perform CPU-bound tasks, offload them to a separate thread using Worker Threads or queue them in a background job system like BullMQ :

// ✅ Production-ready non-blocking worker pattern
const { Worker } = require('worker_threads');

app.post('/api/report', (req, res) => {
  const worker = new Worker('./report-worker.js', {
    workerData: { filePath: './large-report.csv' }
  });

  worker.on('message', (processedData) => {
    res.json(processedData);
  });

  worker.on('error', (err) => {
    res.status(500).json({ error: "Failed to compile report" });
  });
});

2. Leverage Multi-Core Scalability with Clustering

A standard Node.js process runs on a single CPU core. In production environments where your cloud instances have multiple cores, a single thread leaves substantial compute capacity completely idle.

The PM2 Cluster Approach

Instead of manually managing this with the native cluster module, use PM2 cluster mode in production. PM2 handles process lifecycle management and automatic load-balancing between child processes out of the box:

// ecosystem.config.json
{
  "apps" : [{
    "name" : "mealpe-api",
    "script" : "./dist/server.js",
    "instances" : "max", // Automatically scales to all available logical cores
    "exec_mode" : "cluster",
    "env_production": {
      "NODE_ENV": "production"
    }
  }]
}

By deploying in cluster mode, we instantly multiplied our request capacity by 4x on our quad-core database proxy server without changing a single line of API routing logic.

3. Implement Redis Caching for Read-Heavy Routes

The fastest API request is the one that never touches your primary relational database. Database I/O is almost always the p95 latency bottleneck.

At MealPe, venue menus and outlet statuses change infrequently but are queried on every single page load. Caching these responses in Redis (an ultra-fast, in-memory store) reduces response times down to sub-10 milliseconds.

// ✅ Polished API response caching helper
const redis = require('redis');
const client = redis.createClient({ url: process.env.REDIS_URL });

async function getCachedMenu(req, res, next) {
  const { venueId } = req.params;

  try {
    const cachedData = await client.get(`menu:${venueId}`);
    if (cachedData) {
      res.setHeader('X-Cache', 'HIT');
      return res.json(JSON.parse(cachedData));
    }

    // Cache miss - pass to database controller
    res.setHeader('X-Cache', 'MISS');
    next();
  } catch (err) {
    // Fallback gracefully on cache error - don't crash client
    next();
  }
}

4. Query Optimization & Indexing

If your database requests are slow, Node.js will be slow. I audited our queries using EXPLAIN ANALYZE in PostgreSQL and addressed the following:

Missing Indexes: We added indexes on foreign keys and columns used in filtering/sorting (e.g., venue_id, created_at).
N+1 Query Elimination: Replaced recursive database calls inside loops with clean SQL JOIN statements or Sequelize eager loading (include).
Strict Pagination: Never load whole tables into memory. Enforce strict LIMIT and OFFSET (or cursor-based keys) on all listing tables.

5. Enable Compression

Ensure Gzip or Brotli compression is active on all JSON and text payloads. This reduces payload transfer sizes by up to 70% , speeding up rendering times on mobile devices operating on congested cellular networks:

const compression = require('compression');
const express = require('express');
const app = express();

// Compresses all outgoing response payloads automatically
app.use(compression());

Summary of Action Items

Profile Continuously: Use clinic.js or Chrome DevTools to locate memory leaks and CPU blocks under test loads.
Cluster by Default: Scale to multiple cores using PM2 cluster mode.
Cache Strategically: Cache read-heavy API responses using Redis with realistic TTLs.
Tune the Database: Set index paths on Postgres and prevent large scans.

Zero-Downtime Deployments with PM2 and NGINX

Poojan — Sat, 01 Nov 2025 00:00:00 +0000

When your platform processes meal orders across busy institutional cafeterias, downtime isn't just inconvenient - people don't get fed. High availability is a hard operational requirement, not just a product target.

The original deployment process was: SSH into the server, git pull, npm install, and pm2 restart all. This caused a 10–30 second outage per deploy , during which our API dropped orders and hung terminals.

Here is exactly how I set up graceful, zero-downtime deployments for our Node.js server cluster using PM2's native clustering and NGINX upstream proxies.

1. PM2 Cluster Configuration

The foundation of zero-downtime is simple: never let the number of active app instances drop to zero. PM2's cluster mode makes it straightforward to scale your process across all cores:

// ecosystem.config.js
module.exports = {
  apps: [{
    name: 'mealpe-api',
    script: './dist/server.js',
    instances: 'max', // Scale to all available CPU cores
    exec_mode: 'cluster',
    max_memory_restart: '500M',
    listen_timeout: 10000, // Wait 10s for boot signal
    kill_timeout: 5000 // Wait 5s for clean close
  }]
};

instances: 'max' : Spawns a process on each CPU core.
listen_timeout : Instructs PM2 to wait for a database connection or socket handshake before marking a new process as "Online."
kill_timeout : Gives active processes 5 seconds to wrap up in-flight REST queries before forcing a close.

2. Transitioning to Graceful Reloads

Instead of calling pm2 restart (which kills all instances simultaneously), we transition to pm2 reload.

Reload initiates a rolling update : it spawns a new instance, waits for it to become online, then safely turns down an old instance. It repeats this pattern process-by-process, maintaining maximum API capacity:

# Production deploy script (deploy.sh)
#!/bin/bash
set -e

echo "Pulling latest branch code..."
git pull origin main

echo "Installing production-only dependencies..."
npm ci --production

echo "Executing rolling reload..."
pm2 reload ecosystem.config.js --update-env

echo "Deploy successfully completed!"

3. Implementing Application Graceful Shutdowns

PM2 sends a SIGINT trigger to your process before shutting it down. If your application doesn't handle this signal, it terminates instantly, dropping all connections mid-transaction.

You must catch the SIGINT event, close the HTTP port to block new inbound traffic, finish active queries, and release database pools:

// ✅ Professional graceful shutdown hook in server.js
process.on('SIGINT', () => {
  console.log('SIGINT signal received. Starting graceful shutdown sequence...');

  // Stop the HTTP server from accepting new socket sessions
  server.close(async () => {
    console.log('HTTP server successfully closed.');

    try {
      // Release database connection pools cleanly
      await db.end();
      console.log('Database pools released. Exiting cleanly.');
      process.exit(0);
    } catch (err) {
      console.error('Error during database teardown:', err);
      process.exit(1);
    }
  });

  // Force close after a 6-second timeout block if connections hang
  setTimeout(() => {
    console.warn('Forced shutdown active: connections did not close in time.');
    process.exit(1);
  }, 6000);
});

4. Configuring NGINX Load-Balancing

NGINX is our front-door gateway. The key configurations for zero-downtime routing include setting up an upstream pool and instructing NGINX to pass traffic to active processes on failures:

upstream mealpe_backend {
    server 127.0.0.1:3000;
    keepalive 64; # Keep connection channels open to reduce latency
}

server {
    listen 443 ssl http2;
    server_name api.mealpe.in;

    location / {
        proxy_pass http://mealpe_backend;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

        # 🚀 Crucial: if one instance is reloading, try next active worker
        proxy_next_upstream error timeout http_502 http_503;
        proxy_connect_timeout 3s;
        proxy_read_timeout 15s;
    }
}

By adding proxy_next_upstream, if an instance is in the middle of reloading and drops a connection, NGINX instantly retries the query on a sibling process. The client has zero awareness of the deploy event.

5. Webhook CI/CD Automation

To secure execution, we set up a lightweight deployment daemon on our EC2 instance that exposes a secured webhook route. When a PR merges into main on GitHub, our actions pipeline runs tests, builds the typescript bundle, and pings the webhook:

# GitHub Actions CI deployment step
- name: Trigger Server Deploy Webhook
  run: |
    curl -X POST \
      -H "Authorization: Bearer ${{ secrets.DEPLOY_SECRET }}" \
      https://api.mealpe.in/webhooks/deploy

The daemon checks the bearer token (a shared secret) in constant time before running anything. This triggers the automated deploy.sh script locally on the server.

The Outcome

0 seconds of user-facing downtime recorded.
45-second deployment pipeline from git merge to production availability.
Confidence to deploy minor changes and hotfixes safely during standard hours.

DEV Community: Poojan

Scaling PostgreSQL in Production: Indexing, RPC Functions, Views, and Query Optimization

Understanding the Bottlenecks

Composite Indexes Instead of More Indexes

Moving Business Logic into Supabase RPC Functions

Simplifying Data Access with Database Views

Eliminating Unnecessary Data Retrieval

Avoiding SELECT *

Introducing Pagination

Removing Unnecessary Sorting

Optimizing Aggregations

Adopting UUIDv7 for New Tables

Key Lessons Learned

1. Indexes Are Not a Magic Solution

2. Composite Indexes Usually Matter More Than Individual Indexes

3. Database-Side Processing Can Be Extremely Effective

4. Views Improve Maintainability

5. Pagination Should Be Introduced Early

6. Small Optimizations Compound

Final Thoughts

Building an Atomic Delivery Dispatcher with PostgreSQL Row-Level Locking

1. Concurrency Control: Locking with SELECT ... FOR UPDATE

The Locking Mechanics

2. Enforcing State Transitions via State Machines

3. Storing Rich Tracking Data with JSONB Proof of Delivery

Why JSONB instead of structured relational columns?

4. Real-Time Telemetry Updates

Summary of Dispatcher Core Patterns

Designing a Multi-Tenant Subsidy Engine with Concurrency-Safe Budget Tracking

1. Multi-Tenant Relational Isolation

The Architectural Benefit

2. Resolving Subsidy Conflicts: The "Maximum Rule"

Why Reduce?

3. Concurrency-Safe Budget Exhaustion

The Solution: Database-Level Atomic Updates

How the Application Handles It

4. Audit Trail Integrity: The Subsidy Ledger

Summary of Architectural Best Practices

How I Cut AWS Costs by 60% by Fixing Infrastructure Inefficiencies

The Audit: Where Was the Money Going?

1. Fixing Excessive Logging Inefficiencies

The Fix

2. Setting Up Automated S3 Lifecycle Policies

The Fix

3. Offloading Static Asset Serving from Node.js to NGINX

The NGINX Solution

Configured NGINX Routing Block

The Verdict

Scaling a Food-Tech SaaS from 1K to 20K Users

The Early Cracks

1. Database-First Optimization

2. API Architecture Refinement

3. The Lunch Rush Problem

Monitoring That Actually Matters

What I'd Do Differently Next Time

Node.js Performance Optimization: From Slow APIs to Scalable Systems

Performance Benchmarks: The Impact of Optimization

1. Keep the Event Loop Clear (Non-Blocking IO)

The Fix

2. Leverage Multi-Core Scalability with Clustering

The PM2 Cluster Approach

3. Implement Redis Caching for Read-Heavy Routes

4. Query Optimization & Indexing

5. Enable Compression

Summary of Action Items

Zero-Downtime Deployments with PM2 and NGINX

1. PM2 Cluster Configuration

2. Transitioning to Graceful Reloads

3. Implementing Application Graceful Shutdowns

4. Configuring NGINX Load-Balancing

5. Webhook CI/CD Automation

The Outcome

Avoiding `SELECT *`

1. Concurrency Control: Locking with `SELECT ... FOR UPDATE`