Page 9 - SQL

SQL injection has ranked among the most dangerous security flaws in web applications for decades, even though the technical solution has been known for just as long. Parameterized queries structurally separate code from data, least-privilege database accounts limit the damage in a worst case, and string escaping alone remains a fragile crutch with many documented bypasses.

The query optimizer does not decide based on SQL text alone, but based on statistics about data distribution, which can go stale as tables grow. Understanding statistics and query plan cache immediately explains why an unchanged query suddenly gets a different, significantly slower execution plan after data growth.

Change Data Capture captures every change to data rows nearly in real time instead of searching for it in periodic queries. Log based CDC reads directly from the database transaction log and delivers changes reliably to downstream systems, without putting additional load on the source database.

A single database instance hits limits early once read traffic grows. Read replicas distribute SELECT queries across additional instances, taking load off the primary server. The real challenge is not setting up replication itself, but handling replication lag, failover, and deciding which queries are even allowed to hit a replica.

Choosing a primary key is one of the most consequential decisions in table design: a business natural key such as an email address or a product SKU carries meaning, an artificial surrogate key such as an auto-increment value or a UUID is stable and meaningless. This post compares both strategies concretely, presents composite keys as a third option, and delivers a practical decision guide.

NTILE(n) splits a sorted result set into n roughly equal buckets and turns quartile, decile and general percentile analysis into a single SQL query. Instead of exporting customer values, response times or test scores into statistics software, distribution buckets are produced directly in the database, reproducibly and without manual sorting work.

A wrongly chosen join type either returns too few rows, because unmatched records silently vanish, or too many rows, because a missing join condition creates a cartesian product. Inner join, left join, right join and full join differ exactly in which rows from the involved tables end up in the result set when a condition is not met, and anyone who does not understand this builds reports with silently wrong numbers.

EXISTS and IN look interchangeable at first glance, but differ in one decisive point: how they handle NULL values in the inner result set. While EXISTS only checks for the existence of a row and NULL plays no role there, a NOT IN with a NULL-containing list can silently return an empty result set, a behavior that regularly leads to missing rows in reports in production, without any error appearing.

Teams that only find slow queries once users complain are working reactively instead of proactively. With slow query log, pg_stat_statements and a clear sampling process, slow queries can be found systematically before they become a production problem, regardless of which database system is in use.

A thousand individual INSERT statements are almost always slower than the same thousand rows sent as one batch, because every single round trip to the database costs network latency, parsing, and transaction overhead. Batch processing bundles these costs, whether through multi-row INSERTs, bulk-load tools, or larger transactions, and can cut load time by an order of magnitude.