Databases & PDO
Talking to a database from PHP — PDO versus mysqli, prepared statements and transactions, optimistic and pessimistic locking, indexing and hunting the missing index, and connection management under the shared-nothing FPM model.
14 questions
JuniorTheoryVery commonWhat is a database index, what does it speed up, and what does it cost you on writes?
What is a database index, what does it speed up, and what does it cost you on writes?
An index is a separate sorted structure — usually a B-tree — mapping column values to rows, so the engine seeks instead of scanning the table. It speeds up lookups, joins and ORDER BY on those columns, but every INSERT, UPDATE and DELETE must maintain it, so writes slow down.
Common mistakes
- ✗Believing an index is free — every write has to maintain it
- ✗Thinking an index caches rows rather than being a sorted lookup structure
- ✗Adding one index per column instead of a composite index matching the query
Follow-up questions
- →Why can a composite index on
(a, b)serve a query onaalone but not onbalone? - →When would a low-cardinality column such as a boolean flag make a poor index?
JuniorTheoryVery commonWhat is a database transaction, and what does atomicity guarantee when one statement inside it fails?
What is a database transaction, and what does atomicity guarantee when one statement inside it fails?
A transaction groups statements into one unit: either every change commits or none does. Atomicity means a failure part-way leaves the database exactly as it was — a rollback undoes the earlier statements too. Until you commit, other connections do not see your uncommitted rows.
Common mistakes
- ✗Believing statements that already succeeded stay committed when a later one fails
- ✗Thinking a transaction is a table lock rather than an all-or-nothing unit
- ✗Assuming other connections see the rows before the
COMMIT
Follow-up questions
- →What happens to an open transaction if the PHP script dies before committing?
- →How does the isolation level change what your transaction sees mid-flight?
SeniorDesignVery commonA payment provider calls your PHP webhook endpoint on every state change. It retries on any non-2xx response and on a timeout, it does not guarantee ordering, and it may deliver the same event twice — occasionally twice at the same moment, to two PHP-FPM workers. Each event must credit a customer's balance exactly once, and a duplicate must never double-credit. You own the database schema and may add tables. Design the handler: what identifies an event, where the deduplication decision is made, how two concurrent deliveries of the same event are stopped from both winning, how you keep the credit and the dedup record consistent with each other, and what you return to the provider when a retry arrives for an event you have already processed.
A payment provider calls your PHP webhook endpoint on every state change. It retries on any non-2xx response and on a timeout, it does not guarantee ordering, and it may deliver the same event twice — occasionally twice at the same moment, to two PHP-FPM workers. Each event must credit a customer's balance exactly once, and a duplicate must never double-credit. You own the database schema and may add tables. Design the handler: what identifies an event, where the deduplication decision is made, how two concurrent deliveries of the same event are stopped from both winning, how you keep the credit and the dedup record consistent with each other, and what you return to the provider when a retry arrives for an event you have already processed.
Make the provider's event id the key: a processed_events table with a unique constraint on it. Inside one transaction, insert that row and apply the credit — the unique index is what makes the race safe, since the second worker's INSERT fails and its transaction rolls back. Never check-then-insert, that is a race window. On a duplicate return 200, so the provider stops retrying.
Common mistakes
- ✗Checking for the event id and then inserting it — the gap between the two is the race
- ✗Deduplicating outside the transaction that applies the credit
- ✗Returning a non-2xx on a duplicate, which makes the provider retry it forever
Follow-up questions
- →What do you do when the credit commits but your
200response never reaches the provider? - →How would you handle events that arrive out of order for the same customer?
JuniorTheoryCommonUnder the FastCGI process manager PHP-FPM, when does a request open its database connection and when is it closed?
Under the FastCGI process manager PHP-FPM, when does a request open its database connection and when is it closed?
A worker opens the connection lazily on the first query, and PHP closes it when the request ends and the PDO object is destroyed — nothing carries into the next request. Persistent connections are the exception: the socket stays bound to that worker and is reused by its next request.
Common mistakes
- ✗Thinking
PHP-FPMpools database connections the way a Java application server does - ✗Believing a
PDOobject survives into the next request - ✗Assuming a persistent connection is shared across workers rather than bound to one
Follow-up questions
- →What state can a persistent connection leak into the next request on the same worker?
- →How does an unclosed transaction behave when the request ends abruptly?
JuniorTheoryCommonWhat does the database-access layer PDO give you over mysqli, and where does it stop helping?
What does the database-access layer PDO give you over mysqli, and where does it stop helping?
PDO is one driver-agnostic API over MySQL, PostgreSQL, SQLite and more: switching driver changes the DSN, not the call sites. It supports named placeholders and can throw exceptions on error. mysqli is MySQL-only. Neither makes your SQL portable — only the API is.
Common mistakes
- ✗Believing
PDOmakes the SQL itself portable, not just the API - ✗Thinking
mysqlicannot use prepared statements - ✗Assuming
PDOpools connections across PHP-FPM requests
Follow-up questions
- →Which
PDOattribute makes a failed query throw instead of returningfalse? - →When would you still pick
mysqlioverPDOon a MySQL-only project?
MiddleTheoryCommonPHP-FPM has no connection pool. What breaks at scale, and what do connection poolers like pgbouncer change?
PHP-FPM has no connection pool. What breaks at scale, and what do connection poolers like pgbouncer change?
Shared-nothing means every worker opens its own connection, so the total is workers × servers, and the database hits max_connections before PHP saturates. An external pooler sits in between and multiplexes many short client connections onto a few server ones. Persistent PDO connections bind one socket per worker — they do not pool.
Common mistakes
- ✗Calling
PDO::ATTR_PERSISTENTa connection pool — it is one socket bound to one worker - ✗Sizing the FPM pool without checking the database's
max_connections - ✗Assuming a pooler keeps one server connection per client connection
Follow-up questions
- →Why does transaction-mode pooling break server-side prepared statements and session variables?
- →How would you size
pm.max_childrenagainst the database'smax_connections?
MiddlePerformanceCommonA list endpoint got slow as the table grew. How do you prove that a missing index is the cause?
A list endpoint got slow as the table grew. How do you prove that a missing index is the cause?
Find the slow statement first — the slow-query log, or logging around your PDO calls — then run EXPLAIN on it. A full scan (type: ALL, rows ≈ table size, Using filesort) with no usable key is the proof. Add a composite index covering the WHERE columns and then the ORDER BY column, and re-run EXPLAIN.
Common mistakes
- ✗Guessing at the index instead of reading
EXPLAIN's access type and chosen key - ✗Adding one index per column instead of one composite index matching the query
- ✗Putting the
ORDER BYcolumn before theWHEREcolumns in a composite index
Follow-up questions
- →Why must the equality columns precede the range or
ORDER BYcolumn in a composite index? - →What does
Using filesorttell you that therowsestimate on its own does not?
MiddleTheoryCommonWhen do you pick optimistic locking over a pessimistic SELECT ... FOR UPDATE, and what does each cost?
When do you pick optimistic locking over a pessimistic SELECT ... FOR UPDATE, and what does each cost?
Pessimistic locking holds a row lock (SELECT ... FOR UPDATE) until you commit, so writers serialise and can deadlock. Optimistic locking takes no lock: the UPDATE re-checks a version column in its WHERE, and you retry when rowCount() is 0. Optimistic wins under low contention, pessimistic when conflicts are the rule.
Common mistakes
- ✗Thinking optimistic locking still takes a database lock, just a shorter one
- ✗Forgetting the retry loop — an optimistic
UPDATEmatching 0 rows means someone else won - ✗Reaching for a pessimistic lock under low contention and serialising writers for nothing
Follow-up questions
- →What does
SELECT ... FOR UPDATElock when theWHEREcolumn has no index behind it? - →How many times should an optimistic update retry before giving up, and why bound it at all?
MiddleCodeCommonTransfer funds between two accounts atomically with PDO
Transfer funds between two accounts atomically with PDO
Wrap both UPDATEs in beginTransaction() … commit(), and in a catch call rollBack() and rethrow so the caller learns the transfer failed. With PDO::ERRMODE_EXCEPTION a failing statement throws by itself. Bind $fromId, $toId and the amount as parameters, never interpolate them.
Common mistakes
- ✗Skipping the transaction and 'fixing' a failed debit with a compensating write
- ✗Catching the exception and returning, so the caller believes the transfer succeeded
- ✗Interpolating the ids and the amount into the SQL instead of binding them
Follow-up questions
- →What does
PDO::inTransaction()protect you from whentransfer()may be called from an outer transaction? - →How would you make the transfer refuse to overdraw the source account inside the same transaction?
SeniorDebuggingCommonProduction floods with SQLSTATE[HY000] [1040] Too many connections at peak load
Production floods with SQLSTATE[HY000] [1040] Too many connections at peak load
Count the ceiling: four servers times pm.max_children 50 is 200 possible workers, while max_connections is 151 — PHP can demand more connections than MySQL will grant. ATTR_PERSISTENT makes it worse: each worker keeps its socket open between requests, which is why 148 of the 151 are Sleep and only 3 are running a query. Bound the FPM pools, or pool through pgbouncer/ProxySQL — do not just raise max_connections.
Common mistakes
- ✗Raising
max_connectionswithout checking servers timespm.max_childrenagainst it - ✗Reading
Sleepthreads as idle capacity rather than workers holding persistent sockets - ✗Calling
ATTR_PERSISTENTa connection pool — it binds one socket to one worker
Follow-up questions
- →What arithmetic ties
pm.max_childrentomax_connectionsacross several servers? - →What does a transaction-mode pooler give you that
ATTR_PERSISTENTcannot?
SeniorDesignOccasionalCompliance wants an audit trail: for every change to an order, a customer or a payment you must be able to answer who changed what, when and from where — and to reconstruct the state of any record as of any past date. The application is a PHP monolith on PHP-FPM; writes go through an ORM and, in a few legacy corners, through raw SQL. Reads of the trail are rare but must be fast when they happen, and the trail is expected to outgrow the operational tables within a year. It must be impossible for an ordinary application bug to silently skip an entry, and an auditor has to be able to trust that an entry was not edited after the fact. Design it: where the entry is written from and how it stays consistent with the change it describes, what a single entry stores, how you index a table that only ever grows, and how you keep it from swallowing the operational database.
Compliance wants an audit trail: for every change to an order, a customer or a payment you must be able to answer who changed what, when and from where — and to reconstruct the state of any record as of any past date. The application is a PHP monolith on PHP-FPM; writes go through an ORM and, in a few legacy corners, through raw SQL. Reads of the trail are rare but must be fast when they happen, and the trail is expected to outgrow the operational tables within a year. It must be impossible for an ordinary application bug to silently skip an entry, and an auditor has to be able to trust that an entry was not edited after the fact. Design it: where the entry is written from and how it stays consistent with the change it describes, what a single entry stores, how you index a table that only ever grows, and how you keep it from swallowing the operational database.
Write the entry in the same transaction as the change: if the change rolls back, its audit row goes with it, whereas a log written after the commit can be silently lost. Store the actor, entity type and id, the action, a before/after diff, the timestamp, the request id and the address. Index (entity_type, entity_id, created_at) — every read is this record's history. Keep it append-only, revoking UPDATE and DELETE, and partition or archive by time.
Common mistakes
- ✗Writing the audit row outside the transaction, so a rolled-back change keeps its entry
- ✗Indexing only by time when every read is the history of one specific record
- ✗Leaving the table
UPDATE-able, so the trail an auditor reads is not tamper-evident
Follow-up questions
- →What does a database trigger give you here that application code cannot, and what does it lose?
- →How do you stop a table that only grows from slowing the operational database down?
SeniorDesignOccasionalTwo PHP-FPM workers process two orders for the same product in the same millisecond. Each reads stock = 1, each concludes the order can be fulfilled, and each writes stock = 0 — you have sold one item twice. The same shape shows up on a wallet balance, on a seat reservation, and on a coupon with a single redemption left. You cannot serialise the whole endpoint, because the rest of it is slow and independent of the stock check. Design the write path so that the invariant stock >= 0 cannot be broken however many workers arrive at once. Cover what makes read-then-write unsafe here, which of a conditional UPDATE, a pessimistic row lock and an optimistic version check you would reach for and why, what the database itself must guarantee for your choice to hold, what the code does when it loses the race, and how you would prove under load that the invariant really holds.
Two PHP-FPM workers process two orders for the same product in the same millisecond. Each reads stock = 1, each concludes the order can be fulfilled, and each writes stock = 0 — you have sold one item twice. The same shape shows up on a wallet balance, on a seat reservation, and on a coupon with a single redemption left. You cannot serialise the whole endpoint, because the rest of it is slow and independent of the stock check. Design the write path so that the invariant stock >= 0 cannot be broken however many workers arrive at once. Cover what makes read-then-write unsafe here, which of a conditional UPDATE, a pessimistic row lock and an optimistic version check you would reach for and why, what the database itself must guarantee for your choice to hold, what the code does when it loses the race, and how you would prove under load that the invariant really holds.
The read and the write are two statements, so another worker slips between them and the check is stale by the time you act on it. Push the check into the write: UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock >= 1, then read rowCount() — 0 means you lost the race and must fail the order. The engine's row lock makes that single statement atomic. SELECT ... FOR UPDATE also works but serialises writers; an optimistic version check needs a retry loop.
Common mistakes
- ✗Believing a transaction on its own closes the gap between the read and the write
- ✗Writing an absolute value computed in PHP instead of a relative, conditional
UPDATE - ✗Not checking
rowCount(), so a lost race is silently treated as a success
Follow-up questions
- →What does
rowCount()returning 0 tell you that an exception would not? - →When is a pessimistic row lock worth the serialisation it costs you?
SeniorDesignOccasionalYou are designing a multi-tenant SaaS in PHP. Every customer's data must stay isolated, tenants range from ten rows to ten million, and you must be able to restore or export one tenant without touching the others. The runtime is PHP-FPM behind a load balancer, so every worker opens its own database connection. Compare the two shapes — one shared database with a tenant_id column on every table, versus a separate database per tenant — and defend a choice. Address how each affects the total connection count, how you make a query incapable of leaking across tenants, what indexing each demands, how migrations roll out, and what happens when one very large tenant's load starts hurting everyone else.
You are designing a multi-tenant SaaS in PHP. Every customer's data must stay isolated, tenants range from ten rows to ten million, and you must be able to restore or export one tenant without touching the others. The runtime is PHP-FPM behind a load balancer, so every worker opens its own database connection. Compare the two shapes — one shared database with a tenant_id column on every table, versus a separate database per tenant — and defend a choice. Address how each affects the total connection count, how you make a query incapable of leaking across tenants, what indexing each demands, how migrations roll out, and what happens when one very large tenant's load starts hurting everyone else.
A shared database with tenant_id is the default: one connection per worker, one migration run, cheap onboarding. The cost is that every index must lead with tenant_id, and one forgotten WHERE leaks data — so scope it in the data layer, never per query. A database per tenant gives hard isolation and per-tenant restore, but multiplies connections and fans out migrations.
Common mistakes
- ✗Relying on every query remembering
WHERE tenant_idinstead of scoping in the data layer - ✗Forgetting that a database per tenant multiplies connections by the worker count
- ✗Leaving
tenant_idout of the leading index column, so every tenant scans the whole table
Follow-up questions
- →How would you make a cross-tenant read impossible even when a developer writes raw SQL by hand?
- →What changes in your answer once one tenant is a hundred times larger than every other?
SeniorDesignRareYou inherit a 200k-line PHP application still calling the removed mysql_* functions, with SQL string-interpolated across hundreds of call sites. It must keep serving production traffic throughout; there is no test suite, and staging data does not resemble production. Management wants it on PHP 8 and on PDO, and will not fund a rewrite. Describe how you would run the migration: how you find and rank the call sites, what you put in place before touching any of them, how the two data-access styles coexist during the transition, how you handle the code that relied on an implicit connection handle and on autocommit, and how you prove at each step that behaviour has not changed.
You inherit a 200k-line PHP application still calling the removed mysql_* functions, with SQL string-interpolated across hundreds of call sites. It must keep serving production traffic throughout; there is no test suite, and staging data does not resemble production. Management wants it on PHP 8 and on PDO, and will not fund a rewrite. Describe how you would run the migration: how you find and rank the call sites, what you put in place before touching any of them, how the two data-access styles coexist during the transition, how you handle the code that relied on an implicit connection handle and on autocommit, and how you prove at each step that behaviour has not changed.
Do not rewrite in place. Put a thin PDO-backed data-access layer in front, then move call sites through it in slices ranked by traffic and risk, characterising each slice with black-box tests first. Convert interpolation into bound parameters as you go, and make the old implicit autocommit explicit with beginTransaction()/commit(). Ship each slice behind a flag and diff its output against the old path.
Common mistakes
- ✗Treating it as a mechanical find-and-replace when parameter binding is the real change
- ✗Leaving interpolated SQL in place because
PDOis assumed to be safe by default - ✗Ignoring that the legacy code relied on implicit autocommit and a global connection handle
Follow-up questions
- →How would you catch a call site that silently relied on
mysql_*returningfalserather than throwing? - →What would you log during the transition to prove the
PDOpath returns the same rows as the old one?
