Postgres

Constraints

• SET CONSTRAINTS
• SET NOT NULL
• EXCLUDE USING
• Composite Primary Keys
• Conditional and expression unique constraints
• Unique set of columns

Outages

• FKs Without Downtime
• Long Transactions
• DEFAULT
• Drop Column With Index
• CHECK Constraints Without Downtime
• UNIQUE Constraints Without Downtime
• Is This DDL Going To Rewrite The Table?
• Renaming A Table Using VIEW

Indexing

• Indexing for LIKE
• Concurrent index during vacuum
• Postgres BRIN index
• No Automatic Index on FKs
• DROP INDEX CONCURRENTLY
• Composed index vs denormalised column index
• REINDEX CONCURRENTLY
• Visualising a btree index
• NULLS NOT DISTINCT

Planning

• A PSQL Planner Primer

Locks

• lock_timeout
• Locks cheatsheet
• Print lock for query
• Deadlock Demonstration
• Advisory Lock
• Drop trigger locks

Triggers

• Get definition of a trigger

Testing

• Postgres Docker Container

Performance

• OR queries
• JSON filtering vs text casting
• Table bloat
• Slow ORDER BY and LIMIT
• Fastest way to find if table is empty
• UUID vs ID
• Unlogged Tables

Internals

• Building From Source
• Profiling Postgres On Linux
• Profiling Postgres On Mac
• Debugging Postgres
• How Postgres Implements Foreign Keys
• Swapping a Table With its Copy
• Page Pruning
• Fillfactor

Disk

• How table data is stored in disk

Extensions

• pg_repack
• pgcrypto
• how extensions are created

Third Party Packages

• pgroll

Transactions

• Transaction Isolation
• Read only transactions

PSQL

• A Basic psql Workflow

Vacuum

• VACUUM Phases
• Replication slots blocking VACUUM
• Faster autovacuum

Replication

• Replication basics

Documentation

• Postgres Documentation Syntax

Fields

• Choosing between varchar and varchar(n)
• Identity columns
• Updating jsonb columns

WAL

• WAL basics

Sequences

• ALTER SEQUENCE OWNED BY
• ALTER SEQUENCE CYCLE
• Querying sequence information

Functions

• Get definition of a function

Isolation

• Temporary tables

Statistics

• table statistics

Schemas

• schemas basics

Permissions

• schemas
• tables
• functions
• roles

SQL

• Mozilla SQL Style Guide