Stopping expensive queries before they start

Today we're releasing a code for a small PostgreSQL module called plan_filter that lets you stop queries from even starting if they meet certain criteria. Currently the module implements one such criterion: the estimated cost of the query.

After you have built and installed it, you add a couple of settings to the postgresql.conf file, like this:

shared_preload_libraries = 'plan_filter'
plan_filter.statement_cost_limit = 100000.0

Then if the planner estimates the cost as higher than the statement_cost_limit it will raise an error rather than allowing the query to run.

This module follows an idea from a discussion on the postgresql-hackers mailing list some time ago. It was developed by PostgreSQL Experts Inc for our client Twitch.TV, who have generously allowed us to make it publicly available.

Raspberry Pi 2 coming to the buildfarm

Yesterday I ordered a Raspberry Pi 2 Model B, and it should be delivered in a few days. I'm intending to set it up as a buildfarm member. The fact that you can purchase a general purpose computer the size of a credit card with  a quad-core processor and 1Gb of memory (I remember when RAM was counted in kilobytes) and all for USD35.00 is amazing, even when you remember Moore's Law.

I feel so proud :-)

Statistics and ordering operations on JSON fields

The JSON type is not provided with any comparison operators built in, unlike the new JSONB type. One reason for this is that it's not really clear how to do the comparison. In particular, comparing the text values seems wrong, because the white space should not matter, and arguably the order of object keys should not matter either. JSONB doesn't have these problems because it dissolves all the white space and stores object keys in a canonical order, so we have come up with sane if slightly counter-intuitive comparison operations.

This limitation on JSON is somewhat irksome, however. It restricts you from doing some operations on JSON such as DISTINCT, GROUP BY, and ORDER BY.

Another issue is that it causes ANALYZE not to create any rows at all in pg_statistic for JSON columns, so even if all you want to know is the average column width, you can't find it. That makes doing things like measuring table bloat just about impossible.

If you have PLV8 available, you can create operators that work fairly sanely on JSON and that let you generate stats, use DISTINCT etc. The steps are outlined here.

But what if you don't want to load PLV8 just for this? Or what of you can't, like say on a managed service that doesn't provide it? All is not lost.  Here is a version which uses text comparison instead of a PLV8 function.  That means you don't need to have PLV8 loaded. Since it uses text comparison, it is subject to the caveats mentioned about about white space and object keys. But it will work, and you will see rows for the column in pg_statistic. Just be careful using ordering operations or creating indexes, as the results, while consistent, might be surprising.

Moving PLV8 to ECMAScript6

Taras Mitran has just published a blog post about using some of the advanced features of ECMAScript version 6 to make programming with SQL in JavaScript nicer.

He notes that Template Strings allow for multiline strings, which is in itself a significant advance, and that you can also have Tagged Template Strings which can transform the Template String automagically in many interesting ways. His example turns a Template String with embedded variables into a preparable query with a list of parameters. It's very nifty, quite clever in fact.

The other feature that I would really like is proper lexically scoped variables. Perl got these donkeys years ago, and their absence this long in JavaScript has been reprehensible. They are provided for in ES 6.

My understanding is that to get these features we need to provide for PLV8 to build against the latest version of V8 (and possibly provide some initialization flags too.) Unfortunately, the V8 API seems to have changed significantly since the 3.14.5.10 that's available on my Fedora 20 workstation, so enabling it to build with, say, V8 version 4.1.0.14, which is what io.js is using, will take quite a bit of work, probably by someone whose C++-fu is greater than mine.

New release of PLV8

I have released a new version of PLV8, which now builds on PostgreSQL 9.4, as well as containing a number of bug fixes.

It can be downloaded at http://pgxn.org/dist/plv8

Enjoy

pg_repack redux

I got an idea the other day about how to get over the recent problem I had encountered with pg_repack colliding with a lock request on the table made while it was copying data, but before it tried to rebuild indexes. After some considerable time with the debugger, I found that the problem is that pg_repack tries to get the index definition only at the time it builds the index, and that pg_get_indexdef() requires an Access Share lock on the table. The end result is that the lock request gets blocked waiting for pg_repack, and pg_repack gets blocked waiting for the lock requester, and there they stay. My initial solution was to move the index definition fetching to just after the place where the table definition is fetched. However, that ran into problems with existing strong locks, because it's before the brief time near the start of pg_repack's run where it obtains a strong lock on the table and cancels anything else holding such a lock. So I then moved it inside the section where it already holds a strong lock on the table. With that change, it now works both when there is an existing strong lock on the table and when there is a subsequent strong lock request on the table during the copy data phase. I've sent a pull request for this fix to the repo.

Waiting for extra statement stats

One of the things I was hoping would make it into PostgreSQL 9.4 and didn't was some extra statistics in pg_stat_statements. Specifically, it was proposed to add minimum, maximum and (after I proposed it and pressed the case) the standard deviation for execution times. Keeping these stats is very cheap, so there should be close to zero performance impact. They will give you a much better idea of the spread of execution times than a simple average, which might be significantly dominated by outlier times. Unfortunately, these got left out for 9.4, which is rather disappointing. I'm going to see if I can revive the proposal for 9.5.

PostgreSQL 9.4 and beyond

PostgreSQL 9.4 is released today, and once again my main input has been in the JSON area. We've introduced a new spiffy JSON type called "jsonb" that makes performing operations on JSON much, much faster, because it's not stored as plain text, but rather decomposed into the structures and scalar values that go to make up JSON. Much of the credit for this goes to Oleg Bartunov and Teodor Sigaev, the original authors of the hstore module, who created a "nested hstore" module that we eventually based jsonb on. Credit also goes to Alexander Korotkov and Peter Geoghegan, especially for their indexing work, which will make many operations super fast. My role was to make sure that all (or almost all) of the operations that are available for the original json type are also available for the jsonb type.

There have been some benchmark tests that suggest that our jsonb is faster in many respects that other database engines that use json as their native storage.

The nice thing about this is that you can now have a highly performing mixture of traditional relational data and semi-structured treeish data with proper transactional semantics, all in one engine. That's a very attractive proposition for a lot of people looking to design new data stores.

My other JSON contribution was to add a number of functions that allow the construction of arbitrarily complex json documents, with data from the database. This will be particularly useful for clients that expect json in a particular, non-regular shape, and is based on the needs of some actual json-using customers.

Already committed for 9.5, to be released some time next year, are jsonb versions of those json functions we didn't provide jsonb versions of in 9.4, and functions to allow the stripping of object fields with 'null' as the value from json and jsonb values (this can result in much more compact storage in some cases, with no significant semantic loss).

I made a few other contributions to 9.4, but they are not worth mentioning in detail.

I'm now looking for new PostgreSQL projects, not involving JSON. I'm not sure what those should be. For the most part my contributions over the last 12 years have tended not to be in  the area that involves the planning and execution of queries, so maybe it's time for me to dive into that pool. But to do what I don't yet know.

Assignment beats SELECT INTO

While working on some customer code, I noticed that they have a lot of code that reads like this:

SELECT a,b,c
INTO foo.x, foo,y, foo.z;

I wondered why they were doing it that way, and if it might be easier to read if it was just:

foo := (a,b,c);

Now, these aren't quite the same, especially if foo has more than three fields. But even that could be got around.

But before I tried this out I decided to see how they performed. Here's what happened:

andrew=# do $x$ 
declare 
   r abc; 
begin 
   for i in 1 .. 10000000 
   loop 
      select 'a','b',i into r.x,r.y,r.z; 
   end loop; 
end; 
$x$;
DO
Time: 63731.434 ms

andrew=# do $x$ 
declare 
   r abc; 
begin 
   for i in 1 .. 10000000 
   loop 
      r := ('a','b',i); 
   end loop; 
end; 
$x$;
DO
Time: 18744.151 ms

That's a very big difference! Direct assignment takes less than 30% of the time that SELECT INTO takes.

I'm going to dig into why this happens, but meanwhile, I have quite a lot of low hanging performance fruit to pick as a result of this.

One more time: Replication is no substitute for good backups.

I don't know how many times I have had to try to drum this into clients' heads. Having an up to date replica won't protect you against certain kinds of failures. If you really want to protect your data, you need to use a proper backup solution - preferable a continuous backup solution. The ones I prefer to use are barman and wal-e. Both have strengths and weaknesses, but both are incredibly useful, and fairly well documented and simple to set up. If you're not using one of them, or something similar, your data is at risk.

(In case you haven't guessed, today is another of those days when I'm called in to help someone where the master and the replica are corrupted and the last trusted pg_dump backup is four days old and rolling back to it would cost a world of pain. I like these jobs. They can stretch your ingenuity, and no two are exactly alike. But I'd still rather be paid for something more productive.)

pg_repack pitfalls

pg_repack is a terrific tool for allowing you to reorganize a table without needing to hold long running strong locks on the table. That means that that your normal inserts, updates and deletes can continue to run against the table while the reorganization is proceeding.

I have had clients who have run into problems with it, however. In particular, it is possible to get it wedged so that the table is inaccessible and nothing can proceed, unless you either kill the repack operation or kill what is blocking it. Here is a simple example of how to cause problems.

In session 1, do:

pg_repack -e -t foo dbnameset

and in session 2 in psql do:

select pg_sleep(10); lock table foo; rollback;

The sleep gets us past the time when pg_repack is setting up, and happens while it is is doing its CREATE TABLE ... AS SELECT .... When that CREATE TABLE statement finishes, both sessions will be wedged.  Session 2 will be hung because it is unable to lock the table, since pg_repack's other session will hold a weak lock on the table. And nothing, including pg_repack, will be able to do anything with the table.

The solution is to make sure that nothing holds or even tries to obtain any strong long running locks on the table.

One useful thing is to use the check_postgres.pl monitor script to look for things like long running transactions and processes waiting for locks.

Or you can create a more customized test to look for this exact situation.

Most importantly, you need to be aware that problems can occur, and to protect against them happening in the first place.