ScaleGuidev2
NewsSandbox
ScaleGuide — Kubernetes Autoscaling, Explained Visually.
DocsVisualize

Autoscaling

Horizontal Pod AutoscalerVertical Pod AutoscalerCluster AutoscalerKEDA

Deployment Strategies

Blue-Green DeploymentCanary DeploymentRolling UpdateRecreate DeploymentA/B Testing DeploymentShadow (Dark) Deployment

PostgreSQL

Prerequisites & SetupWhy PostgreSQL?Backend ConnectionsPractice ExamplesOfficial Docs Summary

Code Sandbox

SQL QueriesK8s ManifestsDeploy Configs

Backend Connections

How PostgreSQL connects to Node.js, Python, Java, and C++ -- from raw drivers to ORMs and connection pooling.

Understanding how your application connects to PostgreSQL is critical for performance and reliability. This section covers connection patterns for Node.js, Python, Java, and C++ -- from raw drivers to ORMs and connection pooling.

Connection Lifecycle

  1. DNS Resolution -- Resolve database hostname to IP address
  2. TCP Connect -- Establish TCP connection to port 5432 (default)
  3. SSL/TLS Handshake -- Negotiate encrypted connection (if configured)
  4. Authentication -- Send credentials, server validates against pg_hba.conf
  5. Query -- Send SQL, server parses, plans, and runs
  6. Response -- Server returns result set or error
InfoCreating a new connection takes 20-50ms (local) or 100-300ms (remote with SSL). This is why connection pooling is essential for production applications handling many requests.

Node.js

pg (raw driver)

javascript
const { Client } = require('pg');
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'secret',
});

await client.connect();
const res = await client.query('SELECT NOW()');
console.log(res.rows[0]);
await client.end();

pg Pool (recommended for production)

javascript
const { Pool } = require('pg');
const pool = new Pool({
  host: 'localhost',
  max: 20,                    // max connections in pool
  idleTimeoutMillis: 30000,   // close idle after 30s
  connectionTimeoutMillis: 2000,
});

// Pool automatically manages connections
const res = await pool.query('SELECT * FROM users WHERE id = $1', [1]);

Python

psycopg2

python
import psycopg2

conn = psycopg2.connect(
    host="localhost",
    dbname="mydb",
    user="postgres",
    password="secret"
)
cur = conn.cursor()
cur.execute("SELECT NOW()")
print(cur.fetchone())
cur.close()
conn.close()

SQLAlchemy (ORM)

python
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine(
    "postgresql://postgres:secret@localhost/mydb",
    pool_size=5,
    max_overflow=10,
)

with Session(engine) as session:
    users = session.query(User).filter(
        User.active == True
    ).all()

Java

JDBC + HikariCP

java
import com.zaxxer.hikari.*;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("postgres");
config.setPassword("secret");
config.setMaximumPoolSize(10);

HikariDataSource ds = new HikariDataSource(config);
try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(
        "SELECT NOW()")) {
    ResultSet rs = ps.executeQuery();
    rs.next();
    System.out.println(rs.getTimestamp(1));
}

C++

libpqxx

cpp
#include <pqxx/pqxx>
#include <iostream>

int main() {
    pqxx::connection conn(
        "host=localhost port=5432 "
        "dbname=mydb user=postgres password=secret"
    );
    pqxx::work txn(conn);
    auto result = txn.exec("SELECT id, name FROM users");
    for (auto row : result) {
        std::cout << row["id"].as<int>()
                  << ": " << row["name"].c_str()
                  << std::endl;
    }
    txn.commit();
}

Connection Pooling Best Practices

PitfallSymptomFix
No connection poolingConnection exhaustion under load, slow response timesUse a pool (pg Pool, HikariCP, or PgBouncer)
Pool too largePostgreSQL overwhelmed, high memory usage, lock contentionStart with (2 * CPU cores) + disk spindles, benchmark from there
Pool too smallRequests queue up waiting for connectionsMonitor pool wait time, increase max if consistently saturated
Not releasing connectionsPool exhaustion, application hangsAlways use try-finally or context managers to release connections
TipTry the Connection Simulator in the Playground to see the full connection lifecycle animated step by step, including pooling behavior under different configurations.

Real-World Stories

Failure

Notion - Connection Pool Exhaustion During Growth Spike

In early 2021, Notion experienced repeated outages as their user base grew rapidly. The root cause was connection pool exhaustion: each microservice opened its own pool of 20 connections, and with 50+ services, they were hitting PostgreSQL's max_connections limit of 500. Long-running queries held connections while others queued. The fix involved deploying PgBouncer as a centralized connection pooler, reducing each service to 5 pool connections while PgBouncer managed multiplexing. Response times dropped from 2s to 50ms.

Success

Shopify - Zero-Downtime Connection Migration

When Shopify migrated their main PostgreSQL database to a new cluster in 2020, they needed to move 100+ application services without any downtime during Black Friday Cyber Monday (BFCM) preparation. They used PgBouncer's PAUSE/RESUME feature: pause all connections, switch the backend to the new PostgreSQL host, resume. The entire cutover took 3 seconds, with no dropped transactions. Every service reconnected transparently through PgBouncer without code changes.

Failure

Robinhood - The ORM N+1 Query Disaster

During a volatile trading day, Robinhood's Django-based backend slowed to a crawl. Investigation revealed that their ORM was generating N+1 queries: for each portfolio (N=millions), a separate query fetched holdings. A single page load triggered 400+ SQL queries instead of 2. The fix was adding select_related() and prefetch_related() to their Django querysets, reducing database round trips by 99%.