Standard SQL features where PostgreSQL beats its competitors | Hacker News Reader

Standard SQL features where PostgreSQL beats its competitors

slideshare.net

96 pointsMarkusWinand16 comments

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

2. http://modern-sql.com/feature/filter

That's really clever - I just blogged about it https://simonwillison.net/2018/Feb/3/conditional-aggregation...

Thank you Simon!

Welcome :)

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

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.

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.

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.

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"

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.

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.

Redshift is based on PostgreSQL 8.0 (https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-an...). BigQuery is a little out of scope, IMO.

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.

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.

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.

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.

Reply on news.ycombinator.com