Creating reports is a core task of every PostgreSQL database engineer or developer. However, many people think that it is enough to hack up some SQL aggregating some data and execute it. This is not quite true. We have repeatedly seen reports being just plain wrong without people even taking note of it.

Many people are wondering why deleting data from a table in a PostgreSQL database does not shrink files on disk. You would expect storage consumption to go down when data is deleted. This is not always the case.

If your PostgreSQL database is purely maintained (lack of VACUUM) or badly structured, you might face some table bloat. Table bloat is somewhat nasty because it slows down your database and eats up more storage than needed. Therefore it can be important to figure out, if a table is bloated or not. The PostgreSQL contrib […]

In my role as a PostgreSQL consultant and trainer there are a couple of issues, which pop up on a regular basis. One of those issues has to do with set operations. Many people seem to misunderstand the way UNION and UNION ALL work.

After digging through a table consisting of dozens of boolean values I thought it might be helpful to more folks out there to know about bitfields in PostgreSQL. Basically “bit” can be used just as normal char data type. The idea behind bit is to have a fixed set of bits, which can be accessed […]

After my previous post I received mails asking about what was meant by using “generate_series” in an outer join. Let me try to address this issue to proof my point: Many people are using PostgreSQL to aggregate data. In this example we assume that we want to aggregate data in an hourly basis.

Time is one of those unloved topics every programmer has already dealt with. Have you ever written a routine heavily depending on time calculations in C, Python, PHP, Perl, or any other language? I am sure you have fallen in love with time management as much as I did. When it comes to processing time, […]