Postgresql

Quick reference for everything in this series. Bookmark this page. PostgreSQL Docker Quick Start docker run -d \ --name postgres \ -p 5432:5432 \ -e POSTGRES_USER=myuser \ -e POSTGRES_PASSWORD=mypassword \ -e POSTGRES_DB=mydb \ postgres:17 # Connect psql postgresql://myuser:mypassword@localhost:5432/mydb DDL CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ALTER TABLE users ADD COLUMN phone TEXT; ALTER TABLE users DROP COLUMN phone; ALTER TABLE users RENAME COLUMN name TO full_name; DROP TABLE users; DROP TABLE IF EXISTS users; -- Copy table structure CREATE TABLE users_backup AS SELECT * FROM users WHERE false; DML -- Insert INSERT INTO users (email, name) VALUES ('alex@example.com', 'Alex'); INSERT INTO users (email, name) VALUES ('sam@example.com', 'Sam') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -- Select SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY name LIMIT 10 OFFSET 20; SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id; -- Update UPDATE users SET name = 'Alex J' WHERE id = 1 RETURNING *; -- Delete DELETE FROM users WHERE created_at < NOW() - INTERVAL '1 year' RETURNING id; Useful Queries -- Table sizes SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; -- Slow queries (requires pg_stat_statements) SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; -- Lock monitoring SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event IS NOT NULL; -- Index usage SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan; -- Vacuum and analyze VACUUM ANALYZE users; Connection Strings # Standard postgresql://user:password@host:5432/database # With SSL postgresql://user:password@host:5432/database?sslmode=require # Connection pool (PgBouncer) postgresql://user:password@pgbouncer-host:6432/database MongoDB Docker Quick Start docker run -d \ --name mongodb \ -p 27017:27017 \ -e MONGO_INITDB_ROOT_USERNAME=admin \ -e MONGO_INITDB_ROOT_PASSWORD=password \ mongodb/mongodb-community-server:8.0 # Connect mongosh "mongodb://admin:password@localhost:27017" CRUD // Insert db.users.insertOne({ name: "Alex", email: "alex@example.com" }) db.users.insertMany([{ name: "Sam" }, { name: "Jordan" }]) // Find db.users.find({ age: { $gte: 18 } }).sort({ name: 1 }).limit(10) db.users.findOne({ email: "alex@example.com" }) db.users.countDocuments({ active: true }) // Update db.users.updateOne({ _id: id }, { $set: { name: "Alex J" } }) db.users.updateMany({ active: false }, { $set: { archived: true } }) db.users.findOneAndUpdate({ _id: id }, { $inc: { score: 10 } }, { returnDocument: "after" }) // Delete db.users.deleteOne({ _id: id }) db.users.deleteMany({ createdAt: { $lt: cutoffDate } }) // Upsert db.users.updateOne({ email: "new@example.com" }, { $set: { name: "New" } }, { upsert: true }) Query Operators $eq, $ne, $gt, $gte, $lt, $lte // comparison $in, $nin // in/not in array $and, $or, $nor, $not // logical $exists, $type // element $regex // string match $where // JavaScript expression (slow) $elemMatch // match array element Connection Strings # Standard mongodb://user:password@host:27017/database?authSource=admin # Replica set mongodb://user:pass@host1:27017,host2:27017,host3:27017/database?replicaSet=rs0 # Atlas mongodb+srv://user:password@cluster.mongodb.net/database Redis Docker Quick Start docker run -d \ --name redis \ -p 6379:6379 \ redis:8 # Connect redis-cli Commands by Data Type # String SET key value EX 300 # with TTL in seconds GET key INCR counter MSET k1 v1 k2 v2 MGET k1 k2 # Hash HSET user:1 name "Alex" email "alex@example.com" HGET user:1 name HGETALL user:1 HINCRBY user:1 score 10 # List LPUSH list val # push left RPUSH list val # push right LRANGE list 0 -1 # get all LPOP list / RPOP list # pop # Set SADD myset val SMEMBERS myset SISMEMBER myset val SINTER s1 s2 / SUNION s1 s2 # Sorted Set ZADD leaderboard 1500 "alex" ZRANGE leaderboard 0 -1 WITHSCORES ZREVRANGE leaderboard 0 9 # top 10 ZINCRBY leaderboard 100 "alex" # Key management TTL key # seconds remaining EXPIRE key 3600 # set TTL PERSIST key # remove TTL DEL key [key ...] EXISTS key KEYS pattern # NEVER in production — use SCAN SCAN 0 MATCH "user:*" COUNT 100 Connection Strings # Standard redis://localhost:6379 # With password redis://:password@localhost:6379 # With database selection redis://localhost:6379/1 # TLS rediss://user:password@host:6380 SQLite Quick Start (Python) import sqlite3 conn = sqlite3.connect("myapp.db") conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA foreign_keys=ON") conn.execute(""" CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL ) """) conn.commit() Quick Start (Node.js) import Database from "better-sqlite3"; const db = new Database("myapp.db"); db.pragma("journal_mode = WAL"); db.pragma("foreign_keys = ON"); db.exec(`CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL )`); const insert = db.prepare("INSERT INTO users (email, name) VALUES (?, ?) RETURNING *"); const user = insert.get("alex@example.com", "Alex"); Choosing a Database Use Case Best Choice Web app with relational data PostgreSQL Flexible/nested documents MongoDB Caching, sessions, rate limiting Redis Mobile app, desktop app, CLI SQLite Time-series metrics TimescaleDB (PostgreSQL extension) Full-text search at scale Elasticsearch or PostgreSQL FTS Edge/serverless Turso (SQLite), PlanetScale, Neon Complete Series # Article 1 SQL vs NoSQL — When to Use What 2 PostgreSQL Setup and Basics 3 PostgreSQL — Advanced Queries 4 PostgreSQL Indexing and Performance 5 PostgreSQL JSON and Full-Text Search 6 PostgreSQL Transactions and Concurrency 7 PostgreSQL Migrations and Schema Design 8 PostgreSQL Replication and High Availability 9 MongoDB Setup and CRUD 10 MongoDB Data Modeling 11 MongoDB Aggregation Pipeline 12 MongoDB Indexing and Performance 13 Redis Setup and Data Types 14 Redis Caching Patterns 15 Redis Pub/Sub and Streams 16 Redis Best Practices and Production 17 SQLite — When and How to Use It 18 Database Design Patterns 19 ORMs vs Raw SQL — Prisma, SQLAlchemy, GORM 20 Database Cheat Sheet 2026 (this article)

Good schema design is not about theory. It is about patterns that solve real problems — audit trails, multi-tenancy, concurrent updates, and data history. These patterns work with any relational database. Soft Deletes Hard deleting rows permanently removes data. Soft deletes mark rows as deleted without removing them — useful for audit trails, undo functionality, and regulatory compliance. ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ; -- Soft delete UPDATE users SET deleted_at = NOW() WHERE id = 42; -- Query active users only SELECT * FROM users WHERE deleted_at IS NULL; -- Include deleted users SELECT * FROM users; -- Restore UPDATE users SET deleted_at = NULL WHERE id = 42; Create a partial index for performance on active records: ...

A database query takes 50ms. The same query through Redis takes 0.1ms. Caching removes load from your database and makes your application faster. Why Cache? Reduce database load — fewer queries hit the database Lower latency — in-memory reads are 100-500x faster than disk reads Absorb traffic spikes — cache handles burst traffic without scaling the database Caching adds complexity. Only cache what you need to. Cache-Aside (Lazy Loading) The most common pattern. Read from cache first; fall back to database on miss. ...

A single database server is a single point of failure. If it goes down, your application goes down. Replication gives you a copy of your data on another server — for failover, for read scaling, and for backups. How Streaming Replication Works PostgreSQL primary-replica replication works through the Write-Ahead Log (WAL). Every change to the primary is recorded in WAL files. The replica connects to the primary, streams those WAL records, and replays them to stay in sync. ...

Your schema is the foundation of everything. Bad schema decisions are expensive to fix later. Good ones make your application easier to build and scale. Why Migrations? A migration is a versioned SQL file that changes your schema. Instead of running ALTER TABLE manually in production and hoping everyone does the same, migrations track every schema change in version control. Every team member applies the same migrations in the same order. Your CI pipeline applies them automatically. Rollbacks are possible. ...

Two users buy the last item in stock at the same time. Your app charges a card, then the payment service fails. A report runs while someone is editing data. These are concurrency problems. Transactions solve them. What is a Transaction? A transaction is a group of SQL statements that execute as one unit. Either all succeed or none apply. BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; If anything fails between BEGIN and COMMIT, call ROLLBACK to undo everything: ...

PostgreSQL is not just a relational database. It has first-class JSON support and a powerful full-text search engine built in. No separate search service needed for most applications. jsonb vs json PostgreSQL has two JSON types. Always use jsonb. json jsonb Storage Text, preserves whitespace Binary, compressed Indexing Not indexable GIN index supported Query speed Slow (re-parses on each read) Fast Key order Preserved Not preserved CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, metadata JSONB ); INSERT INTO products (name, metadata) VALUES ('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "ssd": 512}, "tags": ["electronics", "work"]}'), ('Phone', '{"brand": "Apple", "specs": {"ram": 8, "ssd": 256}, "tags": ["electronics", "mobile"]}'); jsonb Query Operators -- -> returns a JSON value (keeps JSON type) SELECT metadata -> 'brand' FROM products; -- "Dell" -- ->> returns text SELECT metadata ->> 'brand' FROM products; -- Dell -- #> for nested paths (returns JSON) SELECT metadata #> '{specs, ram}' FROM products; -- 16 -- #>> for nested paths (returns text) SELECT metadata #>> '{specs, ram}' FROM products; -- 16 -- @> containment: does the left side contain the right? SELECT * FROM products WHERE metadata @> '{"brand": "Dell"}'; -- ? key exists SELECT * FROM products WHERE metadata ? 'brand'; -- ?| any of these keys exist SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'price']; -- ?& all of these keys exist SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'specs']; Updating jsonb -- Replace a key UPDATE products SET metadata = jsonb_set(metadata, '{brand}', '"Lenovo"') WHERE id = 1; -- Add a new key UPDATE products SET metadata = metadata || '{"price": 999}' WHERE id = 1; -- Remove a key UPDATE products SET metadata = metadata - 'price' WHERE id = 1; -- Update nested value UPDATE products SET metadata = jsonb_set(metadata, '{specs, ram}', '32') WHERE id = 1; Indexing jsonb A GIN index makes containment queries (@>) and key existence (?) fast: ...

Your query works. But it is slow. The fix is almost always an index. This tutorial covers how indexes work in PostgreSQL, when to use them, and how to confirm they are actually helping. How a B-tree Index Works Without an index, PostgreSQL reads every row in the table to find matches. This is called a sequential scan. For a table with 10 million rows, that means 10 million comparisons. ...

Basic SELECT queries only get you so far. Real applications need joins, aggregations, and complex filtering. This tutorial covers the SQL features you will use every day. We assume you have PostgreSQL running. See PostgreSQL Setup and Basics if you need to set that up first. Sample Schema Let’s use a simple e-commerce schema for all examples: CREATE TABLE users ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, name text NOT NULL, email text UNIQUE NOT NULL, created_at timestamptz DEFAULT NOW() ); CREATE TABLE orders ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, user_id uuid REFERENCES users(id), status text NOT NULL DEFAULT 'pending', total decimal(10, 2) NOT NULL, created_at timestamptz DEFAULT NOW() ); CREATE TABLE order_items ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, order_id uuid REFERENCES orders(id), product text NOT NULL, quantity integer NOT NULL, price decimal(10, 2) NOT NULL ); JOINs JOINs combine rows from multiple tables based on a related column. ...

PostgreSQL is the most popular open-source database in the world. It is fast, reliable, and packed with features. This tutorial gets you up and running with PostgreSQL 17. We use Docker so you do not need to install PostgreSQL on your computer. Start PostgreSQL with Docker docker run -d \ --name postgres17 \ -e POSTGRES_PASSWORD=secret \ -e POSTGRES_USER=admin \ -e POSTGRES_DB=myapp \ -p 5432:5432 \ postgres:17 This starts PostgreSQL 17 in the background. Let’s break down the options: ...