SQLite is enough

Подписаться 497

Просмотров 9 тыс.

50% 1

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
----------------------------

Опубликовано:

12 фев 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 75

@nodidog Месяц назад

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

@MartinBaun Месяц назад

Hey Nodidog, you might check a new video about SQLite in Production ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html

@thomasborg4442 4 месяца назад

Didn't think about the staging environment, but you're 100% spot on

@MartinBaun 4 месяца назад

Thanks Thomas!

@TheDa6781 4 дня назад

Lack of background sound is enough. I dont know why but it is probably the most annoying background noise ever. Small children screaming would possibly be worse. Maybe.

@MartinBaun 4 дня назад

My editor and I decided to up it. The next videos will have small children screaming ;) Just kidding, the next videos in line do not have this :)

@dineshkumarramasamy9849 День назад

Honest and clear man right here who could see through the drama.

@MartinBaun 20 часов назад

Thank you! Yes, so much complicated tech for no reason. KISS is something I myself struggle with, but I try to get better every week

@Imaginativeone_DF Месяц назад

Magnificent video - thanks for making it.

@MartinBaun Месяц назад

Thank you! :) I also made a follow up here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html Tell me what you think :)

@steffengroenandersen Месяц назад

Bro, someone is whistling in the background music on this video 😅 It can make it hard to pay attention - atleast for me.

@MartinBaun Месяц назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html Hygge :))

@bumpersmith 26 дней назад

@@MartinBaun It would be better without the captions also. If we want captions they can be turned on.

@MartinBaun 25 дней назад

@@bumpersmith thank you, I'll talk with my Editor what we can do. The next few videos are unfortunately already made so too late with those. I think we'll probably not have captions on the longer form, but then on the shorts we'll have.

@the_ace248 Месяц назад

I honestly love the whistle lol. It adds a bit of 'fill' to the silence.

@MartinBaun Месяц назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html

@BosonCollider Месяц назад

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.

@MartinBaun Месяц назад

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 ;)

@MartinBaun Месяц назад

Hey Boson, the SQLite in Production video is ready😎 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html

@RihV 12 дней назад

Great video, will definitely give SQLite a try

@MartinBaun 12 дней назад

Thank you :)) Tell me how it goes and if you have any issues!

@earthling017 2 месяца назад

The video is informative and good to see. One feedback - the background score is very distracting. Please change it or get rid of it.

@MartinBaun 2 месяца назад

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?

@samuelm2934 Месяц назад

@@MartinBaun it's the sound/background music for me

@MartinBaun Месяц назад

@@samuelm2934 Thank you so much Samuel! Yes, it is quite distracting. Next videos we'll avoid this.

@MartinBaun Месяц назад

Hey Earthlin👋, you can check our new video "SQLite in Production" ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html I think this time the background music is not distracting from the video. 😎

@vanillaface6097 Месяц назад

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.

@MartinBaun Месяц назад

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?

@charliezugasti Месяц назад

@@MartinBaun can LiteFS be used to replicate the db?

@MartinBaun Месяц назад

@@charliezugasti Cool! I haven't seen LiteFS before. It looks like it can. So many things are happening in SQLite Universe right now.

@blueml08 Месяц назад

Great video, but pls next time - skip the whistle background sounds.

@MartinBaun Месяц назад

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 :)

@MartinBaun Месяц назад

Hey Blueml08, you might want to check our new "SQLite in Production video" 😎 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html

@Alex-zm9ww Месяц назад

are u planning to release the "config" video soon ?

@MartinBaun Месяц назад

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!

@MartinBaun Месяц назад

Hey Alex, the SQLite in Production video is ready ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html

@Alex-zm9ww Месяц назад

@@MartinBaun wow, thanks a lot!

@MartinBaun Месяц назад

@@Alex-zm9ww Thanks for reminding me :))

@mojo6689 Месяц назад

Great video

@MartinBaun Месяц назад

Thank you Mojo! If you like this, you'll probably like my newest video about SQLite in production! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html if you have questions, just write and I'll reply :)

@BrunoBernard-kn6vt 3 месяца назад

Just doing a copy-paste of a db is so underrated 😂

@MartinBaun 3 месяца назад

exactly! It just makes things so much easier. Where do you use Sqlite?

@BrunoBernard-kn6vt 3 месяца назад

@@MartinBaun I have a inventory management system used by my clients. Single instance on a 6$ vps :) Had zero hiccups so far! 🎉

@MartinBaun 3 месяца назад

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

@BrunoBernard-kn6vt 3 месяца назад

@@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!

@_shahdhaval 2 месяца назад

How you handle multiple requests accessing the same db file?

@MartinBaun 2 месяца назад

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.

@MartinBaun Месяц назад

Hey Shahdhaval, the SQLite in Production video is ready ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z6LUgup3bs0.html There I'm explaining why and how to use WAL

@CaribouDataScience Месяц назад

But duckdb is enougher....

@MartinBaun Месяц назад

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

@karaloop9544 Месяц назад

@@MartinBaun sqlite = OLTP, duckdb = OLAP for what it's worth.

@MartinBaun Месяц назад

@@karaloop9544 didn't get that analogy at all. Can you elaborate Kara? :)

@lldadb664 27 дней назад

@@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.

@Sammi84 12 дней назад

Online analytical processing (OLAP) and online transaction processing (OLTP) are two different data processing systems designed for different purposes. OLAP is optimized for complex data analysis and reporting, while OLTP is optimized for transactional processing and real-time updates.

@scotmcpherson 27 дней назад

SQLite is the single most deployed and used database solution on the planet.

@MartinBaun 27 дней назад

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

@scotmcpherson 27 дней назад

@@MartinBaun I use it as the stage 1 database solution for my game servers.

@MartinBaun 27 дней назад

@@scotmcpherson Cool! I just checked your channel and subscribed! Are you making more videos in the future? :)

@scotmcpherson 27 дней назад

@@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!! ☺️

@nuvotion-live Месяц назад

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.

@MartinBaun Месяц назад

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..

@YouTubePavlo 4 месяца назад

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.

@MartinBaun 4 месяца назад

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.

@BosonCollider Месяц назад

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.

@YouTubePavlo Месяц назад

Does it mean that microservices can't eeven possibly work with SQLite?

@MartinBaun Месяц назад

@@RU-vidPavlo 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

@StephanBeal Месяц назад

@@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.