Skip to content
Hiren Limbasiya
Email me
Writing

Backend

Using PostgreSQL and MongoDB in the Same Project

Not SQL versus NoSQL. Both, deliberately. Where Postgres earns relational data and transactions, where Mongo's aggregation pipelines win for analytics, and how to split the two.

SQL versus NoSQL: the real decision

PostgreSQL excels for relational data, transactions, and complex queries. MongoDB shines for document-oriented data and flexible schemas. I use both in the same project regularly.

Schema Design in PostgreSQL

Normalize to 3NF, then selectively denormalize for read performance. Leverage PostgreSQL-specific features for flexibility.

sql
-- Ticket management schema with proper constraints
CREATE TYPE ticket_urgency AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL');
CREATE TYPE ticket_status AS ENUM ('OPEN', 'IN_PROGRESS', 'REVIEW', 'APPROVED', 'CLOSED');

CREATE TABLE tickets (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id      UUID NOT NULL REFERENCES organizations(id),
    title       TEXT NOT NULL,
    description TEXT,
    urgency     ticket_urgency DEFAULT 'MEDIUM',
    status      ticket_status DEFAULT 'OPEN',
    assigned_to UUID REFERENCES users(id),
    machine_id  UUID REFERENCES machines(id),
    metadata    JSONB DEFAULT '{}',  -- flexible extra data
    created_by  UUID NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW(),
    closed_at   TIMESTAMPTZ
);

-- Partial index: only index active tickets (most queries filter by this)
CREATE INDEX idx_tickets_active ON tickets (org_id, status, urgency)
    WHERE status NOT IN ('CLOSED');

-- GIN index for JSONB queries on metadata
CREATE INDEX idx_tickets_metadata ON tickets USING GIN (metadata);

MongoDB Aggregation Pipelines

For analytics dashboards, aggregation pipelines are powerful. $match early to reduce data, $group for aggregations, $facet for parallel computations.

javascript
// Clinical trial analytics: trials by year with demographics
db.trials.aggregate([
  // Stage 1: Filter to relevant trials
  { $match: { status: "Completed" } },

  // Stage 2: Parallel computations with $facet
  { $facet: {
    byYear: [
      { $group: {
        _id: { $year: "$start_date" },
        count: { $sum: 1 },
        avgEnrollment: { $avg: "$enrollment" }
      }},
      { $sort: { _id: 1 } }
    ],
    byCity: [
      { $unwind: "$facilities" },
      { $group: {
        _id: "$facilities.city",
        trialCount: { $sum: 1 }
      }},
      { $sort: { trialCount: -1 } },
      { $limit: 20 }
    ],
    summary: [
      { $group: {
        _id: null,
        total: { $sum: 1 },
        totalEnrollment: { $sum: "$enrollment" },
        conditions: { $addToSet: "$condition" }
      }}
    ]
  }}
]);

Written by Hiren Limbasiya, tech lead and full-stack engineer. Questions or corrections are welcome.

Email meNext note