PostgreSQL for Developers

Section 1: Lab: Getting Started with PostgreSQL 18 on Docker

• Why developers choose PostgreSQL and how it differs from MySQL and SQL Server
• What is new in PostgreSQL 18 for developers, and a look at what PostgreSQL 19 will bring
• Install PostgreSQL 18 with Docker Compose, with volumes and sensible defaults
• Work fluently in psql and manage databases with pgAdmin 4 or DBeaver
• Databases, schemas, roles and basic privileges for an application

Section 2: Choosing the Right Data Types

• Identity column primary keys compared with uuid and uuidv7() in PostgreSQL 18
• numeric versus float for money, and timestamptz versus time zone bugs
• text, varchar and collations, and how they affect sorting and searching Thai text
• Arrays, enums, ranges and domains, and the caveats of each
• Common pitfalls when moving data types over from MySQL or SQL Server

Section 3: Schema Design and Constraints

• Normalizing just enough, and where denormalizing is worth it
• Protect data with NOT NULL, CHECK, UNIQUE and foreign keys with ON DELETE
• Exclusion constraints to prevent overlapping bookings
• Virtual generated columns (the default in PostgreSQL 18) compared with stored ones
• Lab: design an online store schema with a full set of constraints

Section 4: Lab: Modern SQL in Practice

• CTEs and recursive CTEs for hierarchical data
• Window functions: ROW_NUMBER, RANK, LAG, LEAD and running totals
• LATERAL joins, FILTER and GROUPING SETS for multi-dimensional reports
• INSERT ... ON CONFLICT and MERGE, including MERGE ... RETURNING
• The new RETURNING that can reference OLD and NEW values in PostgreSQL 18

Section 5: Lab: JSONB and Full-Text Search

• When to store data as JSONB and when to split it into columns
• JSONB operators, jsonpath and GIN indexes
• Turn JSON into rows with JSON_TABLE and build JSON with SQL/JSON constructors
• Full-text search basics: tsvector, tsquery and ranking results
• Lab: search products by JSONB attributes and description text

Section 6: The Query Planner and EXPLAIN

• How the planner chooses a plan: statistics, cost and ANALYZE
• Read EXPLAIN (ANALYZE, BUFFERS) line by line to find where time goes
• Seq scans, index scans, bitmap scans and the different join methods
• Enable pg_stat_statements to find the queries with the highest total time
• Lab: track down slow queries in a sample app and measure before and after

Section 7: Lab: Designing Indexes That Match Your Queries

• Composite B-tree indexes and getting the column order right
• Partial, expression and covering indexes with INCLUDE
• B-tree skip scan in PostgreSQL 18 and what it changes for index design
• Choosing GIN, GiST or BRIN for the data type at hand
• Why an index is not used, and the cost of having too many indexes

Section 8: Transactions, MVCC and Locking

• How MVCC works and why VACUUM is needed
• Isolation levels: Read Committed, Repeatable Read and Serializable
• Row locks, SELECT ... FOR UPDATE and finding locks that are held too long
• What causes deadlocks and how to write code that avoids them
• Lab: build a job queue with FOR UPDATE SKIP LOCKED

Section 9: PL/pgSQL, Procedures and Triggers

• Write functions and procedures in PL/pgSQL with error handling
• Triggers for audit logs and keeping updated_at current
• SQL functions compared with PL/pgSQL, and function volatility
• Criteria for deciding which logic belongs in the database and which in the app

Section 10: Views, Materialized Views and Partitioning

• Views to hide complexity and limit what data is visible
• Materialized views and REFRESH ... CONCURRENTLY
• Declarative range and list partitioning for large tables
• Partition pruning and choosing a partition key that fits the queries
• Lab: partition a log table by month and test the result

Section 11: Connecting from Applications Safely and Fast

• The cost of a connection and the idea behind connection pooling
• PgBouncer from the developer's side: session versus transaction mode and their limits
• Prepared statements, parameterized queries and preventing SQL injection
• Setting timeouts and handling database errors in code
• Lab: put PgBouncer in front of the app and test it under many concurrent requests

Section 12: Lab: ORMs and Migrations with Node.js

• Connect PostgreSQL to Node.js and TypeScript with Prisma and Drizzle
• Versioned migrations and changing a schema without long table locks
• The N+1 query problem and how to spot it in the logs
• When to stop relying on the ORM and write raw SQL safely

Section 13: Lab: Row-Level Security and LISTEN/NOTIFY

• Design a multi-tenant application with Row-Level Security
• Write policies and pass the user's tenant from the app into the database
• Test that each tenant's data cannot leak to another
• LISTEN/NOTIFY for pushing real-time change notifications to the app

Section 14: pgvector and AI Assistants for SQL

• Store embeddings in a vector column with the pgvector extension
• Build an HNSW index and run similarity search with distance operators
• Combine vector search with ordinary SQL filters in one application
• Use AI assistants to write and explain SQL, and verify the result before using it
• Lab: find similar products from embeddings of their descriptions

Section 15: Lab: Testing and Capstone Project

• Test database code with Docker and repeatable seed scripts
• Capstone: design the database of a small app from schema to indexes
• Measure with EXPLAIN and tune until it meets the targets you set
• Present the work, review it together and wrap up with a pre-production checklist