background

World's Best, Subtransactions, Sharding, Schema Changes | Scaling Postgres 105

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

In this episode of Scaling Postgres, we discuss how Postgres is the world's best database, performance ramifications of subtransactions, the current state of sharding and safe schema changes.

Content Discussed

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your friends, family, and coworkers continue to do well. Our first piece of content is "PostgreSQL is the world's best database". This is from 2ndquadrant.com. This just goes through a list of features that he feels makes Postgres the best database. I'll just go over some of the highlights from here. First is transparent security. So unlike a commercial vendor that usually keeps things secret until they have a solution and then resolves it, any security issue is released in the open and everybody has to work against it to find the solution. So it's very transparent working on the security. The next area is multi-version concurrency control is good for you. Now, the thing to keep in mind in terms of performance is that this, as he mentions, is best for high INSERT and SELECT workloads. So right now, UPDATES and DELETES take a bit of a hit because it marks rows as unused anymore. Once something is UPDATEd, it doesn't do an update in place. 

So essentially, if you have a lot of updates going on, it's creating more and more Postgres rows that need to be vacuumed. So there's a performance penalty for that. With some of the new storage solutions, they're talking about coming online. That may speed up some of this because that still maintains the multi-version version concurrency control, but it's just something to keep in mind in terms of performance. Next is PostgreSQL does everything. He proposes all of the features you would see in NoSQL, Riak, REACT, Redis, Mongo, and it can do them. Although with, of course, maybe not some of the richness that exists in those solutions. The next section is to hook it to a map. So Postgres very significantly used and chose PostgreSQL as its backend database. So there's a lot of support for that. PostgreSQL is growing, leading the way in open source. It is a very stable, well-run project. I have to agree with that. PostgreSQL builds solutions that are stable forever. Now, you can run them forever because it is totally open-source. 

There's no built-in support, of course, but you can run particular versions for others, although they still come with upgrades. Declarative is better than imperative. So he's advocating for SQL versus other types of query languages for a database. Extend PostgreSQL the way you like, so all of the different extensions that you can add to it and even write your own software to make PostgreSQL your own. Go big and go wide. Talking about a lot of performance improvements for significantly large workloads like partitioning, parallel execution, partial indexes, table spaces, caching, et cetera. All the variety of indexes that are available are GiST, SP-gist, KNN Gist, gin, BRIN, B-tree, et cetera. Roll it in, roll it out, they're talking about how you can use this as the heart of a data management platform where you can easily roll data in or send it out. 

You can use logical replication, you can use Foreign Data Wrappers for managing data. The license is wide open. So a very liberal BSD license, fantastic documentation. It's developed with test-driven development to be able to achieve, as he says, quote "This provides for '0 known bug' releases". Internationalization and localization. You can cloud operation, you can operate in the cloud standards compliance, always working towards the standards and language features. So I felt this was an interesting post. You may want to take a look at all the different features that Postgres has, particularly if you're having discussions with other developers with regard to its use.

The next piece of content is "SUBTRANSACTIONS AND PERFORMANCE IN POSTGRESQL". This is from cybertec-postgresql.com. So this is a post he says came from troubleshooting a PostgreSQL performance problem. So first it goes into what are subtransactions? So a transaction, basically you do a BEGIN, you start some level of work and then you eventually end it or COMMIT it. And within that transaction, you can choose to roll it back before the COMMIT and work is done as a unit. Now, subtransactions, he says, are a way to roll back just part of what you are working on, and you can start a subtransaction inside a transaction by creating a SAVEPOINT with a name as an identifier. And then if you want to roll back to that point in the transaction, you just do ROLLBACK TO the save point and then give the name you provided. And here's the use of a subtransaction. So you do a BEGIN, you do some work, you do something that results in an ERROR, division by 0 error. And then when you continued to try to do more work, that transaction was aborted so everything was lost. 

But if you do the same thing, you BEGIN a transaction, you do some work, do a SELECT statement and then you create the SAVEPOINT. Then you create that division by 0 error and at this point in the transaction, you can do a ROLLBACK the save point and now you can do some more work. So it's as if this error never happened because you're reverting to the save point you created. Now, in terms of subtransactions in PL/pgSQL, anytime you do a transaction that has an EXCEPTION block, essentially you're creating a subtransaction here even if it's not executed because I believe we saw that from a previous post that was talking about these and we'll mention why this is a little bit relevant later. Then he talks about compatibility with other databases. Other databases have different behaviors. For example, they may constantly autocommit things whereas PostgreSQL waits for that commit. It doesn't autocommit something. But there are drivers that are used with PostgreSQL. 

As they're saying, the JDBC driver has a parameter called autosave, and that could potentially lead to some performance problems. So he did this as a performance test. He created an unlocked table halfway filled with a fillfactor, inserted some data into it, and then ran pgbench and had a few queries. And he set up a SAVEPOINT. So it ran a SAVEPOINT, set a random number, and then executed a SELECT and then an UPDATE. And he did two tests. One had 60 save points for test number one. The next test had 90 save points. We'll see why this is relevant here. So he had a machine with eight cores. He set the test to run 6 concurrent clients for ten minutes. And then this is the pgbench he ran, and it gives the output here. Then he used this perf command to be able to track what the database is doing. As you can see in test one with 60 subtransactions, the transactions per second were 167. 

Nothing looks relatively out of the ordinary here, but when he goes up to 90 subtransactions, the transaction per second drops by less than half to 60, say 69 transactions per second. So less than half the previous 167, as well as had a lot of work being spent in locking areas like LWLockAttemptLock, LWLockRelease, and LWLockAcquire. So he says, quote "Even if we take into account that transactions in test 2 are one longer, there is still a performance regression of 60% compared to test 1". Now here, he goes over the implementation of how some subtransactions happen, how transactions are stored in the pg_xact directory, how subtransactions are stored in the pg_subtrans subdirectory, and then goes into the visibility, and how you can define whether these transactions and some transactions are visible. But the important point is here it creates a bit of a snapshot to determine what's visible. And it, quote "...has room for at most 64 non-aborted subtransactions per session". 

And there's one transaction allowed per session. "If there are more than 64 subtransactions, the snapshot is marked as suboverflowed". So basically, that's why 60 transactions performed well. But when he went to 90 subtransactions in this test, that's why it slowed down. So he says a quote "A suboverflowed snapshot does not contain all the data required to determine visibility, so PostgreSQL will occasionally have to resort to pg_subtrans". So that results in essentially lightweight locks trying to look up this information. Now, he talks a couple of different ways. How you can diagnose the problem of too many subtransactions is that number one, if your workload performs well when running a single thread a bit badly when run in several concurrent database sessions. Two, you often see the wait event subtransControlLock in pg_stat_activity. Or three, you export a snapshot using the pg_export_snapshot function.

The resulting file in the pg_snapshots subdirectory of the data directory will contain the line SOF:1 to indicate that the subtransaction array overflowed. Now, the thing to keep in mind is that we've been talking about using save points and that's how he did his test here. But this also applies to exception blocks because exception blocks create subtransactions even when not fired. So you can imagine in PL/pgSQL code if you have a loop that goes over and executes this multiple times, once you hit that 64 limit, you're going to start getting performance ramifications of that. So definitely something to keep in mind, which kind of goes to what he's signifying here in the image at the top of this post is that when you're looping over a thousand times and you have an exception block, you're going to start running into performance problems due to subtransactions. So definitely a great blog post. I suggest you check it out.

The next post is "Horizontal Scalability/Sharding in PostgreSQL Core - Missing pieces of the puzzle". This is from highgo.ca. So this is a follow-up post from a series of three that they did back in August 2019 talking about the future of sharding with PostgreSQL. And basically, the intent is to go the Foreign Data Wrapper route. So use Foreign Data Wrappers to create separate foreign servers and essentially those would be the shards. So you have a primary database and it uses the Foreign Data Wrapper technique to treat those as separate shards of a common database. He goes over that process discussing here, how you could potentially create it. A little bit of this is ideally what should be done, but what are the missing pieces of the puzzle? So basically the core PostgreSQL community wants to eventually get to support sharding. 

This is probably similar to a Citus-type solution, but in terms of what's missing is number one, you would need a global Transaction Manager. A two-phase commit for Foreign Data Wrapper transactions. So he mentioned this is in the process and the state of it as well as HighGo software is contributing to this and he says it looks promising for Postgres 14. The next area is a global snapshot for cluster-wide read consistency. So basically visibility of where transactions are across the cluster of machines. Again he goes over the current status of this, although he doesn't project here when it will be. So it still may be a way off. The next area is parallel foreign data wrapper scan, so basically asynchronous query execution. And it looks like this feature is as he says "While this patch is destined for PG-14", they may get a piece of it in Postgres 13. So that's something that could be potentially beneficial. 

And lastly is mostly regarding management, he says shard management. So the partitions on foreign servers do not currently get created automatically as described above. So basically as the management of a cluster, how are you going to handle that in the creation of the different tables on the foreign servers? So this is something that needs to be investigated. Now, they also mentioned they have their own version of PostgreSQL that does something like this, but you can choose to look at that if you'd like. Now, apart from this, he also says there's a need for general management tools as well. So for example, how do you manage the cluster monitor, the machines? How do you do backup and recovery when you have all these different databases sharded around? How do you support High Availability if one of the foreign servers goes down, how do you handle that? So a lot of areas of maintenance and management you need to consider. But if you want to look for what the future of sharding is, here's an updated post you can check out.

The next post is "Fear-free PostgreSQL migrations for Rails". This is from gocardless.com. Now this is dedicated to Rails and it's talking about database migrations that alter the schema essentially of your database. They're talking about how schema changes can run into problems. So first he goes over migrations, what they are, how they work, and then mentions how you can run into problems. Now, for versions prior to 11, if you added a default it would lock the table. That hasn't happened since 11, but there are other areas where you can encounter problems. When a table gets an ACCESS EXCLUSIVE lock, you could get stuck behind a lock queue because there are other locks in front of it and that lock is waiting in a queue that locks everything up. 

So it can still cause problems and essentially downtime for your production environment if you don't do migrations carefully. So they describe that, but then they go over to potentially solving the problem and that they had developed a gem, which is a Ruby library, essentially, called activerecord-safer_migrations. To help prevent that, well, now they have a new solution called Nandi. Now, not many people may be using Ruby, but what's interesting is the techniques that they use. You may want to consult this library or look at what they do. One of the main things they do is they use a lock timeout and a statement timeout. So if any statement exceeds a particular amount of time, it airs out, or if any lock is maintained for greater than 5 seconds, it goes ahead and stops the migration. 

So those are great things to have. But also when you're creating a foreign key, you're locking two different tables with an ACCESS EXCLUSIVE lock and there's a particular technique you need to do in order to not bring your database down. And this library, Nandi, does it for you. So for example, the first step it does is it creates the new column that's needed, then it creates the constraint, and finally validates the constraint. So that's a process to avoid issues and they're continuing to work on this library. So if you want to learn more about ways to migrate your schema safely, definitely check out this blog post, particularly if you use Ruby or Rails. 

The next piece of content is "How to Provision and Deploy Highly Available Postgres with TerraForm and Ansible Automation Scripts". This is from enterprisedb.com and essentially on GitHub, they've released a Postgres deployment repository that contains Terraforms and Ansible scripts to be able to stand up a replicated environment with management tools as well. Now they are using the EnterpriseDB management tools, but still, the fact that they open-sourced it you could definitely look at how they have their Terraform set up, how they have their Ansible set up, to see if you want to use that or even modify it for your own use cases to be able to manage your postgres deployment. So if you're interested in that, definitely a blog post to check out.

The next piece of content is actually a PDF from momjian.us. "Databases, Containers, and the Cloud". So he talks about using Kubernetes and database containers for managing databases and he goes over what it is, and how it can work. I found slide 14 interesting because of the benefits for databases and how rapid creation destructs less overhead than the VM. They're not huge benefits for databases. There's a little bit of a benefit for scaling and migration. In his estimation. The most powerful benefit is automated deployment, although you could be using something like TerraForm and Ansible to essentially do the same thing, which is actually what I

use. I use Ansible to do my deployments, but interesting nonetheless. If you want to check out the Kubernetes and Postgres presentation.

The next piece of content is "Webinar: State of Full Text Search in PostgreSQL 12 [Follow Up]". This is from 2ndquadrant.com. Now this is a webinar that covers a YouTube video we posted actually in Episode 100 about full-text search. So this is the same individual in the presentation and it's a webinar version. It's a little bit longer. So I believe I preferred the version that was presented in Episode 100, but you can check out the webinar version of it just by clicking here to register.

Also from 2ndquadrant.com is "WITH a little help from my friends". So this is basically for Postgres 12 talking about WITH and CTEs and the enhancement that was done in terms of inlining as well as how to use it in use cases.

Next post, also from 2ndquadrant.com is "How to deploy a multi-master BDR cluster in Kubernetes". So again, BDR is bi-directional replication, and how you would deploy that using Kubernetes. They have a video that shows the different commands to run through as well as a text description of it. So if you're interested in BDR on Kubernetes, definitely check out this post.

The next piece of content, the PostgreSQL person of the week is Christophe Pettus. So this is an interview with Christophe and his work and contributions to Postgres.

The last post is "Querying Spatial Features with pg_featureserv". This is from crunchydata.com. So this is the next microservice that Crunchy Data has introduced for the purposes of providing a web interface for PostGIS data. They tell you how to get started with it right here. So if you're interested in that, go ahead and check out this article.

episode_image