SQL

They filter at different stages. WHERE runs before grouping and filters individual rows, so it cannot use aggregate functions. HAVING runs after GROUP BY and filters whole groups, so it can use COUNT, SUM and similar. For example, to find customers with more than five paid orders, you filter status = 'paid' in WHERE, group by customer, then keep groups with HAVING COUNT(*) > 5. Putting row-level conditions in WHERE rather than HAVING is also faster, because the database discards rows before doing the expensive grouping work. Knowing the logical order of a query (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) answers most questions like this.

A typical index is a B-tree: a sorted, balanced tree of key values pointing to rows. Instead of scanning every row, the database descends a few levels of the tree to find a value or the start of a range, then reads forward in order, which also helps ORDER BY and LIMIT. For a composite index, the leftmost-prefix rule applies: an index on (customer_id, created_at) helps filters on customer_id but not on created_at alone. The costs are disk space and slower writes, because every INSERT, UPDATE and DELETE must update each index. So I index for actual query patterns, verified with EXPLAIN, rather than every column.

It happens when you run one query to load a list and then one extra query per item to load related data: 1 query for 50 orders plus 50 queries for their customers. Each query is fast, so it looks fine in development, but in production the round-trips add up and the database gets hammered. ORMs make it easy because accessing order.customer silently triggers a query. The fix is eager loading, such as Laravel's with('customer') or Prisma's include, which loads all related rows in one or two queries, or an explicit JOIN. I also enable lazy-loading warnings or a query counter in development so regressions are caught early.

Isolation decides what one transaction sees of others running at the same time. READ COMMITTED, PostgreSQL's default, sees only committed data, but a second read can return a newer value. REPEATABLE READ, MySQL InnoDB's default, gives a consistent snapshot for the whole transaction. SERIALIZABLE guarantees the result is the same as running transactions one after another, at the cost of aborting some, which the application must retry. I rarely change the default globally. Instead, I protect specific critical sections, like wallet balances or stock, with atomic conditional updates or SELECT ... FOR UPDATE, and use SERIALIZABLE with a retry loop only for complex invariants.

When you need an aggregate or ranking but still want to keep every row. GROUP BY collapses rows: you get one row per customer with their total. A window function adds a calculated column while keeping the detail: every order alongside the customer's running total, its rank, or the date of the previous order. Typical uses are 'latest record per group' with ROW_NUMBER, top-N per category, running totals, period-over-period comparisons with LAG, and percent of total. Before window functions, these needed self-joins or correlated subqueries, which were harder to read and often slower. All major databases support them now.

First measure instead of guessing: enable the query log or use a debugging tool to see every query the request runs, with timings and the total count. If there are dozens or hundreds of similar queries, it is N+1, fixed with eager loading. If one query dominates, run EXPLAIN ANALYZE on it and look for full table scans on big tables, sorts on large sets and poor row estimates. The usual fixes are a suitable composite index, rewriting a condition so it can use the index, selecting fewer columns, or paginating. Then measure again, and keep slow-query logging on in production to catch the next one.

In steps, from cheapest to most complex. First make each query efficient: indexes, fewer round-trips, no N+1, connection pooling with PgBouncer or ProxySQL. Then cache hot, rarely changing reads in Redis. Next, add read replicas and send reporting and read-heavy endpoints there, accepting replication lag; reads that must see the user's own write stay on the primary. Vertical scaling, a bigger machine, is underrated and often buys years. Partitioning large tables by time keeps indexes small and makes archiving cheap. Only after all that do I consider sharding by tenant or customer, because it complicates transactions, joins, migrations and operations enormously. A multi-tenant design with a tenant id on every row keeps that option open without paying for it early.

Auto-increment integers are small, fast to compare and insert in order, which keeps B-tree indexes compact; but they reveal record counts, are guessable in URLs, and need coordination when several systems generate ids. Random UUIDs (v4) can be created anywhere, including offline clients, and do not leak business volume, but they are 16 bytes and insert at random positions, causing page splits and poor cache locality in clustered indexes like InnoDB's. Time-ordered UUIDv7 solves most of that: globally unique, generated anywhere, and roughly sequential, so inserts stay at the end of the index. PostgreSQL 18 ships a uuidv7() function. My usual choice is an integer or UUIDv7 primary key internally, with a separate opaque public id where URLs must not be guessable.

You cannot put an external system inside a database transaction, so you design for partial failure. For events, I use the transactional outbox: the business change and an outbox row are written in the same transaction, and a separate worker publishes outbox rows to the queue and marks them sent. Consumers must be idempotent, because at-least-once delivery means duplicates. For payments, I record the intent first with a unique idempotency key, call the provider with that key, and reconcile through webhooks and a periodic job that checks pending records, so a timeout never leaves money in an unknown state. Unique constraints on external ids make duplicate webhooks harmless. Distributed transactions like two-phase commit exist, but they are rarely worth their operational cost in web systems.