PostgreSQL Hands-On Training Day

Hour 1:  PostgreSQL for the Absolute Beginner 

- Why Postgres?
- Running local Postgres vs other install options
- Connecting via GUI/psql
- psql intro
- Users and permissions
- Schemas
- Object and data types
- Extensions

Hour 2 Getting Started with SQL 

- PostgreSQL dialect
- Basic SQL CRUD
- JOIN types
- Arrays, JSON
- Window functions
- CTEs
- plpgsql functions

Hour 3 Postgres DBA Basics

- (very) Brief review of Postgres hosting options
- Backups, dump/restore, basebackup, other backup tools
- Upgrades and versions
- DR & HA concepts and basics
- Logical Replication
- Connection management
- Disk / storage and vacuum

Hour 4 - Postgres Troubleshooting

- Postgres’ internal catalog tables
- Logging
- Killing malicious actors
  - Statement timeout
  - Finding things in pg_stat_activity
- Monitoring
- Where to get help from the Postgres community

Hour 5 - Postgres Configuration and  Performance Tuning

- Postgres architecture and data movement
- Memory configs/tuning
  - Cache hit ratio
  - Shared buffers
  - Working memory
- I/O
- Scaling out reads and writes

Hour 6 - Postgres Query Tuning

- EXPLAIN
- Pg_stat_statements
- Indexes and sample use cases
- Other performance topics, joins, etc