SE Radio 583: Lukas Fittl on Postgres Performance

Partitioning

Data partitioning is a crucial technique for managing large datasets in Postgres. Lukas Fittl explains that partitioning involves dividing a single table into multiple tables on the same server, which is beneficial for handling time series data efficiently 1. This method allows for faster operations, such as dropping partitions quickly. Fittl also distinguishes between partitioning and sharding, noting that sharding distributes data across multiple servers, enhancing scalability.

Partitioning will help you manage large sets of data on a single server better.

--- Lukas Fittl

He emphasizes that replication is another strategy, where additional reader nodes are used to spread out the read workload, although it doesn't necessarily speed up expensive queries 1.

   

Scaling

Scaling databases effectively requires strategic decisions between horizontal and vertical scaling. Lukas Fittl shares insights from his experience with the Citus extension, which allows for running multiple Postgres servers as a single unit, thus enabling transparent query execution across nodes 2. He warns against the trend of making everything a distributed database, as it can slow down small, fast queries due to the overhead of querying multiple nodes.

If you want more scalability, you just add another node.

--- Lukas Fittl

Fittl also discusses backup strategies, highlighting the performance implications of logical backups like pg_dump, which can slow down operations by running queries against the database 3.

   

Replication

Replication in Postgres plays a vital role in achieving scalability, but it comes with limitations. Lukas Fittl explains that replication involves creating full copies of the database to distribute the read workload 1. However, this doesn't necessarily improve the speed of complex queries. He also touches on join performance, noting that the order and method of joining tables significantly impact performance 4.

The most important choice that the Postgres planner has to make is which order to join the tables in.

--- Lukas Fittl

Fittl describes the three join methods—hash joins, merge joins, and nested loop joins—and their respective efficiencies, emphasizing the need for careful planning to optimize database performance 4.