OmniPITR – update

OmniPITR project that I wrote about some time ago is going on.

Just today I finished tests for omnipitr-backup-slave – part of OmniPITR which lets you make hot-backups of WAL-slave machine – without any additional load on master.

As previously – please download (svn co) and test. In case you have problems – please mail me or contact me on – I'm usually on #postgresql.

Should you use HASH index?

Today, Mattias|farm on IRC asked how to create primary key using HASH index. After some talk, he said that in some books it said that for “=" (equality) hash indexes are better.

So, I digged a bit deeper.

IMPORTANT UPDATE: As of PostgreSQL 10 hash indexes are WAL logged. As such, main point against them is gone.

Continue reading Should you use HASH index?

Tips N’ Tricks – looking for value in all columns of a table

Every so often you might need to find a value regardless of which column it's in.

Of course not in application code, as this would be very slow. But you might be in situation where you just don't know where did application get some value from, and you want to find it in table. Table that has 1-2 fields is trivial to search, but if you have 15 columns, naming them all in WHERE clause is tedious.

Continue reading Tips N' Tricks – looking for value in all columns of a table

Test driven development for PostgreSQL

I have a mixed love/hate relationship with tests.

I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.

But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.

I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.

For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.

Continue reading Test driven development for PostgreSQL