On 24th of October, Peter Eisentraut committed addition to his previous patch, which adds:
Support suffix matching of host names in pg_hba.conf A name starting with a dot can be used to match a suffix of the actual host name (e.g., .example.com matches foo.example.com).
Continue reading Waiting for 9.1 – Suffix matching in pg_hba.conf
Today some guy on IRC asked question, which I didn't fully understand, but which could (probably) be summarized: how to group data into 5 minute intervals, based on some timestamp column.
Well, it seems trivial (as long as you know how to do it), but since he clearly didn't know how to do it (or I misunderstood his problem), here's the explanation:
Some time ago Joshua Tolley described how to reduce bloat from tables without locking (well, some locks are there, but very short, and not really intrusive).
Side note: Joshua: big thanks, great idea.
Based on his idea and some our research, i wrote a tool which does just this – reduces bloat in table.
Continue reading Reduce bloat of table without long/exclusive locks
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.
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
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.
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
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):
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?
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.