ANSI Schmansi, Split-Brain, Performance Scenarios, Parallelism | Scaling Postgres 10

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we review articles covering ANSI Schmansi, split-brain replication issues, performance solutions to business scenarios and parallelism.

Content Discussed

YouTube Video

Podcast Audio


[00:00:00] In this episode of Scaling Postgres, we talk about ANSI Schmancy split brainperformance scenarios and parallelism. I'm creston. Jameson. And this is scaling postgresepisode ten.

[00:00:20] Our first article is ANSI Schmancy how I learned to stop Worrying and Love postgresisms. And this was actually a presentation done at the recent postgres conf in Jersey City, NewJersey. And this can be found at the Sayrope GitHub IO blog because it was actually SayropeSarkuni that did this presentation. So what this article is about is that you can choose to treatyour database in an agnostic way, meaning you store your data in postgres just as you wouldMySQL or Oracle, and use ANSI compliant SQL for everything. And potentially in the future youmay transition to another database. However, this presentation talks about how we learn to stopworrying about that and love all the features and power that comes with using postgres specificfeatures. So this was a really great presentation that covers a lot of different features ofpostgres, especially for developers and how to use it. So here's just to touch on some of theinteresting things that he mentioned.

[00:01:28] Support for dollar quoting. So instead of doing quotes like this in single quotes, youcan use dollar quotes. He talks about a hotel booking example using range types and exclusionsto make sure you don't double book a room.

[00:01:42] He talks about filtered aggregates, as opposed to using a complex case statement,you could use filtered aggregates to do these types of queries. He talks about foreign datawrappers and how they could connect up to Redis or Oracle or other database systems. So,very, very comprehensive. And I've just touched the surface on what's there, but definitely apresentation that you should review.

[00:02:08] Now there is a YouTube post of it that someone put up and it is someone manuallydoing the recording. So if you want to look at the presentation as part of it, you can check outthis YouTube video and I'll include a link in the Show Notes.

[00:02:24] The next article is GitLab crawling back online after breaking its brain in two.Database replication snafu took down three out of five PostgreSQL servers. So this is at theRegister Co UK is where I saw the initial report. It discusses an issue that GitLab was veryrecently experiencing, where they had a split brain issue. And it includes links, and again, I'llinclude these links in the Show Notes where they talk about the issue, actually theirinfrastructure issues area on GitLab itself. And they say there was a database failover that wasaccidentally performed, leading to a split brain problem. So if you're unfamiliar with this, it'sbasically you have a primary database, but suddenly you promote one of the replicas. So nowyou have two primaries and some of your traffic may be talking to the old primary, some may betalking to the new primary. So you have transactions potentially going to both and how do youresolve those? So what I found interesting about this is that it definitely goes into depth and hasdiscussions on how they're dealing with the situation and what steps they're taking.

[00:03:42] So definitely an in depth fly on the wall view of what's going on. And related to thisthey also released this Google Doc that describes the issue as well you may want to review. Sodefinitely if you are responsible for operations you might review this and potentially find out somethings that are beneficial to you.

[00:04:03] The next post is actually a presentation, it's PostgreSQL Ten Performance and you byGabriel Insulin and this and I will include the link in the show notes ofcourse. And this was particularly interesting because he actually goes over different scenarios.For example a scenario where you're collecting quotes, he discusses encountering a particularissue with performance and well a solution to that would be using the native table partitioning,that's part of version ten. He discusses another scenario where using full text JSON searching inpostgres ten could help resolve the issue. So he basically has business cases and thendiscusses how postgres ten can help resolve those. Also talks about using parallel queries forparticular queries that are hitting the database. So basically having a business case and then0:05:08] The next article related to parallelism is actually called let's speed things up and thisis from the Data blog and it discusses the new parallel features, there were a few thatwere added in version 9.6 and also version ten. And he discusses several different parametersthat you can set and even this very useful graph to help you understand what settings theseparameters affect in terms of max worker processes, max parallel workers per gather. So it'sindividual processes that are doing the parallel work and then the number of max parallelworkers and even has a suggestion on how you can configure these given a particular sizedatabase. So here he says a 32 CPU cores and SSDs. So one thing to keep in mind if you'rechanging these configuration settings is that you're not really getting anything for free. Wheneveryou're going to utilize more CPUs or CPU cores for doing parallel operations, that means there'sless available for something else. So if you're already close to getting high on your CPU, 60, 70,80%, and you're hoping that this is going to magically give you faster queries, it all depends onwhat kind of load you're experiencing. You may execute certain queries faster in parallel, but itmay be at the expense of concurrency for non parallel queries. So it's definitely something thatyou'll have to experiment with given your workload, what settings work best.

[00:06:50] So definitely a blog post to check out if you're considering using more of the parallelfeatures that PostgreSQL offers.

[00:06:59] The next post is PostgreSQL's F sync surprise. So this is an article that's discussingan interesting issue with the Linux kernel and how it handles writes to the disk. So PostgreSQLrelies upon the operating system to actually commit items to the disk. It doesn't do direct writesto the disk, it relies on the operating system, if it runs on Linux, the Linux operating system to doit. But there are some cases that some errors can occur and they're not being reported back,which means you could potentially get data corruption. So this goes into a lot of detail and a lotof quotes from I believe listservs are people discussing the issues and how to potentiallyaddress it. So if you're responsible for Ops, again, this is an interesting article to look at to justunderstand what the situation is long term. They're basically moving to Direct IO to the diskwould give Postgres greater control. But there's a quote here, it says it would be a metric ton ofwork to do that and not relying on the operating system to do it. So definitely an interesting issuethat's been brought to the forefront. The next article getting started with Patroni, and this is fromthe blog. So Patroni is a tool for allowing PostgreSQL servers to operate in highavailability configurations so it can automatically fail over from a primary to a replica that'srunning. So this post goes over how to set up Petroni and it says you do need a DistributedConfiguration manager to use Petrone and it indicates it supports Zookeeper, etcd and consolethey're using in their example, etcd or etcd. And they discuss how to install it. Installing Postgres,setting up the configuration files so that you can set up so it can automatically trigger failoverwhen needed. So if you're considering using an automatic failover for your PostgreSQLreplication instances, definitely check out this article. The next article is called Aura Migratormoving from Oracle to PostgreSQL even faster. And this is on the CyberTech Postgresql.comblog and they're describing a tool or utility called or a Migrator. And it basically allows using aOracle foreign data wrapper, enables from a Postgres server to connect to Oracle, copy oversystem tables and then copy over data as it indicates as part of a single transaction. So it'sbasically a way to migrate from an Oracle database to using a PostgreSQL database. Now itassumes that it's just going to be copying over tables, maybe some primary foreign keys, someindexes, some things of that nature that it recreates, not so much the stored procedures thatwould exist in Oracle, you would have to do those manually, but this tool can get you a lot of theway there. So if you're currently using Oracle and considering migrating to PostgreSQL,definitely a blog post to check out the next blog post is Cloud SQL for Postgres SQL nowgenerally available and ready for your production workloads. So much as I reported a nu --mber of weeks ago that Azure has gone into general availability with their PostgreSQLsolution. Now it appears that Google has done the same. So if you use Google Cloud or you'relooking for a hosting platform for your PostgreSQL database, you can check them out.

[00:10:49] And the last article is actually an in depth tutorial that I put together on PostgreSQLreplication monitoring. So I'd done a number of tutorials setting up replication in different waysand I went ahead and did a tutorial about monitoring or what you want to keep track of using thesystem views that PostgreSQL provides. So I go over that, show you how to track for differenttiming and explaining what some of the different columns mean in terms of extracting lag ordelay between a master and replications and what to watch out for when you have replicationactive. Probably the most important points is if you're using replication slots that the replicationslot is active for any active replicas, as well as you have an entry in PG Stat Replication for eachreplica. So if you use streaming replication and want to get a bit more information aboutmonitoring, I encourage you to check it out.

[00:11:48] That does it. For this episode of Scaling Postgres, you can get links to all the contentpresented in the show notes. Be sure to head over to, where you can signup to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes.Thanks. --