We've covered a few of the pragma statements that can increase the performance of your SQLite database. I'm now going to give you a suggested set of pragmas as a starting point. Now, this is super dependent on your hardware, your workload and your data. And so this is a good starting point. I think this is reasonable.

I think this covers some of the faults of SQLite's backwards compatibility which means sometimes that the defaults are a little bit, underpowered maybe for modern hardware. So I'm gonna cover a suggested list, but I encourage you to play with it, tweak it for your own setup. But this is a good place to start. So the very first thing is we want to set journal mode to write a head log. So journal mode equals wall.

We've covered this in-depth, but this allows many readers to operate with 1 concurrent writer. So you get much higher throughput using wall mode versus rollback journaling. The next 1 that we're going to want to set is the busy time out. We talked about this 1 too. So set the busy time out to something.

I think 5, 000 is reasonable if you wanna do, on your request response life cycle, if you want to set it to 2, 000 and on your background jobs you want to set it to 15, 000. That's fine with me. I think that's completely reasonable as well. The next 1 we need to set is pragma synchronous and we're going to set this 1 to normal. The value of normal is 1, which is kind of a middle of the road.

All the way off on the left side of the road you have 0 and that is off, and then you have full at 2 and extra at 3. When you're operating in wall mode, normal is probably enough. And what this setting does is this setting controls how the data, or rather how SQLite will wait for the data to actually be written to the surface of the disk. And so when it's off, SQLite just hands the data to the VFS and says, I trust that you're going to write it. When it is normal, you have a little bit more durability, a little bit more safety.

There is just a very outside chance that a transaction could be rolled back after a power outage at just the right time, but you're not gonna have a corrupt database. Application crashes aren't going to corrupt your database. Full is a little bit safer, but it is a little bit slower. It's more durable, but it's slower. And honestly, it's not really necessary in wall mode, especially with modern day hardware.

And extra is even more just is irrelevant because it's mostly, extra mostly pertains to roll back mode which we're not using. So we're gonna set pragma synchronous to normal. Okay, the next 1 we're gonna set is pragma cash size and we're gonna set this to 2, 000. Now by default this is negative 2, 000 which is pretty confusing. So when it comes to cache size, this is the amount, this is the amount that SQLite will hold in memory.

And so we want to set it a little bit higher where where it is allowed, where our hardware will allow it. If you set it to a negative value, that is an absolute value in kibibytes. So negative 2, 000 is 2 megabytes. Megabytes, mebibytes. If you set it to a positive number, that is the number of pages.

And so in this case, when we set it to 2, 000, if the default page size is 4096 then we end up with about 8 megabytes of cache size. Finally, well there are 2 more. Finally we're gonna set temp store to memory. This is where temporary tables will be stored and we want to put them in memory cause that is going to be the fastest. And then foreign keys, totally up to you, but I like to set foreign keys true a lot of the time because it gives me that nice warm feeling.

This is a suggested set of pragmas. This might be perfect for your application but I don't know anything about your application. I don't know your workload, your hardware, your data set. I don't know anything about what you're up to, except that you're watching this video, which thank you for that. So this is probably a good start.

I would consider, or I would encourage you rather, to test it on as close to a production workload as you can. This will, this will be probably just fine. This will put you off on the right foot. Other, there's 1 other, setting that I just don't know enough about to say. I feel hesitant to offer a suggestion when I don't know enough about it and that's the m map size and this is the number of bytes set aside for memory mapped IO.

You'll see it a lot on, discussions or, forum posts or old question and answer and a lot of people have a lot of different opinions. I tested this as much as I could and I couldn't get it to improve anything. So, it could be the state of my testing, it could be something else. But, this is a setting you'll hear a lot of people talk about and unfortunately I don't have enough experience to say, so I'm not gonna offer any suggestion. But I will leave a few links down below where other people offer suggestions and you can take those, for what you will.

But hopefully, this set of pragmas is going to be good enough to carry you through, to carry you through the workload that you need to use SQLite for.