Every article tagged PostgreSQL, in one place.
Showing 15 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...
You don't always need a separate vector database. The pgvector extension adds a vector type, distance operators and approximate indexes to PostgreSQL, so embeddings live next to the rest of your data. Set up the table CREATE EXTENSION IF NOT EXISTS vector; CRE...
SQLAlchemy 2.0 brought a cleaner, fully typed API and first-class async support. Combined with the asyncpg driver it's a solid choice for FastAPI apps. Install pip install "sqlalchemy[asyncio]" asyncpg alembic Engine and session # db.py from sqlalchemy.ext.asy...
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...
Vector search is great at meaning ("cancel my subscription" finds "how to end your plan") but weak at exact tokens: error codes, product SKUs, function names. Keyword search is the opposite. Hybrid search runs both and merges the results. Keyword search in Pos...
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...
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...
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...
Exact nearest-neighbour search checks every vector, which gets slow past a few hundred thousand rows. Approximate nearest neighbour (ANN) indexes trade a little recall for a huge speed-up. pgvector offers two: HNSW and IVFFlat . HNSW (Hierarchical Navigable Sm...
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...
