Alone, We can do so little; Together, We can do so much– Helen Keller
PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
PostgreSQL 14 brings a variety of features that help developers and administrators deploy their data-backed applications. PostgreSQL continues to add innovations on complex data types, including more convenient access for JSON and support for non-contiguous ranges of data. This latest release adds to PostgreSQL’s trend on improving high performance and distributed data workloads, with advances in connection concurrency, high-write workloads, query parallelism, and logical replication.
PostgreSQL 14 release was made available last week and we shared our excitement in our earlier blog. In this 9-part blog series, we’ll take a look at what are some of the New Features of PostgreSQL 14, with an emphasis on:
- System level Performance enhancements
- Data level Performance enhancements
- Monitoring improvements
- New Configuration parameters
- My Favourite DBA Features
- Developer Features
- aaaand cool little things…
In this first part, let’s take a look at the System level performance enhancements.
Active connection scalability:
PostgreSQL 14 provides a significant throughput boost on workloads that use many connections, with some benchmarks showing a 2x speedup. Postgres 14 brings significant improvements for Database platforms that need a massive number of concurrent database connections. To lay the groundwork here, the PostgreSQL connection concentration layer relies on processes instead of threads. It has been proven that this architecture has some important benefits, however, this poses an overhead at large connection counts. With the PG14 release, scaling active and idle connections has gotten significantly better, and will be a major improvement for the most demanding applications.
To support MVCC – i.e. information which transactions are visible to a session, PostgreSQL takes “snapshots” frequently. MVCC, which stands for Multi-version concurrency control, is one of the main techniques Postgres uses to implement transactions. MVCC lets Postgres run many queries that touch the same rows simultaneously while keeping those queries isolated from each other. Postgres handles transaction isolation by using MVCC to create a concept called “snapshots”.
This can scale poorly when the number of active connections is very large, especially on systems with many CPU cores. The scalability aspect of snapshots has been enhanced better in PG14.
Many thanks to Andres Freund, a member of the Postgres Core Team and the original author of the feature, ran benchmark tests, with various parameters. PostgreSQL 14 showcased over 2x better performance than in Postgres 13. You can find all the details in Andres Freund’s original post and more code-level information about the improvements.
Bottom-Up BTee Index Deletion:
PostgreSQL 14 will bring “Bottom-Up” index entry deletion, which is targeted at reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes.
The database bloat is expected and normal to some extent, when it gets too bloated, the indexes are less efficient for the following use-cases:
- When EXPLAIN PLAN chooses for an index range scan, more pages have to be scanned
- Index pages cached in RAM includes caching the bloated pages, which is a waste of RAM
- Indexes could have more levels than required
In PostgreSQL 14, there is a new opportunistic cleanup strategy for BTree indexes. When it applies, it can be effective in preventing index bloat. It helps mostly with non-HOT updates where the same rows are updated repeatedly.
In our next part tomorrow, we’ll discuss the Performance enhancements and features around Data handling.