← Back to Upcase

Postgres Window Functions

(Geoff Harcourt) #1

@Simon_Taranto, I liked the post. I think users unfamiliar with windowing might try to do this work in Ruby instead of PG, which would be a performance nightmare on a large data set. Question for you: have you tried to execute window functions in PG using Dockyard’s pg-ext gem? Dockyard has written a blog post about supporting windowing and CTE and done some slide decks on it, but actual documentation is thin.

My app uses windowing on several important queries and I would love to get this code into ActiveRecord or at least Arel so that I don’t have huge chunks of SQL literals in my code (and could potentially reuse some pieces between similar queries), but given the quasi-undocumented state of this particular piece of pg-ext I’m concerned about how production worthy the features are. I don’t know if anyone outside Dockyard is using windowing/CTE portions yet.

For curious readers, here’s a slide deck by the author that has some other PG techniques:

1 Like

(Simon Taranto) #2

Hi @geoffharcourt, thanks for reading! I am not familiar with pg-ext but I will check it out and review the available slides. Are you using pg-ext in any of your projects? Do you have any examples you could share? It would be really interesting to go through a refactoring from a SQL literal to a pg-ext implementation. I’ll get in touch if I find a good use case.

0 Likes

(Geoff Harcourt) #3

I haven’t used it yet, but it’s a refactoring I really want to try soon. I’ve only started using windowing in the last few months, so I wanted to be comfortable with the SQL before I started trying to abstract it away with ActiveRecord.

0 Likes

(Simon Taranto) #4

Always a good approach :smile:

0 Likes