Earlier in April I was in Brooklyn attending pgConf US, one of the largest Postgres conferences on the planet. It was a great chance to get an hands of experience of attending and learning about how large conferences. It was a wonderful opportunity to meet various contributors, developers and committers of PostgreSQL Community. I had an invitation from the organizing committee to moderate the panel discussion on Modern Data Management for Enterprises. The panel had representation from popular noSQL technologies, managed database services and SQL technology. I also got a chance to attend a roundtable discussion among core PostgreSQL contributors. Postgres honchos like Dave Page, Bruce Momjian, Stephen Frost and Simon Riggs were answering various questions from audience about enterprise features of PostgreSQL and upcoming features in v9.6.
The conference had discussions around some of the great use-cases of PostgreSQL like MasterCard, MongoDB’s BI connector and InMobi. Presentations by Robert Haas (Parallel Query) and Magnus Hagander (upcoming 9.6 features) was an exposure to the upcoming features of PostgreSQL . While Robert Haas’ presentation was more focused around parallelism in v9.6 and other parallel features planned in future versions, Magnus discussed many features of upcoming v9.6 in great details.
The new version of PostgreSQL has so many great new features that it is being debated to rename it to version 10.
Key features would be –

• Parallel Query Support – Now Postgres will support parallelism and one query can benefit from multiple CPU and I/O handlers. In Postgres v9.6, sequential scans and some of the aggregation functions can execute with parallel workers. As an enhancement over sequential scans, some joint plans might be able to make use of parallelism. If you are exploring parallel queries, you can  listen to the recording.

• Pushdown for postgres_fdw – Now postgres_fdw, will be able to push down sorting requests, joins and DML operations to remote servers. This, when combined with v9.5 feature where Foreign Tables can be part of Inheritance Tree, allows you to achieve basic sharding with PostgreSQL. We did a hangout on 9.5 feature last year, showing how postgres_fdw works with inheritance tree. In our series of hangouts for v9.6, we would be covering these new enhancements in postgres_fdw. Make sure that you subscribe to our Google+ page and Youtube channel.

• Synchronous Replication – Now with v9.6, Postgres replications setups can be synchronous with ‘remote_apply’ mode. This will make sure that primary Postgres server will wait for confirmation that standby server has applied it. This ensures that while connecting to standby database, clients/applications will be able to read the latest copy of data. In 9.6, there is another great feature which will allow users to set how many standby servers should be synchronous. As of 9.5 you can set a list of standby in synchronous standby configuration, but only one of them is used for synchronously following master (the first one available from the list). Come 9.6, you will be able to have multiple synchronous standby. Both these enhancements put together would mean a much higher availability and better read scalability with Streaming Replication.

• Vacuum Enhancements – v9.6 is going to bring about great changes in way old snapshots are handled. Typically long running queries (which might need access to old snapshots) could block cleanup of dead rows leading to bloats in volatile tables. There was no effective workaround to this issue. But in v9.6, you can set a parameter old_snapshot_threshold, which controls the age of snapshot after which dead rows can be cleaned up. This means that you might end up with “snapshot too old” error, if your query is trying to use a snapshot which has passed that threshold age. Another enhancement in VACUUM is related VACUUM freeze, which would help reduce the cost of anti-wraparound vacuuming on large clusters, especially where majority data remains untouched between two cycles. Basically now there is enough infrastructure to avoid vacuuming of pages where all tuples (rows) are already frozen. Most of you may not be able to see impact of this change, in fact both these changes. But those who operate with very busy cluster with huge amount of write transactions, would benefit from these changes.

• Better Monitoring – A pg_visibility extension will now be available, helping you to examine visibility map and page-level visibility information. There is also a generic facility added for reporting command progress and first command which will make use of that facility in v9.6 is VACUUM. This means there is a new view that you need to know about – pg_stat_progress_vacuum. This can tell you quite a bit of information about long running vacuum operations. In future more commands may be using this facility to report their progress and provide info on what they are doing. Last but not the least in my list of monitoring features in v9.6 is addition of wait_event in pg_stat_activity. Until now a user can only see if a particular session is waiting or not (pg_stat_activity.waititing column). Come 9.6, a user will be able to see what is the kind of lock it is waiting on. These enhancements make life of operations people easier and specially when they are debugging issues and performance bottlenecks in heavily loaded systems.

There are a great lot of other features which are worth more details but in favor of space and brevity I am restricting myself to just mention them. There are performance enhancements like index only scans with partial indexes, new backup API, CASCADE support while CREATE EXTENSION to automatically create dependencies and Phrase support in Full Text Search. Also, if you are looking at Streaming Replication features, do join me during my next hangout on 15th June.
PostgreSQL community has already announced the Beta 1 for PostgreSQL v9.6. To see more details what is new in v9.6, do check this wiki page on PostgreSQL Community Website. If you find these features exciting and would like to try them out and share your feedback with us and PostgreSQL Community, please download a package installer from EDB website or from community download site.
Stay tuned and subscribe to Ashnik’s Youtube channel to get updates and know more about new features in v9.6.