Learn the Postgres Staging Database Pattern Using JSONB and Node.js

Introduction

This article demonstrates how to use PostgreSQL as a staging database in front of a NoSQL store, using JSONB columns and the pg package in Node.js.

We run batch jobs that build a new dataset on a schedule, and a public site that reads it. The simple way is a straight line — the job writes to the store the site reads. We do not do that, and this article explains why.

The Problem

A batch job that writes directly to your live store has three problems, and you will meet all three.

Half of the data is visible. The job writes 400 rows out of 900 and then fails. Your visitors now see an incomplete dataset and nothing tells them. A document store gives you no transaction over 900 documents to undo it.

Nobody can review it. Some output is wrong in a way only a person will notice — a bad input file, an API returning null, a rule that misfires. If the only copy is already live, then you are reviewing in production.

Yesterday is gone. When somebody asks why yesterday's numbers looked strange, you cannot answer, because today's run replaced them.

The Solution

Put a staging database in the middle.

batch job  →  Postgres (staging)  →  review  →  Firestore (live)

The job writes to Postgres only. Nothing it writes is visible to any visitor. A separate promote step reads the reviewed rows and pushes them to the live store. That promote step is the only code allowed to touch live data.

The comment at the top of our Postgres file says it simply:

Local staging database. Everything the batch pipeline stages before review lives here — it is never reachable from the public site. Only promote.ts moves reviewed output onward.

Features

• A failed run leaves live data untouched.
• Review is a SQL query, not a look at production.
• Every run is kept by date, so you can compare.
• Only one component writes to what the users read.

With the following steps, you can build it.

1. Design the staging table with JSONB.
2. Create the connection pool.
3. Stage the rows.
4. Promote after review.

Design the Staging Table with JSONB

People ask a fair question here. If the output is documents, why use a relational database?

Because staging and serving want opposite things.

Serving wants one ready-made document per page. That is what a document store is good at.

Staging wants questions. How many rows today compared to yesterday? Which ones changed? How many warnings, and how many different reasons? Those are GROUP BY and COUNT and joins. SQL answers each one in a single line. A document store needs a script.

Review is aggregate querying. So stage where you can query.

The table does not fight the document shape. It stores it as it is.

CREATE TABLE IF NOT EXISTS batch_staged_rows (
  id      BIGSERIAL PRIMARY KEY,
  date    TEXT NOT NULL,
  scanner TEXT NOT NULL,
  idx     INTEGER NOT NULL,
  data    JSONB NOT NULL
);
CREATE INDEX IF NOT EXISTS batch_staged_rows_lookup
  ON batch_staged_rows (date, scanner, idx);

Anything you need to find, count or group by is a real column. Everything else is one JSONB payload.

This is Postgres at its best. The index makes "give me run X, section Y, in order" fast, and the payload can grow new fields with no migration.

JSONB is also queryable by itself with data->>'name', and you can add a GIN index if you need it. So the escape hatch is there.

The other way — one column for every output field — means an ALTER TABLE every time the job changes, on a table that holds temporary data only. That is the wrong trade.

Warnings get their own table with the reason as a real column, because "what went wrong today" is a query you run on every single run.

Create the Connection Pool

let _pool: Pool | null = null;

export function getPool(): Pool {
  if (!_pool) {
    _pool = new Pool({
      connectionString: process.env.DATABASE_URL,
      host: process.env.PGHOST ?? 'localhost',
      port: process.env.PGPORT ? Number(process.env.PGPORT) : 5432,
      user: process.env.PGUSER ?? 'postgres',
      password: process.env.PGPASSWORD,
      database: process.env.PGDATABASE ?? 'staging_db',
    });
  }
  return _pool;
}

Use Pool, not Client. A pool reuses connections. A Postgres connection is heavy — it is one server process each. A batch job doing thousands of inserts with a new Client every time will hit max_connections and take the database down with it.

connectionString comes first and the separate PGHOST and PGUSER fields are the fallback. So one variable works where the host gives you one, and separate values work everywhere else.

Note the IF NOT EXISTS in the schema. It runs once behind a cached promise, so a new machine creates its own tables on first connect.

The Local-Only Rule

In our setup this database is local by design. The batch has to run on the same machine as Postgres, which means running the Function App locally with func start, not the deployed Azure app.

That is a real limitation and I prefer to say it clearly. But it is also the strongest possible version of "staging is not reachable from production" — a cloud function simply cannot reach it. No firewall rule needed.

When the batch has to move to the cloud, use a managed Postgres and keep the live store fed by the promote step only. The design does not change. Only the box changes.

One Warning

When one of our projects retired its batch backend, the batch_staged_* tables stayed behind. Nothing writes to them and nobody dropped them.

A staging layer is infrastructure. Put the removal step in your plan next to the creation step.

Conclusion

In this article we learned how to stage batch output in PostgreSQL with JSONB columns, why a relational staging layer suits a document serving store, and how a promote step keeps half-finished runs away from your users.

You do not need this if your job is small and its output is obviously right or obviously broken. You need it when the output needs judgement before it goes live.

Reference

• PostgreSQL JSON types
• node-postgres connection pooling