11 versatile members of a new, shiny PostgreSQL 9.4 soccer team
A lot has been said and posted about the great new release of PostgreSQL – Flexibility, Scalability and Performance. People are talking about these three enhancements of PostgreSQL v9.4 in almost every blogpost/article since its release. As a PostgreSQL solution provider, our team got a chance to be involved in the beta evaluation of PostgreSQL. While people are talking about these three features in PostgreSQL v9.4, our team tried to go a little more in detail to evaluate what is there for a user from workload perspective. One of the key things that we noticed is while PostgreSQL community has welcomed and embraced the change happening in the data community they have strengthened the roots which form the very foundation of RDBMS.
Being a soccer fan myself, when I looked at the new shiny 9.4 version, an interesting soccer team’s analogy came to my mind.
If PostgreSQL v9.4 were a soccer team, then JSONB definitely was the striker of the team scoring a perfect finish. Like everyone else, we were confused what’s the fuss all about? Wasn’t JSON already part of PostgreSQL (since v9.2)? On digging further, we found out that it comes with great enhancement in the way JSON is stored (and hence JSONB). The enhancements also opened doors for adding a greater deal of functions and operators for working with JSON documents. If JSONB was a striker, it was perfectly assisted in mid-field by these operators, functions and GIN indexes. Yes, you can create GIN indexes on JSONB documents. You can choose from two types of indexes depending on the kind of operations that you would be performing on your data. This capability of GIN index comes in addition to performance enhancement in GIN indexes making it compact and faster. I would rely on GIN indexes being my central attacking mid-fielder.
Like in any soccer team you got to be aggressive, but you cannot leave your grounds un-defended. Well PostgreSQL though has gone all out for having noSQL by accepting unstructured data as mainstream data-type, it has not forgotten the importance of reliability and availability and it role these features play in RDBMS world. There are great new features lined-up for my favourite- ‘Streaming Replication’. You can now create a replication slot and forget about estimating max_wal_senders. Further, you can now have a time-lagging replica which can be lagging behind by a few minutes of few hours. I will keep this short as we had done a blog and a hangout on Streaming Replication features in v9.4.
While these enhancements have further strengthened the existing Streaming Replication feature and they can aptly be positioned in full-back position, we have a true ‘sweeper-keeper’ in this team (well take it, I am Neuer fan). Logical Decoding! Yes, some of you may count it as out of the active game for now. But it is slowly building up its position and as the game progress (in releases to come) you will see this feature evolve and be in mid-filed or rather standing in the penalty box during a corner kick. This enhancement in wal_level would enable more verbose details at individual table level to be captured in WAL files. This has prepared grounds for commercial and open source replication tools to make use of WAL-based replication. So far, while choosing logical replication one had to go with trigger based solution which some data-architects and vendors don’t agree to. In such cases, best way to setup reporting DB was to use Streaming Replication, which required me to replicate everything. So the choice always boiled down to either ‘all or nothing’ or ‘go with structural changes in DB to accept triggers for replication’. With logical decoding this dilemma won’t exist anymore! Slony-I community is already excited about it and EnterpriseDB has already announced that next version of their commercial replications solution (xDB Replication Server) will have support for logical decoding. Further to these promises, if you don’t already know Logical Decoding is one of those changes which is slowly taking PostgreSQL towards Bi-Directional Replication capability! When that project is finally merged in PostgreSQL, it would be a game changer. Didn’t I say it is a sweeper in this team?
Another great feature which I think is the defending one from mid-field in the form of ALTER SYSTEM command. It is again helping PostgreSQL retain its ground among DBAs by making it easier to do runtime configuration changes with a simple command. To complete the list of defenders we have enhancements in updatable views. These enhancements would allow more views to be an ‘updatable view’. At the same time security_barier and CHECK option would help you restrict what can be updated.
A set of features which are steering PostgreSQL further and would ‘assist’ in covering more vast workloads and queries, are enhancements in AGGREGATE Features and Materialized Views. In my personal opinion, each of these are holding the winger’s position and helping PostgreSQL cover more vast OLAP workloads and also cater to simpler Data Warehouse requirements. These features include addition of moving aggregates, order set aggregates, aggregate filters, state_data_size parameter in user defined aggregates and performance enhancements in NUMERIC aggregates. While aggregates are holding one of the wings we see another frequently asked for feature in OLAP/DW holding the other wing- i.e. Materialized Views. Too keep it concise- you can now refresh the materialized views concurrently i.e. while you are performing other read queries on the view.
I always feel that no soccer team is complete without a young enthusiastic player (remember Thomas Muller in 2010 WC?). That spot has been taken by pg_prewarm in this release, it is going to help you avoid the cold-reboot issues with buffer cache. You can pre-load entries in cache. This is an innovative tool which is not available even in some of the well-established commercial databases. These are the kind of changes (players) which has made PostgreSQL rise and challenge the title holder in RDBMS world.
There is one great thing about this team, which is adapting to the playing conditions (read as workload). As the game progresses (i.e. as the deployment scenario changes), the team manager can shuffle the roles of strikers and defenders. The JSONB strikers would go back and hold the ground, i.e. be prepared for a new age requirement of storing unstructured (does not that remind of a forward sprinting back to blocking a sudden attack from the opposition?). There will be cases when recoverability and availability features of Streaming Replication will have to lead the attack e.g. in case of BFSI. We feel that this team is well prepared to take on the title holders of RDBMS market!
So here is our list of 11 PostgreSQL features which is going to win the game for you-
• JSONB
• JSON Operators
• GIN Indexes
• Replication Slot
• Replication
• Logical Decoding
• ALTER SYSTEM Command
• pg_prewarm
• REFRESH MATERIALIZED VIEW
• AGGREGATE Enhancements
• View enhancements
At Ashnik, we have kept a very close eye as this team is evolving, progressing, learning new tricks and scoring more goals. In the year 2014, our team has successfully implemented PostgreSQL for our customers who had new database requirements. We also did an Oracle migration and two other successful Oracle migration POCs. Happy times are ahead for PostgreSQL (and soccer) fans!