PostgreSQL Magic

#1

Post by websec » Fri, Sep 11, 2015, 6:12 AM UTC

Re: PostgreSQL Magic

#2

Post by timonv » Fri, Sep 11, 2015, 7:16 AM UTC

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().

Re: PostgreSQL Magic

#3

Post by rosser » Fri, Sep 11, 2015, 7:46 AM UTC

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)

Re: PostgreSQL Magic

#4

Post by mhd » Fri, Sep 11, 2015, 7:51 AM UTC

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?

Re: PostgreSQL Magic

#5

Post by Hakeashar » Fri, Sep 11, 2015, 7:51 AM UTC

timonv wrote: post #2

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…

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 :)

Re: PostgreSQL Magic

#6

Post by timonv » Fri, Sep 11, 2015, 7:59 AM UTC

mhd wrote: post #4

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…

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.

Re: PostgreSQL Magic

#7

Post by timonv » Fri, Sep 11, 2015, 8:00 AM UTC

Hakeashar wrote: post #5

timonv wrote: post #2

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…

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 :)

As far as I know generally it doesn't matter. But please proof me wrong, sounds important!

Edit: It matters! See comment above :-)

Re: PostgreSQL Magic

#8

Post by ssfak » Fri, Sep 11, 2015, 8:01 AM UTC

timonv wrote: post #2

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…

For windows functions a good tutorial can be found at http://tapoueh.org/blog/2013/08/20-Window-Functions

Postgresql also has good support for SQL-99 : 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...

Re: PostgreSQL Magic

#9

Post by timonv » Fri, Sep 11, 2015, 8:20 AM UTC

ssfak wrote: post #8

timonv wrote: post #2

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…

For windows functions a good tutorial can be found at http://tapoueh.org/blog/2013/08/20-Window-Functions Postgresql also has good support for SQL-99 : 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...

I did not know that about CTEs. Thanks!

Re: PostgreSQL Magic

#10

Post by fleetfox » Fri, Sep 11, 2015, 9:01 AM UTC

timonv wrote: post #2

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…

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.