So, what’s weird about this plan, from a query on a partitioned table? (PostgreSQL 9.3, in this case.)

1
test=> explain select distinct id from orders where order_timestamp > '2016-05-01';
2
                                                                  QUERY PLAN
3
-----------------------------------------------------------------------------------------------------------------------------------------------
4
 HashAggregate (cost=347341.56..347343.56 rows=200 width=10)
5
   Group Key: orders.id
6
   -> Append (cost=0.00..337096.10 rows=4098183 width=10)
7
         -> 

The one thing that everyone knows about compositive indexes is: If you have an index on (A, B, C), it can’t be used for queries on (B) or (B, C) or (C), just (A), (A, B) or (A, B, C), right? I’ve said that multiple times in talks. It’s clearly true, right?

Well, no, it’s not. It’s one of

On a PostgreSQL primary / secondary pair, it’s very important to monitor replication lag. Increasing replication lag is often the first sign of trouble, such as a network issue, the secondary disconnecting for some reason (or for no reason at all, which does happen rarely), disk space issues, etc.

You can find all kinds of complex scripts that do

The slides for my talk, Securing PostgreSQL at PGConf EU 2016 are now available.

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

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,

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

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

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 stats_temp_directory to point to a RAM disk (which has to

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