Blog

Expert Insights

PostgreSQL Expert Insight Series – Blog 1 of 4: Aggregation

By Caspar Scholten, sr. developer
Expert Insight Series visual 1-4

Hello from me

I’ve always admired the desire of the PostgreSQL community to share knowledge and expertise. Following in that tradition I’d like to share a few pearls of PostgreSQL wisdom that I’ve picked up over the years as a PostgreSQL developer. But before I start of, I’d like to point out that there are two ways to interpret the title PostgreSQL developer.

  1. Working at the core, extensions etc. that contribute to PostgreSQL as database to be used. Most of the time this is written in C.
  2. Working with the above to achieve the demands from the business. Creating tables, indexes, functions (SQL-language) but looking at performance of that as well.

I consider myself to be among the developers working with PostgreSQL.

Ok, let’s get started. My aim, over the course of this series of four blogs, is to help point you in the right direction when you’re faced with a problem. I can’t promise I’ll be able to give you the whole answer, but they should help get you started.

Now let’s get to the structure of this series. To start with, there are four subjects I would like to share with you. All four blogs have the background of approaching a task in an alterative way.

  1. Aggregation
  2. Checking specific text within a larger string
  3. Primary Key considerations
  4. Moving data around

I’ll support these subjects with examples from real life situations I’ve encountered. Of course, I’ve anonymised them or taken it to a higher level of abstraction. Now, onto our first blog.

1 of 4: Aggregation

You might think it’s pretty simple to get the last time something happened within a group of one or more attributes. You just write the query with a group by for the non-aggregated columns, right? However, this may come at a cost as the query has to fetch all records related to the non-aggregated columns. Surely there’s a more efficient and faster way to do that?

Ok, so let’s assume that we have a table that contains the GPS points and the time being at that location. Now we can ask the question: When did you arrive at the end of the journey?

We could make a loop with (individual) cursors, but this is not my preference. Not only is it quite intensive to write in plpgsql, but it is also, in my opinion, harder to read and maintain, and probably much slower. We have a database that analyses the query for alternative execution plans. So let’s use that knowledge of the geniuses that have debated and implemented these strategies in the core of PostgreSQL itself. A big “thank you” to the contributors to the internal (open-source) code of PostgreSQL.

Back to the functional question from above…

The usual query would look like this:

SELECT  trk.track_name
,       MAX(trp.location_time)
FROM    routes.track        trk
JOIN    routes.track_point  trp USING (id_track)
WHERE   trp.location_time
            BETWEEN '2020-01-01'
            AND     '2020-06-01'
GROUP BY
        trk.track_name

An alternative way could look like this:

SELECT  DISTINCT ON (trk.track_name)
        trk.track_name
,       trp.location_time
FROM    routes.track        trk
JOIN    routes.track_point  trp USING (id_track)
WHERE   trp.location_time
            BETWEEN '2020-01-01'
            AND     '2020-06-01'
ORDER BY
        trk.track_name
,       trp.location_time DESC

The crux of this alternative is to put the non-aggregated columns in a distinct on (...)‑clause and with additional ordering on the aggregated column.

This alternative only works if you want to find a minimum or maximum of the aggregated column and the order will then be ASC(ending) or DESC(ending) respectively. Now, the observant reader will have noticed there is a drawback – it only works for one aggregated column.

I can’t say that the latest version is faster, but it could be. At least, that was one of the lessons I learned over the years. So it’s worth a try if you come across such a functional question.

Another idea is to use a window-function with partitioning on the non-aggregated columns and using distinct if needed. I admit I’ve haven’t tried this out – but it would be something like:

SELECT  DISTINCT
        trk.track_name
,       MAX(trp.location_time) OVER (PARTITION BY trk.track_name)
...

I haven’t investigated this alternative further because it feels more complex to understand its functional meaning. For future developers it’s harder to understand and maintain this code.

They will no doubt ask themselves, why was it done this way – was there a reason for it? In fact, even the creator of this code has forgotten the reason, so for that reason alone, perhaps it is too complex!

Make sure you check out Blog 2 of 4: Checking text within a larger string.

What are other people reading?

Want to know more?