POSTGRESQLMONGODB

DATABASE DESIGN & OPTIMIZATION

Schema design, indexing strategies, query optimization, and choosing the right database.

SQL vs 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" }
      }}
    ]
  }}
]);

← PREVIOUS

Go for Web Services

Authentication & Authorization

← ALL TOPICS