Before I'll start let me say that I am fan of what Oleg and Teodor did – their work is great, and I do appreciate their time and ideas.
But – I simply don't like the idea of using FTS (Full Text Search) inside of database. Why? Let me show you.
Tag: postgresql
Waiting for 9.1 – hostnames in pg_hba.conf
On 15th of October Peter Eisentraut committed patch (his own) which adds:
Support host names in pg_hba.conf Peter Eisentraut, reviewed by KaiGai Kohei and Tom Lane
Waiting for 9.1 – triggers on views
On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:
Support triggers on views. This patch adds the SQL-standard concept of an INSTEAD OF trigger, which is fired instead of performing a physical insert/update/delete. The trigger function is passed the entire old and/or new rows of the view, and must figure out what to do to the underlying tables to implement the update. So this feature can be used to implement updatable views using trigger programming style rather than rule hacking. In passing, this patch corrects the names of some columns in the information_schema.triggers view. It seems the SQL committee renamed them somewhere between SQL:99 and SQL:2003. Dean Rasheed, reviewed by Bernd Helmle; some additional hacking by me.
Waiting for 9.1 – ALTER TYPE
On 26th of September, Peter Eisentraut committed patch which adds:
Add ALTER TYPE ... ADD/DROP/ALTER/RENAME ATTRIBUTE Like with tables, this also requires allowing the existence of composite types with zero attributes. reviewed by KaiGai Kohei
Performance gains from using foreign keys
Foreign keys are known for couple of things, but speeding up your system is not one of them. But sometimes, having them in place lets you make queries significantly faster.
How? Let me show you example I have seen lately (well, it's simplified example based on something much more convoluted, and definitely longer):
How to group messages into chats?
My jabber server had the feature, that it logs all messages that got sent through it.
This is pretty cool, and useful. And now, i got asked to use it to create list of conversations.
What exactly is this? Whenever I send (or receive) something there is record in database with information about which local user, communication type (send/recv), correspondent, when it happened, and what is the body of message.
And based on this, we want to list messages into chats. How?
Why is my index not being used?
This question (and its variants) show quite often on #postgresql on IRC. People get sequential scans, and are worried that it's slow and bad.
So, I hope that this blogpost will shed some light on the subject why indexes are being chosen to be used, or not.
Waiting for 9.1 – concat, concat_ws, right, left, reverse
On 24th of August, Takahiro Itagaki committed patch:
Log Message: ----------- Add string functions: concat(), concat_ws(), left(), right(), and reverse(). Pavel Stehule, reviewed by me.
Continue reading Waiting for 9.1 – concat, concat_ws, right, left, reverse
How to manage changes to your database?
Every now and then somebody asks how to make diff of database schemata.
Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.
Personally I think that such approach is inherently flawed. Why?
OmniPITR – hot backup on slave
Well, the biggest information is that hot-backups on slave work. And they work fine. Really fine.
Some more information (with nice graph!):