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:
- Please check out http://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.2
- Read more articles about installing the database in 5 minutes or the ability to manage data grids with vFabric Data Director
- Check out the vFabric Postgres documentation
- Download a trial
- Read an overview and more about the features