Home > Blogs > VMware vFabric Blog

3 Key Performance and Scale Improvements with VMware vFabric Postgres 9.2

Recently, vFabric Postgres 9.2 launched with additional cloud computing capabilities like elastic memory management. Some of the most compelling new features are performance-related and take linear scaling to new levels.

This article will cover 3 key improvements as listed below:

  • 4x Improvement with vertical linear scaling for reads
  • 2x Improved write efficiency for write ahead logs
  • Index Only Scans and More

4x Improvement with vertical linear scaling for reads

Modern websites are almost all database driven. When consumers browse online retailer catalogs, 99% of the load is reads and 1% of the load is updates to the data on the tables. Even in highly updated websites, the grand majority of load is from reads. In these high-read usage scenarios, the database needs to handle a high read load on certain tables compared to the other tables in the database. We’ve seen this behavior drive enhancements within databases. For example, many application designs started putting a caching mechanism in their application to limit the database hits.

With the improvements in PostgreSQL 9.2 core, vFabric Postgres 9.2 can handle high read transactions on the same table compared to the previous version.

On a small performance study on a 2-socket/12-core/hyperthreaded  and vSphere 5.1, we found that with vFabric Postgres 9.2 with local clients with 24 vCPUs, could do about 310,000 simple read transactions per second on the same table. From a performance perspective, this is more than a 4X improvement compared to vFabric Postgres 9.1 with same number of vCPUs on the same host hardware.

2x Improved write commit efficiency

In the PostgreSQL defaults, every transaction is committed when the changes are flushed to WAL (Write Ahead Log) files.  When there are too many client connections, every client has to get a lock to first write into the WAL buffers. The lock is then flushed out to the WAL files. In PostgreSQL 9.2, the lock contention surrounding the internal lock was significantly reduced. Also, group commit was improved and improves batching up multiple commits automatically. This is especially useful when there are hundreds of connections doing updates. These enhancements result in more than 2X performance improvement in pgbench tests  on same hardware.

Index Only Scans and More

Prior to this release, indexes used the heap table. Even if all needed columns are in the selected index, the database would still need to visit the heap (table) page to verify that the column value is available for access. In 9.2, the system first checks against the visibility map in memory to see if the whole page is visible. If it is, then there is no need to consult the heap page. By doing so, the system cuts down on unnecessary reads from the heap.  This enhancement is particularly useful for queries where the index has records scattered across tables.

Other performance includes improved in-memory sorts, COPY now creates less WAL volume, there are better statistics, and more.

>> For more information:

Jigneshshah About the Author: Jignesh Shah is the Product Manager for vFabric Postgres. He also has interests in database performance  and have been working with Postgres Community for many years. He was also a key member to deliver the first published benchmark with Postgres.

21 thoughts on “3 Key Performance and Scale Improvements with VMware vFabric Postgres 9.2

  1. Pingback: Scaling for the Information Explosion: Master-Slave Cluster with vFabric Postgres 9.2 on vSphere | VMware vFabric Blog - VMware Blogs

Leave a Reply

Your email address will not be published. Required fields are marked *