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?
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.
Relatively soon we will have 9.0, and we will move with development to 9.1 (we as in: PostgreSQL community, I don’t know C so I can’t help developing myself, but I will at least try to keep up with changes in the ‘Waiting for’ series).
Today, there was this one person on IRC, which asked question and provided some data. While working on helping him (her?), I noticed some things, that bugged me before in other cases, but this time i decided to write about it – it’s kind of rant, and if you (the reader) are the person that … Continue reading “What mistakes you can avoid when looking for help on IRC?”
Question from title sounds weird to you? It’s just a ‘rm backup_filename’? Well. I really wish it was so simple in some cases. One of the servers I’m looking into, there is interesting situation: quite busy database server (2k tps is the low point of the day) very beefy hardware daily backups, each sized at … Continue reading “How to remove backups?”