In the previous article here we talked about how to properly update more than one row at a time, under the title Batch Update. We did consider performances, including network round trips, and did look at the behavior of our results when used concurrently. A case where we want to apply the previous article approach is when replicating data with a trigger based solution, such as SkyTools and londiste. Well, maybe not in all cases, we need to have a amount of UPDATE trafic worthy of setting up the solution.
Now that you’re all using the wonders of Cooperative Consumers to help you efficiently and reliably implement your business constraints and offload them from the main user transactions, you’re reaching a point where you have to clean up your development environment (because that’s what happens to development environments, right?), and you want a way to start again from a clean empty place. Here we go. It used to be much more simple than that, so if you’re still using PGQ from Skytools2, just jump to the next step.
While working a new PostgreSQL architecture for an high scale project that used to be in the top 10 of internet popular web sites (in terms of visitors), I needed to be able to off load some processing from the main path: that’s called a batch job. This needs to be transactional: don’t run the job if we did rollback; the transaction, process all events that were part of the same transaction in the same transaction, etc.
Let’s begin the Skytools 3 documentation effort, which is long overdue. The code is waiting for you over at github, and is stable and working. Why is it still in release candidate status, I hear you asking? Well because it’s missing updated documentation. WalMgr is the Skytools component that manages WAL shipping for you, and archiving too. It knows how to prepare your master and standby setup, how to take a base backup and push it to the standby’s system, how to archive (at the satndby) master’s WAL files as they are produced and have the standby restore from this archive.
You can find skytools3 in debian experimental already, it’s in release candidate status. What’s missing is the documentation, so here’s an idea: I’m going to make a blog post series about skytools next features, how to use them, what they are good for, etc. This first article of the series will just list what are those new features. Here are the slides from the CHAR(11) talk I made last month, about that very subject: