Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about concurrency. Listen, notify ZFS and PG rewind. I'm creston Jameson. And this is scaling postgres. Episode 22.
[00:00:15] You all right. Our first post this week is PostgreSQL eventbased processing, and this is from the Tap Oueh.org blog. Now, this continues on with his series on modeling for concurrency. So in this post he talks about how to process events coming in. Now, previously he was talking about using Tweet Activity and using Inserts as opposed to updates. And the last thing that was discussed was computing and Caching where he talked about using materialized views. So in this post he talks about using triggers to actually update a summary table. So basically he talks about creating a daily counters that tracks by day the retreats and favorites that are being tweeted. He creates a basic function to be able to update those values when a new value gets inserted into the Tweet Activity detail. So for each row, execute this procedure that he created. And in doing this, he shows by day you can see retweets things that were deretweeted, what was favored and defavorited. Now, he says on purpose he made this first implementation an antipattern because with regard to concurrency scenarios, it's not really going to work that well because of the case where you have where the first transaction attempts to update a daily counter, it doesn't see it and wants to insert it. The same thing happens with a second transaction it wants to update, it doesn't see it goes to Insert. And one of those transactions is going to fail because it's going to try to insert a transaction that's already there. And basically a better implementation of this solution is to use the on conflict clause of the Insert into command. In addition, he adjusts the counter to have it be by message ID instead of by day and using Insert into. With on conflict, you avoid that type of issues. However, you still do have a concurrency problem because essentially you're taking all of the inserts and you're still doing an update to a single table. And if you have a lot of inserts, a lot of Tweet activity, you're going to run into concurrency and locking issues trying to update that summary table. It's still not a great high concurrency implementation. Now, in his next related post, he discusses using Listen and Notify as one implementation solution, and I'll mention a third that was mentioned in a previous episode of Scaling Postgres. However, if you don't have a lot of activity, this on conflict do and update implementation could work for you.
[00:03:00] The next post is PostgreSQL. Listen notify and again, this is from the Tapoeh.org blog and the next post in the PostgreSQL concurrency series he's doing. And here he talks about using Listen and Notify. This is basically Pub sub, and he's using it to create an event publication system that notifies a third service. In this case, a Go client that he has created. So essentially, using Listen Notify, he kind of gets around some of the concurrency issues of the previous post that you can run into by doing the update. So this is a pretty long post and it goes into a lot of detail. But if you're interested in using Listen Notify in this way, I definitely suggest checking out this post, particularly if you're thinking about using it for other purposes. But one section I thought was very interesting is his listing of the limitations of Listen Notify from the perspective that notifications are only sent to connected client connections. And quote, it is not possible to implement queuing correctly with the PostgreSQL Listen Notify feature. So an interesting post and definitely one I suggest checking out.
[00:04:12] Now, this was a post mentioned in a previous episode of Scaling Postgres, but if you're looking to do summary tables or roll up tables of events coming in, this post offers a third solution to what was examined previously and it's called Scalable Incremental data Aggregation on Postgres and Citus. Now, this is from the Citus Data blog, but basically the implementation can be carried out with any version of postgres. So if you're looking for a third alternative that may be a possibly better implementation or at least a different one not using Listen Notify that can handle concurrency, I definitely suggest going back and checking out this post.
[00:04:54] The third post this week is Amazon EC two instance update, faster processes and more memory. Now, I mention this because these are for larger instance sizes. One set is the Z 1D, which is for compute intensive instances. So basically high CPU power and the D means it has a local NVMe SSD storage. They've also brought out the R Five and the R 5D. So again, it has some local NVMe storage with the D designation. And because these are memory instances, I typically see these used a lot for database systems. What's even more interesting is that quote we are also planning to launch R Five bare metal, r 5d bare metal and Z 1d bare metal instances. So from the previous post we saw that there are some advantages to running on Bare Metal getting the most compute power out of your instances, particularly for database applications. So if you use AWS, these are definitely some new instances that are coming out you may want to consider migrating to.
[00:06:02] The next post is ZFS private beta on Citus Cloud and this is from the Citrusdata.com Blog. This is a general announcement about ZFS being available on Citus Cloud, but the intro part of this post gives a good explanation of ZFS kind of what it is and some of the potential advantages. A lot of times I see PostgreSQL installed on Linux and using the ext four that they're mentioning here for their disk system. However, ZFS is one that offers compression which can reduce the size of the data on the disk as well as potentially offer some performance improvements because your disks don't have to travel as far to pull out the same amount of data. So there is the potential for a CPU cost to get a disk access gain by using something like CFS. And we even had some previous posts that we covered on scaling postgres. Talking about an implementation where someone was using things like foreign data wrappers with a different file system to store essentially older data on a compressed I believe they didn't end up going with ZFS, but another solution. But if you have a lot of data you're dealing with, you may want to check out different compression solutions. And this is one post that talks about using ZFS for that purpose. So if you're interested or needing to do that, definitely a blog post to check out.
[00:07:25] The next post is relatively short, but it's Pgrewind and checkpoints. Caution, this is from the Build blog and he's talking about PG Rewind. Now this is a utility that allows you when you're doing streaming replication, to be able to fail back or to go back to a previous replica. So we have a primary database and a replica. If you promote that replica, you can use PG Rewind in certain cases on the old primary to be able to start enable it to become a replica of the new primary. And essentially you can flip back and forth between primary to replica, et cetera, between two different database systems. Now, they ran into particular issue because they were testing this flip back and forth process and they did it very quickly and they noticed some errors due to timeline issues. And they believe, and it's a hypothesis at this point that the checkpointing process hadn't fully completed yet. And one thing that they suggested doing is doing a start a select PG underscore start underscore backup and then do a stop. However, other commenters of this post suggesting just issuing a checkpoint to see if that could potentially work to basically introduce a delay to ensure a checkpoint occurs prior to doing a PG Rewind flip. So if you use PG Rewind, definitely a blog post to check out to make sure that you're not running into the particular problems that they ran into.
[00:08:59] The next post is that Google Checksum tool and this is again from thebuild.com and this talks about a recent tool that's been released for doing on disk checksums in PostgreSQL by Google. So this is definitely a good tool to check out to verify that your database files are valid. And they talk about here how PostgreSQL Eleven has a similar capability. However, you have to do it with your database system offline, whereas this tool developed by Google and it labels you to do it for files that are online or offline. So definitely a set of tools to check out just to verify the integrity of your database on a periodic basis.
[00:09:40] The last post is uniting SQL and NoSQL for monitoring why PostgreSQL is the ultimate data store for Prometheus and the subtitle is how to use Prometheus, PostgreSQL, and TimescaleDB in Grafana for storing, analyzing, and visualizing metrics. So Prometheus collects system metrics or application metrics from an application or application environment. TimescaleDB is a extension of Postgres optimized for storing time series data, and Grafana basically creates graphs of data from different sources, of course, including Prometheus. Now, this is on the Timescale.com blog, so of course they're going to advocate using Timescale DB in this instance, but they have a few different tools that they've outlined, and this is a pretty long post, but they have a few different tools that they've outlined to help integrate Prometheus with PostgreSQL. So what they mentioned is that Prometheus is good for storing time series data, but it is basically a good short term storage solution. And that with some of the tools they've introduced, you can actually send that data. That's being retained to PostgreSQL for long term storage and analysis utilizing some of the special features of Timescale DB to be able to analyze it in an efficient fashion. So if you're using PostgreSQL and potentially Prometheus and Grafana, maybe this is a potential solution you want to look out to be able to do a longer term retention of your metrics data for your systems. So definitely a blog post to check out that does it. For this episode of Scaling Postgres, you can get links to all the content presented in the show notes. Be sure to head over to Scalingposgres.com, where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks, Our.
