News for hackers. Stuff that matters.
Re:PostgreSQL Magic by greggyb on Friday September 11, 2015 @09:25AM (#10203508)
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.

Re:PostgreSQL Magic by asolove on Friday September 11, 2015 @10:35AM (#10203876)
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.)

Re:PostgreSQL Magic by danmaz74 on Friday September 11, 2015 @03:47PM (#10205787)
> 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.

Re:PostgreSQL Magic by greggyb on Friday September 11, 2015 @11:19AM (#10204145)
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.

Re:PostgreSQL Magic by j_s on Friday September 11, 2015 @12:07PM (#10204477)
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-...

Re:PostgreSQL Magic by crc32 on Friday September 11, 2015 @09:33AM (#10203542)
I think because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.
Re:PostgreSQL Magic by greggyb on Friday September 11, 2015 @10:33AM (#10203857)
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.
Re:PostgreSQL Magic by davidgerard on Friday September 11, 2015 @02:22PM (#10205308)
I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.
Re:PostgreSQL Magic by threeseed on Friday September 11, 2015 @04:54PM (#10206120)
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.
Re:PostgreSQL Magic by dragonwriter on Friday September 11, 2015 @05:07PM (#10206189)
> 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.

If you need enterprise support for Postgres, there are vendors that offer it (EnterpriseDB is probably the closest to a "first party" equivalent.)

Re:PostgreSQL Magic by davidgerard on Friday September 11, 2015 @05:10PM (#10206206)
Our third-party Oracle support were most keen to offer PG support. They can tell which way the wind's blowing.

(We haven't taken them up on it. We seriously can't see how we'll need it. But if you do need it, it's there.)

Re:PostgreSQL Magic by threeseed on Friday September 11, 2015 @05:34PM (#10206301)
Sure. But they are few and far between and absolutely do not have the same SLAs as someone like an Oracle, NEC, IBM etc. They are generally mom+pop shops. It doesn't really cut it for enterprises which is generally where you are finding Oracle instances.
Re:PostgreSQL Magic by davidgerard on Friday September 11, 2015 @05:04PM (#10206172)
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.

Re:PostgreSQL Magic by threeseed on Friday September 11, 2015 @05:36PM (#10206308)
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.

Re:PostgreSQL Magic by davidgerard on Friday September 11, 2015 @07:22PM (#10206665)
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.)

Re:PostgreSQL Magic by mistermann on Friday September 11, 2015 @09:52AM (#10203633)
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).
Re:PostgreSQL Magic by greggyb on Friday September 11, 2015 @10:31AM (#10203848)
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.
Re:PostgreSQL Magic by jeltz on Friday September 11, 2015 @11:13AM (#10204104)
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.
Re:PostgreSQL Magic by greggyb on Friday September 11, 2015 @11:21AM (#10204157)
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.
Re:PostgreSQL Magic by yread on Friday September 11, 2015 @10:31AM (#10203843)
There are window functions in sql server, too

https://www.simple-talk.com/sql/learn-sql-server/window-func...

Re:PostgreSQL Magic by greggyb on Friday September 11, 2015 @10:35AM (#10203873)
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.

Re:PostgreSQL Magic by dragonwriter on Friday September 11, 2015 @04:00PM (#10205847)
> 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.