Hand-picked tutorials, guides and examples on Databases — explained simply.
Showing 40 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...
Redis is an in-memory data store known for sub-millisecond responses. It's more than a key-value cache: each data structure solves a specific class of problems elegantly. Strings: cache values and counters SET page:/pricing "<html>..." EX 300 # cache for 5 min...
MongoDB stores data as JSON-like documents in collections . If you know SQL, most concepts map directly; the big shift is designing around how data is read rather than normalising everything. Vocabulary Table → collection . Row → document (stored as BSON, whic...
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...
A cache in front of a slow database query or API can cut response times from hundreds of milliseconds to one. Getting freshness and failure behaviour right is the hard part. Cache-aside (lazy loading) async function getProduct(id) { const key = `cache:product:...
"Schemaless" doesn't mean "no design". In MongoDB, schema design starts from one question: what does the application read together? Embed when data is read together and owned by the parent { "_id": "ord_42", "customerId": 7, "status": "paid", "shippingAddress"...
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(...
When several app instances run the same scheduled job, you often want exactly one of them to do the work. A Redis lock is a simple way to coordinate. Acquire with SET NX PX import { randomUUID } from 'node:crypto'; async function acquireLock(name, ttlMs) { con...
Without an index, MongoDB scans every document in a collection ( COLLSCAN ). Indexes make queries fast, and compound index order matters as much as in SQL. Creating indexes await orders.createIndex({ customerId: 1 }); await orders.createIndex({ status: 1, crea...
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...
Redis Streams are append-only logs with consumer groups. They sit between simple Redis lists and full message brokers like Kafka: persistent, replayable and easy to run. Producing events await redis.xAdd('orders', '*', { // '*' = auto-generated id (timestamp-s...
The aggregation pipeline processes documents through a series of stages, each transforming the output of the previous one. It's MongoDB's answer to SQL's GROUP BY , joins and reporting queries. Revenue per day const revenue = await orders.aggregate([ { $match:...
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 ....
Pub/Sub lets one process publish a message to a channel and every subscribed process receive it instantly. A classic use is keeping WebSocket servers in sync. The problem it solves With three app instances behind a load balancer, user A's WebSocket might be on...
MongoDB itself is flexible about document shape. Mongoose adds schemas, validation, defaults and helpers on top of the official driver, which brings structure to application code. Defining a model import mongoose from 'mongoose'; await mongoose.connect(process...
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...
Ranking millions of players by score with SQL means sorting large tables. A Redis sorted set keeps members ordered by score at all times, so rank queries take microseconds. Adding and updating scores await redis.zAdd('lb:global', [{ score: 1520, value: 'asha'...
Single-document updates in MongoDB have always been atomic. Since version 4.0 (replica sets) and 4.2 (sharded clusters), MongoDB also supports multi-document ACID transactions. Do you need one? Often a good document design avoids the need. Updating an order an...
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 years Redis was BSD-licensed open source. In 2024 Redis (the company) switched new versions to source-available licences. In response, contributors forked the last BSD version as Valkey , hosted by the Linux Foundation. Later, Redis added the open-source A...
Change streams let your application subscribe to changes in a collection, database or entire cluster. Instead of polling, you get each insert, update and delete as it happens. Watching a collection const stream = orders.watch( [{ $match: { operationType: { $in...
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...
Redis keeps data in memory. Whether anything survives a restart depends on its persistence settings, and the right choice depends on how you use Redis. RDB: point-in-time snapshots # redis.conf save 3600 1 # snapshot if at least 1 change in an hour save 300 10...
If your application data already lives in MongoDB, you can store embeddings in the same documents and query them with Atlas Vector Search , without running a separate vector database. Store embeddings with your documents await articles.insertOne({ title: 'Rese...
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...
The default in-memory session store loses every session on restart and doesn't work with more than one server. Redis is the standard fix: fast, shared and with built-in expiry. Setup npm install express-session connect-redis redis import express from 'express'...
Flexible documents are great until a bug writes price: "free" or forgets a required field. MongoDB can validate documents on every insert and update, regardless of which application wrote them. Creating a collection with a validator await db.createCollection('...
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...
Redis lives in RAM, so memory is the resource you'll manage most. Without limits, a growing cache can exhaust the server and get Redis killed. Set a limit # redis.conf maxmemory 2gb maxmemory-policy allkeys-lru Leave headroom on the machine for the OS, client...
skip() and limit() are the obvious way to paginate, but skip(100000) still walks past 100,000 documents. Range-based pagination keeps every page equally fast. Range (keyset) pagination // First page const page = await posts .find({ status: 'published' }) .sort...
