Concurrency, Listen & Notify, ZFS, pg_rewind | Scaling Postgres 22

In this episode of Scaling Postgres, we review articles covering concurrency, how to use listen & notify, zfs, caution regarding pg_rewind.

Content Discussed

• Asynchronous Notifications in Postgres
• PostgreSQL Event Based Processing
• PostgreSQL LISTEN/NOTIFY
• Amazon EC2 Instance Update – Faster Processors and More Memory
• ZFS Private Beta on Citus Cloud
• pg_rewind and checkpoints: caution!
• That Google Checksum Tool
• Uniting SQL and NoSQL for Monitoring: Why PostgreSQL is the ultimate data store for Prometheus

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres, we talk about concurrency. Listen, notify ZFSand 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 thisis from the Tap Oueh.org blog. Now, this continues on with his series on modeling forconcurrency. So in this post he talks about how to process events coming in. Now, previously hewas talking about using Tweet Activity and using Inserts as opposed to updates. And the lastthing that was discussed was computing and Caching where he talked about using materializedviews. So in this post he talks about using triggers to actually update a summary table. Sobasically he talks about creating a daily counters that tracks by day the retreats and favoritesthat are being tweeted. He creates a basic function to be able to update those values when anew value gets inserted into the Tweet Activity detail. So for each row, execute this procedurethat he created. And in doing this, he shows by day you can see retweets things that werederetweeted, what was favored and defavorited. Now, he says on purpose he made this firstimplementation an antipattern because with regard to concurrency scenarios, it's not really goingto work that well because of the case where you have where the first transaction attempts toupdate a daily counter, it doesn't see it and wants to insert it. The same thing happens with asecond transaction it wants to update, it doesn't see it goes to Insert. And one of thosetransactions 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 Insertinto command. In addition, he adjusts the counter to have it be by message ID instead of by dayand using Insert into. With on conflict, you avoid that type of issues. However, you still do have aconcurrency problem because essentially you're taking all of the inserts and you're still doing anupdate to a single table. And if you have a lot of inserts, a lot of Tweet activity, you're going torun into concurrency and locking issues trying to update that summary table. It's still not a greathigh concurrency implementation. Now, in his next related post, he discusses using Listen andNotify as one implementation solution, and I'll mention a third that was mentioned in a previousepisode of Scaling Postgres. However, if you don't have a lot of activity, this on conflict do andupdate implementation could work for you.

[00:03:00] The next post is PostgreSQL. Listen notify and again, this is from the Tapoeh.org blogand the next post in the PostgreSQL concurrency series he's doing. And here he talks aboutusing Listen and Notify. This is basically Pub sub, and he's using it to create an event publicationsystem 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 postthat you can run into by doing the update. So this is a pretty long post and it goes into a lot ofdetail. But if you're interested in using Listen Notify in this way, I definitely suggest checking outthis post, particularly if you're thinking about using it for other purposes. But one section Ithought was very interesting is his listing of the limitations of Listen Notify from the perspectivethat notifications are only sent to connected client connections. And quote, it is not possible toimplement queuing correctly with the PostgreSQL Listen Notify feature. So an interesting postand definitely one I suggest checking out.

[00:04:12] Now, this was a post mentioned in a previous episode of Scaling Postgres, but ifyou're looking to do summary tables or roll up tables of events coming in, this post offers a thirdsolution to what was examined previously and it's called Scalable Incremental data Aggregationon Postgres and Citus. Now, this is from the Citus Data blog, but basically the implementationcan be carried out with any version of postgres. So if you're looking for a third alternative thatmay be a possibly better implementation or at least a different one not using Listen Notify thatcan 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 andmore memory. Now, --I mention this because these are for larger instance sizes. One set is the Z 1D, which is forcompute intensive instances. So basically high CPU power and the D means it has a localNVMe SSD storage. They've also brought out the R Five and the R 5D. So again, it has somelocal NVMe storage with the D designation. And because these are memory instances, I typicallysee these used a lot for database systems. What's even more interesting is that quote we arealso planning to launch R Five bare metal, r 5d bare metal and Z 1d bare metal instances. Sofrom the previous post we saw that there are some advantages to running on Bare Metal gettingthe most compute power out of your instances, particularly for database applications. So if youuse AWS, these are definitely some new instances that are coming out you may want toconsider migrating to.

[00:06:02] The next post is ZFS private beta on Citus Cloud and this is from the Citrusdata.comBlog. This is a general announcement about ZFS being available on Citus Cloud, but the intropart of this post gives a good explanation of ZFS kind of what it is and some of the potentialadvantages. A lot of times I see PostgreSQL installed on Linux and using the ext four thatthey're mentioning here for their disk system. However, ZFS is one that offers compressionwhich can reduce the size of the data on the disk as well as potentially offer some performanceimprovements because your disks don't have to travel as far to pull out the same amount ofdata. So there is the potential for a CPU cost to get a disk access gain by using something likeCFS. And we even had some previous posts that we covered on scaling postgres. Talking aboutan implementation where someone was using things like foreign data wrappers with a differentfile system to store essentially older data on a compressed I believe they didn't end up goingwith ZFS, but another solution. But if you have a lot of data you're dealing with, you may want tocheck out different compression solutions. And this is one post that talks about using ZFS forthat 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 isfrom the Build blog and he's talking about PG Rewind. Now this is a utility that allows you whenyou're doing streaming replication, to be able to fail back or to go back to a previous replica. Sowe have a primary database and a replica. If you promote that replica, you can use PG Rewindin certain cases on the old primary to be able to start enable it to become a replica of the newprimary. 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 weretesting this flip back and forth process and they did it very quickly and they noticed some errorsdue to timeline issues. And they believe, and it's a hypothesis at this point that the checkpointingprocess hadn't fully completed yet. And one thing that they suggested doing is doing a start aselect PG underscore start underscore backup and then do a stop. However, other commentersof this post suggesting just issuing a checkpoint to see if that could potentially work to basicallyintroduce a delay to ensure a checkpoint occurs prior to doing a PG Rewind flip. So if you usePG Rewind, definitely a blog post to check out to make sure that you're not running into theparticular problems that they ran into.

[00:08:59] The next post is that Google Checksum tool and this is again from thebuild.com andthis talks about a recent tool that's been released for doing on disk checksums in PostgreSQL byGoogle. 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 todo it with your database system offline, whereas this tool developed by Google and it labels youto do it for files that are online or offline. So definitely a set of tools to check out just to verify theintegrity of your database on a periodic basis.

[00:09:40] The last post is uniting SQL and NoSQL for monitoring why PostgreSQL is theultimate data store for Prometheus and the subtitle is how to use Prometheus, PostgreSQL, andTimescaleDB in Grafana for storing, analyzing, and visualizing metrics. So Prometheus collectssystem metrics or app --lication metrics from an application or application environment. TimescaleDB is a extension ofPostgres optimized for storing time series data, and Grafana basically creates graphs of datafrom 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 fewdifferent tools that they've outlined, and this is a pretty long post, but they have a few differenttools that they've outlined to help integrate Prometheus with PostgreSQL. So what theymentioned is that Prometheus is good for storing time series data, but it is basically a good shortterm storage solution. And that with some of the tools they've introduced, you can actually sendthat data. That's being retained to PostgreSQL for long term storage and analysis utilizing someof the special features of Timescale DB to be able to analyze it in an efficient fashion. So ifyou're using PostgreSQL and potentially Prometheus and Grafana, maybe this is a potentialsolution you want to look out to be able to do a longer term retention of your metrics data foryour systems. So definitely a blog post to check out that does it. For this episode of ScalingPostgres, you can get links to all the content presented in the show notes. Be sure to head overto Scalingposgres.com, where you can sign up to receive weekly notifications of each episode,or you can subscribe via YouTube or itunes. Thanks, Our. --