Lesser-known Postgres features

Re:Lesser-known Postgres features by NightMKoder on Tuesday November 09, 2021 @01:22PM (#29165300)

One of the hardest types of queries in a lot of DBs is the simple `min-by` or `max-by` queries - e.g. "find the most recent post for each user." Seems like Postgres has a solution - `DISTINCT ON` - though personally I've always been a fan of how BigQuery does it: `ARRAY_AGG`. e.g.

  SELECT user_id, ARRAY_AGG(STRUCT(post_id, text, timestamp) ORDER BY timestamp DESC LIMIT 1)[SAFE_OFFSET(0)].*
  FROM posts
  GROUP BY user_id

`DISTINCT ON` feels like a hack and doesn't cleanly fit into the SQL execution framework (e.g. you can't run a window function on the result without using subqueries). This feels cleaner, but I'm not actually aware of any other DBMS that supports `ARRAY_AGG` with `LIMIT`.

[ Parent | on HN ]

Re:Lesser-known Postgres features by bbirk on Tuesday November 09, 2021 @05:12PM (#29168212)

I would use lateral join. Alternatively you can use window function instead of lateral join, buy from my experience lateral joins are usually faster for this kind of top-N queries in postgres.

select user_table.user_id, tmp.post_id, tmp.text, tmp.timestamp

from user_table

left outer join lateral (

select post_id, text, timestamp

from post

where post.user_id = user_table.user_id

order by timestamp

limit 1

) tmp on true

order by user_id

limit 30;

[ Parent | on HN ]

Re:Lesser-known Postgres features by zX41ZdbW on Tuesday November 09, 2021 @08:23PM (#29170039)

In ClickHouse we have argMin/argMax aggregate functions. And also LIMIT BY. It looks like this:

    ... ORDER BY date DESC LIMIT 1 BY user_id

[ Parent | on HN ]

Re:Lesser-known Postgres features by zX41ZdbW on Tuesday November 09, 2021 @08:24PM (#29170049)

And we have groupArray aggregate function with limit.

> I'm not actually aware of any other DBMS that supports `ARRAY_AGG` with `LIMIT`.

So, put ClickHouse in you list :)

[ Parent | on HN ]

Re:Lesser-known Postgres features by mnahkies on Tuesday November 09, 2021 @02:05PM (#29165958)

I've generally used `PARTITION BY` to achieve the same effect in big query (similar to what was suggested in the article for postgres).

Does the `ARRAY_AGG` approach offer any advantages?

[ Parent | on HN ]

Re:Lesser-known Postgres features by NightMKoder on Tuesday November 09, 2021 @02:44PM (#29166470)

I assume you mean `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) as row_number` with a `WHERE row_number = 1`. We ran into issues with memory usage (i.e. queries erroring because of too high memory usage).

The `ARRAY_AGG` approach uses a simple heap, so it's as efficient as MIN/MAX because it can be computed in parallel. ROW_NUMBER however needs all the rows on one machine to number them properly. ARRAY_AGG combination is associative whereas ROW_NUMBER combination isn't.

[ Parent | on HN ]

Re:Lesser-known Postgres features by mnahkies on Tuesday November 09, 2021 @03:13PM (#29166860)

That makes sense, I might have a play with the array agg approach. I'm especially curious if it has any impact on slot time consumed

[ Parent | on HN ]