Like in the parable of the Blind Men and an Elephant, Postgres code can look like many different applications of computer science depending on where you look: operating systems, transactions, distributed computing, linear programming, information storage and retrieval, etc. In a number of places it also looks like a compiler front end. One might think that lexical scanning (i.e. turning a text...

With every new release of PostgreSQL, there are a range of performance enhancements. Some are system-wide and affect every user, but most are highly specific to a certain use case. In this post, I am going to briefly highlight three improvements in PG12 that speed up certain operations. 1. Minimal decompression of TOAST values TOAST values are values that take up too much space to be stored...

The problem If your database has a large number of small tables, you likely have a lot of wasted space. To demonstrate this, let’s create a table with a single record: create table foo (str text); insert into foo values ('a'); VACUUM foo; Now let’s find out the path of the file containing our data, relative to the data directory: select pg_relation_filepath('foo'); pg_relation_filepath -----------...