Some time ago, I wrote blogpost about how to pick a task from queue, without locking. It was written in 2013, and as such it couldn’t reflect everything we have now in PostgreSQL – namely SKIP LOCKED – which was added to PostgreSQL over year later. Two people mentioned SKIP LOCKED in comments, but it … Continue reading “Picking task from queue – revisit”
Recently I noticed that more and more cases that I deal with could use some partitioning. And while theoretically most people know about it, it’s definitely not a very well-understood feature, and sometimes people are scared of it. So, I’ll try to explain, to my best knowledge, what it is, why one would want to … Continue reading “Partitioning – what? why? how?”
I had to setup it relatively recently, and hit some roadblocks, so figured I’ll write about my experiences – for myself in the future, or for anyone else that might want to set it up.
When working with PostgreSQL you generally want to get information about slow queries. The usual approach is to set log_min_duration_statement to some low(ish) value, run your app, and then analyze logs. But you can log to many places – flat file, flat file on another disk, local syslog, remote syslog. And – perhaps, instead of … Continue reading “What logging has least overhead?”
Word of warning: this blogpost is about thing related to Bash (well, maybe other shells too, didn’t really test), but since I found it while doing Pg work, and it might bite someone else doing Pg related work, I decided to add it to “postgresql” tag. So, due to some work I had to do, … Continue reading “A tale of automating tests of Pg with Bash”
Last time I wrote about what explain output shows. Now I’d like to talk more about various types of “nodes” / operations that you might see in explain plans.
One of the questions that gets asked quite a lot, is: how can you use variables in your queries? Other databases have them, does PostgreSQL? Actually the answer is “no”. But, it is actually very easy to work around, and what’s more important – quite often, what you need are not variables in queries, but … Continue reading “Variables in SQL, what, how, when?”
So, you just installed your PostgreSQL, and you have no idea how to use it – there is no icon in the menu of your OS, so how can you use it? Well, with the dreadful command line. Of course – some people will never get used to textual programs. They need a GUI. That’s … Continue reading “Command line tools? In XXI century? No way! Yes way!”
In PostgreSQL 8.4 we got CTE – Common Table Expressions. Since then we have this great tool available, but apparently for some people it’s still black magic. CuTE, but still magic. I’ll try to make it a bit less magical, and more understandable.
Some of you might be familiar with pgBouncer project. Some are not. Some understand what/how/why it does, others do not. This blog post is to have a place where I can point people who have question about how it works, why, and when it makes sense to use it (pgBouncer that is).