---

Source: z2h.fyi/cheatsheets/postgresql-fundamentals — Zero to Hero cheatsheets for developers.

PostgreSQL Fundamentals

Quick Overview

PostgreSQL is a powerful open‑source relational database. It supports advanced features like JSONB, full‑text search, and extensions.

Getting Started

• Install PostgreSQL 16 (brew install postgresql@16).
• Start the server: pg_ctl -D /usr/local/var/postgres start.
• Create a test database: createdb mydb.

Core Concepts

Concept	Description
Tables	Structured storage of rows/columns.
Schemas	Namespaces for organizing objects.
Indexes	Speed up lookups, e.g., B‑tree, GIN.
Extensions	Add functionality (PostGIS, pgcrypto).
JSONB	Store semi‑structured data with indexes.

Essential Commands / Syntax

• CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
• CREATE INDEX idx_users_name ON users USING btree (name);
• SELECT * FROM users WHERE name ILIKE '%john%';
• ALTER TABLE users ADD COLUMN meta JSONB;
• CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Common Patterns

• Pagination – LIMIT 10 OFFSET 20.
• Full‑text search – to_tsvector('english', body) @@ plainto_tsquery('search term').
• Upserts – INSERT … ON CONFLICT (id) DO UPDATE SET ….

Gotchas & Tips

• Use EXPLAIN ANALYZE to inspect query plans.
• Prefer parameterized queries to avoid SQL injection.
• Remember that VACUUM reclaims space after many deletes.

Next Steps

• Read the official PostgreSQL documentation (v16).
• Take the “PostgreSQL for Developers” course on Udemy.