At EnterpriseDB, we have built our history and reputation advancing community PostgreSQL to meet the demands of the enterprise. With the recent release of our Postgres Plus Advanced Server 9.3, we have continued that tradition. The performance enhancements to partitioning and materialized views that we developed and integrated into the core PostgreSQL 9.3 for our Postgres Plus version 9.3 release represent a key milestone in our enterprise development roadmap.
With our Postgres Plus 9.3 release, announced on Nov. 19, EnterpriseDB has shattered a long-held partitioning barrier that has limited the workloads Postgres could efficiently support. This means EDB’s Postgres Plus can now support workloads with much larger data sets.
Let’s take a closer look at two key features we have implemented in Postgres Plus Advanced Server 9.3 that boost performance.
The practical limit for Postgres had been around 100 partitions per table, beyond which a noticeable degradation in performance would occur. With the new partitioning enhancements in Postgres Plus, tables can now support hundreds and even thousands of partitions per table with orders of magnitude better performance than in the past. By replacing PL/pgSQL trigger based partitioning with C code and implementing a fast partition pruning feature, we have seen INSERT speeds for 1 million records across 1000 partitions that are 450 times faster and SELECTS up to 75 times faster than our prior version.
In summary, our Postgres Plus Advanced Server 9.3 can now handle tables with thousands of partitions with much less impact on performance than either our Postgres Plus 9.2 or PostgreSQL 9.3 for many applications. Of course, your application’s mileage will vary but the magnitude of the improvements warrant taking a second look at partitioning with Postgres Plus if performance was an issue in the past.
One of our engineers, Vibhor Kumar, performed some tests and recently blogged about the results in greater detail.
The materialized views feature allows DBAs to replace slow, resource-intensive runtime queries, complex joins, or lengthy scans of data with simple, faster reads from pre-joined pre-sorted and stored results. This makes it faster and easier for end users who are tapping into frequently used applications, such as reporting apps, or users with data warehouse-like needs, to get results more quickly. Depending on the complexity of the queries and amount of data, the performance improvements can be quite dramatic.
In Postgres Plus, the materialized views feature was also implemented to support Oracle’s Materialized Views syntax. This is yet another way that popular features in Oracle are supported to make using Postgres Plus easy for Oracle or non-Oracle shops – you not only save money with inexpensive open-source based software but you can continue to utilize your Oracle skills as well.
With these new performance boosters, our Postgres Plus can support an even greater range of applications. For companies whose data volumes have increased, Postgres Plus now has the performance to support many more partitioning dependent applications and companies can expand their deployments. Many enterprises seeking to get started with Postgres Plus do so by deploying Postgres to support new or refactored applications or easily migrated applications. As their skills and experience develop and they realize success, enterprises quickly expand their Postgres deployments to support more applications.
Look for further posts in the days and weeks to come as we examine in greater detail some of the enhancements we’ve integrated into the core PostgreSQL to deliver greater performance, flexibility and ease-of-use for the enterprise.
For a more detailed look at new features and capabilities in Postgres Plus Advanced Server 9.3, please review the recording of our recent webinar.
Gary Carter is Director of Product Marketing at EnterpriseDB.Performance Boosters in Postgres Plus Advanced Server 9.3