A Christmas present: PostgreSQL 9.5 RC1

It's been much-delayed, but PostgreSQL 9.5 RC1 is finally available.  This release contains not only fixes for all known issues in 9.5 features, it also contains an "ultimate solution" to multixact truncation handling.  Multixacts were the cause of multiple bugfix releases in versions 9.3 and 9.4, and several hackers have taken the opportunity in 9.5 to implement a new design for this.  This has been a large part of the delay in releasing 9.5.

So: if you plan to upgrade to 9.5, download and test now.  Final will be released in early January, barring discovery of new ghastly bugs in the RC.

To make it easier for you to test, I've updated the postgres95-test Docker image, ready for you to download and try out whatever you want.

9.5 brings us a lot of cool features which are likely to justify upgrading for you:

• UPSERT
• Row Level Security
• Grouping Sets/CUBE/ROLLUP
• Faster sorts for text
• FDW push-down and partitioning support
• BRIN indexes

If you want to read up on the features so you know what to test:

• What's New in 9.5
• My articles on 9.5 for LWN: Part 1 and Part 2
• Discussion on Multxact fixes
• Guide to 9.5 beta testing

9.4 Beta, Postgres-XL, and pgCon Events

So, in case you somehow missed it, the PostgreSQL 9.4 Beta 1 is out.  Yaay!  Here's what I have to say about that:

libdata=# select title, 
    bookdata #> '{"publication_info", 0, "isbn"}' as isbn
from booksdata 
where bookdata @> '{ "publication_info" : [{"publisher": "Avon"} ] }'::jsonb 
order by bookdata #> '{"publication_info", 0, "price"}' DESC;

                 title                |    isbn    
--------------------------------------+-----------------
 The Bewitched Viking                 | "0-06-201900-7"
 When a Scot Loves a Lady             | "0-06-213120-6"
 Eternal Prey                         | "0-06-201895-7"
 My Irresistible Earl                 | "0-06-173396-2"
...

Download the beta now and test it out!  Break it!  Tell us how you broke it!  It's a beta, and it's up to you to make sure that the final release is as robust as possible.

Speaking of betas, there's a new new open source big data option on the block: Postgres-XL.  This is a fork of PostgresXC, which supposedly resolves the blockers which have kept PostgresXC from being ready for production use.  I look forward to trying it out when I get a chance.

Finally, I wanted to remind everyone about the Clustering Summit, the PostgresXC Pizza Demo, and the Unconference at pgCon next week.   Especially, I still need two assistants to help me with the unconference.  Email me at josh-at-postgresql.org if you're available to help with setup at the unconference.

Testing 9.2: Autovacuum Logging

Since PostgreSQL 9.2 is in beta now, I've been using some generously donated cloud server time on GoGrid to test various features in the new release.  One of the ones which is working brilliantly is the new, more verbose logging for Autovacuum. 

Greg Smith and Noah Misch added additional information to the logging you get when you set log_autovacuum_min_duration.  This includes information on the work done by autovacuum (buffers, pages and tuples read and written), as well as information on when autovacuum "skips" because it can't get a lock.  Here's a sample:

LOG:  automatic analyze of table "bench2.public.pgbench_tellers" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  automatic vacuum of table "bench2.public.pgbench_branches": index scans: 0
        pages: 0 removed, 2 remain
        tuples: 114 removed, 200 remain
        buffer usage: 44 hits, 0 misses, 3 dirtied
        avg read rate: 0.000 MiB/s, avg write rate: 45.334 MiB/s
        system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  automatic analyze of table "bench2.public.pgbench_branches" system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec
LOG:  automatic analyze of table "bench2.public.pgbench_tellers" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

This will go a long way towards helping users troubleshoot autovacuum in the future.  Great work, Greg and Noah!