Skip to content
Pressentify
Home
Learn
Handbooks
32 print-ready volumes across 6 handbooks
7 volumes
System Design
2 volumes
AWS Production
4 volumes
Python
6 volumes
FastAPI
7 volumes
AI Full-Stack
6 volumes
Django
Topics
14 courses, beginner to production
Python
PostgreSQL
System Design
Django
MongoDB
AWS
AI Full-Stack
Data Structures & Algorithms
JavaScript
React
Docker
FastAPI
Next.js
MySQL
Cheatsheets
8 one-page quick references
Python
PostgreSQL
System Design
Django
MongoDB
AWS
AI Full-Stack
Data Structures & Algorithms
DSA Lab
12 animated lessons, synced to running code
Binary Search
Two Pointers
Maximum Subarray (Kadane’s)
Move Zeroes
Minimum Size Subarray Sum
Roadmap
Study planner
Job hunt
AI Interview
Blog
Sign in
Get started
Cheatsheets
/
PostgreSQL
PostgreSQL cheat sheets
22 sheets · 22 sections
Quick reference
Full course
01
PostgreSQL Fundamentals
1 sheet
PostgreSQL Fundamentals
PostgreSQL as a relational database management system
Core PostgreSQL terminology
Server, database, schema and connection
02
SQL Fundamentals
1 sheet
SQL Fundamentals
SELECT, INSERT, UPDATE, DELETE and RETURNING
WHERE, ORDER BY, LIMIT/OFFSET, GROUP BY, HAVING, DISTINCT
CASE, COALESCE, NULLIF, CAST and type conversion
Aliases, expressions, scalar and aggregate functions
NULL semantics and three-valued logic
Operator precedence and parentheses
03
Joins
1 sheet
Joins
INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN
CROSS JOIN and Self Joins
How Join Predicates Affect Correctness and Performance
One-to-One, One-to-Many and Many-to-Many Joins
Recognizing Accidental Cartesian Products and Duplicate-Row Explosions
Determining Why a JOIN Changes Row Cardinality
04
Subqueries and CTEs
1 sheet
Subqueries and CTEs
Scalar, Correlated and Non-Correlated Subqueries
Using EXISTS and NOT EXISTS Appropriately
IN vs EXISTS and NULL-Related Edge Cases
Common Table Expressions Using WITH
Recursive CTEs and When They Are Appropriate
When a CTE Improves Clarity vs Complicates Optimization
05
Window Functions
1 sheet
Window Functions
OVER(), PARTITION BY and ORDER BY in Window Expressions
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and Value Functions
Running Totals, Moving Averages and Top-N-Per-Group
Window Frames: ROWS and RANGE Conceptually
When Window Functions Beat Self Joins or Nested Queries
06
PostgreSQL Data Types
1 sheet
PostgreSQL Data Types
Numeric Types: smallint, integer, bigint, numeric, real, double precision
Text Types: varchar, char and text
boolean, bytea, UUID, JSON/JSONB, Arrays, Enums and Range Types
Date/Time Types: date, time, timestamp, timestamptz, interval
Why numeric Is Usually Preferred Over money
Implicit vs Explicit Casts and Type-Conversion Behavior
PostgreSQL-Specific Types vs Portable SQL Types
07
NULL, Three-Valued Logic and Data Semantics
1 sheet
NULL, Three-Valued Logic and Data Semantics
NULL as Unknown/Missing, Not an Ordinary Value
IS NULL and IS NOT NULL
How NULL Affects Comparisons, Joins, Aggregates and Boolean Expressions
COALESCE and NULLIF
NOT IN Pitfalls When NULL Values Exist
Designing Schemas So NULL Means Meaningful Absence
08
Constraints and Data Integrity
1 sheet
Constraints and Data Integrity
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK Constraints
Composite Keys and Composite Unique Constraints
DEFERRABLE Constraints and Deferred Validation
ON DELETE and ON UPDATE Behavior
Why Critical Invariants Belong in the Database
Using Constraints to Prevent Duplicate, Orphaned or Invalid Data
09
Schema Design and Data Modeling
1 sheet
Schema Design and Data Modeling
Normalization: 1NF, 2NF, 3NF and When Denormalization Is Justified
Modeling Entities, Relationships, Cardinality and Optionality
Choosing Surrogate vs Natural Keys Deliberately
UUID vs Integer/Bigint Identifiers
Designing Audit Fields: created_at, updated_at, created_by, deleted_at
Soft Deletes: Query, Uniqueness and Cleanup Implications
Designing for Future Changes Without Premature Complexity
10
Primary Keys, Foreign Keys and Relationship Design
1 sheet
Primary Keys, Foreign Keys and Relationship Design
Understanding Referential Integrity
Modeling One-to-One, One-to-Many and Many-to-Many
Using Junction Tables for Many-to-Many Relationships
Understanding Composite Foreign Keys
ON DELETE Options and Their Operational Impact
Detecting Dangerous Cascading Deletes in Production Schemas
11
Views
1 sheet
Views
Standard Views and Their Benefits/Limitations
Materialized Views and REFRESH MATERIALIZED VIEW
When a View Improves Abstraction vs Hides Expensive Queries
Permissions Around Views and Ownership
Materialized-View Refresh Considerations for Production
12
Sequences and Identity Columns
1 sheet
Sequences and Identity Columns
Sequences and Generated Identifiers
GENERATED AS IDENTITY
Sequence Gaps and Why They Are Normally Acceptable
How Sequences Behave With Rollback
Why Application-Level max(id) + 1 Is Unsafe
13
PostgreSQL Functions and Procedures
1 sheet
PostgreSQL Functions and Procedures
SQL Functions and PL/pgSQL Functions
Parameters, Return Types, Variables, Control Flow and Exceptions
Procedures and Transaction-Related Differences
When Database-Side Logic Is Appropriate
Avoiding an Unnecessarily Complex Database Runtime
14
Triggers
1 sheet
Triggers
BEFORE, AFTER and INSTEAD OF Triggers
Row-Level vs Statement-Level Triggers
Appropriate Uses for Triggers
Trigger Ordering and Hidden Side Effects
Why Trigger-Only Workflows Are Harder to Reason About
15
Transactions — Core Competency
1 sheet
Transactions — Core Competency
BEGIN, COMMIT and ROLLBACK
Atomicity and Transaction Boundaries
Autocommit Behavior
Savepoints and Partial Rollback
Transaction Isolation Levels
How Transaction Duration Affects Locks, Vacuum, Bloat and Concurrency
Why Long-Running Transactions Are Dangerous in Production
16
Isolation Levels and Concurrency
1 sheet
Isolation Levels and Concurrency
Dirty Reads, Non-Repeatable Reads, Phantom Reads and Serialization Anomalies
PostgreSQL's Behavior Under Its Isolation Levels
Optimistic vs Pessimistic Concurrency Control
Using SELECT ... FOR UPDATE Appropriately
FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE
NOWAIT and SKIP LOCKED Use Cases
Race Conditions in Inventory, Payments, Counters, Jobs and Uniqueness Workflows
17
MVCC — Must Understand
1 sheet
MVCC — Must Understand
Multi-Version Concurrency Control
Updates Create New Row Versions
Transaction Snapshots and Visibility
Why Readers Generally Do Not Block Writers, and Vice Versa
Dead Tuples
How VACUUM Removes Obsolete Row Versions
18
VACUUM, ANALYZE and Autovacuum
1 sheet
VACUUM, ANALYZE and Autovacuum
VACUUM, VACUUM FULL and ANALYZE
Autovacuum Workers and Why Autovacuum Is Essential
Routine Vacuum vs Table-Rewriting VACUUM FULL
Table and Index Bloat
How Long-Running Transactions Can Prevent Cleanup
Statistics Collection and Stale Statistics
Autovacuum Thresholds and Scale Factors
19
Storage and Table Internals
1 sheet
Storage and Table Internals
Heap Tables
Pages, Tuples and Visibility Information
TOAST for Oversized Values
Why UPDATE Can Increase Table Size
HOT (Heap-Only Tuple) Updates
Why Fillfactor Can Matter for High-Update Workloads
20
Index Fundamentals
1 sheet
Index Fundamentals
Why Indexes Speed Reads but Increase Storage and Write Cost
Index Scan, Bitmap Index Scan and Sequential Scan
Selectivity and Cardinality
Why Indexing Every Column Is Harmful
Composite Index Ordering
Leftmost-Prefix Behavior
Covering and Index-Only Scans
21
B-Tree Indexes
1 sheet
B-Tree Indexes
The Default B-Tree Index
Equality, Range, ORDER BY and Prefix Use Cases
Composite B-Tree Indexes
Partial Indexes and Expression Indexes
Unique Indexes
Index-Only Scan Requirements, by Index Type
22
Other Index Types
1 sheet
Other Index Types
Hash Indexes
GiST and SP-GiST Use Cases
GIN for Arrays, JSONB and Full-Text Search
BRIN for Very Large, Physically Correlated Tables
Choosing Indexes by Operator Class and Workload, Not Habit
01
PostgreSQL Fundamentals
1 sheet
PostgreSQL Fundamentals
PostgreSQL as a relational database management system
Core PostgreSQL terminology
Server, database, schema and connection
04
Subqueries and CTEs
1 sheet
Subqueries and CTEs
Scalar, Correlated and Non-Correlated Subqueries
Using EXISTS and NOT EXISTS Appropriately
IN vs EXISTS and NULL-Related Edge Cases
Common Table Expressions Using WITH
Recursive CTEs and When They Are Appropriate
When a CTE Improves Clarity vs Complicates Optimization
07
NULL, Three-Valued Logic and Data Semantics
1 sheet
NULL, Three-Valued Logic and Data Semantics
NULL as Unknown/Missing, Not an Ordinary Value
IS NULL and IS NOT NULL
How NULL Affects Comparisons, Joins, Aggregates and Boolean Expressions
COALESCE and NULLIF
NOT IN Pitfalls When NULL Values Exist
Designing Schemas So NULL Means Meaningful Absence
10
Primary Keys, Foreign Keys and Relationship Design
1 sheet
Primary Keys, Foreign Keys and Relationship Design
Understanding Referential Integrity
Modeling One-to-One, One-to-Many and Many-to-Many
Using Junction Tables for Many-to-Many Relationships
Understanding Composite Foreign Keys
ON DELETE Options and Their Operational Impact
Detecting Dangerous Cascading Deletes in Production Schemas
13
PostgreSQL Functions and Procedures
1 sheet
PostgreSQL Functions and Procedures
SQL Functions and PL/pgSQL Functions
Parameters, Return Types, Variables, Control Flow and Exceptions
Procedures and Transaction-Related Differences
When Database-Side Logic Is Appropriate
Avoiding an Unnecessarily Complex Database Runtime
16
Isolation Levels and Concurrency
1 sheet
Isolation Levels and Concurrency
Dirty Reads, Non-Repeatable Reads, Phantom Reads and Serialization Anomalies
PostgreSQL's Behavior Under Its Isolation Levels
Optimistic vs Pessimistic Concurrency Control
Using SELECT ... FOR UPDATE Appropriately
FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE
NOWAIT and SKIP LOCKED Use Cases
Race Conditions in Inventory, Payments, Counters, Jobs and Uniqueness Workflows
19
Storage and Table Internals
1 sheet
Storage and Table Internals
Heap Tables
Pages, Tuples and Visibility Information
TOAST for Oversized Values
Why UPDATE Can Increase Table Size
HOT (Heap-Only Tuple) Updates
Why Fillfactor Can Matter for High-Update Workloads
22
Other Index Types
1 sheet
Other Index Types
Hash Indexes
GiST and SP-GiST Use Cases
GIN for Arrays, JSONB and Full-Text Search
BRIN for Very Large, Physically Correlated Tables
Choosing Indexes by Operator Class and Workload, Not Habit
02
SQL Fundamentals
1 sheet
SQL Fundamentals
SELECT, INSERT, UPDATE, DELETE and RETURNING
WHERE, ORDER BY, LIMIT/OFFSET, GROUP BY, HAVING, DISTINCT
CASE, COALESCE, NULLIF, CAST and type conversion
Aliases, expressions, scalar and aggregate functions
NULL semantics and three-valued logic
Operator precedence and parentheses
05
Window Functions
1 sheet
Window Functions
OVER(), PARTITION BY and ORDER BY in Window Expressions
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and Value Functions
Running Totals, Moving Averages and Top-N-Per-Group
Window Frames: ROWS and RANGE Conceptually
When Window Functions Beat Self Joins or Nested Queries
08
Constraints and Data Integrity
1 sheet
Constraints and Data Integrity
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK Constraints
Composite Keys and Composite Unique Constraints
DEFERRABLE Constraints and Deferred Validation
ON DELETE and ON UPDATE Behavior
Why Critical Invariants Belong in the Database
Using Constraints to Prevent Duplicate, Orphaned or Invalid Data
11
Views
1 sheet
Views
Standard Views and Their Benefits/Limitations
Materialized Views and REFRESH MATERIALIZED VIEW
When a View Improves Abstraction vs Hides Expensive Queries
Permissions Around Views and Ownership
Materialized-View Refresh Considerations for Production
14
Triggers
1 sheet
Triggers
BEFORE, AFTER and INSTEAD OF Triggers
Row-Level vs Statement-Level Triggers
Appropriate Uses for Triggers
Trigger Ordering and Hidden Side Effects
Why Trigger-Only Workflows Are Harder to Reason About
17
MVCC — Must Understand
1 sheet
MVCC — Must Understand
Multi-Version Concurrency Control
Updates Create New Row Versions
Transaction Snapshots and Visibility
Why Readers Generally Do Not Block Writers, and Vice Versa
Dead Tuples
How VACUUM Removes Obsolete Row Versions
20
Index Fundamentals
1 sheet
Index Fundamentals
Why Indexes Speed Reads but Increase Storage and Write Cost
Index Scan, Bitmap Index Scan and Sequential Scan
Selectivity and Cardinality
Why Indexing Every Column Is Harmful
Composite Index Ordering
Leftmost-Prefix Behavior
Covering and Index-Only Scans
03
Joins
1 sheet
Joins
INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN
CROSS JOIN and Self Joins
How Join Predicates Affect Correctness and Performance
One-to-One, One-to-Many and Many-to-Many Joins
Recognizing Accidental Cartesian Products and Duplicate-Row Explosions
Determining Why a JOIN Changes Row Cardinality
06
PostgreSQL Data Types
1 sheet
PostgreSQL Data Types
Numeric Types: smallint, integer, bigint, numeric, real, double precision
Text Types: varchar, char and text
boolean, bytea, UUID, JSON/JSONB, Arrays, Enums and Range Types
Date/Time Types: date, time, timestamp, timestamptz, interval
Why numeric Is Usually Preferred Over money
Implicit vs Explicit Casts and Type-Conversion Behavior
PostgreSQL-Specific Types vs Portable SQL Types
09
Schema Design and Data Modeling
1 sheet
Schema Design and Data Modeling
Normalization: 1NF, 2NF, 3NF and When Denormalization Is Justified
Modeling Entities, Relationships, Cardinality and Optionality
Choosing Surrogate vs Natural Keys Deliberately
UUID vs Integer/Bigint Identifiers
Designing Audit Fields: created_at, updated_at, created_by, deleted_at
Soft Deletes: Query, Uniqueness and Cleanup Implications
Designing for Future Changes Without Premature Complexity
12
Sequences and Identity Columns
1 sheet
Sequences and Identity Columns
Sequences and Generated Identifiers
GENERATED AS IDENTITY
Sequence Gaps and Why They Are Normally Acceptable
How Sequences Behave With Rollback
Why Application-Level max(id) + 1 Is Unsafe
15
Transactions — Core Competency
1 sheet
Transactions — Core Competency
BEGIN, COMMIT and ROLLBACK
Atomicity and Transaction Boundaries
Autocommit Behavior
Savepoints and Partial Rollback
Transaction Isolation Levels
How Transaction Duration Affects Locks, Vacuum, Bloat and Concurrency
Why Long-Running Transactions Are Dangerous in Production
18
VACUUM, ANALYZE and Autovacuum
1 sheet
VACUUM, ANALYZE and Autovacuum
VACUUM, VACUUM FULL and ANALYZE
Autovacuum Workers and Why Autovacuum Is Essential
Routine Vacuum vs Table-Rewriting VACUUM FULL
Table and Index Bloat
How Long-Running Transactions Can Prevent Cleanup
Statistics Collection and Stale Statistics
Autovacuum Thresholds and Scale Factors
21
B-Tree Indexes
1 sheet
B-Tree Indexes
The Default B-Tree Index
Equality, Range, ORDER BY and Prefix Use Cases
Composite B-Tree Indexes
Partial Indexes and Expression Indexes
Unique Indexes
Index-Only Scan Requirements, by Index Type
