Some people are afraid of triggers. Reasons for this are not really understandable for me, but I guess it stems from the fact that these are usually application developers, and not database admins. Or they encountered some kind of problem with triggers, and now they tend to think that triggers are inherently evil. But they … Continue reading “How I Learned to Stop Worrying and Love the Triggers”
Yesterday on irc someone asked: Hi, how do I get top 5 values from a column group by another column?? From further discussion, I learned that: total rows in table is 2 million. It’ll have unique words of less than 1 million.. (approx count) I didn’t have time yesterday, but decided to write a solution, … Continue reading “Getting top-N rows per group”
As I wrote earlier, we (my wife and me) went to Maldives. This is brief summary, with some photos below.
Some time ago I wrote a blogpost about why index might not be used. While this post seemed to be well received (top link from depesz.com on reddit), it doesn’t answer another question – what index to create for given situation. I’ll try to cover this question now. IMPORTANT UPDATE: As of PostgreSQL 10 hash … Continue reading “What index to create?”
For various reasons, and in various cases, bloat happens. Theoretically autovacuum protects us all, but sometimes it doesn’t. Sometimes someone disables it, or mis-configures, or bad planet alignment happens, and we end up in deep bloat. What to do then? Vacuum? Vacuum Full? Cluster? No. pg_reorg!
Every so often someone asks why sorting behaves irrational. Like here: $ SELECT string FROM test ORDER BY string; string ———- dean deer de luca depesz de vil dyslexia (6 ROWS) Why aren’t “de luca” and “de vil” together?
Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn’t get page count, and easily jump to page number N. I did some thinking on the subject, and I think I found a way to make it all work. Quite … Continue reading “Pagination with fixed order”
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?
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 … Continue reading “Should you use HASH index?”
UPDATE Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there. END OF UPDATE Fight! But more seriously – people tend to use various data types, and there have been … Continue reading “CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03”