Database Synchronous vs Asynchronous IO

Key Takeaways

Why is it slow? Well, it's blocking. It's not allowing me to do other things that I might need to do as a process. I can be doing tons of CPU intensive work, and you just blocked me? How dare you? That is rude. The oldest trick of the book to do asynchronous IO is to offload it to a thread or another process. So, we're still doing synchronous IO, but someone else is blocked, not me. I'm free. I'm free. As a main process, I'm free. I'm just going to spin a process, and I'm going to ask that process to actually do the IO for me. We need to start with the idea of synchronicity versus asynchronicity. I talked about this many times in this channel and my podcast. Essentially, to be synchronous is Think of it as a client, you're calling some sort of an API, some sort of a back end, some sort of a server. Doesn't matter. If your call blocks the execution of the rest of the instructions in your program, that is a synchronous call. We have called this API synchronous because it did not allow you to move to the next instruction unless you get an answer, okay? So, you're blocked. And when you're blocked, uh you don't get to execute the rest of your instructions. Asynchronous, on the other hand, is you run, and you delegate that job to some other party, often, and then you move on. And then, sometime later, you either check if this is ready, or you get a callback, like with Node.js, how Node.js does it, that hey, your result, remember this thing you asked about a few milliseconds ago? It's ready. You can You can get the results here. So, that's the gist of synchronous versus asynchronous execution, okay? And And we can dive into details just just by that. But, we want to kind of move that into the IO concept. So, what does it mean to do asynchronous IO? We're kind of uh giving some concrete examples here. We're doing an IO. Uh that means it's either a read or a write. In this context, since we're talking about Postgres, we're not talking about network, which is its own beast. We're talking about files, right? We're reading and writing to disk, right? And we're dealing with system calls, that is the kernel system calls, that allows us to read from files because a database is just a bunch of files, believe it or not. Yeah. Shocking, right? The data The The kernel provides you with a beautiful system calls that allows you to do IO. Often, all of these methods are synchronous. That means read, for example, is a system call that allows you, given a file descriptor that you have opened for a file on disk, I want to read that file, and I want to put whatever I read, this many bytes, in this pointer, copy the data, and move it to this memory location where it lives in my user space. That's a read call, and that is a blocking call. It's a blocking call. If you call that, your process will switch from user mode to kernel mode because you called the system call, and then your That's it. You cannot move to the next instruction. You cannot. Your process is blocked because it's synchronous, right? And until the kernel take that read, talks to the IO controller, NVMe, or whatnot, does the actual block read, uh uh gets the result. We're talking here about buffered IO, so we're writing into the page cache, to the kernel page cache, and then gets that Get a copy of the of that whatever we read into the page cache, and then we get you now from the page cache to your memory location, and that is a CPU cost. You're copying data from the page cache into your specified buffer, okay? So, that's a read. Write I is identical, right? It just works the other way, right? But, it's synchronous, right? So, I'm blocked when I do that. What's wrong with that? Well, if it's wrong If you're doing a read, you get something, and you read and read and read and think of it You implement a full table scan. You read, get something, process it. Read, block, get something, process it. As it's As opposed to read read read read read, execute, and then let me worry about that later. So, yeah, think about it. Why is it slow? Well, it's blocking. It's not allowing me to do other things that I might need to do as a process. Like, I can have I can be doing tons of CPU intensive work, and you just blocked me? How dare you? That is rude, right? So, synchronous IO does this. Blocking. Asynchronous IO allows you to uh unblock yourself. Well, the the oldest trick of the book to do asynchronous IO is to offload it to a thread or another process. So, we're still doing synchronous IO, but someone else is blocked, not me. I'm free. I'm free. As a main process, I'm free. I'm just going to spin a process, and I'm going to ask that process to actually do the IO for me. Guess who's doing that? Node.js. Yeah, for the longest time, Node.js exclusively worked on that mode for files, for files. Don't yell at me. For files. Network is a different beast. I talk about all of this, by the way, in my Node.js course, if you're you're interested to know the details and the inners the inner the inner workings of this. Go hit to node.win. win, w i n. It will redirect you. Get you a coupon. Worker thread. Beautiful design. Nothing wrong with that. Okay? So, So, let do this asynchronous IO, but let someone else get blocked. So, we're still getting blocked, right? And Node.js, by the way, uses a library called libuv to implement that piece, right? Uh in recent releases, they switched to IO_uring, which is what we're going to about to talk to next, right? But, essentially, the worker pool is a very nice idea to do asynchronous IO. It's just like let someone else be blocked. It's a very simple design, yet we still give the feel of asynchronicity. Okay? So, that's That's asynchronous IO. Now, asynchronous IO, again, I'm talking about file IO here, nothing about network. Different beast, again. Here, asynchronous IO, there is a new interface. I say new, it's been there for years now. It's called IO_uring, which is a shared memory between the kernel and the user space, where the user can submit a job, and the kernel can pick that job, actually does the read, and then writes back the results into the um in into the shared memory space, and then the kernel the the the user can check if the result is ready. So, it's a pure asynchronous model. If you really think about it, someone else is doing the blocking read, and that's the kernel, right? We're just offloaded the game. That's the whole the game, by the way. We just tell someone else to do it. It's just we present it in a in a pretty way, okay? So, everything, at the end of the day, is is synchronous. There's no escape. Someone else has to pay the price, right? Okay. So, that's IO_uring. Of course, I think Node.js supports it, right? I keep referencing Node.js because all Node and all these framework and Postgres, they're all software. They all need to read from disk and do network, and they're all kind of a back end infrastructure, which is the specialty of this channel of this channel. So, synchronous IO, asynchronous IO, briefly.