Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
Google · SQL & Databases
Practice SQL & Databases questions specifically asked in Google interviews – ideal for online test preparation, technical rounds and final HR discussions.
Questions
76
Tagged for this company + subject
Company
View company-wise questions
Subject
SQL & Databases
Explore topic-wise practice
Go through each question and its explanation. Use this page for targeted revision just before your Google SQL & Databases round.
Casting a timestamp to DATE groups all times on the same calendar day. It is widely supported across engines. String formatting works but can introduce locale issues and sort quirks.
SELECT CAST(sale_ts AS DATE) AS d, SUM(amount) FROM sales GROUP BY CAST(sale_ts AS DATE);
For complete preparation, combine this company + subject page with full company-wise practice and subject-wise practice. You can also explore other companies and topics from the links below.
CASE lets you route rows into buckets based on business rules, then aggregate each bucket in one scan. This avoids extra joins and keeps logic in the query. You can also map null or out-of-range values into a safe category before you group. COALESCE substitutes defaults for NULL so aggregates and comparisons behave predictably. For example, you can turn missing amounts into zero for totals, or fill missing categories with “Unknown” so the group appears in the report instead of disappearing.
SELECT COALESCE(category,'Unknown') AS cat,
SUM(CASE WHEN status='PAID' THEN amount ELSE 0 END) AS paid
FROM invoices
GROUP BY COALESCE(category,'Unknown');A SELF JOIN uses aliases to treat the same table as two roles. It helps express parent child links or supervisor employee relations. Aliases are mandatory so the columns from each role can be referenced unambiguously.
SELECT e.emp_id, e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON m.emp_id = e.manager_id;
Optimizers try different join orders to minimize intermediate row counts. Starting with the most selective filters reduces data early and speeds the whole plan. Good statistics and appropriate indexes help the optimizer choose correctly. Indexes on join keys enable efficient lookups and hash or merge joins. Composite indexes that match the join and filter columns in order can be especially powerful in star schemas.
SELECT f.sale_id, d.date_id FROM fact_sales f JOIN dim_date d ON d.date_id = f.date_id WHERE d.yyyy_mm = 202509;
Start by capturing the plan to see join order, access paths, and estimated versus actual row counts. Look for scans on very large tables where a selective index could be used, and for big mismatches between estimated and actual rows that signal bad statistics or non-SARGable predicates. Then check operators with the highest cost or time. Add or adjust indexes to support the most selective predicates and common joins. Fix non-SARGable expressions, reduce columns (avoid select star), and ensure filters happen as early as possible. Re-run EXPLAIN ANALYZE to verify the change improved row counts and timing.
EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';
Filtering the raw column with a range allows an index seek. Wrapping the column in a function blocks index use in many engines and forces scans. A half-open range (greater-than-or-equal to start and less than next day) is even safer across time types and precision.
WHERE created_at >= '2025-10-01' AND created_at < '2025-10-02'
A dirty read returns data that may later roll back. A non-repeatable read happens when a row read twice returns different values after another commit. A phantom read adds or removes qualifying rows between two scans of the same predicate, usually on ranges.
SELECT * FROM orders WHERE status='NEW'; -- second read returns different result set
MVCC keeps past versions long enough to serve a snapshot consistent with each transaction’s view. Readers do not wait for writers and vice versa in common cases. Engines must vacuum or purge dead versions to reclaim space and control index bloat.
START TRANSACTION; -- snapshot is taken here SELECT * FROM orders WHERE id=10;
Lost updates occur when two transactions read the same row, compute new values, and write back, with the second write overwriting the first. Each transaction appeared correct alone, but the combined effect drops one change. Prevent it with optimistic concurrency: store a version or timestamp and add it to the WHERE of the UPDATE. If zero rows update, retry with fresh data. Or use SELECT FOR UPDATE to lock the row before computing, which is pessimistic but simple for hot records.
UPDATE accounts SET balance = balance + :delta, version = version + 1 WHERE id = :id AND version = :version;
A CTE created with WITH gives a readable name to an intermediate query and exists only for that statement. Many engines inline it like a subquery. CTEs are about clarity and stepwise logic. Performance depends on the optimizer and data shape, not the syntax alone.
WITH recent AS ( SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' ) SELECT * FROM recent WHERE status = 'PAID';
First, identify each customer’s latest order using ROW_NUMBER over orders partitioned by customer and ordered by date. Filter to rn equals one to keep the latest row. Next, compute the average per category in a separate step. Join the two steps and compare the latest order amount to the category average. This avoids correlated re-computations and keeps the intent simple to tune.
WITH latest AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) rn
FROM orders o
), avg_cat AS (
SELECT category_id, AVG(total) AS avg_total FROM orders GROUP BY category_id
)
SELECT l.customer_id, l.total > a.avg_total AS above_avg
FROM latest l
JOIN avg_cat a ON a.category_id = l.category_id
WHERE l.rn = 1;A pure SQL window cannot maintain a running DISTINCT across changing frames efficiently. One approach is to pre-aggregate to daily unique users per app, then apply a running SUM over those daily counts. This reduces cardinality and keeps the window light. For exact moving distincts at row granularity, use specialized features (e.g., HyperLogLog approx algorithms) or maintain rollups in staging tables. Exact per-row distinct windows tend to be heavy and may require materialization steps.
-- daily uniques first, then window WITH d AS ( SELECT app_id, day, COUNT(DISTINCT user_id) AS du FROM events GROUP BY app_id, day ) SELECT app_id, day, SUM(du) OVER (PARTITION BY app_id ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS du_7d FROM d;
Deferrable constraints allow complex multi-step changes that temporarily violate rules within a transaction. The final state must pass checks at commit. Use this for bulk swaps or cycle updates in parent–child graphs.
ALTER TABLE t ADD CONSTRAINT fk DEFERRABLE INITIALLY DEFERRED;
Composite B-trees are left-prefix. Predicates on leading columns enable seeks. Skipping the first column usually prevents efficient use. Adding a selective range on created_at after equality filters still works well.
CREATE INDEX idx_loc_time ON places(country, city, created_at);
Nested loops shine when the outer side is small and the inner side can be probed by an index fast. Hash joins work well for large inputs and equality joins without indexes. Merge joins are ideal when both sides are ordered on the join keys, avoiding extra sorts. Knowing these trade-offs helps you design indexes and predicates that steer the optimizer toward efficient operators.
-- add index to favor nested loops CREATE INDEX idx_items_order ON order_items(order_id);
Cardinality estimates drive plan choices. If the engine expects far fewer or far more rows than reality, it can pick scans, sorts, or joins that are too expensive. Refresh statistics and increase histogram detail for skew. For filtered workloads, use filtered statistics or extended stats when available.
-- Postgres ANALYZE orders; -- SQL Server UPDATE STATISTICS orders WITH FULLSCAN;
Mask or anonymize in the database before export or restore. Replace direct identifiers (email, phone, name) with deterministic but fake values so joins and uniqueness still work. Randomize quasi-identifiers and dates within safe bands to preserve distribution while breaking re-identification. Automate the process as a repeatable pipeline. Store the masking logic in version control, run it as part of backup-to-test jobs, and restrict access to the pre-masked dump only. Validate that constraints and key relationships remain intact after masking.
UPDATE users
SET email = CONCAT('user', id, '@example.test'),
phone = LPAD(id::text, 10, '0');Preview the effect with a SELECT using the same predicate. Then run the UPDATE or DELETE. Consider transactions and backups when touching many rows. Cascades may propagate changes across tables.
-- preview SELECT * FROM orders WHERE status='CANCELLED'; -- then DELETE FROM orders WHERE status='CANCELLED';
NULL means unknown. Comparisons like col = NULL are unknown, so conditions fail. Use IS NULL and IS NOT NULL. Sort order places NULLs either first or last depending on the engine or explicit NULLS FIRST or LAST. Aggregates skip NULLs except COUNT(*). Use COALESCE to substitute defaults, and design constraints to avoid surprise NULLs where business rules require values.
SELECT COALESCE(phone,'N/A') AS phone FROM users ORDER BY phone NULLS LAST;
COUNT(column) counts only non-NULL entries in that column. COUNT(*) includes every row regardless of NULLs. When you need unique non-NULLs, use COUNT(DISTINCT col).
SELECT COUNT(*) all_rows, COUNT(email) emails FROM users;
Conditional aggregation uses CASE to route rows into different buckets, then aggregates once. It is efficient and expressive. On PostgreSQL, FILTER(...) is an alternative, but CASE works across engines.
SELECT SUM(CASE WHEN status='PAID' THEN amount ELSE 0 END) AS paid_amt, SUM(CASE WHEN status='REFUND' THEN amount ELSE 0 END) AS refund_amt FROM invoices;
A LEFT JOIN with WHERE right.id IS NULL is a classic anti-join. It is readable and can use indexes on the join keys. Be sure the join predicate covers the date range so matched rows are only from 2025. NOT EXISTS with a correlated subquery is also correct. It stops on first match and handles NULLs cleanly. Engines often produce similar plans; prefer the pattern your team uses consistently.
SELECT p.product_id
FROM products p
LEFT JOIN sales s
ON s.product_id = p.product_id
AND s.sale_date >= '2025-01-01' AND s.sale_date < '2026-01-01'
WHERE s.product_id IS NULL;
-- or
SELECT p.product_id
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM sales s
WHERE s.product_id=p.product_id
AND s.sale_date >= '2025-01-01' AND s.sale_date < '2026-01-01'
);RIGHT JOIN keeps all rows from the right input and matches from the left; LEFT JOIN does the opposite. Swapping table order turns one into the other. Most teams standardize on LEFT JOIN for readability and consistency.
SELECT * FROM A LEFT JOIN B ON B.k = A.k; -- same rows as SELECT * FROM B RIGHT JOIN A ON B.k = A.k;
Filters on the right table that go in WHERE will eliminate NULL-extended rows and effectively collapse the join to INNER. Put such filters inside ON so unmatched rows are still preserved. Use WHERE only for predicates that should apply after the join, such as filtering by a left table column.
SELECT c.id, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID' WHERE c.active = 1;
The join expanded each order into one row per item. If you later sum an order level column, it repeats per item and inflates the total. This is a cardinality trap common with one-to-many joins. Fix by aggregating at the right level before joining, or by summing item level amounts instead of order level amounts. Another option is to SELECT DISTINCT at the order level if your goal is only to deduplicate, but pre-aggregation is more precise for sums.
SELECT SUM(oi.line_total) FROM orders o JOIN order_items oi ON oi.order_id = o.id;
EXISTS is a semi-join. It returns true on the first match and stops. It does not duplicate the left row, so you avoid DISTINCT. IN can work if the subquery is deduplicated and non-null. EXISTS is safer and often produces efficient plans with good indexes.
SELECT c.customer_id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );
You can define many CTEs in a query. Later CTEs can read from earlier ones, which helps you build complex logic step by step. This structure also keeps each step testable and easier to reason about during reviews.
WITH s AS ( SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id ), r AS ( SELECT * FROM s WHERE spend > 1000 ) SELECT * FROM r;
First, rank rows per email by a stable order such as created time or an auto id. Use ROW_NUMBER in a CTE to assign one to the “keeper”. This is easy to reason about and test. Then filter to the rows with row number equal to one. You get exactly one row per email without a self join. This pattern is clear and works across engines with window support.
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) rn FROM users ) SELECT * FROM ranked WHERE rn=1;
They protect not only existing rows but also the gaps between index keys that satisfy a range predicate. This blocks phantoms by preventing inserts into the protected range while the unit runs. They appear in engines like InnoDB at Repeatable Read with range scans. They can reduce concurrency, so scope them with selective predicates and good indexes.
-- InnoDB example range SELECT * FROM orders WHERE amount BETWEEN 100 AND 200 FOR UPDATE;
A correlated subquery approach joins employees to a per department maximum and filters on equality. It is portable and simple to read, but can struggle with ties without extra logic. A window function ranks employees inside each department and then you filter to rank one. This is clear, flexible for ties, and usually efficient because it scans once and avoids self joins.
SELECT dept_id, emp_id, salary
FROM (
SELECT dept_id, emp_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
FROM employees
) x
WHERE rn=1;PARTITION BY splits the result set into logical groups. The function runs separately within each partition. ORDER BY sets the sequence inside each partition so the function can compute running or ranked results. Leaving PARTITION BY out means the entire result is a single partition. Leaving ORDER BY out makes ranking undefined and turns many analytics into simple per-partition aggregates.
SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_ts)
LAG reads a value from a previous row in the same ordered partition. When that row does not exist, the default is used. Supplying a safe default keeps arithmetic stable and avoids NULL propagation in first-row calculations.
SELECT day, amount,
LAG(amount, 1, 0) OVER (PARTITION BY product ORDER BY day) AS prev_amt
FROM daily_sales;GROUP BY returns one row per group and removes detail. Window aggregates compute per-row metrics across related rows, so the original detail is preserved. This allows you to display both row-level attributes and group-level statistics in one result.
SELECT order_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS cust_total
FROM orders;Use a conditional expression inside the window aggregate. Wrap the amount in CASE so only qualifying rows contribute to the running sum. The window still spans all rows in order, so non-paid rows appear with the same running total carry-over. This pattern is efficient because the table is scanned once and the condition is applied row by row. It avoids extra joins and keeps the business rule close to the metric.
SELECT invoice_id, status, amount,
SUM(CASE WHEN status='PAID' THEN amount ELSE 0 END)
OVER (ORDER BY issued_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS paid_running
FROM invoices
ORDER BY issued_at;RANGE frames operate on value ranges, not physical row counts. When many rows share the same ORDER BY value, the frame can expand to include all peers. Running totals may jump by batches, and averages might include more rows than you expect. To avoid surprises, use ROWS frames for precise row counts, or include a tiebreaker in ORDER BY like an id column to create a strict ordering. For first and last value, specify an explicit ROWS frame that covers exactly the rows you want.
SUM(amount) OVER ( ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS safe_running
A non-clustered index is a separate structure (key → row locator). It accelerates point lookups, filters, joins, and sorts. Choose columns that are selective and appear often in predicates or ordering to see the most benefit.
CREATE INDEX idx_users_email ON users(email);
Composite indexes are left-prefix. To fully seek, you must filter on the leading column(s). Filtering only on the second column often needs a scan. Plan index column order by your most common predicates and how they combine.
CREATE INDEX idx_users_cc_email ON users(country_code, email);
When a parent row changes or is deleted, the engine must find matching children. An index on the child foreign key avoids full scans and reduces lock time. It also makes child-to-parent joins fast in day-to-day queries.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
On every INSERT, UPDATE, or DELETE, each affected index must be updated. This adds CPU, I/O, and potential page splits. Over-indexed tables show slower bulk loads and heavier lock contention under write-heavy workloads. Mitigate by indexing only what you query often, and by consolidating composite indexes to cover multiple access patterns. For large one-time loads, disable or drop non-essential indexes and recreate them after the load. Batch writes and keep transactions short to reduce blocking.
/* Strategy example: create minimal indexes first, add others after profiling */
EXPLAIN shows how the optimizer intends to execute your SQL: join order, access paths, estimated rows, and operator costs. With ANALYZE, you also see actual rows and timing. You use this to spot scans where seeks are expected, poor join choices, and misestimates that hint at stale statistics or non-SARGable predicates.
EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id=o.customer_id WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';
With input sorted by group keys, a streaming aggregate walks once and emits groups as they change. It avoids building a hash table and reduces memory. Hash aggregate is great for large, unsorted sets but may spill if memory is tight.
SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id; -- index on (customer_id) enables stream aggregate
Filter pushdown means applying selective predicates as close to the base tables as possible. This prevents unnecessary row explosion in joins and reduces the amount of data flowing to sorts, aggregates, and window operations. Smaller inputs improve cache locality, cut memory use, and allow different join algorithms. The effect is multiplicative across multi-join plans, so early reduction often yields the biggest gains.
SELECT ... FROM orders o JOIN items i ON i.order_id=o.id WHERE o.status='PAID' AND o.created_at>=CURRENT_DATE-INTERVAL '30 days';
First make the ORDER BY for the window align with an index to enable ordered scans and reduce sorting. Partition by selective keys to keep frames small. Prefer ROWS frames for predictable work and add a tiebreaker to the order to avoid large peer groups. If you only need aggregates at coarser grains, pre-aggregate in a CTE or temp table before the window step. For time series, consider partitioning or pruning to restrict the scanned time range.
CREATE INDEX idx_sales_pid_day ON sales(product_id, day);
SELECT product_id, day,
SUM(amount) OVER (PARTITION BY product_id ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rt
FROM sales WHERE day >= CURRENT_DATE - INTERVAL '180 days';DEFAULT supplies a value when the insert omits that column. It does not run if you pass an explicit value, including NULL. Combine NOT NULL with DEFAULT for robust domain rules.
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Savepoints let you protect a block of work inside a larger unit. If the block fails, you roll back to the savepoint and continue with other steps. They are useful for best-effort operations like optional inserts or per-row validations during batch processing.
BEGIN; SAVEPOINT s1; -- try some write ROLLBACK TO SAVEPOINT s1; COMMIT;
Use idempotency keys stored with a unique constraint. The client sends a stable key per logical operation. Wrap the insert or update in a transaction that first checks or inserts the key row. If the same key arrives again, the unique constraint forces an upsert path that returns the original result without applying the effect twice. Pair this with atomic upserts on balances or ledger entries and commit once. Keep the key TTL long enough to cover realistic retry windows.
INSERT INTO idempotency_keys(key, result_hash) VALUES(:k, :h) ON CONFLICT (key) DO NOTHING; -- proceed only once; fetch prior result when conflict occurs
EXISTS stops at the first matching row and is not affected by NULLs in the subquery. It works well with an index on the correlated predicate. IN compares a value to a set. If the subquery can yield NULLs, NOT IN can return no rows due to three-valued logic. Deduplicate the subquery or prefer NOT EXISTS.
SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM order_items i WHERE i.order_id = o.id );
Correlated subqueries can execute once per outer row. A join lets the optimizer find an efficient join order and use indexes on the join keys. Ensure the join does not multiply rows unintentionally. Use grouping or semi-join style predicates if you only need existence.
SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.vip = true;
Choose window functions when you need to keep row detail while adding analytics like running totals, ranks, or group totals. Windows compute across related rows but do not collapse the result, so you can show both the raw row and the metric together. GROUP BY is right when you want one row per group. It summarizes and discards the original granularity. Use it for final aggregates, and windows for per-row insights.
SELECT order_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS cust_total
FROM orders;When a parent row changes or is deleted, the engine must verify or locate matching child rows. Without an index, it scans the whole child table, which is slow and holds locks longer. That increases contention and deadlock risk. An index on the foreign key lets the engine seek directly to the children. It speeds joins, shortens lock durations, and keeps hot paths predictable under load.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
LAG peeks backward and LEAD peeks forward along the ordered rows in the same partition. This is perfect for day-over-day changes or trend detection. Because they run inside the same result set, you avoid extra joins and keep the query simpler and faster.
SELECT day, amount,
amount - LAG(amount) OVER (ORDER BY day) AS delta
FROM sales_by_day;ORDER BY sorts ascending by default. Use DESC for reverse order. Without ORDER BY, row order is not guaranteed, even if it looks stable in small tests.
SELECT name FROM customers ORDER BY name;
Aliases are temporary names for columns or tables in a single query. They improve readability and help when the same table is referenced twice. They do not change schema or metadata; they exist only during the statement.
SELECT e.id AS emp_id, d.name AS dept FROM employees e JOIN departments d ON d.id = e.dept_id;
EXISTS can stop on the first match and leverage an index on the right table. That avoids duplicate inflation and extra sorts. JOIN plus DISTINCT may add more work because the left row can multiply before being deduplicated.
SELECT c.id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id=c.id AND o.total>0 );
NOLOCK (read uncommitted) sacrifices correctness for speed. You can see rows twice, miss rows, or read values that roll back later. Use snapshot-based isolation for non-blocking reads while keeping logical consistency.
SELECT * FROM orders WITH (NOLOCK); -- may be inconsistent
GRANT gives a principal rights on objects (tables, views, procedures). REVOKE removes those rights. Grant to roles, then attach users to roles for clean, auditable access.
GRANT SELECT, INSERT ON orders TO app_writer; REVOKE INSERT ON orders FROM app_writer;
PERCENTILE_CONT computes a value at the requested percentile using interpolation across ordered data. It can run as an ordered-set aggregate or as a window function. PERCENT_RANK and CUME_DIST return relative ranks between zero and one. NTILE splits rows into buckets.
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY region) AS p90_amount
If two rows share the same sort key, the engine may return them in any order. Adding a stable tiebreaker like id keeps rankings and running totals deterministic. This prevents odd jumps in running metrics and inconsistent test results.
ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn
First, use ROW_NUMBER over (PARTITION BY customer ORDER BY created_at DESC, id DESC) then filter rn equals one. It is readable and fast with a matching index. Second, use a join to a subquery that selects MAX(created_at) per customer. It can be concise but may miss ties unless you add a tiebreaker. The window approach is safer and easier to extend.
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY created_at DESC, id DESC) rn
FROM orders
)
SELECT * FROM ranked WHERE rn=1;A foreign key enforces referential integrity. Values in the child column must exist in the referenced parent key. This prevents orphan records and maintains valid relationships as data changes.
CREATE TABLE orders ( id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers(id) );
A plain UNIQUE often allows multiple NULLs, which is the desired behavior. If your engine treats NULLs as equal, emulate with a filtered or partial unique index on non-NULL rows. That enforces uniqueness only when a value exists. This pattern keeps flexibility for missing values while preventing duplicates when the field is provided.
-- PostgreSQL CREATE UNIQUE INDEX uniq_users_email ON users(email) WHERE email IS NOT NULL;
Some engines need to confirm row visibility (e.g., via visibility maps) before serving from the index alone. If visibility info is not set, they probe the base table. Regular vacuuming and recent access can improve index-only scans.
-- Postgres example: VACUUM ANALYZE helps visibility maps
Bitmap indexes encode presence bits and combine quickly with AND/OR, ideal for star-schema filters on low-cardinality attributes. They are less suited to heavy OLTP writes due to maintenance overhead.
-- Engine-specific; common in Oracle and some warehouses
A leading wildcard prevents a seek. Use specialized text search: trigram or n-gram indexes (e.g., Postgres pg_trgm with GIN/GIST) or full-text search for token queries. For pure suffix matches, maintain a reverse-indexed column (store reversed strings) and seek on 'nos%'. If search can be constrained to prefix, change UX to anchor the left side (LIKE 'son%') which is index-friendly with B-tree.
-- Postgres trigram CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_trgm_name ON people USING GIN(name gin_trgm_ops); -- or store reversed_name and query WHERE reversed_name LIKE 'nos%';
Multi-Version Concurrency Control keeps old versions to serve snapshots. Readers avoid waiting and do not take long read locks. Writers update a new version and mark old versions for cleanup. This design improves read concurrency but needs vacuuming or GC to reclaim old versions.
-- Snapshot at statement start SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Hints force a specific join, index, or plan shape. They may help in a pinch, but as data grows or distributions change, a forced plan can degrade. Fix root causes first: SARGability, statistics, and indexing. Use hints sparingly and document why.
-- Example (vendor-specific) SELECT /*+ INDEX(o idx_orders_status) */ * FROM orders o WHERE status='PAID';
The optimizer estimates row counts from column and histogram statistics. If stats are stale or too generic, it may think many rows match and pick a scan. Update statistics, increase histogram buckets for skewed columns, or create filtered stats if supported. Also check parameter sniffing and ensure literals or sniffed values reflect common cases. Verify that predicates are SARGable.
-- Postgres ANALYZE users; -- SQL Server UPDATE STATISTICS users WITH FULLSCAN;
Project only needed columns before the GROUP BY using a subquery so fewer bytes flow through sort or hash. If the grouping key has an index that matches the order, use an index-ordered scan to avoid a full sort. Pre-aggregate upstream where possible, or use partial aggregation by partition key. In some engines, materializing to a temporary table and indexing the group keys first reduces spills and memory pressure.
SELECT k, SUM(v) FROM ( SELECT key AS k, value AS v FROM big_table WHERE dt>=:d ) s GROUP BY k;
Capture before-and-after execution plans and compare join order, operator choices, and estimates. Check if new cardinality models or changed defaults altered heuristics. Verify statistics and compatibility settings; sometimes a minor setting flips behavior. As a quick mitigation, use plan freezing or hints while you fix root causes. Then make predicates SARGable, add missing indexes, and consider breaking the query into optimized stages. Document the change and add a regression test so future upgrades keep the better plan.
-- SQL Server EXEC sp_create_plan_guide_from_handle @name='freeze_q', @plan_handle=...;
TCL manages the unit of work. COMMIT makes changes permanent. ROLLBACK undoes the unit. SAVEPOINT lets you roll back partway. DDL often auto-commits in some engines. Be mindful of boundaries so side effects are predictable.
BEGIN; UPDATE accounts SET bal=bal-100 WHERE id=1; SAVEPOINT s1; UPDATE accounts SET bal=bal+100 WHERE id=2; ROLLBACK TO s1; -- optional COMMIT;
2PC uses a prepare phase to ensure all participants can commit, then a commit phase to finalize atomically. It provides ACID across systems at the cost of latency and blocking under failures. Modern systems also use consensus replication to achieve similar guarantees.
-- Coordinator: PREPARE TRANSACTION 'tx1'; then COMMIT PREPARED 'tx1';
You are seeing phantoms or concurrent updates changing rows in the range. Between scans, other units inserted or updated values that match the predicate, so the second read changed. Fix by using a higher isolation level. Serializable prevents phantoms by locking the predicate. Or design the report to read a stable snapshot, such as Repeatable Read in MVCC, or use a time-boxed ETL snapshot table for consistent reads.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT SUM(amount) FROM sales WHERE dt BETWEEN :d1 AND :d2;
Encrypt at rest to protect media theft. Use TLS in transit to protect the wire. For highly sensitive fields, consider app-level encryption or tokenization with secure key management and rotation.
ssl = on -- enable TLS per engine -- TDE or disk encryption configured at server/storage layer
A good audit entry ties user, timestamp, target, and the change. Use append-only audit tables or built-in extensions. Keep the path tamper-evident and searchable.
INSERT INTO audit_log(actor, action, table_name, row_id, old_val, new_val) VALUES (current_user, 'UPDATE', 'orders', :id, :old, :new);
Asynchronous replicas are great for read scale and reporting. They do not slow writes, but can lag. On failover you may lose recent commits equal to the lag. They suit relaxed RPO. Synchronous replicas acknowledge commits only after a replica confirms. This reduces or eliminates data loss at the cost of higher write latency and tighter coupling. Choose per workload, per region, and document expected RPO/RTO.
-- Concept: sync commit waits for replica ACK; async returns after local log flush
Wrap the steps in a transaction so all succeed or all roll back. Insert the detail row, update the aggregate with a precise predicate, then select the final state to confirm. Keep the transaction short to reduce lock time. Check for errors at each step and commit only when all statements succeed. This preserves consistency under concurrent load.
BEGIN; INSERT INTO orders(id, customer_id, total) VALUES(?, ?, ?); UPDATE customers SET order_count = order_count + 1 WHERE id = ?; SELECT order_count FROM customers WHERE id = ?; COMMIT;