Starting new blog series – explanation of various configuration parameters.
I will of course follow no schedule or order – if I'd had to – it would be my job, and in this way – it's fun.
First configuration parameter to write about is checkpoint_completion_target.
Continue reading Understanding postgresql.conf : checkpoint_completion_target
Finally, on 25th of October, Tom Lane committed patch which does:
Allow new values to be added to an existing enum type. After much expenditure of effort, we've got this to the point where the performance penalty is pretty minimal in typical cases. Andrew Dunstan, reviewed by Brendan Jurd, Dean Rasheed, and Tom Lane
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):