35+ PostgreSQL & Node.js Interview Questions 2025: Connection Pooling, Transactions & Optimization

PostgreSQL powers some of the world's most demanding applications—Instagram, Spotify, Reddit. When interviewers ask about PostgreSQL in a Node.js context, they're testing whether you understand database fundamentals beyond basic CRUD. Connection pooling, transaction isolation, query optimization—these separate developers who've built production systems from those who've only followed tutorials.

This guide covers the essential PostgreSQL questions you'll face in Node.js backend interviews, from basic connections to advanced optimization techniques.

Table of Contents

1. Connection and Pooling Questions
2. Parameterized Queries and Security Questions
3. Transaction Questions
4. Migration Questions
5. Query Optimization Questions
6. PostgreSQL Features Questions
7. Error Handling Questions

Connection and Pooling Questions

Connection management is fundamental to building performant Node.js applications with PostgreSQL.

How do you connect to PostgreSQL from Node.js?

The standard approach uses the pg (node-postgres) library with a connection pool. The Pool manages database connections efficiently—creating them as needed, reusing them across requests, and handling failures automatically.

You create a pool once at application startup rather than connecting per-request. Each new connection has approximately 30ms overhead from TCP handshake and authentication. The pool maintains idle connections ready to use, dramatically reducing latency for subsequent queries.

// db.js - Database connection setup
const { Pool } = require('pg');
 
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,                // Maximum connections in pool
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if can't connect in 2s
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: false }
    : false
});
 
// Verify connection on startup
pool.on('error', (err) => {
  console.error('Unexpected pool error', err);
  process.exit(-1);
});
 
module.exports = pool;

What is connection pooling and why is it important?

Connection pooling maintains a cache of database connections that can be reused across multiple requests. Without pooling, each database operation would require establishing a new TCP connection, performing authentication, and negotiating session parameters—taking 20-50ms each time.

With pooling, you pay this cost once when the connection is created, then reuse it for thousands of queries. The pool automatically manages the connection lifecycle: creating new connections when needed, returning them to the pool after use, and cleaning up stale connections.

// What happens under the hood
const pool = new Pool({ max: 10 });
 
// Request 1: No idle connections, creates new one (~30ms)
await pool.query('SELECT 1');
 
// Request 2: Reuses connection from Request 1 (~0.1ms)
await pool.query('SELECT 2');
 
// 11 concurrent requests: 10 run immediately, 1 waits in queue
const promises = Array(11).fill().map(() =>
  pool.query('SELECT pg_sleep(1)')  // 1 second each
);
await Promise.all(promises);  // Takes ~2 seconds total
 
// Check pool status
console.log({
  total: pool.totalCount,      // Total connections created
  idle: pool.idleCount,        // Available connections
  waiting: pool.waitingCount   // Queued requests
});

What happens when all pool connections are busy?

When all connections in the pool are in use, new queries wait in a queue until a connection becomes available. If connectionTimeoutMillis is set, queued requests will fail after that timeout period. This prevents indefinite waiting but can cause errors under heavy load.

The solution depends on your situation. You can increase the max connections setting up to PostgreSQL's limit (typically 100 by default). For very high throughput applications, you might use a connection pooler like PgBouncer that sits between your application and PostgreSQL, supporting thousands of client connections with fewer actual database connections.

When should you use Pool versus Client?

The Pool should be your default choice for most database operations because it handles connection management automatically. When you call pool.query(), it acquires a connection, executes the query, and releases the connection back to the pool.

You need a dedicated Client only when you require the same connection across multiple operations—specifically for transactions. Transactions must execute all their queries on the same connection to maintain isolation and atomicity.

// Pool - for most operations (automatic connection management)
await pool.query('SELECT * FROM users');
 
// Client - for transactions (need same connection throughout)
const client = await pool.connect();
try {
  await client.query('BEGIN');
  // ... multiple queries on same connection
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();  // CRITICAL: always release
}

Critical warning: Forgetting client.release() causes connection leaks. The pool eventually exhausts, and all new queries hang waiting for connections that will never be returned.

Parameterized Queries and Security Questions

Security awareness with database queries is essential for any backend developer.

How do you prevent SQL injection in Node.js?

SQL injection is prevented by using parameterized queries, which separate the query structure from the data values. With the pg library, you use $1, $2 placeholders in your SQL and pass the actual values as a separate array. The driver handles proper escaping automatically.

Never concatenate user input into SQL strings using template literals or string concatenation. Even if you think you're sanitizing the input, there are countless edge cases that can bypass your validation. Parameterized queries make SQL injection structurally impossible.

// ✅ CORRECT: Parameterized query
async function getUserById(id) {
  const result = await pool.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [id]
  );
  return result.rows[0];
}
 
// ❌ WRONG: SQL injection vulnerability
async function getUserByIdUnsafe(id) {
  const result = await pool.query(
    `SELECT * FROM users WHERE id = ${id}`  // NEVER DO THIS
  );
  return result.rows[0];
}

How do you write parameterized queries for different operations?

Parameterized queries work for all SQL operations—SELECT, INSERT, UPDATE, DELETE. The placeholder numbers correspond to the position in the values array: $1 is the first element, $2 is the second, and so on.

For INSERT operations, you can use the RETURNING clause to get the inserted data back without a second query. This is particularly useful for getting auto-generated IDs or default values.

// Insert with returning
async function createUser(name, email, passwordHash) {
  const result = await pool.query(
    `INSERT INTO users (name, email, password_hash, created_at)
     VALUES ($1, $2, $3, NOW())
     RETURNING id, name, email, created_at`,
    [name, email, passwordHash]
  );
  return result.rows[0];
}
 
// Search with multiple conditions
async function searchUsers(searchTerm, limit = 20, offset = 0) {
  const result = await pool.query(
    `SELECT id, name, email
     FROM users
     WHERE name ILIKE $1 OR email ILIKE $1
     ORDER BY created_at DESC
     LIMIT $2 OFFSET $3`,
    [`%${searchTerm}%`, limit, offset]
  );
  return result.rows;
}

Transaction Questions

Understanding transactions demonstrates knowledge of data integrity and concurrent operations.

How do you handle transactions in Node.js with PostgreSQL?

Transactions ensure that multiple database operations either all succeed or all fail together. You need a dedicated client from the pool because all queries in a transaction must run on the same connection. The pattern is: acquire client, BEGIN, execute queries, COMMIT on success or ROLLBACK on failure, then always release the client.

The try/catch/finally pattern is essential. The finally block guarantees the client is released back to the pool regardless of whether the transaction succeeded or failed.

async function transferMoney(fromAccount, toAccount, amount) {
  const client = await pool.connect();
 
  try {
    await client.query('BEGIN');
 
    // Debit from source account
    const debitResult = await client.query(
      `UPDATE accounts
       SET balance = balance - $1
       WHERE id = $2 AND balance >= $1
       RETURNING balance`,
      [amount, fromAccount]
    );
 
    if (debitResult.rowCount === 0) {
      throw new Error('Insufficient funds or account not found');
    }
 
    // Credit to destination account
    await client.query(
      `UPDATE accounts
       SET balance = balance + $1
       WHERE id = $2`,
      [amount, toAccount]
    );
 
    // Record the transfer
    await client.query(
      `INSERT INTO transfers (from_account, to_account, amount, created_at)
       VALUES ($1, $2, $3, NOW())`,
      [fromAccount, toAccount, amount]
    );
 
    await client.query('COMMIT');
    return { success: true };
 
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Key insight: The WHERE balance >= $1 in the debit query prevents overdrafts at the database level—even if application logic has a bug, the transaction fails safely.

How do you create a reusable transaction helper?

For cleaner code and consistent transaction handling across your application, create a reusable wrapper function. This helper encapsulates the BEGIN/COMMIT/ROLLBACK pattern and ensures proper client release, reducing boilerplate and preventing connection leaks.

The callback pattern allows you to pass any set of queries to execute within the transaction context.

async function withTransaction(callback) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}
 
// Usage
await withTransaction(async (client) => {
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  return { transferred: 100 };
});

Migration Questions

Migration knowledge demonstrates understanding of team workflows and deployment processes.

What are database migrations and why are they important?

Migrations are versioned scripts that modify your database schema in a controlled, repeatable way. Each migration has a timestamp and contains the changes needed to move the schema from one version to the next—creating tables, adding columns, creating indexes.

Without migrations, you'd be manually running SQL scripts and hoping everyone's database matches. Migrations provide version control for your schema: every team member applies the same changes in the same order, CI/CD pipelines automatically migrate staging and production, and if something breaks, you can rollback.

// Using Knex migrations
// migrations/20240115120000_create_users.js
 
exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email').unique().notNullable();
    table.string('password_hash').notNullable();
    table.string('name');
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
 
    // Index for email lookups
    table.index('email');
  });
};
 
exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

# Run migrations
npx knex migrate:latest
 
# Rollback last migration
npx knex migrate:rollback
 
# Create new migration
npx knex migrate:make add_user_roles

How do you manage migrations with Prisma?

Prisma offers a modern approach with type safety and automatic migration generation. You define your schema in a declarative format, and Prisma generates the SQL migrations by comparing your schema to the current database state.

This approach catches schema changes automatically and generates type-safe client code that matches your database exactly.

// prisma/schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
 
  @@index([email])
}
 
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
 
  @@index([authorId])
}

# Generate migration from schema changes
npx prisma migrate dev --name add_posts_table
 
# Apply migrations in production
npx prisma migrate deploy

Query Optimization Questions

Understanding query optimization separates mid-level from senior developers.

How do you use EXPLAIN ANALYZE to diagnose slow queries?

EXPLAIN ANALYZE is your primary tool for understanding query performance. It shows the query execution plan—how PostgreSQL actually retrieves and processes data—along with real timing information. This reveals whether your query uses indexes efficiently or falls back to slow sequential scans.

The output shows each step of query execution, the method used (index scan vs sequential scan), and the actual time spent. This information guides your optimization decisions.

async function analyzeQuery(query, params) {
  const result = await pool.query(
    `EXPLAIN ANALYZE ${query}`,
    params
  );
  return result.rows.map(r => r['QUERY PLAN']).join('\n');
}
 
// Usage
const plan = await analyzeQuery(
  'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
  [123]
);
console.log(plan);

-- Output shows what's happening
Index Scan using idx_orders_user_date on orders
  Index Cond: (user_id = 123)
  Rows Removed by Filter: 0
  Planning Time: 0.150 ms
  Execution Time: 0.045 ms

Red flags to look for:

• Seq Scan on large tables → needs index
• High Rows Removed by Filter → index not selective enough
• Sort operations → consider index with ORDER BY columns

How do you create effective indexes?

Indexes dramatically speed up queries by allowing PostgreSQL to find rows without scanning the entire table. Without an index, PostgreSQL performs a sequential scan—checking every row. With an index, it can jump directly to matching rows.

Create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses. For queries that filter on multiple columns, composite indexes can be more efficient than multiple single-column indexes.

-- Without index: Sequential scan on 1M rows (~500ms)
SELECT * FROM users WHERE email = 'john@example.com';
 
-- With index: Index scan (~1ms)
CREATE INDEX idx_users_email ON users(email);
 
-- Composite index for common query patterns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
 
-- This query uses the composite index efficiently
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;

How do you find slow queries in production?

PostgreSQL provides the pg_stat_statements extension that tracks execution statistics for all queries. This helps identify which queries consume the most time and resources, allowing you to focus optimization efforts where they'll have the greatest impact.

Enable this extension in production to monitor query performance over time and catch regressions early.

// Finding slow queries in production
async function getSlowQueries() {
  // Requires pg_stat_statements extension
  const result = await pool.query(`
    SELECT query, calls, mean_time, total_time
    FROM pg_stat_statements
    ORDER BY mean_time DESC
    LIMIT 10
  `);
  return result.rows;
}

How do you solve the N+1 query problem?

The N+1 problem occurs when you execute one query to fetch a list of items, then N additional queries to fetch related data for each item. This is extremely inefficient—10 users with their posts becomes 11 queries instead of 1 or 2.

The solution is to batch your queries using JOINs or aggregate functions. PostgreSQL can return related data in a single query using json_agg() to group related rows.

// N+1 Problem: 1 query for users + N queries for posts
const users = await pool.query('SELECT * FROM users LIMIT 10');
for (const user of users.rows) {
  // This runs 10 separate queries!
  const posts = await pool.query(
    'SELECT * FROM posts WHERE user_id = $1',
    [user.id]
  );
}
 
// Solution: JOIN or batch query
const result = await pool.query(`
  SELECT u.*,
         json_agg(p.*) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  LIMIT 10
`);

PostgreSQL Features Questions

Knowledge of PostgreSQL-specific features demonstrates depth beyond basic SQL.

How do you use JSONB for flexible data storage?

JSONB (Binary JSON) allows you to store semi-structured data within a relational database. Unlike regular JSON, JSONB is stored in a decomposed binary format that supports indexing and efficient querying. This gives you schema flexibility for certain fields while maintaining the benefits of relational structure.

JSONB is ideal for storing metadata, user preferences, event payloads, or any data where the schema varies between records.

// Store and query JSON data
await pool.query(`
  CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
  )
`);
 
// Insert JSON
await pool.query(
  `INSERT INTO events (type, data) VALUES ($1, $2)`,
  ['user_signup', { userId: 123, source: 'google', plan: 'pro' }]
);
 
// Query inside JSON
const result = await pool.query(`
  SELECT * FROM events
  WHERE data->>'source' = 'google'
  AND (data->>'plan')::text = 'pro'
`);
 
// Index JSON fields for performance
await pool.query(`
  CREATE INDEX idx_events_source ON events ((data->>'source'))
`);

How do you work with PostgreSQL arrays?

PostgreSQL supports array columns, allowing you to store multiple values in a single field. This is useful for tags, categories, or any multi-value attribute that doesn't require a separate table.

Arrays support operators for containment checks, overlaps, and element access, making queries concise and efficient.

// PostgreSQL arrays are powerful
await pool.query(`
  CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    tags TEXT[]
  )
`);
 
// Insert with array
await pool.query(
  `INSERT INTO articles (title, tags) VALUES ($1, $2)`,
  ['PostgreSQL Tips', ['database', 'postgresql', 'backend']]
);
 
// Query arrays
const result = await pool.query(`
  SELECT * FROM articles
  WHERE 'postgresql' = ANY(tags)
`);

What is the difference between PostgreSQL and MySQL?

PostgreSQL and MySQL serve different strengths. PostgreSQL has stronger ACID compliance, better support for complex queries (CTEs, window functions), and advanced data types (JSONB, arrays, full-text search). Its MVCC implementation handles concurrent writes better, making it excellent for write-heavy workloads.

MySQL is often faster for simple read-heavy workloads and has historically been easier to set up and administrate. For Node.js backends with complex data requirements, PostgreSQL is typically the better choice. For simple CRUD applications with high read throughput, MySQL can be appropriate.

Error Handling Questions

Robust error handling is essential for production database applications.

How do you handle database connection errors?

Database connections can fail for various transient reasons—network issues, server restarts, connection limits. Implementing retry logic with exponential backoff handles these gracefully without failing user requests unnecessarily.

Identify which errors are transient (worth retrying) versus permanent (should fail immediately). Connection refused, admin shutdown, and crash shutdown are typically transient.

// Retry logic for transient failures
async function queryWithRetry(sql, params, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await pool.query(sql, params);
    } catch (err) {
      const isTransient =
        err.code === 'ECONNREFUSED' ||
        err.code === '57P01' ||  // admin_shutdown
        err.code === '57P02';    // crash_shutdown
 
      if (!isTransient || attempt === maxRetries) {
        throw err;
      }
 
      // Exponential backoff
      await new Promise(r => setTimeout(r, Math.pow(2, attempt) * 100));
    }
  }
}

How do you handle database credentials securely?

Database credentials should never be hardcoded in your source code. Use environment variables that are loaded from a secrets manager in production—AWS Secrets Manager, HashiCorp Vault, or platform-specific solutions like Heroku config vars.

In development, use .env files that are gitignored. For CI/CD, inject secrets at runtime. The pg library reads standard PostgreSQL environment variables (PGHOST, PGUSER, etc.) automatically, which integrates well with container orchestration platforms.

Quick Reference

Related Articles

• Complete Node.js Backend Developer Interview Guide - comprehensive preparation guide for backend interviews
• SQL JOINs Interview Guide - Master all JOIN types with visual examples
• MongoDB Interview Guide - NoSQL alternative: when to choose MongoDB vs PostgreSQL
• Node.js Advanced Interview Guide - Async patterns that work with database operations
• System Design Interview Guide - Database scaling and architecture decisions