And maybe not on the wall, but instead in your SQLz, eating your data. But a bit more seriously. Ever since PostgreSQL 8.4 we have window functions, but still I see people which do not know it or are wary to use it. That’s why I decided to write a piece on window functions. How … Continue reading “Window, window on the wall …”
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”
Similar question has been asked many times on mailing lists and on IRC. Sometimes it’s not mail sending, but file/directory creation, or something else that generally requires some interaction with “world outside of database”. Can it be done? Sure. How, then?
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”
First of all – just today I committed patch for Pg::Explain – which is the workhorse behind explain.depesz.com. This patch fixes calculation of exclusive time for explain nodes, and the best thing about it is – I didn’t write it. It’s full patch provided by someone else – Filip Rembiałkowski – my former colleague, friend, … Continue reading “Two years of explain.depesz.com”
One of the questions that pop up frequently on IRC is how to see queries are now executed on the server, and what queries were earlier. Theoretically answer to this is simple – pg_stat_activity and log_min_duration_statement. Or log_statement. What is the difference? That’s exactly why I’m writing this post.