PostgreSQL 9.2: A rock-solid component for your Cloud infrastructure

by Marcos Ortiz

PostgreSQL 9.2 is out

I opened my mailbox today and for my surprise, I received great news from Selena Deckelmann, one of the main contributors to the excellent database management system (Did I say to you that this is my favorite? Proud user since version 8.0), announcing that PostgreSQL 9.2 was released today, with a lot of new features, major improvements, and bug fixes.

With this new release, PostgreSQL have become in a great choice for web platforms hosted in Cloud services like Amazon Web Services for example, with many amazing services that offer to PostgreSQL like one of the databases that are ready to be used. But I will talk about that later. Right now, I let you the list of the major improvements in this new version of PostgreSQL:

Native support for JSON and Range types

“Native JSON support in PostgreSQL provides an efficient mechanism for creating and storing documents for web APIs. We use front-end libraries like jQuery to request tabular and tree-structured data; and the new features make it convenient and provide performance advantages in retrieving that data as JSON “

said Taras Mitran, Senior Architect at IVC Inc.

Range Types allow developers to create better calendaring, scientific, and financial applications. No other major SQL database supports this feature, which enables intelligent handling of blocks of time and numbers.

Covering indexes

From PostgreSQL’s wiki:

Index-only scans are a major performance feature added to Postgres 9.2. They allow certain types of queries to be satisfied just by retrieving data from indexes, and not from tables. This can result in a significant reduction in the amount of I/O necessary to satisfy queries. During a regular index scan, indexes are traversed, in a manner similar to any other tree structure, by comparing a constant against Datums that are stored in the index. Btree-indexed types must satisfy the trichotomy property; that is, the type must follow the reflexive, symmetric and transitive law. Those laws accord with our intuitive understanding of how a type ought to behave anyway, but the fact that an index’s physical structure reflects the relative values of Datums actually mandates that these rules be followed by types. Btree indexes contain what are technically redundant copies of the column data that is indexed.

Read more here

Performance and replication improvements

With the addition of linear scalability to 64 cores, index-only scans and reductions in CPU power consumption, PostgreSQL 9.2 has significantly improved scalability and developer flexibility for the most demanding workloads. Organizations like the U.S. Federal Aviation Administration and Heroku.com run applications on PostgreSQL, and HP has adopted it for their remote support software and to power their HP-UX/Itanium solutions.

Improvements in vertical scalability increase PostgreSQL’s ability to efficiently utilize hardware resources on larger servers. Advances in lock management, write efficiency, index-only access and other low-level operations allow the database engine to handle even larger-volume workloads.

Numerically, this means:

  • Up to 350,000 read queries per second (more than 4X faster)
  • Index-only scans for data warehousing queries (2–20X faster)
  • Up to 14,000 data writes per second (5X faster)

Also, the addition of cascading replication enables users to run even larger stacks of horizontally scaled servers under PostgreSQL 9.2.

PostgreSQL and the Cloud

The adoption of PostgreSQL have grown in the last years and the cloud have played a key role on it. There are good services that rely on PostgreSQL like:

  • Heroku Postgres: this service began in 2011 and it has grown until to become on one of the first choices when you want a great database system ready to be used, fast and secure. They have several plans for almost every type of user, so you can see it by yourself. The main contact here is Matthew Soldo, who is the Product Manager at Heroku for this service, or you can see the presentation on the last PgCon 2012 from the Technical Lead for this service, Peter van Hardenberg.
  • Engine Yard: Now in the EngineYard Cloud service, PostgreSQL is the default database.
  • “PostgreSQL 9.2 will ship with native JSON support, covering indexes, replication and performance improvements, and many more features. We are eagerly awaiting this release and will make it available in Early Access as soon as it’s released by the PostgreSQL community,”
  • said Ines Sombra, Lead Data Engineer, Engine Yard.
  • EnterpriseDB Cloud Database: EnterpriseDB put a Database-as-a-Service platform, ready to work with Amazon Web Services, OpenStack or the HP Cloud Servicess platform.

So, there are good choices to work with PostgreSQL in the Cloud, just you should evaluate the plans, services for each and choose the best one for your needs.

What are you waiting for?

Well, don’t waist more time and try this new release in your PC, MacBook or wherever you want, and give me your comments about this new version. Then, if you are a Database Architect or a Solution Architect that want to use PostgreSQL 9.2 in the Cloud, stay tuned with these services for its new announcements.

Happy Hacking !!! Marcos Luis Ortíz Valmaseda about.me/marcosortiz @marcosluis2186