A curated list of awesome resources to understand PostgreSQL. Inspired by awesome-postgres
This is a WIP, contributions are welcome!
-
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 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 behindpalloc(postgres' replacement ofmalloc), 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
-
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
-
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