Last week, I had an interesting discussion in the Postgres hackers mailing list about integrating pg_reorg features (possibility to reorganize a table without locks on it) directly into postgres core. Community strongly suggested that pg_reorg cannot be integrated as-is in the contribution modules of postgres core, and instead postgres should provide native ways to reorganize a table without taking heavy locks. This means that a table could be reindexed or clustered, and at the same time read and writes operations could still happen in parallel. What is particularly useful when an index is broken in a production database, as you could keep your table free of access for the other sessions running while the table is reorganized.

pg_reorg is a postgresql module developed and maintained by NTT that allows to redistribute a table without taking locks on it. The code is hosted by pg_foundry here. However, pgfoundry uses CVS :(, so I am also maintaining a fork in github in sync with pgfoundry here.

pgbadger is a recent Postgres module presented during the lightning talks of PGCon 2012 by its original author Gilles Darold. It is thought as an alternative to PgFouine, able to replace it thanks to its flexibility, extensibility and performance.

This post has as goal to provide basics to help you understanding how work triggers in PostgreSQL. A trigger is the possibility to associate an automatic operation to a table in case a write event happens on this given table.

For the last couple of days I have been working on merging the code of Postgres-XC from 9.1 to 9.2. The release 1.0 of XC has been based on 9.1, but it is time to move forward and steal the latest PostgreSQL amazing features :). The plan was to plug in the code of XC up to the intersection of PostgreSQL master branch and 9.2 stable branch, pretty interesting for two things:

• Possibility to create stable branches of Postgres-XC based on the 9.2 stable branch of Postgres
• Preserve the code for merges with future PostgreSQL releases

Postgres-XC, as a sharding cluster (write-scalable, multi-master based on PostgreSQL) has currently a huge limitation related to the way tables are distributed. Just to recall, tables can be either replicated, distributed by round robin, hash or modulo. For hash and modulo the distribution can be done based on the values of one column. Distribution type is defined thanks to an extension of CREATE TABLE.

PostgreSQL 9.2 introduces a new performance feature called Index-Only scans, which was really something missing in core for performance of scan index. Here is the commit that introduced the feature in core.

This short manual targets PostgreSQL users looking for a smooth introduction to dblink.

First you need to download the latest version of Postgres-XC from here.

One of the features that has been really improved the last couple of weeks is the stabilization of remote query planning for DML for Postgres-XC standard planner. And this has consequences on rules, because a rule is fired on Coordinators by design, and you need to provide a global way to plan queries correctly with remote nodes. Just to recall, a rule is the possibility to define an alternative action when doing an INSERT, UPDATE or DELETE on a table. Another important point is that the query of a rules is not planned at the moment of the rule creation, but after rule is fired, however it doesn’t change the fact that a correct query planning is needed at a moment or another.