5 ms·

As a fellow Postgres amateur wizard, love the positive attention that postgres seems to be getting more and more, and some half databases less and less (unless

by timonv 11y ago

As a fellow Postgres amateur wizard, love the positive attention that postgres seems to be getting more and more, and some half databases less and less (unless you actually need map-reduce, ofcourse. You probably don't. /trollface) What I miss though in this article, and where I think postgres shines majorly compared to other rel dbs, are window functions. It allows you to apply a partition to a set. You can do some great wizardly magic with this, like 'give me each row matching this and that, which matches the last occurence of given column'. Edit: WITH clauses (CTE) are great for avoiding a lot of nesting with subqueries and/or reusing subqueries throughout the main query. They have added functionality for recursion, but I suppose that unless you do some kind of tree traversal on big data sets, benefits of that are soso, readabillity and all that. Edit2: I had to double check this, I never use custom types, using UNNEST() ARRAY[] on a custom type is superfluous. Just use ROW().

Hakeashar 11y ago
Isn't a CTE in Postgres (unlike in MS SQL, AFAIR) also an optimization fence? Just something to keep in mind when using it as a substitute for subquery, readability vs performance and all that :)
- timonv 11y ago
  As far as I know generally it doesn't matter. But please proof me wrong, sounds important! Edit: It matters! See comment above :-)
- rosser 11y ago
  Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.
  - glogla 11y ago
    > Sometimes, that's even the behavior you want. Sadly, in most cases, this mean you have to decide between ugly and performant, or nice and slow code. I hope this gets fixed soon. Nobody should have to write queries like this: select blah blah blah from x, (select blah blah blah from y, (select blah blah blah from z, (select blah blah from w where a=b and c=d) where z.id = w.id and p = 2 and q = 4) where z.id = y.different_id and r = 3 and t = 'BLAH' and u not in (select u from w)) l where l.id = x.id CTEs allow you to build "lisp-like" pipeline where you transform your data as you go and are able to give the intermediate results useful names.
    - radiowave 11y ago
      Of course, you can first write the query with CTEs, then convert to the nested form. It's just substitution. (For bonus points, keep the original CTE form, but commented out, to help with troubleshooting further down the road.)
ssfak 11y ago
For windows functions a good tutorial can be found at http://tapoueh.org/blog/2013/08/20-Window-Functions http://tapoueh.org/blog/2013/08/20-Window-Functions Postgresql also has good support for SQL-99 : http://www.slideshare.net/MarkusWinand/modern-sql http://www.slideshare.net/MarkusWinand/modern-sql WITH clauses (CTE) are great, but they are "optimization fences" in Postgresql : http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...
- timonv 11y ago
  I did not know that about CTEs. Thanks!
fleetfox 11y ago
http://cramer.io/2010/05/30/scaling-threaded-comments-on-django-at-disqus/ http://cramer.io/2010/05/30/scaling-threaded-comments-on-dja... This is where recursions is useful. For many scenarios adjacency list + recursive CTE scales better than any other hierarchy model.
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.
hessenwolf 11y ago
Do you mean Postgres's implementation of Excel pivot tables? ;)
- lcswi 11y ago
  tablefunc() :-)
  - hessenwolf 11y ago
    Cool.

by timonv 11y ago

Hakeashar 11y ago

Isn't a CTE in Postgres (unlike in MS SQL, AFAIR) also an optimization fence? Just something to keep in mind when using it as a substitute for subquery, readability vs performance and all that :)

timonv 11y ago
As far as I know generally it doesn't matter. But please proof me wrong, sounds important! Edit: It matters! See comment above :-)
rosser 11y ago
Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.
- glogla 11y ago
  > Sometimes, that's even the behavior you want. Sadly, in most cases, this mean you have to decide between ugly and performant, or nice and slow code. I hope this gets fixed soon. Nobody should have to write queries like this: select blah blah blah from x, (select blah blah blah from y, (select blah blah blah from z, (select blah blah from w where a=b and c=d) where z.id = w.id and p = 2 and q = 4) where z.id = y.different_id and r = 3 and t = 'BLAH' and u not in (select u from w)) l where l.id = x.id CTEs allow you to build "lisp-like" pipeline where you transform your data as you go and are able to give the intermediate results useful names.
  - radiowave 11y ago
    Of course, you can first write the query with CTEs, then convert to the nested form. It's just substitution. (For bonus points, keep the original CTE form, but commented out, to help with troubleshooting further down the road.)

ssfak 11y ago

For windows functions a good tutorial can be found at http://tapoueh.org/blog/2013/08/20-Window-Functions http://tapoueh.org/blog/2013/08/20-Window-Functions Postgresql also has good support for SQL-99 : http://www.slideshare.net/MarkusWinand/modern-sql http://www.slideshare.net/MarkusWinand/modern-sql WITH clauses (CTE) are great, but they are "optimization fences" in Postgresql : http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...

timonv 11y ago
I did not know that about CTEs. Thanks!

fleetfox 11y ago

http://cramer.io/2010/05/30/scaling-threaded-comments-on-django-at-disqus/ http://cramer.io/2010/05/30/scaling-threaded-comments-on-dja... This is where recursions is useful. For many scenarios adjacency list + recursive CTE scales better than any other hierarchy model.

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.

hessenwolf 11y ago

Do you mean Postgres's implementation of Excel pivot tables? ;)

lcswi 11y ago
tablefunc() :-)
- hessenwolf 11y ago
  Cool.