Michael Paquier

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.

Awesome news! thank you for sharing this. I found this post which confirms IRC and suggests it was an improvement in PG 12: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ Today is a great day to have been wrong on the Internet. :)

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

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…

Yep! A lot of older posts about CTEs largely advice against them for this reason. Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it. When not materializing, filters may push through the CTEs.

Until postgres 12 (if you were using postgres) CTEs were an optimization fence. Where filters would not get pushed down into the CTE if they were only specified outside the CTE (but in fields from the CTE). https://paquier.xyz/postgresql-2/postgres-12-with-materialize/

As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query. Exceptions: 1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call. 2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized. https://paquier.xyz/postgresql-2/postgres-12-with-materialize/

Did you happen to try it with a CTE/WITH expression? Prior versions are not supposed to inline those, and the current version allows you to explicitly {pre,pro}scribe inlining: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/

One of the big changes in 12 allows the optimizer to work properly with CTEs, which was a major barrier to using the more expressive language features. There a good writeup here: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/

SCRAM authentication looks like a nice security improvement also. No description on the site but http://paquier.xyz/postgresql-2/postgres-10-scram-authentication/ gives a good overview.

That's weird, what's your checkpoint_timeout? The default is 5 minutes, so you certainly shouldn't be pushing to S3 every 10s if the DB is idle. Apparently PG 10 will improve your use case, though: http://paquier.xyz/postgresql-2/postgres-10-checkpoint-skip/ EDIT: Also, wal-e compresses by default, so even those regular WAL files should be much smaller than 10MB. Are you sure the DB is really idle?

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-res… (example hook that does this)

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/