SQLite is enough
Vložit
- čas přidán 12. 02. 2024
- SQLite is enough.
You should not use MySQL or Postgres.
And you should definitely not use queues
or other fancy cloud services like that
Here is why
Simplicity
Complexity is coming from everywhere,
and to keep ourselves sane,
we have to pursue simplicity.
At the end of the video
I'll show why SQLite is enough
Benchmarks and shit
SQLite benefits
But first, isn't SQLite just a toy technology?
Not really
SQLite gives a fresh breath of air
It is the sleek database
It is a super duper relational database
but just a file.
There are no servers.
It is incomprehensible what this does to your flow.
Elegant things happen when you just have a file.
1. Setup and permissions
2. Staging environment
3. Wonderful little things like Metabase
Why can you do this?
Standard Traffic
46% of websites received 1,001-15,000 visitors
19.3% received 15,001-50,000 visitors
23.2% received 50,001-250,000 visitors
11% received 250,001-10 million visitors
Only 0.5% of websites received more than 10 million visitors per month
blog.hubspot.com/blog/tabid/6...
Performance?
```
Insert 50,000 took 113.7 ms ([NOMUTEX, 1000, 8192, EXCLUSIVE, IMMEDIATE, OFF, TRUNCATE])
Insert 50,000 took 114.3 ms ([NOMUTEX, 1000, 8192, EXCLUSIVE, EXCLUSIVE, OFF, MEMORY])
Insert 50,000 took 114.3 ms ([NOMUTEX, 8000, 8192, EXCLUSIVE, IMMEDIATE, OFF, MEMORY])
Insert 50,000 took 114.8 ms ([NOMUTEX, 4000, 8192, EXCLUSIVE, IMMEDIATE, OFF, TRUNCATE])
Insert 50,000 took 115.0 ms ([NOMUTEX, 1000, 8192, NORMAL, IMMEDIATE, OFF, TRUNCATE])
Insert 50,000 took 115.1 ms ([NOMUTEX, 2000, 8192, EXCLUSIVE, IMMEDIATE, OFF, TRUNCATE])
Insert 50,000 took 115.5 ms ([NOMUTEX, 1000, 8192, NORMAL, IMMEDIATE, OFF, MEMORY])
Insert 50,000 took 116.0 ms ([NOMUTEX, 1000, 8192, NORMAL, EXCLUSIVE, OFF, TRUNCATE])
Insert 50,000 took 116.0 ms ([NOMUTEX, 4000, 8192, NORMAL, EXCLUSIVE, OFF, MEMORY])
Insert 50,000 took 116.2 ms ([NOMUTEX, 4000, 8192, EXCLUSIVE, IMMEDIATE, OFF, MEMORY])
...[snip]...
Insert 50,000 took 307.0 ms ([NOMUTEX, 1000, 8192, NORMAL, EXCLUSIVE, NORMAL, MEMORY])
Insert 50,000 took 316.3 ms ([FULLMUTEX, 1000, 1024, EXCLUSIVE, EXCLUSIVE, NORMAL, MEMORY])
Insert 50,000 took 335.3 ms ([FULLMUTEX, 2000, 4096, NORMAL, EXCLUSIVE, NORMAL, WAL])
Insert 50,000 took 348.9 ms ([NOMUTEX, 2000, 4096, NORMAL, IMMEDIATE, OFF, MEMORY])
Insert 50,000 took 351.4 ms ([NOMUTEX, 1000, 1024, NORMAL, IMMEDIATE, FULL, TRUNCATE])
Insert 50,000 took 355.1 ms ([FULLMUTEX, 2000, 1024, NORMAL, IMMEDIATE, NORMAL, TRUNCATE])
Insert 50,000 took 368.1 ms ([FULLMUTEX, 1000, 1024, NORMAL, EXCLUSIVE, FULL, MEMORY])
Insert 50,000 took 368.1 ms ([FULLMUTEX, 2000, 4096, NORMAL, EXCLUSIVE, NORMAL, MEMORY])
Insert 50,000 took 436.4 ms ([NOMUTEX, 1000, 1024, NORMAL, IMMEDIATE, FULL, WAL])
Insert 50,000 took 541.6 ms ([NOMUTEX, 1000, 4096, EXCLUSIVE, IMMEDIATE, FULL, MEMORY])
```
ericdraken.com/sqlite-perform...
How do you use SQLite
Don't forget to subscribe because one of the next video will be how to use SQLite in production
Shorts MSG
Checkout the full video below.
Yes, SQLite is enough
----------------------------
Goleko is the best project management tool with innovative features such as:
1. Screen recording on tasks directly
2. Time management directly built-in
3. Smartboard and our activity app help you to see everything that is going on.
Besides that, Goleko is a swift, simple, and yet powerful project management tool.
Imagine all the complicated features but so simple even your coworker can understand them.
A generous free version helps you get started. Checkout it out
Goleko.com
----------------------------
I use SQLite by default. If a project needs a client + server structure, anticipates lots of concurrent writes, or requires more granular permissions, then I'd use Postgres - but they rarely do. SQLite is very, very capable, im a big proponent. Also, it is trivial to spin up an in-memory database with SQLite - great for lightening fast tests
Hey Nodidog, you might check a new video about SQLite in Production czcams.com/video/z6LUgup3bs0/video.html
Didn't think about the staging environment, but you're 100% spot on
Thanks Thomas!
Magnificent video - thanks for making it.
Thank you! :) I also made a follow up here
czcams.com/video/z6LUgup3bs0/video.html
Tell me what you think :)
Bro, someone is whistling in the background music on this video 😅 It can make it hard to pay attention - atleast for me.
Hey Steffen!
Yea we got told this. We are never doing that again hehe. Like 10 people complained about it so far.
By the way, if you wanna know about running SQLite in Production, I just released this
czcams.com/video/z6LUgup3bs0/video.html
Hygge :))
@@MartinBaun It would be better without the captions also. If we want captions they can be turned on.
I think the advantage of copy and pasting your database is a little overrated. If you are setting up a pipeline to test and deliver your software, replicating databases between environments is abstracted away and automated. Automation is easier than copy and pasting files between environments. It’s more work to set up the first time, but then more reliable and hands off from then on.
Hey Avsync - Audio Video Sync? :P
For our staging environment we literally just run the following command locally
scp user@PRODSERVER:./db.db /tmp/db.db
scp /tmp/db.db user@STAGSERVER:./db.db
and when we make a new project, we just copy paste this command. It is that easy. Sometimes we need to also copy uploaded files, and then we use rsync.
It seems silly, it seems stupid, but it is so easy to work with comparaed to pipelines etc..
I honestly love the whistle lol. It adds a bit of 'fill' to the silence.
thanks for writing this. Hehe it is a bit funny, so many complained about the whistle, but it is also our most performant video so far :)
By the way, if you liked this, I made a follow up video here
czcams.com/video/z6LUgup3bs0/video.html
There's a few caveats to the "just copy the file" argument, you do need to lock the database file using sqlite commands to take backups if it is in journaling mode. It's a bit less of an issue in WAL mode where you can have multiple readers, but you still can't just use cp if the database file is in use. You can use tools like litestream but then you are technically back to having a streaming replica setup although a simpler one.
Sqlite is really really nice when it is an option though. It makes testing a lot easier, and sharding between users or self-service/provisioning is absolutely trivial which is another major advantage imo. If you need a lot of databases sqlite is your friend.
Hey Boson,
Yes, that's (kinda) true. They warn against using cp as it can corrupt some of the data, but I never had an issue doing this.
For backups, I also use
sqlite3 your.db ".dump" > backup.sql
that should work wtihout any issues. Litestream is really cool concept, I haven't tried it but it is on the todo list.
By the way, Monday I'll release a new video called "SQLite in Production - a master course". You'll probably enjoy that one ;)
Hey Boson, the SQLite in Production video is ready😎 czcams.com/video/z6LUgup3bs0/video.html
I agree SQLite is fast, but you also need replication if you don't want to be down when one server is down. There's BedrockDB which is distributed SQLite with same benefits, but it's replicated.
Yes, but it comes with a caveat. a lot more complexity. We have like 1 hour of downtime on most of our Hetzner machines per year. They just keep running when you 1. keep them overpowered and 2. keep it simple.
BedrockDB sounds cool though, tell me more about it. How's the difference between litestream?
@@MartinBaun can LiteFS be used to replicate the db?
@@charliezugasti Cool! I haven't seen LiteFS before. It looks like it can. So many things are happening in SQLite Universe right now.
How you handle multiple requests accessing the same db file?
Great question, SQLite can handle it in most cases. If you have many apps doing continous queries it can be a problem, but usually it is not.
You should probably set a PRAGMA to WAL, but it is very easy one liner.
Hey Shahdhaval, the SQLite in Production video is ready czcams.com/video/z6LUgup3bs0/video.html
There I'm explaining why and how to use WAL
The video is informative and good to see. One feedback - the background score is very distracting. Please change it or get rid of it.
Hey Earthling, thank you!
What do you mean with the background score?
1. the sound?
2. the memes ?
3. the background I am filming at?
@@MartinBaun it's the sound/background music for me
@@samuelm2934 Thank you so much Samuel!
Yes, it is quite distracting. Next videos we'll avoid this.
Hey Earthlin👋, you can check our new video "SQLite in Production" czcams.com/video/z6LUgup3bs0/video.html
I think this time the background music is not distracting from the video. 😎
are u planning to release the "config" video soon ?
Hey Alex,
Thanks for asking. I completely forgot about this video.
I'll start working on it. Probably have it for you in a few days ;)
Stay tuned!
Hey Alex, the SQLite in Production video is ready czcams.com/video/z6LUgup3bs0/video.html
@@MartinBaun wow, thanks a lot!
@@Alex-zm9ww Thanks for reminding me :))
Great video
Thank you Mojo! If you like this, you'll probably like my newest video about SQLite in production!
czcams.com/video/z6LUgup3bs0/video.html
if you have questions, just write and I'll reply :)
SQLite is the single most deployed and used database solution on the planet.
yep, Linux, Firefox, Chrome, Phone apps.
But most developer frown upon using it on the server as their main database. They probably should consider it before going with some more complicated stack
@@MartinBaun I use it as the stage 1 database solution for my game servers.
@@scotmcpherson Cool! I just checked your channel and subscribed!
Are you making more videos in the future? :)
@@MartinBaun yes, since I am in transition from Unity to Godot, I don’t have anything fun to share yet, but getting there and will produce content again once there is something fun to show. No one who cares about games will care to know my network stack, database choices, and bitpacking. They want to see game content. Imagine that!! ☺️
But duckdb is enougher....
DuckDB looks very interesting. Can you tell me more about it? I see it is in-memory but persist to disc. So I could imagine it is super fast
@@MartinBaun sqlite = OLTP, duckdb = OLAP for what it's worth.
@@karaloop9544 didn't get that analogy at all. Can you elaborate Kara? :)
@@MartinBaun I don’t know anything about DuckDB but I’m assuming based on what @karaloop9544 said is that it is made for analytical queries. I imagine a good candidate for extract transform and load (ETL) scenarios when you need a reporting (OnLine Analytical Processing OLAP) DB based off of your transactional (OnLine Transaction Processing OTLP) DB.
Just doing a copy-paste of a db is so underrated 😂
exactly! It just makes things so much easier. Where do you use Sqlite?
@@MartinBaun I have a inventory management system used by my clients. Single instance on a 6$ vps :)
Had zero hiccups so far! 🎉
cool, yeah And backup is so easy and simple Kudos to you for not doing what most people would do. Making it "web-scalable" with cloud infrastructure and complicated stuff
@@MartinBaun that's right! Also I am looking at SQLite extensions and litstream for disaster recovery.
One example of extensions is to compress the data with time. Since everything is one server it has only 20Gb. I am planning to try it. Benchmarks say it can reduce 1GB of data to 72MB which is insane!
How about difference between sqlite and, for example, postgres? There may be a lot of problems if you will be top 10% visited sites
But in general SQLite is great for pet-projects, startups etc.
The 99% of websites should be fine unless you have very specific needs.
The change that you'll become more than a 1% visited website is very low.
And if you do, then switching from SQLite3 to Postgres is a lot easier than the reverse.
You need postgres if more than one running process needs to connect to the db. Postgres can be used as a message bus for example, while sqlite can't. But sqlite is very useful when you need a database that is owned by one application.
Does it mean that microservices can't eeven possibly work with SQLite?
@@CZcamsPavlo
You can but it is probably not a great choice.
You could for example have each microservice have their own sqlite database.
Probably not great as I mentioned. As @bosoncollider mentioned, Postgres is probably better here
@@BosonCollider "You need postgres if more than one running process needs to connect to the db" is not true. SQLite's forum and source control system are both sqlite-based and are in concurrent use 24/7 by many users, each request being processed by a separate CGI application instance (as opposed to a single persistent server process). Aside from those, at least one separate application continually polls the databases for queued email notifications to send out.
Great video, but pls next time - skip the whistle background sounds.
Thank you Blue,
You're not the only one saying this, and I wont have this on next time :)
By the way, I am soon making the configuring / mastering SQLite in production.
Stay tuned :)
Hey Blueml08, you might want to check our new "SQLite in Production video" 😎 czcams.com/video/z6LUgup3bs0/video.html