Nikolay and Michael discuss queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling.
A couple of apologies-in-advance:
A couple of apologies-in-advance:
- Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPS
- We also had a couple of audio issues, sorry!
Here are links to a few things we mentioned:
- Recent discussion on Hacker News
- PgQ
- What is SKIP LOCKED (blog post by Craig Ringer)
- autovacuum
- Postgres queues (blog post by Brandur)
- pg_repack
- Our episode on partitioning
- Nikolay’s Twitter poll
- Subtransactions Considered Harmful (blog post by Nikolay)
~~~
What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.
If you would like to share this episode, here's a good link (and thank you!)
~~~
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai
- Michael Christofides, founder of pgMustard
With special thanks to:
- Jessie Draws for the amazing artwork
Creators & Guests
What is Postgres FM?
A weekly podcast about all things PostgreSQL
[00:00:00] Michael: Hello and welcome to Postgres FM a weekly show about all things PostgreSQL. I'm Michael, founder of pgMustard. This is my co-host Nikola, founder of Postgres ai hey Nikolay, what are we talking about today?
[00:00:10] Nikolay: Hi, Michael. Last time we, we talked about, Readly workloads and, the case when we have data being not changed for significant appearance. So how, Can we adjust, everything for such kind of workload? Let's talk about different case to extend this discussion.
Let's consider, uh, the case when we change things very quickly particularly let's discuss the idea of using pogs, for queuing or message system like, We have some tasks we need to process them and consider being processed and so on. So instead of using things like RabbitMQ Kafka sidekick or something like, that, let's consider having similar thing right on pause.
[00:00:56] Michael: Yeah, I hadn't about it being nearly the opposite of last Rick's episode. But yeah, there was a popular discussion on Hack Can News very recently on this and after we ch.
[00:01:05] Nikolay: it's one of the most popular topics related to database. in general, if you check how many times it was like every backend engineer, I think experienced backend engineer implemented this on their own, queue in, in relational data.
[00:01:26] Michael: Well, it feels to me a little bit like the Myas QL versus Postgres thing, right? It's like people comparing people kind of having strong opinions one way or the other
[00:01:35] Nikolay: Or storage procedures first.
[00:01:37] Michael: Yeah, exactly. It's one of those things that there are good arguments on both sides. Should we even use a relational database as a queue?
If, if so, why? Like when, and if not, Which of the, actually it doesn't tend to be which of the various queuing specific services to use. It seems to be much more of a holy war between should you use a dedicated queuing tool or can you get away with putting it in a relational database.
[00:02:02] Nikolay: Yeah, I, uh, there are two big camps.
[00:02:05] Michael: Mm-hmm.
[00:02:05] Nikolay: I would say if you are, if you, you cannot stop, uh, people from using ORMs or from using storage procedures. And, uh, in my opinion, Q in POCUS is absolutely. Nice and good idea if it's implemented, correctly, and if it's ready to scale, to grow, to handle performance aspects.
[00:02:24] Michael: Yeah. Do you want to talk about why, we use it versus a dedicated curing tool?
[00:02:29] Nikolay: yeah, let's, let's talk about why. When we develop something, at some point we need it. We need some, uh, queuing mechanism or message delivery system, message bus. So there are differences in terminology here, but it doesn't matter. We have some signal we need to propagate a signal to and need to process it.
Asynchronous, asynchronously is a key word here. Yes. Asynchronous processing and, uh, latency should be. Triple should be big, large, should be good. And uh, and that's it, right? And in some case,
[00:03:02] Michael: maybe. Maybe like exactly once,
[00:03:05] Nikolay: Well, yeah, there might be multiple consumers, there might be cases when we want to retry, so when we, we want a reliable delivery of any message and so on. Like obviously like many, many requirements that are set, but the question is, should we start using special tool separate to our relational database, such as po? Or we can use POGS itself, for example, and you cannot stop people, from using pogs. First of all, I observe, like I observe it. First of all, I did it.
And eventually I, quite quickly, I switched to P GQ from scs. It was very good in terms of recs, but it requires some maintenance and you cannot, uh, use it on managed pocus unless it's directly supported, because it requires additional, uh, workers, next to poss. you cannot use it on rds, for example, right?
But, but, uh, I liked the idea to be inside database due to number of reasons we'll return to them. Then in my career, like consulting career, a lot of consulting practice and also startup, career. So I have two things, to notice. One, quite often you come to some database trying to help them work with POS and see hotspots.
Sometimes, quite often one of or few hotspots is some table, which is, uh, having, q like workloads and it, it experiences a lot of blood, sudden degradation and performance and they ask you to help. This is very, very common request. For any consulting dba, we've experienced, which is a sign that people try to keep it in relational database and have problems.
And this is of course, cons item, right? Because, uh, if you do it in a straightforward way, not thinking in advance, you will end up, uh, with this hotspot in terms of performance issues. And then, on the other, Every few weeks on Hacker News and all other places we see a discussion.
so many discussions, like new tool to implement, uh, Q in, in pogs, or we replaced Rapid Q just with pogs. Uh, this tool, that tool for Ruby, for, for Python, anything like so many tools. I even remember how I helped. Like eight years ago or so, I have, there is a small library called Delayed Jobs.
Uh, Ruby guys shouldn't know it. It's quite old. found my clients using it and complaining like it doesn't handle our law. Just check into the code, check to PK statements and understand like two simple actions and it can scale hundred.
I even opened pool request in their GitHub, but they, I think, uh, they still didn't merge it saying We, we need support to support my sql.
That's why this like, I dunno, but I had a lot of thumbs up from people who found how to improve performance of delayed jobs library. And we will talk about how to make it, right, but back to the reasons question why inside database obvious. Obviously you don't want to manage, get another system sometimes.
Right. And then one more thing, it's, A C I D principles, right? We we want autonomous. Exactly. We want autonom. And this is probably one of the, of the brightest reason here. And we want of course backups like, uh, H characteristics and.
[00:06:31] Michael: Crash recovery,
[00:06:33] Nikolay: Many, many things like right, isolation, like many things. And pogs covers, in a very good way.
Many, many requirements we, might have. for example, if you work with some sy, if you have monolith or it's a microservice, and you, you need something internally because we need to distinguish situations when we need, queuing system inside something, or we need it as a mechanism to communicate between two services, for example.
Right. But if it's inside, you have transactions, you rely on transactions, of course you do. You don't. req, for example, or Kafka, to have inconsistent data, right? For example, we commit a transaction, but then we start, we started to write event, to our external additional tool. Somehow it didn't accept rights, inconsistency, or even worse, we had transaction, inside transaction.
We were wrote to Kafka, and then we had. Now we have event, which should not be there again, inconsistency, very not nice situation. But you know what, uh, people sold this, there are microservice patterns, it's called transactional outbox. Let me explain very, it, it's very simple idea.
I, I'm quite sure I implemented myself it several times, but people give it a nice name. those our listeners who. Our backend engineers probably know it with transactional outbox, especially if you worked with a microservices approach.
So when you inside your transaction doing some things and you need to create, uh, a message, you just write it to a special table. This like the box stable, you right a row, you can eat, everything is good, right? And then later consumer. Pull it, this message and deliver to your messaging system outside of pocus.
So can be, again, Kafka, anything sidekick, many systems, right? And, uh, and Mark has, has already delivered, right? It can be in batches somehow. A synchronously. You can speed it up with various ways, but in this case, uh, you have guaranteed delivery and eventually consistent state, which for microservices.
It's quite, quite good. It's very simple idea and it works,
[00:08:45] Michael: I do think you're right. I do think the A C I D. performance of Postgres or, you know, the strengths of Postgres there really is the main reason that it should be chosen. But I think the main reason it is chosen in general is actually the other one. I think it's the simplicity angle or not having multiple, like people are familiar with it already.
Not having to learn a new system, not having to do backups of another system, not having to do ops on another system.
[00:09:11] Nikolay: It doesn't crash as often as req does. Right.
[00:09:16] Michael: Yeah, well people, I think quite a lot of the time I'm reading these blog posts, and at least in the comments people are suggesting, you know, it isn't that that tool is necessarily bad, it's that there's a le that the people writing the post didn't know about one feature in it or didn't know how to overcome a certain issue, or they were holding the tool on.
So it's, it's not necessarily that you can't do those things with those tools. It's more. You have to learn them, you have to get good at them. And it's, it's, that takes experience and time. And why do that? If you've already got a perfectly capable tool, even if it's not the perfect tool, perfectly capable one in your back pocket that you already know the.
[00:09:53] Nikolay: right. Yeah. So this is are obvious and I, and I myself, I work with all systems, uh, in. Apps sometimes there is a mix actually. But I'm definitely, if I need to choose, I, I belong to the camp, which, uh, says it's perfectly okay idea to have q right inside. Pause. But you need to take care of a few things. Just a few things, actually. Two, two things. That's it.
[00:10:17] Michael: I am gonna interrupt you cuz I've, I actually was gonna wait, ask you this later, but I'm tempted to ask you it now is there, do you think there's a scale at which you would stop saying that you should use Postgres for cuing or is it as scalable as other?
[00:10:33] Nikolay: definitely we consider a regular POG approach with single primary node. So there is definitely, the ceiling. When we rate our CPU or disc with rights. And this right call can cannot be offloaded to standby notes. So we need to, we need multiple writer notes or multiple primary node at this case.
You can either, you need to either scale using pogs, uh, in one way or another, probably charting or, or something else. But microservices approach, uh, minimizes this need it postpones it very. Because like, if you split vertically to services, you need one of the services, uh, not to be, able to be split further.
And you need it to be so big that you already need charting, right? So you need either to scale, somehow to have multiple primary node or you need to ready to give up and, say, okay. Inside postal, we need to offload it. But this moment will be so far, far from the beginning. If you have, for example, modern Intel or amg, epic, third generation, Intel is like 128 VCPUs and any big cloud provider can provide it to easily. Or, these epic processors, 244 VCPUs, you can scale your rights really, really well. you can have dozens of thousands of rights if they are quite well tuned. And you are okay per second, I mean,
[00:12:03] Michael: Yeah, I was just gonna check for Yeah. Makes it per day according to some of the hacker
[00:12:08] Nikolay: perk. So like you, you can, you can handle billions of rights per day on one.
[00:12:13] Michael: Wow.
[00:12:15] Nikolay: It's, it's, it's definitely possible, but you will need to take care of several things. I said two, actually I have three already. Items usually I always said like, okay, yet another article is popping up on Hacker News. Stop. Like, we had it last week once again. Right? And my regular checklist for such articles or new tools, does it use for updates? Keep. And
[00:12:39] Michael: That was a huge change. That was a huge improvement,
[00:12:41] Nikolay: No, I will explain why. No, I'm, I'm about to exclude it from my list.
I will, let's discuss it. Let's discuss it. But, uh, normally it's in the list, but in my opinion, it's overrated by, uh, but you, no. Like, there are, there is a spirit, uh, Growth, right? When you have experience, you think, wow, this is super important.
When you have more experience, you think, well, it's overrated. It's not that important. There are more important things. So, okay, for up, let's keep locked. Let's keep it, in the list for a while. Maybe we'll remove it in the end of our, discussion. Second is how people address that type and bloat issues like, right,
[00:13:24] Michael: Huge. That's the one big downside, I'd say, of the Postgres. so yeah, that's, that Obviously locking is important and we should talk about how to, how to avoid, yeah. Put bloat seems to me like the one that not many people talk about and not many of the tools solved for
[00:13:40] Nikolay: If people don't talk about blood when talking about Q like workloads, uh, they do poor job. I mean, imposs, this is, uh, poor quality material because this is actually number one problem when you grow because how you handle, uh, already processed roast that apples, like we have high churn, system in this case, right?
We have something which expires very quickly. We processed it, we need to get rid of it. And, Postgres, N V C is very well known to have issues in this. Data issues or bloat issues as a consequence of that apple issues. And, uh, how you handle it defines, uh, your limits basically. And when you defines how fast you will, start having performance skills in your system.
Number three item is, uh, your queries and how efficient they are. How, many buffers they touch each time, right? So if you check explain and large buffers, so we want as low, buffer numbers is possible.
[00:14:46] Michael: Great.
[00:14:47] Nikolay: Hint, index only scan, right?
[00:14:49] Michael: Yeah, I was gonna suggest we talk about bloat first, cuz I feel like the locking one's gonna get a little bit, uh, subtle and I think we'd be, it'd be nice to have had the bloat discussion already because I think that's gonna play into it. And then maybe do the queer performance thing as like a tuning thing last. What do you think?
[00:15:07] Nikolay: yeah. Well bloat is the number one question for me these days. At least. Like, uh, if you ask me five years ago, I would probably start talking about, uh, you see I started with skip lock first, but it's already quiet advertised so everyone knows about ski skip block. We return to it and discuss why it's not, not number one anymore for me, but why blow is number.
It's because, this is where you will have this hotspot issue. Like, this is exactly when performance will start degraded very quickly. And, for example, if you like, question is, check your start user tables and check double steps. How many inserts deletes updates you. of course a lot of inserts.
It's like we have incoming, events, but then what do we do with these topples? Do we update them or just delete or both? And what's the ratio here? Looking at this ratio, we understand our pattern, right? And each update, physically consists of delete and sort. We know it. I'm not tired to keep talking and re reminding this.
If you say, update my table, set ID equals id, where ID equals one, you will have new topple even if logically you didn't change anything. Right? So it produces new topple marking old one as dead as, as deleted first, not
[00:16:30] Michael: Yep. And worse might, may update indexes as well.
[00:16:34] Nikolay: Well, uh, yes. So, so it, it depends. Uh, hot updates, uh, are smarter a little bit si since we, we didn't actually change the value, right? Probably this update will become hot, but it depends also if we have space in
this same page
[00:16:53] Michael: Even if that's an index column, that's cool if it does that. I didn't realize it
[00:16:56] Nikolay: It's okay. Id column is is primary key, probably, right? It's our surrogate primary key, but, we didn't change value, I believe hu I I'm not a hundred percent confident here.
Worth checking, but, uh, but I believe since we actually didn't change the value in this, uh, idle update, right? Uh, in this case, hot update might happen.
[00:17:18] Michael: but my main point is that updates in general we have the right amplification problem as well as just the new, uh, tick topple in the
[00:17:27] Nikolay: Yeah, I might, I might, I might be wrong. And hot update might, might not happen if, even if we don't change. But e even if I'm right, in some cases we still won't have hot update. For example, if there is no more space in the same page, we need to go to another page. In this case, definitely we will have right amplification, all indexes.
The table has, will need to receive these rights and all these rights will go to wall. It will go to backups. It will go to replication, and it's. Multi, amplification of rights, actually. Right. Amplification. That's why the name. Right. And it's, it's, it's a lot of overhead. So why I'm talking about this because in the queue approach, what do we do with our record? we probably market like, status SQLs.
Or changing from painting or deleted, like soft delete. Like we don't actually row exists, but it's marked as deleted. Right. Or like from painting to try to, to process something. We have some status or something. Right. Or we can just directly delete it. Right.
Also. But updates will produce, deleted dead topples and delete will produce dead topples. And if we go very fast and we want to go very. We will end up accumulating a lot of debt apples. And then the question, uh, will auto automat can be able to catch up with default settings, it won't be able to catch up.
Right. we will accumulate a lot, lot of debt apples and then auto automat can, will delete them. Convert to bloat. A vacuum is a converter of data oppos to b. Right. In most cases, I'm joking, right? But, but in most cases it's so, because we already created new pages, and if OWA deletes that apples not in the end of table it cannot truncate this page.
It'll keep, maybe whole page will be empty sitting there, just, contributing to bloat. That's it.
[00:19:21] Michael: I think this is slightly counterintuitive though, cause I think some of the body vacuum settings, for example, are scale based. So for example, cause we are dealing with a queue table of only, probably, of only unprocessed rows, or let, I'm assuming you're deleting them.
You, you might think that even if it's a bit bloated, it's not a big table still. But if we're talking about the case where we keep them around, or a case where we have a bit of a runaway queue for a while, or you know, in the case of an issue, this can quickly. Happen and crucially is not undone. So each time we get an accumulation, we'll accumulate more in future unless we have a strategy for over time, getting rid of that bloat.
[00:20:08] Nikolay: Well, yes, also don't, don't forget that, some long transaction might happen or on standby reporting to via host by feedback being on and preventing auto automating from deleting the taps. We, it says, I, I found that taps, but I cannot delete them because probably some transaction still needs it and it's a global problem.
So it keeps them and then again converts them to load. And this is exactly a hotspot I mentioned. This is, this looks like, we. A thousand life rows, but the table size is 10 gigabytes. You say what? 10 gigabytes, 4,000 rows. What's happening here?
[00:20:42] Michael: Yeah, you shared with me that there's a good, really good blog post by Brander about this specific issue. they argue at the end that that's a really good, reason to not have this in your main database and have maybe a separate, that's my interpretation of the
[00:20:56] Nikolay: Good, good posture from conclusions.
[00:20:59] Michael: Oh, interesting. Great. Okay. So what can we do instead?
[00:21:02] Nikolay: we can do like insane but quite reliable way. You just put vacuum, fool to crown.
Why not? It takes a second. It blocks everyone, but in inside the second have fresh state.
[00:21:17] Michael: Well, I guess it is a cue and therefore, what's the worst that happens things are delayed a little bit,
[00:21:22] Nikolay: Yeah.
[00:21:22] Michael: can't insert into it.
[00:21:23] Nikolay: Yeah.
I must admit, I never did it for, for serious clients. but I consider it as a valid idea in some cases. If you need to, like stop this. Stop this hotspot from happening. Just put it as a very, fast and like quicken dirty mitigation approach. It'll help this like latency spike of one second for, for all those who work with this table, it's not a big issue compared to constant pain they feel from, degradation related to bloat.
And that taps by the way, that taps themselves can be a problem, for example. When you delete on batches, you do everything right, but, and, and you have index only scan, then, then you start noticing that index only scan, even if it, deals with same number of rows, somehow deals with more and more buffers.
Why? Because it scans through all those that doubles all the time. And solution to that, by the way, also, like probably vacuum is one thing, but probably in some cases solution is to, I like independent queries. I, I'm big fan of them in the patent queries. They like find me next thousand of pros to process, process and delete or just delete.
The, sometimes we just need to delete clean up jobs. Something like find next thousand, delete find next. I don't care. I have, reliable way to order them. I have indexes all good index on LI scan, but somehow then more degrades, degrades over time and you realize cannot catch up and you just keep scanning through all those dead taps.
And the solution is to introduce context and. Move away from in potency. Just remember last ID, for example or something, timestamp and start, start from there. You, you skip all those dead apples,
[00:23:06] Michael: I saw yeah, the item pregnancy things have, have an interesting one. I saw somebody discussing using keys and having, and then maybe they called it some kind of jitter to make sure that if, if, for example, everyone had skipped, so maybe we'll get onto this with the skipped locks.
If, for example, all workers had skipped ahead. A job left behind that number or behind that date or when, um, like an old job so that you might have to sometimes forget, like one of your workers maybe forgets its latest, um, key every now and again. Exactly.
To go back and fetch the mold. Exactly. So there, there, I think there, there's a little bit of extra engineering needed if you go
[00:23:47] Nikolay: Uh, well, yeah, but at first run it'll scan through all the apples and then it'll be, keep remembering the position, for example. It depends. I, I'm still a big fan of APAT queries. I like them. I do, but sometimes I, we need to, uh, we have, trade off here. Right. But we didn't discuss the main to blo.
[00:24:08] Michael: Before we do, before we do quickly vacuum full, what would hap what happens with indexes? Do they get they rebuilt.
Okay. And because It's a small amount of data,
[00:24:18] Nikolay: you can, we can run, park, uh, against table and all its indexes. It'll be the same, it'll be better of course. And people do this actually, but like a forest works
[00:24:29] Michael: but there's no point because we have a better
[00:24:31] Nikolay: Well, repacking is a good, better than vacuum full, of course, because, uh, it'll lead to much, various brief, period of, exclusive lock, right?
So very, very brief and it's very graceful and so on. Vacuum full is a big hammer, of course, like boom, like, and we are very good again. so Geria Park is a valid solution for those who want to get rid of problem. Continue working as nothing happened. But, uh, if you want to design the system to scale for really good TPS transactions per second, uh, and we flow latency and handle a lot of, uh, events per second, in reliable way inside po, you need partitioning, right, union partitioning, and, uh, a good lesson can be learned from p gq, from Skype, as I mentioned.
it existed 20 years
ago
[00:25:20] Michael: Yeah, this is super cool and I hadn't even really thought this through, but they can't have been using for update, skip locked for it because it didn't exist then. It was, uh, skip
[00:25:30] Nikolay: it's not, But that's, well, advisor locks probably already existed. I'm not sure. I don't remember. But we, we didn't start, talking about, how, to improve, throughput. So let's, return to skip lock, uh, slightly later. I'm talking
[00:25:44] Michael: Yes.
[00:25:45] Nikolay: dead type issues
[00:25:47] Michael: Right. And they had partitioning.
[00:25:49] Nikolay: Exactly.
Declarative partitioning didn't exist that time. So they used, inheritance based partitioning, three partitions and rotation, right? So we use one partition. We maintaining another one, and one third one is like on idle. And maintaining means like we
just truncate.
[00:26:06] Michael: Yeah,
[00:26:07] Nikolay: That's it. Truk is
fast.
[00:26:08] Michael: I was trying to work out why there are three. Is it that we have one that new roads are going into currently? One where we are trying to finish the final jobs in it, just, you know, while we're switching between partitions. And then the third one that. Always gonna
[00:26:22] Nikolay: Honestly, I don't remember. I remember I used it and I actually applied, similar approach in different systems because it's very smart approach. Skype, Had requirement to handle, uh, billion users. So they designed very good, system here and it's, it's still alive by the way. It lacks good documentation probably.
But I, asked, people, about Q in pos and I was super surprised. I'm checking numbers right now, 105 votes on Twitter. I asking on LinkedIn as well, and 7.6%. So
maybe it's like.
Eight people around there said, I'm a P GQ user. So, big respect. P GQ is great. Still. Like you need to cook it, of course, right?
But, it's enterprise, uh, level solution. If you manage to work with pg q, you can work with very large volumes of events per second. Uh, low latencies. You are in good. But, nowadays we have, declarative partitioning and for example, timescale also, helps automated.
And I wonder if there are around people who implemented a good queue system based on timescale. If there are people, such people around, please let us know. I would like to. Because I have similar situations, in my area as well. so partitioning gives you, ability, gives you benefits.
We discussed benefits from partitioning, but here most benefits are you can, divide and conquer, right? So you have partitions which are already fully processed. You can just drop them,
right? Re get
[00:27:55] Michael: And, And, that's important because whatever bloat they've accumulated during the time that they, during the time that they're ex, exactly that, that's all now gone.
[00:28:06] Nikolay: right? So instead of fighting with blood, you just, forget about it, get rid of it, and that's it. But of course you need, to design it in a way that, it's not like we work with all partitions all the time, right? So you, you, you, you need to have some partitions, which already fully. So you need consider them as like done and you just drop them.
Or you can implement some rotation with truncate. So you truncate actually recreates file and you have fresh state again, like no zero rows, indexes are empty. You start from scratch, zero blood. So this is ultimate solution to bloat. Just drop, get rid of it fully inside table. And partitions is a physical table, so we can, do it.
and also of course it gives you data locality for a lot of benefits. So you can keep all partitions for, quite long, even if they not some B load and so on. New partitions don't see them and they don't suffer from issues. All partitions.
[00:29:05] Michael: Yeah, but the main benefit is very, very simple and easy bloat management.
[00:29:10] Nikolay: Right, right. So right now, skip four updates, skip locked. It's very cool feature, but it's needed when you, when one worker is not enough. Right. In some cases it's, enough.
[00:29:23] Michael: that's a really good point. Lots of cases.
[00:29:26] Nikolay: it's related to third, let's, let's talk about, I, I'm trying to download, uh, the importance of skip lock and I do it by purpose.
Let's discuss, query, performance. We've already touched, this, uh, a lot of that top accumulated case, right? But in general, the advice is try to, have index only. Here, OWA should be tuned again because we want, hip fetches to be as low as possible. Ideally zero all the time.
in practice, it's usually non zero, but we don't want, uh, like index, only scan.
It's good when. It's like when have, have hip fetches is close to zero because in this case with, uh, executor doesn't need to consult hip if it does need to consult hip, it's, it's similar to index scan already. Right? It's so degraded Index only scan is index scan, right. So, to have real index on the scan, we need to keep auto working quite aggressive to keep visibility maps up to.
To know which pages are marked or all visible. So if they're marked all visible, uh, index only scan can avoid consulting to heap and deal only with index. And to achieve that, sometimes we need to use cover cover in indexes. So include additional columns. It depends.
[00:30:46] Michael: Yeah, or even in, in later versions of Postgres, I don't think there's that much benefit of the includes. Um, I think if anything, there might even be benefit of ha not using it cause of the de-duplication.
[00:30:59] Nikolay: Oh yeah. Advice is to consider post 14 or or 15. So 14 is great, 13 was good, 14 is great already. The B3 application is great. it, uh, helps with bloat as well,
[00:31:13] Michael: But I think it only D duplicates on ordered columns, though not on Includes, for example.
[00:31:19] Nikolay: there are several things there,
[00:31:21] Michael: Okay, nevermind. Let's, it's a minor point, but in index only scan. So is this for the update query? Like which queries
[00:31:28] Nikolay: select, for, uh, for queries like, find next, item to process or multiple items,
right? so this kind of, queries and, of course for updates or deletes, if we deal with updates. We try, we want to try to make them hot, hip only topple updates To do that, sometimes I see people, um, consider reducing fil factor even for tables, which is a hundred by default. So they reduce it for like, So let's have room in each, we have, we have artificial blood initially, like 50% is
our,
[00:32:04] Michael: mind it. Yeah,
[00:32:05] Nikolay: but our,
updates are faster. If we set
status equals processed, most likely it goes to the same page. index simplification is good. So,
[00:32:15] Michael: And you probably then can't or don't want to index your status column, for example.
[00:32:20] Nikolay: Right, right. Well, yeah, it depends. But yeah, we need to take care. We need to be careful. Sometimes we try to avoid additional index because we know we will be updating for status, for example. Right. And we know like, okay, how many roles with these like parameters. Will have status, like multiple statuses, maybe?
No. Like let, let's, let's not have status in indexes, for example, and in this case, but because if you have status in indexes, in any of indexes, even if in wear clouds in part, part partial index, and then you update status to something, of course this update cannot be hot anymore. So that's it. So it's full fledged update, having all right.
Amplification. Right. So we. Speaking of query performance related to our workload, we need to like, we probably can, uh, divide it to two parts. First, the task of purely select how to find next row to process or batch, right, the index on discussion, be there and second right itself. It can be update, it can be delete.
Well insert also interesting, but I hope insert is quite fast usually. Maybe also worth, checking, but usually it's like number three item probably. So update. It's good to have hot update, delete. What can we do with delete? I dunno, with delete, we just market as a pro, as a that tap. That's it. So, sometimes also people like to have.
To,
to send a result to application code. Also handy. Sometimes people like to have multiple stages in the right cte.
Combining, maybe update, delete something like in single query. And this is, this is reduces around re round trip. So in one query you can do multiple things altogether and in single query from application code.
But overall you can reach like It's good if you, your cell are much less than one millisecond, even if you have a lot of rows. Our QT table usually do, doesn't have a lot of rows. Right. Especially if it's partitioned and so on. And for, uh, right operations. One or a few milliseconds is, is a good performance usually.
Right? If it's more than 10 milliseconds, I would
doubt
it's good, right?
[00:34:37] Michael: Well, especially cuz we're talking about updating only a single row in Jetmore,
[00:34:41] Nikolay: Well, Yeah, yeah.
yeah.
[00:34:42] Michael: so it feels like we need to talk about the the
[00:34:45] Nikolay: time to talk about skip lock. Okay. Now, okay, now we see, for example, our right operations take two milliseconds. It means that we cannot process when, when we have single worker, latency and throughput are very well connected, right? It's like you have a second two millisecond.
50 operations per second only. This is our throughput. If it's not enough, okay, let's have multiple workers. We might have multiple workers due to some different reasons sometimes, right? Because, uh, just one worker does this, another worker does that, I don't know.
Well, but if it's for, for throughput, we must understand that they might conflict.
Conflict. They will conflict eventually. Right. And, If one pro, like we cannot update same row at the same time twice, right? So if one is updating until the very end of transaction commuter rollback, exclusive lock will be on this row, right? And another worker will be just waiting on it. It can be seen in weight events analysis very well.
In this case, we can use, uh, for update, no wait or for updates. Keep blocked. No wait. Will just. Like you fail as fail fast and you try startup approach, right? So, or skip log, give me next row or few rows.
[00:36:10] Michael: Yep.
[00:36:10] Nikolay: Batching also interesting topic by the way, but let's, we, we don't have
time for it, but yeah,
so.
If you handle that apples and blo very well, if you have tuned queries and you know that your queries will be always quite fast. You probably don't need multiple workers. But of course if you need multiple workers, go ahead and use Keep locked. But remember, if you, for example, use jungle or something and like.
Sometimes like sub transactions might pop up and with, for updates, keep locked. Uh, if you have sub transactions, you might have issues on replication some and like, so go and read my article about sub transactions. There are dangers there so regularly Select for update is okay, but combined with sub transactions, it's not.
[00:37:01] Michael: Good point.
[00:37:01] Nikolay: But in general, it's a good thing. By the way, someone on Twitter mentioned us. The problem when, uh, the executor needs to scan over a lot of rows, which already locked. It's a similar problem as we discussed with that doubles, right? So we have a lot of locks, rose already locked and trying to find unlocked ones.
We have like degraded performance as well, right? But I, I
[00:37:24] Michael: Yeah,
[00:37:24] Nikolay: of edge case.
[00:37:27] Michael: I think so. It'd have to be like, you'd have to be doing things in batches and maybe have lots of workers before. I could see that becoming a big issue. But yeah, definitely can see how it would happen at scale.
[00:37:37] Nikolay: Yeah, so that's it. And if you, if you have multiple workers, you definitely can achieve many, many thousands of transactions per second on good hardware, of course, and maybe dozens of thousands of transactions per second. That should be enough for most systems
already these days.
Right.
[00:37:55] Michael: 99.99% I think.
[00:37:58] Nikolay: Well, there are cases when we need more, but this, this is already good, good enough, so summary is bloat is our main enemy here. That apple and bloat
in partitioning is,
Yeah.
With proper, rotation or partition strategy. Right.
Second is, uh, check, explain, analyze buffers. And, uh, Have good plans for your queries, and only finally for eski block. And if you see some tool which addresses all of these things, surprise only p GQ does it. Like
[00:38:35] Michael: And I'm not even sure they use the ladder,
[00:38:37] Nikolay: so many attempts to implement it, reimplement it. And when I say, guys, what about blood to, to do? So if you see, if you find the tool which addresses all these three things, I don't care about language, please tell me.
I will advertise it. Yes, because I see dozens of thems to, to implement Q in pos. But these guys fail in these three areas.
If where? Where Sometimes implements people log, for example. It's very well advertised as I mentioned, but they fail to do BLO part or some indexes are not well.
[00:39:15] Michael: Wonderful. Well thanks so much
Nicola. Thanks everyone for
listening.
[00:39:18] Nikolay: I do hope we help some, uh, Q builders, qin PO builders, , because we need better tools or maybe some, people can resurrect p GQ in terms of, uh, I dunno, like, provided as a service.
Why not Some
managed post providers.
[00:39:33] Michael: it would be good if
some managed. know they'd have to install something else, but I think it would be popular,
[00:39:39] Nikolay: If it's just one
click, uh mm-hmm. Mm-hmm.
[00:39:43] Michael: thanks
[00:39:43] Nikolay: Good.
[00:39:44] Michael: Nik. Like
see
you
[00:39:44] Nikolay: Thank you Bye-Bye.
[00:39:46] Michael: Bye.