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

Understanding Unintuitive TABLESAMPLE Results

In 9.5 Alpha 2:

create table mil ( id int, val text );
insert into mil select i, i::text || '-val'  from
generate_series(1,1000000) as gs(i);
analyze;

postgres=# select * from mil tablesample system ( 0.04 );
 id | val
----+-----
(0 rows)

Huh, what?

So, what I didn't understand here is the way rows are selected for TABLESAMPLE SYSTEM.  Since SYSTEM is page-based, I thought that we selected the requested % of pages, and then pick that many pages at random.  Since this table had exactly 185 rows per page, it should return 370 rows every time (2 pages).  But that's not what happened. In fact, running the following query I got a variety of counts:

SELECT count(*) FROM (select * from mil tablesample system ( 0.04 ) ) as a;

370
370
370
555
555
185
0
925

925?  0?  What the hey?

What's really happening is that pages for SYSTEM are selected a different way.  Each page is checked against the probability once.  This means that, while on average you'll get the number of pages you're expecting, the numbers will vary from request to request quite a bit.

This also means that SYSTEM is a bad choice for really small sample sizes, like 0.01%.  BERNOULLI is better, because it'll be checking by row, and therefore the size of the return sample will be much more predictable.  It will still have a bit of variation, though; in my testing, +/- 10% on a few hundred rows.

Gulcin Yildirim has a great explanation of this on the 2nd Quadrant blog.

So, what if you need TABLESAMPLE to get a very specific number of rows for you?  Well, that's why Petr Jelinek wrote the optional (loadable) SYSTEM_ROWS sampling method.  This can be loaded as the tsm_system_rows extension in 9.5.

Hopefully that helps folks be less confused than me about how TABLESAMPLE works.

Time to test 9.5 Alpha 2

PostgreSQL 9.5 Alpha 2 is out.  We're still calling this one an Alpha instead of a Beta, because major replumbing is still underway.  However, most of the issues reported with Alpha 1 have been fixed.  A bunch of really useful bug reports came from regular users who tested out the alpha with their own workloads or tests, so please keep up the good work!

And, if you didn't test Alpha 1, well, we still need your help testing Alpha 2.  Here's some ideas for what to test.

Again, to make testing easier for folks, I've created a docker image of Alpha 2.  If you already run docker, simply run jberkus/postgres95-test and you can test our features without needing to worry about how to install Postgres.  Specific notes on this test image are on the wiki.

As part of updating to alpha2, I managed to shrink the image by about 200MB by getting rid of a lot of build code.  However, I did not remove 150MB of build and compile tools because I'm expecting testers to want to install additional extensions or tools in the image.

Test PostgreSQL 9.5 Alpha in a Docker container

Yay, the first alpha of PostgreSQL 9.5 is out!  This has lots of cool stuff in it, which you can read about elsewhere.  What I'm posting here is a new experiment: offering up Docker images for testing the alphas and betas.

TL:DR = Image here, information and instructions on the wiki.

This occurred to me last week at DockerCon (naturally enough); one of the reasons more people don't test the PostgreSQL beta releases is that it can be a pain to install them, especially with all of the stuff you want to test.  So I've just put a couple days' work into making it easier for you: all you gotta do is install the image and you can test away.

And, since you're testing in a container, you don't have to worry about messing up the PostgreSQL installation on the desktop/laptop/server you normally use.  If you feel like doing your testing in a public cloud, many of them have ways to run containers as cloud servers, so that's easy to do.

I created this image crammed full of stuff: all of contrib, jsquery, pgxn, PL/python and PL/perl, a sample database, etc., so that you'd have something to test.  I wasn't able to include PL/Lua due to build issues, nor PostGIS because I'm building from source, and building PostGIS from source is kinda scary.  If you want to help me build a better test image, though, the Dockerfile project for it is here.

In the meantime, I've removed another excuse for not testing PostgreSQL 9.5.  So what are you waiting for?  Test already!