15 questions found
What is database sharding, and what problem does it solve that replication alone doesn't address?
Advanced
Sharding splits a single logical dataset horizontally across multiple separate database instances (shards), each holding a distinct, non-overlapping subset of the data (commonly partitioned by a shard key like customer ID) -- unlike replication, which copies the entire dataset to multiple servers to scale reads, sharding scales both read AND write capacity by distributing the total data volume and write load itself across many independent servers, addressing the case where a dataset has simply grown too large or write-heavy for any single server to handle.
// Simplified shard routing based on customer ID
function getShardForCustomer(customerId) {
const shardIndex = customerId % NUM_SHARDS;
return shardConnections[shardIndex];
}
const shard = getShardForCustomer(customerId);
await shard.query('INSERT INTO orders ...', [orderData]);
Real-world example
A multi-tenant SaaS platform whose largest customers generated more write volume than a single database server could handle shards its orders table across four separate database instances by customer ID, distributing both the storage and write load across all four rather than concentrating it on one server.
Common follow-ups: What's the operational complexity cost of sharding compared to simply using a larger, more powerful single database server (vertical scaling)?;How do you handle a query that needs to aggregate data across multiple shards?
Architecture & Design Patterns;Performance Optimization & Profiling
What is a database seed script, and how does it differ from a migration?
Intermediate
A migration changes the database's schema (structure); a seed script populates the database with actual data -- either essential reference data an application needs to function (like default roles or configuration records) or realistic sample data for local development and testing -- run separately from and typically after migrations, since seeding requires the schema to already exist.
// seed.js
async function seed() {
await db('roles').insert([
{ name: 'admin', permissions: 'all' },
{ name: 'viewer', permissions: 'read-only' },
]);
}
Real-world example
A team's local development setup runs migrations to create the schema, then a seed script that populates a handful of test users and sample orders, letting any new developer get a fully working local environment with realistic data in a single command.
Common follow-ups: How do you keep seed data consistent and non-duplicating if the seed script is accidentally run more than once?;Should production databases ever run seed scripts, and if so, for what kind of data?
Git & Project Management;CLI Tools & Scripting with Node.js
What is a database connection leak in a Node.js application, and how does it typically happen when using a connection pool?
Advanced
A connection leak occurs when code checks out a connection from the pool (like pool.connect() in the pg driver) but fails to release it back to the pool -- commonly caused by an error being thrown between acquiring and releasing the connection without a properly structured try/finally block, which over time exhausts the pool's available connections, eventually causing every subsequent request to hang waiting for a connection that never becomes available.
// Leak: if the query throws, client.release() is never called
const client = await pool.connect();
const result = await client.query('SELECT ...'); // if this throws, leak!
client.release();
// Fixed: release always happens, even on error
const client = await pool.connect();
try {
const result = await client.query('SELECT ...');
} finally {
client.release();
}
Real-world example
A production API that gradually became unresponsive under load, requiring periodic restarts to recover, was eventually traced to a missing try/finally around a specific query, which meant that any time that particular query failed, its connection was silently never returned to the pool.
Common follow-ups: How would you monitor a connection pool's metrics in production to detect a leak before it causes an outage?;What's the difference between a connection leak and simply having too small a pool size for the actual load?
Error Handling;Performance Optimization & Profiling
What is the difference between a one-to-many and a many-to-many relationship in a relational database, and how is each modeled?
Intermediate
A one-to-many relationship (like one author having many books) is modeled with a foreign key on the 'many' side's table pointing back to the 'one' side. A many-to-many relationship (like many students enrolled in many courses) requires an intermediate junction table containing foreign keys to both related tables, since neither table alone can hold a foreign key representing multiple associations on both sides.
-- One-to-many: books.author_id references authors.id
CREATE TABLE books (id SERIAL PRIMARY KEY, author_id INT REFERENCES authors(id));
-- Many-to-many: a junction table
CREATE TABLE enrollments (
student_id INT REFERENCES students(id),
course_id INT REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);
Real-world example
A course-registration system models the many-to-many relationship between students and courses using an 'enrollments' junction table, which also conveniently stores relationship-specific data like the enrollment date and grade that wouldn't fit naturally on either the students or courses table alone.
Common follow-ups: What additional data can a junction table hold beyond just the two foreign keys, and why is that useful?;How does an ORM like Sequelize or a Mongoose implementation model a many-to-many relationship in code?
SQL Queries;Databases & ORMs (MongoDB/Mongoose
SQL/Sequelize)
What is a database deadlock, and how might it occur between two concurrent transactions in a Node.js application?
Advanced
A deadlock occurs when two (or more) transactions each hold a lock the other needs, and each is waiting for the other to release its lock first, resulting in neither ever proceeding -- commonly arising when two transactions update the same set of rows in a different order (transaction A locks row 1 then waits for row 2, while transaction B locks row 2 then waits for row 1). Most databases detect this situation automatically and forcibly abort one of the transactions with a deadlock error, which the application must catch and retry.
// Deadlock risk: two transfers happening concurrently in opposite directions
// Transaction A: locks account 1, then tries to lock account 2
// Transaction B: locks account 2, then tries to lock account 1
// Mitigation: always acquire locks in a consistent order (e.g., by ID)
const [firstId, secondId] = [accountA, accountB].sort();
await client.query('SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [firstId]);
await client.query('SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [secondId]);
Real-world example
A money-transfer feature experiencing occasional deadlock errors under concurrent load is fixed by always acquiring row locks on the two involved accounts in a consistent order (sorted by account ID) regardless of which account is the sender or receiver, eliminating the circular wait condition entirely.
Common follow-ups: How does an application correctly detect and automatically retry a transaction that failed specifically due to a deadlock, as opposed to a different kind of error?;What database-level tools exist for diagnosing which specific queries are involved in a recurring deadlock?
Error Handling;Architecture & Design Patterns