Step-by-step articles on PostgreSQL, part of our SQL collection.
Showing 10 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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
