6 ms·

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's. I've seen comments

by greggyb 11y ago

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's. I've seen comments like this several times that seem to imply that Postgres is exceptional either due to having window functions, or like in this one where the tone sounds as if Postgres does them particularly better. I'm curious, as I do almost 100% of my production work in MS SQL Server, where I am able to do everything that comes up when "Postgres window functions" are mentioned, whether there is extra functionality in Postgres compared to other RDBMS's window function implementations? Is there a reason that Postgres seems to get special attention for window functions? Thanks.

crc32 11y ago
I think because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.
- greggyb 11y ago
  This does make some sense. Sometimes I forget how abnormal my own frame of reference is in comparison with the typical (at least of the vocal subset) HN poster.
- davidgerard 11y ago
  I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.
  - threeseed 11y ago
    Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.
    - davidgerard 11y ago
      That's half the justification. Aaand it turns out the justification doesn't hold against practice. We're discovering that literally everything is better with Postgres. Mostly because instead of a single expensive point of failure, every app gets its own clustered PG pair. Because we can, because we don't have to think about licensing ever again. Just everything not having to play nicely with anything else makes a huge difference. The other nice thing is that PG is administerable by clear-thinking (and understand relational databases) non-specialists who can read a manual. You don't actually need big-ticket support unless you do. And, guess what? Our Oracle support was most keen to offer Postgres support, because they too can tell which way the wind is blowing. (PG 9.3 out of Ubuntu 14.04 repos. Failover pair with a primary and standby. Primary streams write-ahead log records to standby as they’re generated. Some script gaffer-tape to watch for primary failure and fail over (I think we haven’t ever yet actually had to invoke this). Conversions done by hand with ora2pg then faff and twiddling and unit tests. Gotchas: malformed sql that Oracle accepts but PG chokes on. All cobbled together just following the docs, almost certainly better ways to do all this.) As for anyone else doing it ... we were buying AppDynamics (which is frickin' awesome btw) and talking to them about our plans to move from Oracle to PG. They said quite a few of their customers were thinking similarly. So maybe it's our own personal bubbles differing, but I think it's happening in at least some quarters.
      - threeseed 11y ago
        AppDynamics which I know very well is used by web shops. Very few of those are using Oracle and it wouldn't surprise me at all if they were moving to something else. The majority of Oracle instances however are in the back rooms of enterprises.
        davidgerard 11y ago
        Yeah, that's pretty plausible. Key point: it's possible. And unlike MySQL, Postgres is actually a proper database that can do what Oracle, MSSQL etc do. (We're also looking askance at our remaining MSSQL and have replaced one instance of the two with PG.)
mistermann 11y ago
Whenever I read articles like this, Postgres seems to have so many more small niceties and syntaxes than MSSQL though (I don't have a list of things, it just seems "easier" and more flexible).
- greggyb 11y ago
  I don't disagree in the general case, but I'm particularly interested in window functions here, as they are part of the SQL standard and seem equivalent between MS SQL Server and Postgres (I don't have near enough experience in other RDBMS's to speak to their implementations), despite being called out with some regularity as a Postgres-specific nicety.
  - jeltz 11y ago
    The only one I know of is that PostgreSQL has some nice ordinary aggregate functions which can be used well with window functions, like bool_and() and array_agg(). I can't think of an example on top of my head where it is useful but I know I have used them both.
    - greggyb 11y ago
      Thanks. There are definitely features in Postgres that I'm jealous of in the MS SQL Server world, and you've listed a couple of them.
yread 11y ago
There are window functions in sql server, too https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/ https://www.simple-talk.com/sql/learn-sql-server/window-func...
- greggyb 11y ago
  I am well aware. My confusion is, therefore, why do window functions get called out specifically, fairly commonly, as a Postgres-specific piece of awesomeness. Window functions are awesome, and they are a part of the SQL standard, thus my confusion. As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle.
  - dragonwriter 11y ago
    > As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle. License issues (and not just licensing costs, though that's often a factor) often mean that DB2, MS SQL, and Oracle are excluded options for non-technical reasons when MySQL and/or Postgres are under considerations.
asolove 11y ago
I think it's largely a question of community. Many users of MS SQL or Oracle learned advanced SQL or platform-specific features which they take for granted. They work at companies that spend lots of money for DBs and hire people who use them very well. Postgres also has a core community of very knowledgeable folks. But most of its user base, and especially those new to using it, would otherwise use MySQL. They only know small bits of SQL, have only a basic understanding of why any company would need a DBA, and only learn things like this when they absolutely need them for a task. And, for that group of people, MS and Oracle probably aren't serious choices, so the fact that a free database has these cool features seems exciting to them. (Note that I don't say this with distain. I'm a former-mysql user who now uses postgres on Heroku and am constantly learning things like this. I wouldn't even have understood your perspective until I started working with people who knew Oracle and MS SQL so thoroughly.)
- greggyb 11y ago
  Thanks very much. It is helpful to understand more of the background from someone who's seen multiple sides of the situation. For my work especially (BI consulting), fluency in SQL is incredibly useful, and specifically I don't think there's a single person at my company who doesn't use window functions regularly in all their development work. Other advanced constructs are very common as well. Again, I appreciate understanding the world outside my own little bubble. Thanks.
- danmaz74 11y ago
  > so the fact that a free database has these cool features seems exciting to them It IS exciting to them. And to anybody who can't afford Oracle or other expensive enterprise solutions.
j_s 11y ago
This may be related to how long Postgres has had support for various window functions, vs. MSSQL only gaining equivalent support in v2012 (AFAIK). Also, the various query optimization struggles older versions of MSSQL had. http://sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views http://sqlperformance.com/2013/03/t-sql-queries/the-problem-...

by greggyb 11y ago

crc32 11y ago

I think because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.

greggyb 11y ago
This does make some sense. Sometimes I forget how abnormal my own frame of reference is in comparison with the typical (at least of the vocal subset) HN poster.
davidgerard 11y ago
I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.
- threeseed 11y ago
  Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.
  - davidgerard 11y ago
    That's half the justification. Aaand it turns out the justification doesn't hold against practice. We're discovering that literally everything is better with Postgres. Mostly because instead of a single expensive point of failure, every app gets its own clustered PG pair. Because we can, because we don't have to think about licensing ever again. Just everything not having to play nicely with anything else makes a huge difference. The other nice thing is that PG is administerable by clear-thinking (and understand relational databases) non-specialists who can read a manual. You don't actually need big-ticket support unless you do. And, guess what? Our Oracle support was most keen to offer Postgres support, because they too can tell which way the wind is blowing. (PG 9.3 out of Ubuntu 14.04 repos. Failover pair with a primary and standby. Primary streams write-ahead log records to standby as they’re generated. Some script gaffer-tape to watch for primary failure and fail over (I think we haven’t ever yet actually had to invoke this). Conversions done by hand with ora2pg then faff and twiddling and unit tests. Gotchas: malformed sql that Oracle accepts but PG chokes on. All cobbled together just following the docs, almost certainly better ways to do all this.) As for anyone else doing it ... we were buying AppDynamics (which is frickin' awesome btw) and talking to them about our plans to move from Oracle to PG. They said quite a few of their customers were thinking similarly. So maybe it's our own personal bubbles differing, but I think it's happening in at least some quarters.
    - threeseed 11y ago
      AppDynamics which I know very well is used by web shops. Very few of those are using Oracle and it wouldn't surprise me at all if they were moving to something else. The majority of Oracle instances however are in the back rooms of enterprises.
      - davidgerard 11y ago
        Yeah, that's pretty plausible. Key point: it's possible. And unlike MySQL, Postgres is actually a proper database that can do what Oracle, MSSQL etc do. (We're also looking askance at our remaining MSSQL and have replaced one instance of the two with PG.)

mistermann 11y ago

Whenever I read articles like this, Postgres seems to have so many more small niceties and syntaxes than MSSQL though (I don't have a list of things, it just seems "easier" and more flexible).

greggyb 11y ago
I don't disagree in the general case, but I'm particularly interested in window functions here, as they are part of the SQL standard and seem equivalent between MS SQL Server and Postgres (I don't have near enough experience in other RDBMS's to speak to their implementations), despite being called out with some regularity as a Postgres-specific nicety.
- jeltz 11y ago
  The only one I know of is that PostgreSQL has some nice ordinary aggregate functions which can be used well with window functions, like bool_and() and array_agg(). I can't think of an example on top of my head where it is useful but I know I have used them both.
  - greggyb 11y ago
    Thanks. There are definitely features in Postgres that I'm jealous of in the MS SQL Server world, and you've listed a couple of them.

yread 11y ago

There are window functions in sql server, too https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/ https://www.simple-talk.com/sql/learn-sql-server/window-func...

greggyb 11y ago
I am well aware. My confusion is, therefore, why do window functions get called out specifically, fairly commonly, as a Postgres-specific piece of awesomeness. Window functions are awesome, and they are a part of the SQL standard, thus my confusion. As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle.
- dragonwriter 11y ago
  > As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle. License issues (and not just licensing costs, though that's often a factor) often mean that DB2, MS SQL, and Oracle are excluded options for non-technical reasons when MySQL and/or Postgres are under considerations.

asolove 11y ago

I think it's largely a question of community. Many users of MS SQL or Oracle learned advanced SQL or platform-specific features which they take for granted. They work at companies that spend lots of money for DBs and hire people who use them very well. Postgres also has a core community of very knowledgeable folks. But most of its user base, and especially those new to using it, would otherwise use MySQL. They only know small bits of SQL, have only a basic understanding of why any company would need a DBA, and only learn things like this when they absolutely need them for a task. And, for that group of people, MS and Oracle probably aren't serious choices, so the fact that a free database has these cool features seems exciting to them. (Note that I don't say this with distain. I'm a former-mysql user who now uses postgres on Heroku and am constantly learning things like this. I wouldn't even have understood your perspective until I started working with people who knew Oracle and MS SQL so thoroughly.)

greggyb 11y ago
Thanks very much. It is helpful to understand more of the background from someone who's seen multiple sides of the situation. For my work especially (BI consulting), fluency in SQL is incredibly useful, and specifically I don't think there's a single person at my company who doesn't use window functions regularly in all their development work. Other advanced constructs are very common as well. Again, I appreciate understanding the world outside my own little bubble. Thanks.
danmaz74 11y ago
> so the fact that a free database has these cool features seems exciting to them It IS exciting to them. And to anybody who can't afford Oracle or other expensive enterprise solutions.

j_s 11y ago

This may be related to how long Postgres has had support for various window functions, vs. MSSQL only gaining equivalent support in v2012 (AFAIK). Also, the various query optimization struggles older versions of MSSQL had. http://sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views http://sqlperformance.com/2013/03/t-sql-queries/the-problem-...