Search
30 results for “sql”
Search results
SQL Injection Prevention
How SQL injection actually works, why parameterized queries are the real fix (not string escaping), and the defense-in-depth practices that back them up.
SQL Joins & Query Planning
Why the same JOIN can execute as a nested loop, a hash join, or a merge join depending entirely on table size and available sort order, and why "it's impossible to suppress nested loops entirely" is a real, documented planner constraint.
PostgreSQL Fundamentals
How tables, joins, aggregates, window functions, and transactions fit together in everyday PostgreSQL work, from the actual internals of a single index (covered in PostgreSQL Indexes) to the SQL you write day to day.
PostgreSQL Indexes
How B-Tree, Hash, GIN, BRIN, and covering indexes work in PostgreSQL, when to create them, how to inspect usage, and the write-overhead trade-offs at scale.
PostgreSQL detects a deadlock between two transactions. What does it actually do, and can you predict which transaction survives?
PostgreSQL automatically detects the circular wait (a deadlock) and resolves it by aborting one of the involved transactions, letting the other(s) proceed. The documentation is explicit that which transaction gets aborted is difficult to predict and shouldn't be relied upon, there is no guarantee it's the "smaller" transaction, the one that started the wait, or any other predictable rule. Applications need to handle a deadlock-abort the same way they'd handle a serialization failure: catch it and retry the aborted transaction, rather than assuming a specific transaction will always be the one sacrificed.
A PostgreSQL primary crashes right after committing a transaction, under asynchronous replication. Is that transaction guaranteed to exist on the standby?
No. Asynchronous replication (the default) confirms a commit on the primary without waiting for the standby to receive or apply the corresponding WAL records, there's typically a small delay, often under a second, between a commit and its visibility on the standby. If the primary crashes in that window, before the WAL records reached the standby, that transaction is lost even though the client was already told it committed successfully. This is the specific, documented risk asynchronous replication accepts in exchange for not adding network round-trip latency to every commit.
What is PostgreSQL's default transaction isolation level, and what specific anomaly does it still allow that a stricter level would prevent?
PostgreSQL defaults to Read Committed, where each individual statement within a transaction sees a fresh snapshot of everything committed as of that statement's start, not the transaction's start. This prevents dirty reads (seeing another transaction's uncommitted changes) but still allows non-repeatable reads, running the same SELECT twice in one transaction can return different results if another transaction committed a change in between, because each statement gets its own snapshot rather than the transaction using one snapshot throughout. Repeatable Read fixes this specific anomaly by taking one snapshot at the start of the transaction and using it for every statement within it.
What's the practical difference between Repeatable Read and Serializable, given that PostgreSQL's Repeatable Read already prevents phantom reads?
PostgreSQL's Repeatable Read goes beyond the SQL standard's minimum and already prevents phantom reads via snapshot isolation, but it can still allow a specific class of anomaly called a serialization anomaly, where the combined effect of several concurrently-committed transactions is not equivalent to any possible serial (one-at-a-time) ordering of them, even though each transaction individually looks consistent. Serializable adds predicate locking on top of snapshot isolation specifically to detect and prevent that remaining anomaly, guaranteeing that the outcome is always equivalent to transactions having run one at a time in some order. The cost is the same as Repeatable Read's, more serialization failures the application must retry, in exchange for the strongest correctness guarantee available.
How does PostgreSQL decide whether to use an index?
The query planner estimates the cost of each available access path using table statistics (row count, column cardinality, correlation) gathered by ANALYZE. It compares sequential-scan cost against index-scan cost for the specific query. For small tables, or queries that touch a large fraction of rows, a sequential scan often wins even when an index exists, an index is not free to use if it does not narrow the result much. Run EXPLAIN ANALYZE to see the chosen plan, and re-run ANALYZE after large data changes so statistics stay accurate.
Why does string escaping alone not fully prevent SQL injection?
Escaping tries to neutralize special characters (like quotes) so user input cannot break out of its intended string literal, but it is easy to get wrong, different databases and contexts (string literals, numeric contexts, identifiers, LIKE patterns) have different escaping rules, and a single missed case reopens the vulnerability. Parameterized queries avoid the problem entirely: user input is sent to the database separately from the query structure, so it can never be interpreted as SQL syntax regardless of its content.
Can an ORM fully prevent SQL injection on its own?
An ORM prevents injection for the queries it builds using its own query API, because those are parameterized under the hood. It does not protect against injection if raw SQL is still used, for example, string-interpolating a value into a raw query method, or building dynamic column/table names from user input (which parameterization cannot help with, since identifiers cannot be bound as parameters and need allowlisting instead). ORMs reduce the attack surface; they do not eliminate the need to think about it.
What are the three join algorithms PostgreSQL's planner can choose between, and what does each one actually do?
A nested loop join takes each row from one table (the outer side) and scans the other table (the inner side) for matches, the simplest algorithm, and cheap specifically when the inner side is small or has a usable index so that scan is fast per outer row. A hash join builds an in-memory hash table from one input keyed on the join column, then probes it with rows from the other input, efficient for large inputs with no useful sort order or index. A merge join requires both inputs already sorted on the join key (or sorts them first) and then walks both sorted streams in lockstep, efficient specifically when that sorted order already exists or is useful for something else in the query anyway.
PostgreSQL's documentation says it's "impossible to suppress nested-loop joins entirely" even with enable_nestloop off. What does that tell you about relying on planner hints to force a specific join algorithm?
That specific guarantee is documented only for `enable_nestloop`: turning it off only discourages the planner by making nested loops look artificially expensive in cost estimation, it can't hard-disable them, because for some queries a nested loop is the only viable plan at all (for example, certain correlated subquery shapes), so the planner will still use one if it must. `enable_hashjoin` and `enable_mergejoin` don't carry that same caveat, disabling either one actually can prevent the planner from choosing that join type, since a nested loop (or the other remaining method) is always available as a fallback plan. In practice, though, all three settings are best treated as a debugging/diagnostic tool for understanding planner behavior, not a reliable production mechanism for forcing a specific join algorithm, the actual fix for a bad plan is almost always better statistics (via `ANALYZE`) or a better index, not overriding the planner's method choice.
PostgreSQL vs MySQL
How PostgreSQL and MySQL actually differ in data types, concurrency, indexing, and replication, and which one fits which workload.
Database Normalization
What 1NF, 2NF, and 3NF actually require, why normalization removes update anomalies, and when denormalizing on purpose is the right call.
What problem does normalization solve?
Normalization removes redundant data by splitting it across related tables, which prevents update anomalies: without it, the same fact (say, a customer address) can be duplicated across many rows, so an update has to touch every copy or the data silently goes inconsistent. Normalization also prevents insertion anomalies (needing unrelated data to exist before you can insert a new fact) and deletion anomalies (losing unrelated data as a side effect of deleting one row).
What is the practical difference between 2NF and 3NF?
2NF removes partial dependencies: every non-key column must depend on the whole primary key, not just part of a composite key. 3NF goes further and removes transitive dependencies: a non-key column cannot depend on another non-key column. A classic 3NF violation is storing both zip_code and city on an orders table, where city is really determined by zip_code, not by the order itself, city belongs in its own lookup table.
When is denormalizing a good idea?
When read performance matters more than write simplicity and the redundancy is deliberately managed, for example, caching a computed total on an orders row instead of summing line items on every read, or duplicating a display name to avoid a join on a hot path. The key is that it is a conscious trade-off with a plan for keeping the duplicate data consistent (triggers, application logic, or accepting eventual consistency), not an accident.
What is the difference between `INNER JOIN` and `LEFT JOIN`?
`INNER JOIN` returns only rows where the join condition matches in both tables, a row from either side with no match is dropped entirely from the result. `LEFT JOIN` returns every row from the left table regardless of whether a match exists on the right, filling in `NULL` for the right table's columns when there isn't one. Reaching for `INNER JOIN` when you actually need "every user, even ones with zero orders" silently drops exactly the rows you wanted to see, which is why an unexpectedly short result set is one of the most common join bugs.
What is the difference between GROUP BY aggregation and a window function like `ROW_NUMBER() OVER (...)`?
`GROUP BY` collapses every row in a group into a single output row per group, you lose access to the individual rows once you've aggregated. A window function computes its result (a rank, a running total, a row number) per row while keeping every individual row in the output, `PARTITION BY` defines the grouping for that calculation, but nothing is collapsed. This is why "give me each order plus that customer's running total" needs a window function, while "give me one row per customer with their total" is a plain `GROUP BY`.
Why does wrapping several statements in `BEGIN`/`COMMIT` matter, even for a multi-step operation that's logically one action?
Without an explicit transaction, PostgreSQL commits each statement independently the moment it succeeds; if a multi-step operation (debit one account, credit another) fails halfway through, the database is left in an inconsistent, partially-applied state with no way to undo the completed step. Wrapping the statements in `BEGIN`/`COMMIT` groups them so they can be committed or rolled back together, but that alone isn't automatic protection against every failure mode: PostgreSQL only aborts a transaction on its own when a statement raises an actual SQL error, an `UPDATE` that runs successfully but matches zero rows (a mistyped account id, for instance) is not an error at all, and would otherwise be committed as if the transfer had actually happened. Making the transaction genuinely safe means checking that each `UPDATE` affected exactly the one row expected and issuing an explicit `ROLLBACK` if either check fails, only issuing `COMMIT` once both checks pass.
What is the difference between a composite index and two separate single-column indexes?
A composite (multi-column) index on (a, b) stores rows sorted by a, then by b within each a. It efficiently serves queries filtering on a alone, or on a and b together, but not on b alone. Two separate single-column indexes let Postgres combine them via a bitmap AND/OR, which works but is usually slower than one well-ordered composite index for the common query pattern. Column order in a composite index should match the most selective, most-frequently-filtered column first.
When would you choose a partial index over a full index?
When queries only ever filter on a subset of rows, for example WHERE is_active = true, or WHERE deleted_at IS NULL. A partial index (CREATE INDEX ... WHERE condition) only stores entries for matching rows, so it is smaller, faster to scan, and cheaper to maintain on writes than indexing the whole table, at the cost of only being usable when the query's WHERE clause matches (or implies) the index condition.
What is the difference between a parameterized query and simply concatenating a sanitized string?
A parameterized query sends the SQL command and the user-supplied values as two separate things to the database driver, the driver (or the database itself, for prepared statements) binds the values into the query plan without ever treating them as part of the SQL text. String concatenation, even "sanitized," still builds one text string where user input and SQL syntax share the same channel, any gap in the sanitization logic can be exploited. Parameterization removes that shared channel entirely rather than trying to police it.
Why would a hash join beat a nested loop join for two large, unindexed tables, but lose to a nested loop join when one table is tiny?
A nested loop join's cost scales with (outer rows) × (cost of an inner-side lookup per row), so with two large unindexed tables, the inner-side scan is expensive and gets repeated for every single outer row, making the total cost grow multiplicatively. A hash join instead pays a roughly one-time cost to build a hash table from one side, then does a cheap lookup per row from the other side, additive rather than multiplicative, which wins decisively at scale. But when one table is tiny, the nested loop's "repeat the inner scan per outer row" cost is trivial regardless, and it avoids the hash table's build overhead entirely, so the simpler algorithm wins for small inputs specifically.
Database Connection Pooling
Why transaction pooling gives far better connection reuse than session pooling, and why that same efficiency silently breaks SQL PREPARE, SET, LISTEN, and session-level advisory locks, even though PgBouncer can support protocol-level named prepared statements under transaction pooling when max_prepared_statements is enabled.
Database Locking & Deadlocks
How PostgreSQL's row-level lock modes actually differ in strength, why it can't predict which transaction a deadlock will abort, and why acquiring locks in a consistent order is the real fix, not a "nice to have".
Database Partitioning
How range, list, and hash partitioning each split one logical table into physical pieces, and why partition pruning depends entirely on the WHERE clause matching partition bounds directly, not on any index.
Database Replication
Why asynchronous replication can silently lose the most recent commits if the primary crashes, what synchronous_commit's three levels actually each guarantee, and how to measure replication lag instead of assuming it's small.
Database Transactions & Isolation Levels
Why PostgreSQL's default Read Committed isolation still allows non-repeatable reads and phantom reads, and why Repeatable Read and Serializable trade that risk for transactions your application has to be ready to retry.