Awesome PostgreSQL resources

A curated list of awesome resources to understand PostgreSQL. Inspired by awesome-postgres

This is a WIP, contributions are welcome!

Contents

• Administration
• Hacking postgres
• Internals
• SQL

Administration

• Autovacuum, explained for engineers
  A presentation describing autovacuum.
  Ilya Kosmodemiansky, 2015

• Common misconceptions about locking in PostgreSQL
  A blog post clarifying locks in PostgreSQL.
  Robert Wysocki, 2015

• It's a view, it's a table... no, it's a materialized view!
  A fairly complete introduction to materialized views.
  Robert Wysocki, 2015

• Postgres autovacuum is not the enemy
  A blog post explaining the rationale behind autovacuum.
  Joe Nelson, 2016

• PostgreSQL monitoring cheatsheet
  A blog post listing some useful metrics when monitoring PostgreSQL.
  Russ Garett, 2015

• PostgreSQL primary key type analysis
  A blog post comparing the performance of primary keys using integer sequences and various UUID generation methods.
  Adam Brusselback, 2016

Hacking postgres

• Hacking PostgreSQL
  A conference presentation that gives an overview of PostgreSQL's source code, some important structures, and other useful info to get started in hacking postgres.
  Stephen Frost, 2018

• Introduction to MemoryContexts
  AllocationSet internals
  Examples of palloc overhead
  How much benefit do we get from Allocation set?
  A series of blog posts explaining the rationale behind palloc (postgres' replacement of malloc), how to use it and its consequences on performance.
  Tomas Vondra, 2014

• Postgresql hooks documentation
  A github repository documenting all function hooks in PostgreSQL
  Begishev Nikita and Goncharov Vladimir, 2018

• Review of Patch Reviewing
  An explanation of how to review a patch in postgres
  Stephen Frost, 2018

• Serverside Programming in C
  A good intro to get started in hacking postgres, including memory management, string handling, error reporting, useful structures, etc.
  Heikki Linnakangas, 2018

• Writing Postgres extensions - the basics
  Writing Postgres extensions - types and operators
  Writing Postgres extensions - debugging
  Writing Postgres extensions - testing
  Writing Postgres extensions - code organization and versioning
  A series of blog posts that explain in details how to code your own extensions
  Manuel Kniep and adjust GmbH, 2015

Internals

• Access path selection in a relational database management system
  The original article describing the query optimizer dynamic algorithm for join order selection, originally implemented in System R but still used in many DBMSs, including PostgreSQL.
  Patricia Selinger et al., 1976

• All you need to know about sorting in Postgres
  An overview of the sorting algorithms used in PostgreSQL and how PostgreSQL chooses which one to use.
  Madusudanan.B.N, 2016

• A look at how postgres executes a join
  A detailed description of join execution.
  Pat Shaughnessy, 2015

• Backend flowchart
  A chart describing the role of each component in the backend. Don't forget to also check the full index that is associated to it.
  PostgreSQL, 2012

• Buffer manager
  A detailed description of the cache handling in PostgreSQL.
  Hironobu Suzuki, 2016

• Explaining CREATE INDEX CONCURRENTLY
  A blog post explaining in great detail how PostgreSQL builds an index without locking the table from updates.
  Pavan Deolasee, 2017

• Explaining the query optimizer
  A presentation introducing the query planner.
  Bruce Momjian, 2016

• Explicit locking
  The section in PostgreSQL docs that explains the various locks available and how they interact with each other.
  PostgreSQL, current

• HOT
  The readme in the PostgreSQL source code that describes in great details how Heap-Only Tuples (HOT) work.
  PostgreSQL, 2012

• Index internals
  A presentation (video available) that describes how the different indexes in PostgreSQL store data.
  Heikki Linnakangas, 2016

• PostgreSQL internals through pictures
  A presentation doing a broad overview of PostgreSQL internals.
  Bruce Momjian, 2016

• PostgreSQL query optimization
  A blog post introducing query evaluation plans and the algorithm to generate them.
  Jincheng Li, 2016

• Query execution techniques in PostgreSQL
  A presentation that describes how plan nodes are executed.
  Neil Conway, 2007

SQL

• Explanation of jsonb introduced by PostgreSQL
  A stackoverflow answer that explains concisely the differences between hstore, json and jsonb formats.
  pozs, 2014

• PostgreSQL cheat sheet: string functions
  A cheat sheet for string operations in PostgreSQL.
  PostgreSQL Backup, 2016

• Postgres window magic
  A presentation detailing the possibilities of window functions.
  Bruce Momjian, 2017

• SQL joins visualizer
  Describe the data you want to get from a join using Venn diagrams, and the corresponding SQL join is generated.
  Alexey Vasiliev, 2016