postgresql-data-layer

To ensure zero-downtime PostgreSQL migrations, apply forward-only, idempotent, and rolling-deploy-safe patterns that maintain backward compatibility. This approach prevents locking and downtime during Node.js schema changes.

Connection pool sizing for PostgreSQL requires balancing available database connections with Node.js concurrency. Apply pool sizing guidance, statement timeouts, and monitoring hooks to avoid exhausting database connections under load.

Use JSONB columns for flexible, ephemeral data and normalized relational tables for structured, query-heavy data. Apply specific guidance on appropriate JSONB uses versus relational columns to balance query performance and schema consistency.

Handle ACID transactions and parameterized queries in Node.js by implementing the repository pattern with transaction handling examples. This prevents SQL injection and ensures atomic updates to the PostgreSQL database.

Data requiring durability belongs in PostgreSQL, while ephemeral state belongs in Redis. Apply a dual-store decision framework to determine what data goes where, optimizing both persistence and performance in Node.js backends.

Add partial or concurrent indexes in PostgreSQL when optimizing query performance for specific subsets of data without blocking writes. Apply production-ready indexing strategies to reduce query bottlenecks and improve Node.js backend efficiency.