So, some time ago, Pg devs added multi ranges – that is datatype that can be used to store multiple ranges in single column. The thing is that it wasn’t really simple how to get list of ranges from within such multirange. There was no operator, no way to split it. A month ago Alexander … Continue reading “How to get list of elements from multiranges?”
Recently someone asked, on irc, how to make table partitioned. The thing is that it was supposed to be done with new partitioning, and not the old way. The problem is that while we can create table that will be seen as partitioned – we can’t alter table to become partitioned. So. Is it possible?
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?”
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?”
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?”
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).
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 …”