10 posts tagged with "Backend"

An index is a structure sorted by the column's values. Wrap a function around the column — date_part('year', created_at), LOWER(email), CAST(...) — and the thing you are comparing is no longer the sorted value, so the database cannot use the index and falls back to scanning the whole table. A condition that can use an index is called sargable. Measured on PostgreSQL 16 with 200,000 rows: the function-wrapped version runs in 29.6 ms with a Seq Scan, the range-rewritten version runs in 5.4 ms with a Bitmap Index Scan — about 5.4× faster, returning the same 37,518 rows.

LEFT JOIN keeps every row from the left table and fills in NULL where nothing matches. But WHERE runs after the join, so any condition placed on a right-hand column also removes those NULL rows — and the LEFT JOIN becomes an INNER JOIN with no warning whatsoever. Measured on PostgreSQL 16: a plain LEFT JOIN gives 4 rows, adding WHERE leaves 2 rows, but moving that same condition into ON gives 3 rows — which is the correct answer.

NULL in SQL is not a value — it is a marker meaning "unknown". Every comparison against it yields UNKNOWN, and WHERE only keeps rows that evaluate to TRUE, so UNKNOWN is discarded exactly like FALSE. The most serious consequence lands on NOT IN: a single NULL anywhere in the subquery makes the entire query return zero rows, even when the data plainly exists. I ran it on PostgreSQL 16: with the same intent, NOT IN returns 0 rows while NOT EXISTS returns 3. No error, no warning — the query just quietly returns the wrong answer.

Copying every incoming header onto an outgoing request is an anti-pattern, and it fails in three different directions: correctness (one non-ASCII value makes HttpClient throw), security (you trust and relay data the client set itself), and architecture (headers injected by infrastructure become part of your application's contract). The fix is an allowlist — an explicit list of headers permitted to pass — placed in a shared DelegatingHandler. When you write that handler, mind one trap: it does not live in the request's scope.

EF Core's DbContext is already a Unit of Work: it collects changes in the change tracker and pushes them to the database inside one transaction when you call SaveChanges. Writing your own IUnitOfWork purely to call SaveChanges is therefore usually redundant. ABP is a different matter entirely: there, the Unit of Work is ambient, opens automatically per request, and SaveChangesAsync inside a UoW does not commit the transaction — only CompleteAsync does. Misreading that one point is the source of most "the data is there sometimes" bugs.