
Standard SQL features where PostgreSQL beats its competitors - MarkusWinand
https://www.slideshare.net/MarkusWinand/standard-sql-features-where-postgresql-beats-its-competitors
======
orf
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...](https://docs.djangoproject.com/en/2.0/ref/models/conditional-
expressions/#conditional-aggregation)

2\. [http://modern-sql.com/feature/filter](http://modern-
sql.com/feature/filter)

~~~
simonw
That's really clever - I just blogged about it
[https://simonwillison.net/2018/Feb/3/conditional-
aggregation...](https://simonwillison.net/2018/Feb/3/conditional-aggregation-
django-20/)

~~~
orf
Thank you Simon!

------
combatentropy
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
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.

------
zkomp
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
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
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
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.

------
minimaxir
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
Redshift is based on PostgreSQL 8.0
([https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-
an...](https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-
sql.html)). BigQuery is a little out of scope, IMO.

~~~
tejasmanohar
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.

------
craigkerstiens
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.

------
mjw1007
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
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.

