5 ms·

Standard SQL features where PostgreSQL beats its competitors

96 points by MarkusWinand 9y ago

craigkerstiens 9y ago
Postgres isn't always perfect and for a few things lagged behind for a few years (namely upsert and better replication support). Fortunately we got both of these in recent releases, but this post is a great collection of areas where it already shined with some powerful features. In particular really love the details on check constraints and null in Postgres.
minimaxir 9y ago
It might be worthwhile to add cloud data warehouses with SQL interfaces (e.g. Redshift/BigQuery) to the slides, as I believe they have a similar feature set to PostgreSQL.
- da_chicken 9y ago
  Redshift is based on PostgreSQL 8.0 (https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-an...). BigQuery is a little out of scope, IMO.
  - tejasmanohar 9y ago
    Frankly, I’d say both are out of scope since access patterns are entirely different. They make different trade-offs so anything less than a holistic review of both is a false comparison.
orf 9y ago
The first feature in the deck is FILTER, which I can't help but mention I added support for (with a case/when fallback) to Django 2.0. So, if you're using the new Django release you can use this syntax on any aggregate/annotate via the filter kwarg[1] now. There was one user report on the github PR that said FILTER was 10-15% faster over the equivalent CASE/WHEN, due to the null filtering I think. It's a shame only Postgres supports this supposedly standard syntax. Edit: Heh, I initially got (stole) the idea of using FILTER + CASE from modern-sql.com[2], which is run by the author of this slide deck. So I guess I aught to thank Markus for this! 1. https://docs.djangoproject.com/en/2.0/ref/models/conditional-expressions/#conditional-aggregation https://docs.djangoproject.com/en/2.0/ref/models/conditional... 2. http://modern-sql.com/feature/filter http://modern-sql.com/feature/filter
- MarkusWinand 9y ago
  Welcome :)
- simonw 9y ago
  That's really clever - I just blogged about it https://simonwillison.net/2018/Feb/3/conditional-aggregation-django-20/ https://simonwillison.net/2018/Feb/3/conditional-aggregation...
  - orf 9y ago
    Thank you Simon!
zkomp 9y ago
It was a good talk at fosdempgday. Well paced, funny at times, esp the part comparing booleans with mysql "booleans" and "check constraints" (spoiler neither boolean nor checked) Too bad I'm stuck on 9.1 for the time being which lack many of these really nice features.
- daurnimator 9y ago
  Why are you stuck on 9.1? 9.2 was released in september 2012. Upstream support (bug+security fixes) for 9.1 was dropped in november 2016.
  - zkomp 9y ago
    I know... The main reason is it is hard to avoid downtime when upgrading older postgres versions (given a large and bussy enough server) The situation is getting so much better with logical decoding in recent version. When the upgrade day finally come it will be like christmas with Lateral, Filter, JsonB, Percentile_Disc/Cont, and "upserts"
    - daurnimator 9y ago
      9.1 => 9.6 is the last supported version to upgrade to. You're going to have to upgrade in 2 steps to get to 10 anyway.
mjw1007 9y ago
Some of the information looks off. The chart shows CHECK constraints appearing in PostgreSQL in 8.3, but they've been there since at least 7.1.
- MarkusWinand 9y ago
  I'm sorry it is not so obvious: there is a difference between red, which means it doesn't work, and transparent, which means I didn't test any further back.
combatentropy 9y ago
I learned a few things, despite having used Postgres for many years: (1) the filter clause with aggregates to more succinctly pivot tables, (2) the not-valid clause with check constraints to apply only to new data while leaving old rows alone, and (3) something about inverse-distribution functions --- I actually didn't learn these yet but bookmarked it for later, because it's too hard to read on a Saturday ;) It really is amazing how much you can prepare your data before it leaves your database, or how raw you can leave your data before sending it there, letting Postgres take care of it. I wonder how much middle code would be saved (PHP, Python, Perl, etc.) if more programmers knew more SQL, and especially if they chose Postgres more often. I also wonder why SQL seems to be the language that developers know least, even though it seems like it should be easiest, with its declarative, natural-sounding syntax. (I admit that the naturalness is deceptive. Just because "select color, count(1) from favorites where age < 13 having count(1) > 3 order by 1" sounds like English doesn't mean you can tell exactly what it will do before reading the manual.)
- barrkel 9y ago
  The language isn't particularly compositional (why both HAVING and WHERE? why must we repeat SELECT column expressions in HAVING, ORDER BY etc?), and the "natural" grammar means it usually isn't very discoverable in an editor. The grammar puts operations out of order (SELECT should be last! And usable multiple times, to simplify expressions!), and multi-word clauses with contextual meaning are harder to complete effectively in a syntax-aware editor. SQL is also a bit of a two-edged sword. You can do very powerful things with it that are infeasible in normal code, because they'd require round-tripping enormous quantities of data. OTOH it's easy to write code that performs and scales dreadfully, which will normally have knock-on effects across the whole production system, which is pretty scary. There are usually multiple ways of writing what is semantically the same query, that perform quite differently, depending on how smart your target planner is. Despite the language being nominally declarative, you still need to keep in mind potential execution plans as you write the code, and verify on reasonably sized data sets that the plan you expected, or one better than it, was selected. It's been my experience that most developers don't have cost model estimation baked into their brains, so the more advanced cost estimation you need to do with a declarative language is even rarer.
deleted 9y ago
[deleted]

96 points by MarkusWinand 9y ago

Postgres isn't always perfect and for a few things lagged behind for a few years (namely upsert and better replication support). Fortunately we got both of these in recent releases, but this post is a great collection of areas where it already shined with some powerful features. In particular really love the details on check constraints and null in Postgres.

minimaxir 9y ago

It might be worthwhile to add cloud data warehouses with SQL interfaces (e.g. Redshift/BigQuery) to the slides, as I believe they have a similar feature set to PostgreSQL.

da_chicken 9y ago
Redshift is based on PostgreSQL 8.0 (https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-an...). BigQuery is a little out of scope, IMO.
- tejasmanohar 9y ago
  Frankly, I’d say both are out of scope since access patterns are entirely different. They make different trade-offs so anything less than a holistic review of both is a false comparison.

orf 9y ago

The first feature in the deck is FILTER, which I can't help but mention I added support for (with a case/when fallback) to Django 2.0. So, if you're using the new Django release you can use this syntax on any aggregate/annotate via the filter kwarg[1] now. There was one user report on the github PR that said FILTER was 10-15% faster over the equivalent CASE/WHEN, due to the null filtering I think. It's a shame only Postgres supports this supposedly standard syntax. Edit: Heh, I initially got (stole) the idea of using FILTER + CASE from modern-sql.com[2], which is run by the author of this slide deck. So I guess I aught to thank Markus for this! 1. https://docs.djangoproject.com/en/2.0/ref/models/conditional-expressions/#conditional-aggregation https://docs.djangoproject.com/en/2.0/ref/models/conditional... 2. http://modern-sql.com/feature/filter http://modern-sql.com/feature/filter

MarkusWinand 9y ago
Welcome :)
simonw 9y ago
That's really clever - I just blogged about it https://simonwillison.net/2018/Feb/3/conditional-aggregation-django-20/ https://simonwillison.net/2018/Feb/3/conditional-aggregation...
- orf 9y ago
  Thank you Simon!

zkomp 9y ago

It was a good talk at fosdempgday. Well paced, funny at times, esp the part comparing booleans with mysql "booleans" and "check constraints" (spoiler neither boolean nor checked) Too bad I'm stuck on 9.1 for the time being which lack many of these really nice features.

daurnimator 9y ago
Why are you stuck on 9.1? 9.2 was released in september 2012. Upstream support (bug+security fixes) for 9.1 was dropped in november 2016.
- zkomp 9y ago
  I know... The main reason is it is hard to avoid downtime when upgrading older postgres versions (given a large and bussy enough server) The situation is getting so much better with logical decoding in recent version. When the upgrade day finally come it will be like christmas with Lateral, Filter, JsonB, Percentile_Disc/Cont, and "upserts"
  - daurnimator 9y ago
    9.1 => 9.6 is the last supported version to upgrade to. You're going to have to upgrade in 2 steps to get to 10 anyway.

mjw1007 9y ago

Some of the information looks off. The chart shows CHECK constraints appearing in PostgreSQL in 8.3, but they've been there since at least 7.1.

MarkusWinand 9y ago
I'm sorry it is not so obvious: there is a difference between red, which means it doesn't work, and transparent, which means I didn't test any further back.

combatentropy 9y ago

I learned a few things, despite having used Postgres for many years: (1) the filter clause with aggregates to more succinctly pivot tables, (2) the not-valid clause with check constraints to apply only to new data while leaving old rows alone, and (3) something about inverse-distribution functions --- I actually didn't learn these yet but bookmarked it for later, because it's too hard to read on a Saturday ;) It really is amazing how much you can prepare your data before it leaves your database, or how raw you can leave your data before sending it there, letting Postgres take care of it. I wonder how much middle code would be saved (PHP, Python, Perl, etc.) if more programmers knew more SQL, and especially if they chose Postgres more often. I also wonder why SQL seems to be the language that developers know least, even though it seems like it should be easiest, with its declarative, natural-sounding syntax. (I admit that the naturalness is deceptive. Just because "select color, count(1) from favorites where age < 13 having count(1) > 3 order by 1" sounds like English doesn't mean you can tell exactly what it will do before reading the manual.)

barrkel 9y ago
The language isn't particularly compositional (why both HAVING and WHERE? why must we repeat SELECT column expressions in HAVING, ORDER BY etc?), and the "natural" grammar means it usually isn't very discoverable in an editor. The grammar puts operations out of order (SELECT should be last! And usable multiple times, to simplify expressions!), and multi-word clauses with contextual meaning are harder to complete effectively in a syntax-aware editor. SQL is also a bit of a two-edged sword. You can do very powerful things with it that are infeasible in normal code, because they'd require round-tripping enormous quantities of data. OTOH it's easy to write code that performs and scales dreadfully, which will normally have knock-on effects across the whole production system, which is pretty scary. There are usually multiple ways of writing what is semantically the same query, that perform quite differently, depending on how smart your target planner is. Despite the language being nominally declarative, you still need to keep in mind potential execution plans as you write the code, and verify on reasonably sized data sets that the plan you expected, or one better than it, was selected. It's been my experience that most developers don't have cost model estimation baked into their brains, so the more advanced cost estimation you need to do with a declarative language is even rarer.

deleted 9y ago

[deleted]