Month: August 2013
Reporting: Creating correct output
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.
Table bloat revisited: Making tables shrink in PostgreSQL
UPDATED August 2023 – Table bloat in PostgreSQL: Many people wonder 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. To show how this works, I have compiled some examples. […]
Detecting table bloat in PostgreSQL
If your PostgreSQL database is purely maintained (lack of VACUUM) or badly structured, you might face some table bloat. UPDATE Sept. 2023 – See this blog for a more recent update on this topic: Table bloat revisited. Table bloat is somewhat nasty because it slows down your database and eats up more storage than needed. […]
Common mistakes: UNION vs. UNION ALL
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.
Reducing the number of columns with bit
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 a normal char data type. The idea behind bit is to have a fixed set of bits, which can be […]
Time in PostgreSQL: Outer joins
UPDATED AUGUST 2023: 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 with an example: Many people use PostgreSQL to aggregate data. In this example, we assume that we want to aggregate data on an hourly basis. Here’s […]
Time in PostgreSQL: The simple way
UPDATED August 2023: Time is one of those unloved topics every programmer has to deal with. Have you ever written a routine heavily dependent on time calculations in C, Python, PHP, Perl, or any other language? In that case, I’m sure you’ve fallen in love with time management as much as I have. When it […]