I just want to throw out there, one of the weaknesses I've seen is the lack of fast options for pivoting (wide <-> long). I've used the tablefunc module: https://www.postgresql.org/docs/13/tablefunc.html but it's not particularly fast in my experience (maybe they've improved it since it has been awhile) and the syntax is a bit odd. I'm spoiled with R (newer version) tidyr's pivot_wider and pivot_longer.

Gosh, yes it's been awhile. I posted this, ahem, over 8 years ago: https://stackoverflow.com/questions/15415446/pivot-on-multip.... Guess what, while to many things change, I still use SQL.

I should, uh, try it again. Or try PL/R, PL/Python, or PL/pgsql. There are options.