Not exactly Mihail, pagination is what you do with LIMIT and OFFSET, and has been there since forever.
Window functions are useful to correlate different rows and building charts or leaderboard, giving them weights, using them as a state machine (since it's a rolling window you can know which row is "previous") and other stuff.
You can obviously do that all of that stuff using a programming language (and sometimes you should) but if you don't need to, why not use them ;) ?
No, I get it, I see how it is both useful and how it might be obscure even to those that write some SQL.
I was just making fun of the hyped up description.
I'm also in the camp that says SQL is over used. The two popular and malignant ways it's overused is when one DB is shared between many if not all microservices (regardless of how well written the stored procedures are), and ORMs when used mixed with procedural app code and not as query builders.
SQL can be an acceptable structured datastore (with logic) for a given service, alone or together with other sources of truth. But not every service would benefit from one of those.
But most importantly, it's a poor object store and an even worse inter-service communication channel. And I've seen it used as both a lot.
No, I get it, I see how it is both useful and how it might be obscure even to those that write some SQL.
It took me a while to get it but I was actually writing a basic leaderboard and all of the "in language" solutions I tried were dog slow. Window functions instead use indexes :)
I'm also in the camp that says SQL is over used.
I don't know, it really depends on too many factors.
The two popular and malignant ways it's overused is when one DB is shared between many if not all microservices (regardless of how well written the stored procedures are),
Eh eh yeah, say no to stored procedures 99.99% of the time. The only placed I worked at where we had those was at a financial company and they used procedures written in Python that did math calculations inside the DB. A mess to maintain but insanely fast.
Say no to microservices that synchronize through a shared DB. That's a distributed monolith :D
Well said :-)
Window functions are my favorite "hidden" super power. The intro of the linked article says it all:
Not sure of this.
Ah ah I think it's just a hyperbole, but they really are useful
I hope they are! :)
They are, you can do a long way to calculate base statistics or setup things like leaderboards where you give rows scores based on some columns
Oh my lord, pagination.
I am so profoundly shocked by the concept of pagination I'm litterally SHYDDING MAISELF right now.
Not exactly Mihail, pagination is what you do with
LIMITandOFFSET, and has been there since forever.Window functions are useful to correlate different rows and building charts or leaderboard, giving them weights, using them as a state machine (since it's a rolling window you can know which row is "previous") and other stuff.
You can obviously do that all of that stuff using a programming language (and sometimes you should) but if you don't need to, why not use them ;) ?
No, I get it, I see how it is both useful and how it might be obscure even to those that write some SQL.
I was just making fun of the hyped up description.
I'm also in the camp that says SQL is over used. The two popular and malignant ways it's overused is when one DB is shared between many if not all microservices (regardless of how well written the stored procedures are), and ORMs when used mixed with procedural app code and not as query builders.
SQL can be an acceptable structured datastore (with logic) for a given service, alone or together with other sources of truth. But not every service would benefit from one of those.
But most importantly, it's a poor object store and an even worse inter-service communication channel. And I've seen it used as both a lot.
It took me a while to get it but I was actually writing a basic leaderboard and all of the "in language" solutions I tried were dog slow. Window functions instead use indexes :)
I don't know, it really depends on too many factors.
Eh eh yeah, say no to stored procedures 99.99% of the time. The only placed I worked at where we had those was at a financial company and they used procedures written in Python that did math calculations inside the DB. A mess to maintain but insanely fast.
Say no to microservices that synchronize through a shared DB. That's a distributed monolith :D
I meant stored procedures in SQL itself.
CREATE PROCEDURE.Yeah, let's avoid stored procedures if we can. Agreed