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 – using “wrong” index

More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic.

Example of such case are for example comments or posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.

So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?

Continue reading Tips n' Tricks – using “wrong" index

Waiting for 8.5 – better messages for unique violation

On 1st of August Tom Lane committed patch from Itagaki Takahiro:

Improve unique-constraint-violation error messages to include the exact
values being complained of.
In passing, also remove the arbitrary length limitation in the similar
error detail message for foreign key violations.
Itagaki Takahiro

and later added an extension to it:

Department of second thoughts: let's show the exact key during unique index
build failures, too.  Refactor a bit more since that error message isn't
spelled the same.

Continue reading Waiting for 8.5 – better messages for unique violation