High-Performance PostgreSQL Applications

PostgreSQL is a very convenient relational database system, and unlike other database solutions, its popularity has been growing steadily over the past two decades. From Netflix to Instagram, Reddit, and Spotify, more and more companies are switching to PostgreSQL because it’s the most advanced open-source relational database system.  
 
If we compare the StackOverflow surveys from 2018 to 2024, we can see that its adoption among professional developers grew from 33% to 51%. Being such a popular option for many database applications, it’s of paramount importance to make sure we are getting the best out of it. 

While starting an application with PostgreSQL is fairly easy, designing a high-performance database application is not a trivial task. Knowing its inner workings can make the difference between a high-performance PostgreSQL application and one that barely crawls.  

In this training, I’m going to explain how PostgreSQL works and how you can avoid the most common issues that can cause performance bottlenecks.

Agenda
 
Day 1

1. Introduction 

    •   PostgreSQL history and evolution
    •   Why has PostgreSQL become such a popular database option?
   
2. PostgreSQL Architecture

    •   Storage engine, shared buffers, OS cache
    •   Connection management and pooling

3. Database modeling
 
    •   Choosing compact column types
    •   Column Tetris
    •   Primary Key options (numerical vs. UUID)
    •   Table relationships (one-to-many, one-to-one, many-to-many)
    •   Indexes (B+Tree, Hash, GIN)
 
4. Logging, statistics and monitoring
 
   •   Logging SQL statements
   •   Slow query log
   •   Execution plans
   •   Auto explain
   •   pg_stat_io, pg_stat_statements, pg_stat_all_tables, and pg_stat_all_indexes
 
5. SQL essentials
 
   •   Beyond SQL:92
   •   Logical operation ordering
   •   JOIN options (Cartesian, Inner, Outer, Lateral)
   •   Subqueries and semi-join optimization 
   •   Derived Tables, CTE (Common Table Expressions)
 
6. Advanced SQL
 
   •   Recursive CTE
   •   Window Functions
   •   Pivot
   •   Upsert and Merge
 
Day 2
 
7. Pagination and projections
 
   •   Offset pagination
   •   Keyset pagination
   •   Streaming
   •   Projection best practices
 
8. Batching and Statement Caching
 
   •   Statement vs. PreparedStatement batching
   •   SQL injection
   •   JDBC batching for INSERT, UPDATE, and DELETE
   •   Statement lifecycle
   •   Statement caching

9. Transactions and concurrency control
 
   •   Race conditions
   •   ACID
   •   Concurrency control
   •   2PL (Two-Phase Locking)
   •   MVCC (Multi-Version Concurrency Control)
   •   Phenomena
   •   Dirty Write,
   •   Dirty Read,
   •   Non-Repeatable Read,
   •   Phantom Read,
   •   Read Skew,
   •   Write Skew,
   •   Lost Update
   •   Isolation levels
   •   Explicit locking (shared, exclusive, advisory locks)
   •   SKIP_LOCKED and NOWAIT
   •   Optimistic locking

Prerequisites/ Required knowledge

It is recommended to bring your own notebook so that you can configure and run tests associated with the material we are going to go through this training.

It is expected that attendees are familiar with Java, Maven, and IDE tools like IntelliJ IDEA or Eclipse.

The attendees can set up the test environment we are going to use during the training by following the instructions provided in the training GitHub repository.

Target audience

This workshop is for any application developer who happens to work with PostgreSQL.