Blue-Green Schemas in PostgreSQL

Concept

Two schemas (blue, green) coexist in one database. Traffic routes to one via search_path. The inactive schema is rebuilt while the active one serves queries. At time T, the switch is made.

Key constraint: app code must never schema-qualify table names — all routing is via search_path.

Setup

CREATE SCHEMA blue;
CREATE SCHEMA green;
GRANT USAGE ON SCHEMA blue, green TO app_role;

Switching

Ad-hoc

ALTER DATABASE mydb SET search_path TO green, public;

Scheduled (pg_cron)

SELECT cron.schedule('flip-schema', '0 0 1 4 *',
  $ALTER DATABASE mydb SET search_path TO green, public$);

Application-controlled (recommended)

App evaluates a switch time on each connection checkout:

func schemaForNow(switchAt time.Time) string {
    if time.Now().After(switchAt) {
        return "green"
    }
    return "blue"
}

// On connection checkout:
conn.Exec("SET search_path TO " + schemaForNow(switchAt) + ", public")

Switch time can be read from config/DB so it’s adjustable without redeployment.

Production Pattern

Combine both layers:

pg_cron flips the database-level default at T
App re-evaluates search_path on each connection checkout
Pool drain at T flushes stale connections

This gives automatic cutover, no stale connections, and instant rollback.

Rollback

ALTER DATABASE mydb SET search_path TO blue, public;

Sub-second. Old schema is untouched until explicitly dropped.

Retire & Rebuild Cycle

[active: blue] → build green → flip to green → drain pool
                                               → blue now idle
                                               → rebuild blue for next cycle

Drop when no longer needed as rollback target:

DROP SCHEMA blue CASCADE;

Caveats

Concern	Notes
Sequences	Schema-local sequences reset on rebuild; use shared sequences in `public` if continuity matters
Foreign keys	Cross-schema FKs work but complicate `DROP SCHEMA`; avoid
Connection pooling	PgBouncer caches `search_path`; must flush pool or use startup query
In-flight transactions	Transactions open at T straddle the switch; design for idempotency around cutover
Schema-qualified queries	Any hardcoded `blue.tablename` in app or migrations will break the abstraction

Best Suited For

ETL / bulk data refresh (load into inactive schema, flip)
Read-heavy datasets with periodic full rebuilds
Time-triggered data releases

Less suited for high-write OLTP where coordinating in-flight writes across the cutover adds complexity.