Step-by-step articles on SQL, part of our Databases collection.
Showing 20 articles
PostgreSQL has become the go-to database for startups and enterprises alike. It's open source, reliable, and handles workloads that once needed several different databases. What developers love Rich SQL : CTEs, window functions, UPSERT , RETURNING , lateral jo...
If your MySQL habits date from version 5.x, you're missing features that make queries simpler and schema changes safer. Common table expressions WITH monthly AS ( SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue FROM orders GROUP BY mont...
Indexes are the single biggest lever for query performance. The right one turns a full table scan into a lookup of a few pages; the wrong ones slow down every write for nothing. B-tree: the default CREATE INDEX orders_customer_idx ON orders (customer_id); Work...
Most slow MySQL queries are missing an index or can't use the one that exists. EXPLAIN shows you which. Find slow queries SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5; -- seconds Or query performance_schema.events_statements_summary_by_di...
EXPLAIN shows how PostgreSQL plans to run a query. EXPLAIN ANALYZE actually runs it and adds real timings. It's the most important tool for fixing slow SQL. Run it EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, o.total_paise FROM orders o WHERE o.customer_id = 42 AND...
Duplicate rows creep in through double-clicked buttons, retried imports and missing constraints. Here's how to find them, clean them up and stop them coming back. 1. Find duplicates SELECT email, COUNT(*) AS copies FROM subscribers GROUP BY email HAVING COUNT(...
JSONB stores JSON in a binary format that PostgreSQL can query and index efficiently. It's ideal for data whose shape varies: webhook payloads, settings, product attributes. JSON vs JSONB json stores the text as-is; jsonb parses it into a binary form, drops du...
MySQL's JSON column type validates documents on insert and stores them in an efficient binary format. It's handy for flexible attributes alongside normal columns. Storing and reading CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name V...
Two users buy the last item at the same moment. Without the right transaction design, you sell it twice. These patterns prevent that. Transactions BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHER...
"Deadlock found when trying to get lock; try restarting transaction" is one of the most common MySQL errors in busy apps. It's not a bug in MySQL; it's two transactions waiting on each other. How a deadlock happens Transaction A Transaction B UPDATE accounts ....
Each PostgreSQL connection is a separate server process using memory. Hundreds of app instances each opening twenty connections will exhaust max_connections and slow the database down. Pooling fixes this. Layer 1: the application pool import pg from 'pg'; expo...
Both are mature, fast, open-source relational databases that run some of the biggest sites in the world. For most applications either will work; the differences show up at the edges. Where PostgreSQL leads Richer SQL and types : arrays, ranges, JSONB with powe...
Most schema changes are instant. A few take heavy locks or rewrite entire tables, which can freeze a busy app. Knowing which is which is the key to safe migrations. Always set a lock timeout SET lock_timeout = '5s'; ALTER TABLE orders ADD COLUMN notes TEXT; Ev...
LIMIT 20 OFFSET 100000 looks innocent, but MySQL must read and throw away 100,000 rows to return 20. Deep pages get slower and slower. The problem SELECT id, title, created_at FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000; -- reads 100,02...
For many apps, PostgreSQL's built-in full-text search is good enough to skip running a separate search engine. It handles stemming ("running" matches "run"), ranking and boolean queries. Core concepts SELECT to_tsvector('english', 'The runners were running qui...
On a table with hundreds of millions of rows, the wrong ALTER TABLE can lock writes for hours. MySQL offers several ways to avoid that. 1. Ask for INSTANT ALTER TABLE orders ADD COLUMN gift_message VARCHAR(255) NULL, ALGORITHM=INSTANT; Instant changes only tou...
Window functions compute values across related rows without collapsing them like GROUP BY does. Combined with CTEs, they replace a lot of application-side data crunching. Window function basics SELECT customer_id, id AS order_id, total_paise, ROW_NUMBER() OVER...
MySQL 8.0 reached end of life in April 2026, so it no longer receives security fixes. The current long-term support line is MySQL 8.4 LTS . The new release model LTS releases (8.4) - long support with bug and security fixes only. The right choice for productio...
PostgreSQL 18 was released in September 2025. Here are the changes most relevant to application developers and operators. Asynchronous I/O A new asynchronous I/O subsystem lets PostgreSQL issue several reads at once instead of waiting for each one. Sequential...
A backup you've never restored is a hope, not a backup. Here's how to take consistent MySQL backups and prove they work. Logical backups with mysqldump mysqldump --single-transaction --routines --triggers --events \ --set-gtid-purged=OFF -h db -u backup -p sho...
