The Build

2 November 2016


Unclogging the VACUUM at PGConf EU

The slides for my presentation Unclogging the VACUUM at PGConf EU in Tallinn, Estonia are now available.

4 June 2016


Recent Slides

16 February 2016


Always Do This #5: The Elements of postgresql.conf Style

Everyone has their own style, unfortunately, on how they edit postgresql.conf. Some like to uncomment specific values and edit them at the point they appear in the default file, some like to tack overrides onto the end… and some do a mixture of those (don’t do that).

My personal preference is to leave everything in the default file commented, and include an overrides file (postgresql.local.conf). That way, I have an easy reference for what the defaults are, and minimum changes when a new version of postgresql.conf lands (you do know that checkpoint_segments is obsolete in 9.5, and you can’t specify it, right?). It’s easy for me to see what I’ve changed, since I can just consult that one included file.

I highly recommend this. But whatever you do, don’t do the “some things in the middle, some things at the end,” please. The next person to edit the file will thank you.

11 February 2016


Indexes Are Not Cheap

I’ve noticed an increasing tendency in PostgreSQL users to over-index tables, often constructing very complex partial indexes to try to speed up very particular queries.

Be careful about doing this. Not only do additional indexes increase the plan time, they greatly increase insert time.

By way of example, I created a table with a single bigint column, and populated it with:

time psql -c "insert into t select generate_series(1, 100000000)"

That run without any indexes took 1 minute, 55 seconds; that run with eight indexes on the same table took 26 minutes, 39 seconds, or almost 14 times slower.

Regularly consult pg_stat_user_indexes and drop indexes that aren’t being used. Your disk space and insert times will thank you.

9 February 2016


Django 1.8/1.9 and PostgreSQL: An Ever-Closer Union

I’ll be speaking about Django and PostgreSQL at PyCon US 2016.

8 February 2016


PostgreSQL High Availability, 2016 Edition

The slides from my talk at PG Day at FOSDEM 2016 are now available.

2 February 2016


Always Do This #4: Put stats_temp_directory on a memory file system

The PostgreSQL statistics collector generates a lot of very important statistics about the state of the database. If it’s not working, autovacuum doesn’t work, among other problems. But it does generate a lot of write activity, and by default, that goes back onto the database volume.

Instead, always set statstempdirectory to point to a RAM disk (which has to be owned by the postgres user, with 0600 permissions). The statistics are written back to the database on shutdown, so in normal operations, you won’t lose anything on a reboot. (You’ll lose the most recent statistics on a crash, but you will anyway; the statistics are reset on recovery operations, including restart from a crash.)

This can substantially cut down the amount of write I/O the main database storage volume has to receive, and it’s free!

25 January 2016


PostgreSQL High Availability, 2016 Edition

I’ll be speaking about PostgreSQL High Availability at PGDay 2016 at FOSDEM.

21 January 2016


JSON Home Improvement at SCALE 14x

The slides from my presentation, JSON Home Improvement at SCALE 14x, are now available.


A simple JSON difference function

More as an example than anything else, I wanted a function that would take two JSONB objects in PostgreSQL, and return how the left-hand side differs from the right-hand side. This means any key that is in the left but not in the right would be returned, along with any key whose value on the left is different from the right.

Here’s a quick example of how to do this in a single SELECT. In real life, you probably want more error checking, but it shows how nice the built-in primitives are:

    SELECT jsonb_object_agg(a.key, a.value) FROM
        ( SELECT key, value FROM jsonb_each(l) ) a LEFT OUTER JOIN
        ( SELECT key, value FROM jsonb_each(r) ) b ON a.key = b.key
    WHERE a.value != b.value OR b.key IS NULL;
    LANGUAGE sql;

« Older Entries

Newer Entries »