Second Unconference Day at PgCon

We will be having our second annual PostgreSQL Unconference on the Saturday after pgCon.  For those of you who weren't there last year, the wiki explains what you do on an unconference day.  We are able to hold this again thanks to the generous sponsorship of Salesforce.com, who continue to support PostgreSQL development.

While the Unconference will be mostly organized at 10am that day, you can get a jump on other session proposers by adding your session proposals to the wiki.  These session ideas will be allowed to first during the pitch session.  I also urge folks who will be attending the Unconference to put your names by session ideas you'd like to attend.

I also need the help of two volunteers to help me with organizing the Unconference on the day of.  This will not prevent you from attending and/or leading sessions; I just need some folks to help me put up signs, organize the schedule, and make sure that notes get taken in each session.  Contact me if you are available to help with this.

Unfortunately, if you weren't already planning to attend the unconference, it's probably too late to change your travel plans; Saturday night rooms in Ottawa are pretty much unavailable due to the marathon.

P.S. if you're into replication and clustering, don't miss the 2014 Clustering Summit and the PostgresXC Pizza Demo on Tuesday of pgCon week!  (the Pizza demo will be at 7pm, location TBD).

Simplifying Replication Position

Two years ago I wrote a post explaining how to determine relative log position in order to determine which replica was furthest ahead.  This is so you can choose the "best" replica to fail over.

Thanks to the miracle of PostgreSQL rapid development (Thanks Euler!), those complicated calculations are no longer necessary if you're using PostgreSQL 9.2 or later.  Particularly, this function makes your life much easier:

pg_xlog_location_diff(loc1, loc2)

So, reprising my old post, we want to determine three things, and can now do it in three one-liners:

Determine the absolute position in the transaction log, so that all replicas can be compared:

SELECT pg_xlog_location_diff ( pg_current_xlog_location(), '0/0000000' );

Determine the replay lag in megabytes, to see roughly how long it'll take this replica to "catch up":

SELECT pg_xlog_location_diff ( pg_last_xlog_receive_location(),  pg_last_xlog_replay_location() ) / 1000000;

Determine if a server is completely "caught up":

SELECT pg_xlog_location_diff ( pg_last_xlog_receive_location(),  pg_last_xlog_replay_location() ) = 0;

See, things have gotten much simpler!

BTW, the above has just been incorporated into HandyRep, in the select_replica_furthest_ahead plugin.

New New Index Bloat Query

Earlier this week Ioguix posted an excellent overhaul of the well-known Index Bloat Estimation from check_postgres.  However, I felt that we needed several additional changes before the query is ready for me to use in our internal monitoring utilities, and thought I'd post our version here.

Here's the New New Index Bloat Query.

Here's what I changed:

1. Had it pull btree indexes only, because the calculations don't work for GIN/GiST indexes.
2. Moved all of the nested subqueries up into WITH clauses for easier readability and maitainability.   Since all supported Postgres versions now do CTEs, I didn't see any reason to work around them.
3. Removed the \set statements, because they don't work for embedding the query in a script.  And they only need setting dynamically if you've compiled PostgreSQL with nonstandard options, anyway.
4. Removed a couple of CASE statements aimed at 7.X support; who cares?
5. Added some extra informational columns for using this in interactive mode: table size, pretty index size, and index scans.  This helps folks figure out whether to rebuild an index, ignore the bloat or to drop it.
6. In the example query, filtering down to indexes with bloat over 50% and 50MB, which is our threshold for "significant bloat"

Example output:

-[ RECORD 27 ]+-----------------------------
database_name | prod-web
schema_name   | partitions
table_name    | transactions_201308
index_name    | transactions_201308_uuid_idx
bloat_pct     | 52.9
bloat_bytes   | 351649792
bloat_size    | 335 MB
index_bytes   | 664788992
index_size    | 634 MB
table_bytes   | 4570447872
table_size    | 4359 MB
index_scans   | 263




So this index would be a good candidate for deletion, since it's 50% bloated and seldom scanned.



Now, there's still more to do on this.  We need a similar query for GIN/GiST/SPGiST.  The query could use some more cleanup; removing one-letter table aliases, and GROUP BY 1,2,3,4 stuff.  But it's vastly improved for checking which of your indexes need VACUUM/REINDEX.  Thanks, Ioguix!