Practical SQL Queries

Use jsonb when you query inside the document (indexable, containment); use json only when you must preserve the exact original text.

Scale in layers: tune, replicate, cache, then shard, and plan up front for consistency, shard-key choice, and cross-shard queries.

OLTP serves fast single-row transactions; OLAP scans few columns across billions of rows: keep heavy analytics off your transactional database.

Yes: set a default time zone at the JVM, Jackson/JSON, database session, and OS/Docker layers, and standardize all of them on UTC so the whole application behaves deterministically everywhere.

PRIMARY KEY identifies, UNIQUE forbids duplicates, NOT NULL demands a value, DEFAULT fills one in, CHECK validates the value, and FOREIGN KEY ties rows to real parents: all enforced by the database itself.

A CHECK is a boolean rule the database enforces on every INSERT and UPDATE, rejecting bad values for every writer, so it cannot be bypassed like app-only validation.

EXISTS asks "is there at least one?" and stops at the first match, IN asks "is this value in a built list?", and NOT EXISTS asks "is there none?" safely even with NULLs.

LATERAL top-N: loop the groups, for each run ORDER BY key DESC LIMIT N.

`MAX` grouped by city, `SUM` grouped by department, and `DENSE_RANK() = 2` (or a nested max-below-max subquery) for the second-highest salary.

Store timestamps in UTC, convert to local only at the edges, schedule with timezone- and DST-aware logic, and keep clocks NTP-synced so a specific-time job fires at the correct instant everywhere.

Measure latency as percentiles, throughput, connections, CPU and I/O, cache hit ratio, locks, and the slow-query log together, and alert on percentiles and saturation rather than averages.

A regression means something changed, so compare now versus before and fix the delta: do not reflexively add an index.

Find the top CPU queries first, fix the biggest offenders, then load-test under realistic peak to prove it actually holds.

PITR restores the base backup, then replays the write-ahead log up to one instant before disaster.

Design backups backward from RPO and RTO: daily full plus continuous WAL, tiered retention, cross-region copies, and tested restores.

Shared locks allow many concurrent readers, Exclusive locks allow one writer, Intent locks signal fine-grained locking for cheap compatibility checks, and lock escalation trades many row locks for a coarse table lock to cut overhead at the cost of concurrency.

A deadlock is a circular lock wait; InnoDB detects the cycle and rolls back one transaction as the victim, so the application must catch the error and retry.

Order only by timestamp and ties are broken arbitrarily and can flip between runs: add a unique tie-breaker like `id DESC` so the latest-row-per-group result is deterministic.

`NOT IN` with any NULL in the set returns zero rows because `x <> NULL` is UNKNOWN: always use `NOT EXISTS`, which is NULL-safe and short-circuits.

SQL is written SELECT-first but runs FROM first and SELECT late, so aliases created in SELECT are not visible to WHERE.

`GROUP BY email HAVING COUNT(*) > 1` finds dupes, but the app's check-then-insert races under concurrency: only a DB `UNIQUE` constraint truly prevents duplicates.

Logical is portable but slow, physical is fast but engine-specific, incremental is small but restores as a chain.

UUIDv7 is approximately ordered (clock skew breaks global sequence), must be stored as 16-byte binary not CHAR(36), and leaks creation time so it is an identifier, never a secret.

NOT NULL on a column with existing nulls errors out; add the column nullable, backfill the nulls in small batches, then enforce NOT NULL.

A cursor processes query rows one at a time in a loop; use it only when set-based SQL genuinely cannot express the logic, because it is slow and holds resources and locks.

A sequence is a standalone, shareable number generator; identity binds one to a single table's column: and both gap on caching and rollbacks because they promise uniqueness and order, not contiguity.

Consistent hashing moves only K/N keys when nodes change, and virtual nodes give each physical node many ring spots for even balance.

Dimensional modeling optimizes analytics reads with star for speed and snowflake for less redundancy, unlike normalized OLTP built for safe writes; the logical model is the abstract blueprint and the physical model is its DBMS-specific implementation.

SQL injection happens when user input becomes part of the SQL code; prevent it by binding input as parameters so data can never be executed as code, backed by validation, ORMs, and least privilege.

The VARCHAR max depends on the database engine, and BYTE semantics count bytes while CHAR semantics count characters: the difference matters under multi-byte encodings like UTF-8.

PACELC says: during a Partition choose A or C (like CAP), Else during normal operation choose Latency or Consistency: it covers the healthy case CAP ignores.

Oracle's `CLOB` maps to PostgreSQL's `TEXT`: a plain, unlimited-length string type you read and write like any normal column.

Use POST (or PUT) with the sensitive value in the request body over HTTPS: never put Aadhaar/PAN in a GET URL, because URLs are logged, cached, and leaked via history and referrers.

OFFSET re-reads everything before the page (slower the deeper you go); keyset seeks straight to the page using the last-seen key (fast at any depth).