8 ms·

PostgreSQL Magic

237 points by websec 11y ago

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.
rosser 11y ago
Quibble: the "now()" function doesn't return the time of statement start; it returns the time of transaction start. $ psql -q rosser=# begin; rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row) rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row)
- cleansy 11y ago
  That's also not completely acurate: postgres=> SELECT now(), now(), clock_timestamp(), clock_timestamp(); now | now | clock_timestamp | clock_timestamp -------------------------------+-------------------------------+-------------------------------+------------------------------ 2015-09-11 09:57:00.414422+00 | 2015-09-11 09:57:00.414422+00 | 2015-09-11 09:57:00.419087+00 | 2015-09-11 09:57:00.41909+00 Now() stays the same for the entire statement as well. clock_timestamp() doesn't.
  - fgutmann 11y ago
    If you ever manage to have multiple transactions in one statement please email me.
    - deleted 11y ago
      [deleted]
    - lsaferite 11y ago
      Thanks for the chuckle. (Yes, I know this adds nothing to the discussion)
    - AlterEgo20 11y ago
      Easy. Just use dblink or foreign table to execute part of the statement inside other transaction. dblink to the same server/db is often used as way to create an "AUTONOMOUS_TRANSACTION"
- 11y ago
mhd 11y ago
I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Also: If someone has a good version-control wrapper for stored procedures, that would be swell. And while I'm doing the wishful thinking shtick, maybe a Coffeescript-like preprocessor and a good linter?
- timonv 11y ago
  We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.
  - pjungwir 11y ago
    Yes, it can make a huge difference. I did this once when I had arrays of ~1 million floats and had to compute statistics on them. I wound up implementing a bunch of stats functions as C stored procedures that operate on arrays, and brought query times down from ~12 seconds to ~20 milliseconds: https://github.com/pjungwir/aggs_for_arrays/ https://github.com/pjungwir/aggs_for_arrays/ Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy: http://illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p... Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior. Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)
    - Erwin 11y ago
      Thanks -- I had a a query on 9.1 using MEDIAN (implemented in pl/psql) going from 12 seconds to 0.2 seconds by switching to array_to_median(array_agg(the_column)). I found it was faster to actually query a million values and calculate the median in Python than to use that pl/psql MEDIAN so it's nice this can be done easier with array_agg + your thing.
sigma2015 11y ago
What tool do you use for visualizing table relationships (foreign key constraints f.x.) in PostgreSQL?
- ahachete 11y ago
  To generate table diagrams that you can navigate (in HTML), SchemaSpy (http://schemaspy.sourceforge.net/ http://schemaspy.sourceforge.net/) is really good.
- bni 11y ago
  IntelliJ
- claysmithr 11y ago
  http://www.pgadmin.org/ http://www.pgadmin.org/ ?? but I am also new to postgresql. There are also some commercial tools available.
  - sigma2015 11y ago
    how?
- allenwlee 11y ago
  http://ondras.zarovi.cz/sql/demo/ http://ondras.zarovi.cz/sql/demo/
deleted 11y ago
[deleted]
dsugarman 11y ago
The best part of this for me was the batch updates. I am a little confused at the need for a user defined type that is a copy of the schema of the table. Wouldn't it be better to do use: ::table_name%ROWTYPE; That way you wouldn't need to maintain a table and a type with the exact same schema, no?
zrail 11y ago
I have seen magic done with custom types and aggregates. For example, I know of projects with run-length-encoded bitsets and custom aggregates for doing set operations and counts, sort of like Redis' bitset commands but built into PG. This is a massive space optimization, because otherwise you'd just have a join table with zillions of tiny rows.
whistlerbrk 11y ago
So many amazing features that are incredibly relevant to modern web development. I've switched over a personal project from MySQL to PG recently and this time I'm not looking back. On 9.4 I have the HSTORE and JSONB types as well as range types which are incredibly useful. If you have a solid language library wrapper for PG you can spend far less time mangling data from one format to the next and just get to work. I love it.

237 points by websec 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.

rosser 11y ago

Quibble: the "now()" function doesn't return the time of statement start; it returns the time of transaction start. $ psql -q rosser=# begin; rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row) rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row)

cleansy 11y ago
That's also not completely acurate: postgres=> SELECT now(), now(), clock_timestamp(), clock_timestamp(); now | now | clock_timestamp | clock_timestamp -------------------------------+-------------------------------+-------------------------------+------------------------------ 2015-09-11 09:57:00.414422+00 | 2015-09-11 09:57:00.414422+00 | 2015-09-11 09:57:00.419087+00 | 2015-09-11 09:57:00.41909+00 Now() stays the same for the entire statement as well. clock_timestamp() doesn't.
- fgutmann 11y ago
  If you ever manage to have multiple transactions in one statement please email me.
  - deleted 11y ago
    [deleted]
  - lsaferite 11y ago
    Thanks for the chuckle. (Yes, I know this adds nothing to the discussion)
  - AlterEgo20 11y ago
    Easy. Just use dblink or foreign table to execute part of the statement inside other transaction. dblink to the same server/db is often used as way to create an "AUTONOMOUS_TRANSACTION"
11y ago

mhd 11y ago

I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Also: If someone has a good version-control wrapper for stored procedures, that would be swell. And while I'm doing the wishful thinking shtick, maybe a Coffeescript-like preprocessor and a good linter?

timonv 11y ago
We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.
- pjungwir 11y ago
  Yes, it can make a huge difference. I did this once when I had arrays of ~1 million floats and had to compute statistics on them. I wound up implementing a bunch of stats functions as C stored procedures that operate on arrays, and brought query times down from ~12 seconds to ~20 milliseconds: https://github.com/pjungwir/aggs_for_arrays/ https://github.com/pjungwir/aggs_for_arrays/ Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy: http://illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p... Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior. Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)
  - Erwin 11y ago
    Thanks -- I had a a query on 9.1 using MEDIAN (implemented in pl/psql) going from 12 seconds to 0.2 seconds by switching to array_to_median(array_agg(the_column)). I found it was faster to actually query a million values and calculate the median in Python than to use that pl/psql MEDIAN so it's nice this can be done easier with array_agg + your thing.

sigma2015 11y ago

What tool do you use for visualizing table relationships (foreign key constraints f.x.) in PostgreSQL?

ahachete 11y ago
To generate table diagrams that you can navigate (in HTML), SchemaSpy (http://schemaspy.sourceforge.net/ http://schemaspy.sourceforge.net/) is really good.
bni 11y ago
IntelliJ
claysmithr 11y ago
http://www.pgadmin.org/ http://www.pgadmin.org/ ?? but I am also new to postgresql. There are also some commercial tools available.
- sigma2015 11y ago
  how?
allenwlee 11y ago
http://ondras.zarovi.cz/sql/demo/ http://ondras.zarovi.cz/sql/demo/

deleted 11y ago

[deleted]

dsugarman 11y ago

The best part of this for me was the batch updates. I am a little confused at the need for a user defined type that is a copy of the schema of the table. Wouldn't it be better to do use: ::table_name%ROWTYPE; That way you wouldn't need to maintain a table and a type with the exact same schema, no?

zrail 11y ago

I have seen magic done with custom types and aggregates. For example, I know of projects with run-length-encoded bitsets and custom aggregates for doing set operations and counts, sort of like Redis' bitset commands but built into PG. This is a massive space optimization, because otherwise you'd just have a join table with zillions of tiny rows.

whistlerbrk 11y ago

So many amazing features that are incredibly relevant to modern web development. I've switched over a personal project from MySQL to PG recently and this time I'm not looking back. On 9.4 I have the HSTORE and JSONB types as well as range types which are incredibly useful. If you have a solid language library wrapper for PG you can spend far less time mangling data from one format to the next and just get to work. I love it.