Michael Paquier

Sorry. When you said "really proud" and "so great", it was perhaps an emotive choice of words.

In the end the semantics of CTEs should be much the same throughout. There may be small differences, and perhaps larger ones such as being able to update through a CTE, something like

with x as (...)
update x set ...

I don't know if PG supprts this, and it wouldn't break my heart if it didn't. It would be easy to work around.

Also IIRC PG has 'materialized' and 'recursive' keywords. No biggie.

So the semantics are substantially the same. Difference is, how the optimiser treats it. That means you will get the same results back but the time difference may be enormous. This explains it: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/

In my previous work, the predicate pushdown (which is the posh term for the optimiser rewriting the query as the above link demonstrates) made CTEs usable. Without them, performance would have destroyed the company.

Can we get round it? Mostly, yes, but it would have been more work for us (meaning more human hours) which the DB optimiser should have saved us from.

The only unique thing CTEs bring to the party is recursion. Otherwise they are no more than a convenience, albeit a great one.

HTH

EDIT: MSSQL has a good optimiser when it works. I recently wrote a trivial CTE and had it take a minute to run, WTF? Looked at the query plan, something was very wrong there, pulled out the CTE part and put it into a temp table, ran in…

The state can never fit on just a single machine, unless you're perfectly confident that single machine will never fail :)

Distributed consistency as discussed in this blog post is not really about scaling write throughput (which is the problem solved by horizontal/vertical partitioning), but rather about keeping replicas in sync with each other, seeing the same state. The usual suspects, MySQL and Postgres, don't really have any sort of replication that's totally synchronous (although they're getting there[1, 2]).

As for scaling writes, I agree that chopping up your Postgres database is the safest choice for now, but it's generally true that this is hacky, and kind of a pain to architect your application around it. Solutions like Spanner and FaunaDB aim to free the application developer from this burden, without having to give up the large feature set of relational databases by using a NoSQL database.

[1] https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

[2] http://paquier.xyz/postgresql-2/postgres-10-quorum-sync/

You mean event triggers[1] right?

For the problem mentioned in the blog post — protecting roles — it's not possible to use event triggers because PostgreSQL doesn't fire[2] a DDL event for `CREATE/ALTER/DROP ROLE`.

If an event for role modification was added, then maybe the same could be accomplished with event triggers(perhaps in pure SQL). Though hooks will always be more strict, since you can enforce conditions by preloading the extension in C code, without custom SQL. Also, hooks can enforce a condition even in presence of superusers[3], something that event triggers can't because a superuser can drop them.

[1]: https://www.postgresql.org/docs/current/sql-createeventtrigger.html

[2]: https://www.postgresql.org/docs/current/event-trigger-matrix.html (full list of events fired)

[3]: https://paquier.xyz/postgresql-2/hooks-in-postgres-super-superuser-restrictions/ (example hook that does this)

can confirm.

That said, one can often capture much of the performance benefit with Postgres indexes - basically, instead of trying to tightly-pack the base tables, copy the data into indexes which are tightly packed exactly as you need. Put another way, Postgres indexes are synchronously updated materialized views - and yes, the Postgres planner/optimizer will automatically answer queries from an index if it's faster and all the columns are in there.

I esp recommend reading about function and expression indexes, as well as the (new) covering indexes.

http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-indexing-tricks-that-surprise-everyone
https://www.endpoint.com/blog/2013/06/10/postgresql-functional-indexes
https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/

This is a bit outside my area of expertise. Not sure about the best way to do the automated promotion process, but looking through these docs there's mention of a change to libpq that allows you to specify all the potential hosts and then have it only connect to whichever one is accepting writes (target_session_attrs).

http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/

Edit to add: I'm also interested to hear the approaches people use here. Today we've been setting up WAL-E to run our db backups so we're in that headspace. I'd actually like to decommission my main db server and provision another one to take its place. Getting the new one up and running from the wal-e archives is easy enough, the switch-over is a little more complex.

Take a look at https://github.com/hapostgres/pg_auto_failover it's quite simple to use and manage. You can use libpq support for target_session_attrs (been there since Pg 10 - https://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ ) so you don't need active loadbalancer in front of your Pg cluster.

You can however create your own health/status check service on top of pg_autoctl show state and use HAProxy if required.

I don't think there's something easier to setup and manage than pg_auto_failover, Patroni always appeared very complicated to me.