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”
After loooong pause, adding next (well, second) post to the “series“. This time, I’d like to describe how logging works. And I don’t mean binary logging (WAL), but the log for us, humans, to read. Before I will go to the postgresql.conf options, let me first describe what happens when PostgreSQL wants to log something.
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”
At home I have couple of machines, but one of them takes relatively long time to login to. It looks like this: =$ time ssh blob id -u 1000 real 0m0.401s user 0m0.010s sys 0m0.000s While 0.4s is not very long, it bugged me. And today, I decided to look into it, to see … Continue reading “A tale of slow ssh connections”
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?
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.
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?”
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).