SQLite vs PostgreSQL or MySQL

MySQL and Postgres are quite popular databases, but did you know there's another, perhaps even more common one, SQLite! It's a database embedded in your application and is the most widely used one in the world. How come? Because it's embedded in everyday applications, like your web browser for instance. But here's the twist, you can embed SQLite's power into your own applications. Even better, it's so potent and nifty that it can cater to your specific requirements and distribute the workload across numerous users. Kent Dodds is going to explain to us how SQLite works and ways we can capitalize on it. We're notably familiar with MySQL and Postgres, and they're great solutions that we use frequently. From an operational standpoint, SQLite is unique because the whole database is contained in just one file, it's as simple as that. Above everything, thanks to its indexing system, it's fully equipped to manage large data sets effectively. For example, it says here that SQLite has zero latency impact. Why is that? Because it's embedded right next to your applications. This gives app developers one less thing to worry about, like how many queries you're making against the data set. With the database at your fingertips, you can make numerous queries at once while maintaining solid performance. And one interesting thing that isn't mentioned in this document is that SQLite is often the secret ingredient in many high-performance, scalable and low-latency memory database solutions. This multi-faceted technology is harnessed and repackaged in a way that you could simply get without any cost by integrating it directly into your apps. A world where there is one less service to worry about sounds great to me. This is in contrast with using Postgres, MySQL and other databases which require you to maintain and be skilled in operating these systems. These systems have their own considerations when scaling. With SQLite, however, not dealing with these additional systems is a significant benefit. Ownership costs are reduced, product simplicity and operation and uptime are improved. And guess what? SQLite comes with extra performance benefits too. Your app and product's data volume needs should dictate whether SQLite can benefit you. From a product and business viewpoint, reducing complexity and cost is important. Many enterprise-based businesses do this by leveraging SQLite behind the scenes and repackaging it. Let's look at SQLite database size: generally considered to handle smaller data sets, maybe just a few gigabytes. However, Kent talks about exabytes. Amazingly, SQLite can handle exabytes! Most web developers don't even come close to needing that volume. Even databases like MySQL and Postgres, while capable, need an infrastructure to handle such volume. But it seems SQLite can also handle this. Large amounts of data can be efficiently stored in a SQLite database. Excitingly, SQLite can be faster at retrieving data than other systems, and even from the file system directly: it's 35% faster than accessing individual files directly from the disk. Moreover, the read-write latency of SQLite is competitive with individual files on a disk. SQLite is a mature database, extensively used and continuously optimized over the years. The fact that it can be integrated into your app with such performance capabilities suggests that you can build powerful apps, dealing with large volumes of data, swiftly. This will guarantee an impressive user experience. Compared to more expensive systems like Postgres and MySQL, SQLite presents an exciting alternative. You should definitely give SQLite a go: just import the client library and point it at a file.db name. You can even keep it in-memory. Through SQLite, you can eliminate the need to run separate containers for Postgres and MySQL. SQLite does have some limitations. For example, subscriptions of data, a neat pattern with Postgres and MySQL, which allows the database to serve as an event emitter, is not supported by SQLite. Similarly, workbenches and SQL coordinated systems cannot connect directly to SQLite. Extra effort is required with SQLite to bring the data into your overall data environment. Another drawback is that SQLite does not support plugins, popular with vector databases, or enumerations. Should you use SQLite as your database of choice? The answer is likely yes, given the performance, accessibility, and capability to be shared across multiple systems. While more traditional systems like MySQL and Postgres offer their own advantages, they require additional efforts and skills to manage and scale. SQLite simplifies things and offers enhanced performance. As long as your main purpose isn't heavy business data and analytics, SQLite seems a pretty good choice. An aspect to appreciate about SQLite is its ability to handle exabyte volumes of data; a feature I didn't think was possible before. It appears as a very satisfying database to use.

[@KentCDodds-vids]

Oh hi 👋

[@StephenBlum]

Hi Kent! Love your videos and your SQL article is amazing! Looking forward to reviewing more of your content. Let me know what might be a good article to read and create another video 🙌🎉

[@thunder____]

I think it's worth mentioning that a given SQLite file can only have one active connection at a time, which adds some complication for use on a web server where there may be multiple applications trying to connect to the file at once. It also does not support password protection, so it is not suitable for use with sensitive data on a web server.

[@StephenBlum]

that's a fantastic point. The single connection requires the developer to plan ahead on concurrency considerations. For example you could use MPSC/channels to create a dedicated SQLite thread that services requests in your application. This is extra work for the developer. SQLite skips security altogether. Services like PosgreSQL and MySQL have security and concurrency already built-in and taken care of for you! 😄

[@anze3db]

@@StephenBlum multiple threads can connect to a single sqlite database file without a problem, so you'd never need to implement something like a dedicated SQLite thread in your application. The default journal_mode doesn't allow concurrent reads though, but you can fix this by switching to write ahead logging (WAL). Writes and transactions are problematic because they block your whole database - so for write heavy apps MySQL/Postgres are much better since they support per table/per row locks.

[@StephenBlum]

@@anze3db That's sounds amazing! Write Ahead Logging WAL is a great option. The developer won't have to implement read concurrency when they use WAL mode. Your idea is fantastic. Thank you! 😄🙌

[@bryanstark324]

I know this, so I never tried it in an app that is deployed to multiple devices. How would you get it to sync? The crazy idea I had was to store it on SharePoint as a file. Then, each person syncs with that SharePoint document library. They each have their own copy, but when OneDrive syncs with the connection to SharePoint, the database will sync changes made by that individual user. It's too scary to try in real life, but that was my thought. How else can you have multiple users?

[@StephenBlum]

​@@bryanstark324 good question! Multi-user local DB "on-device" sync'ed with cloud file storage. That's a really good idea! The safest way is to start with an event-based delivery like PubNub (for delivery guarantee) to send/receive the read/write/create/delete events. Then you can commit those changes to each device in real-time. Each device will receive changes from all other devices (like mobile apps and web apps). Also the devices can catch-up when they boot up using the write log sync Persistence API. This allows each DB to stay in-sync in real-time and to sync offline data as well. This is similar to CRDT. Another approach is to use WAL mode SQLite and Min.io or S3 to and a lambda cloud function to accept all writes to a primary writer store, then each device receives WAL events by syncing from the S3 File, or periodically just fetches the S3 file for changes using HEAD requests. Lots of options! The first option will allow for instant sync. The second option will have a delay based on HEAD request frequency.

[@BrunoBernard-kn6vt]

I think SQLite is for most use cases for small businesses. Using WAL journaling you can get around 10K write simultaneously. Not every saas, has a target to get 1M users. If you are around and below 1K users. You should be fine to do anything that other db can do. SQLite can do more than you can think of. 🎉 Also... SQLite is so flexible than you can easily migrate to MYSQL or Postgres Later :)

[@StephenBlum]

Nice! 🙂 Those are good stats. SQLite is a great starting point. It is comforting to know that, when needed, you can migrate to my SQL or PostgreSQL

[@codecaine]

It is already recommended :)

[@denysolleik9896]

Just make sure whatever database you go with supports the types of data you might want to query (and index). I don’t believe JSON and spatial are supported.

[@StephenBlum]

@denysolleik9896 good point! There's considerations to take when looking at SQLite. It's simpler and has less built-in features. For JSON support you have to go a few steps further creating indexes leveraging a virtual column pattern. antonz.org/json-virtual-columns/ explains you can create secondary columns from JSON data, this allows you to create indexes from specific fields inside the JSON payload. For spatial queries you also need to take a step further and index number fields to be compared within bounding-boxes and distance queries.

[@yjawhar]

JSON is supported by SQLite and there are plugins and a special type of index for Geo data

[@StephenBlum]

@@yjawhar that's right! SQLite just recently added support for JSON 🎉🎉🎉

[@StephenBlum]

@denysolleik9896 SQLite added JSON support 🎉

[@LARathbone]

To play Devil's advocate, why SHOULD SQLite support JSON? It supports strings. Why not parse the JSON with a library better equipped to do so, and then replace the cell data back with JSON as a string, again, using the JSON parser or library?

[@j3553hh]

Very helpful video. Thanks! Keep at it.

[@StephenBlum]

Glad it was helpful! More videos are in the works 😊🙌

[@codecaine]

I love using python. People are like but it is a slow Language. Most the time the bottlenecks will be the database. Most IO operations are written in C for Python so there is not much performance difference. Then you have other c libraries like numpy, pandas etc... Sometimes I use multiple languages together it just really depends on the project I am working on.

[@StephenBlum]

you're spot on! 😄 Python might not be the fastest language in terms of raw execution speed. It has strengths! It is easy to read. It has a lot of community support and a vast ecosystem of libraries like NumPy, pandas, PyTorch. These libraries are often written in C or C++ for performance. Python can use the C library speed. And you're right about the database. The bottleneck in applications is I/O operations rather than the language itself. Using Python in combination with C, C++, or even languages like Rust when necessary provides robustness and high-performance. Choosing the right tool for the job! Great to hear you're enjoying your work with Python and other languages. Keep experimenting and pushing boundaries! 🚀🐍

[@HowWebsite]

I am trying to learn how to build out my own server and run things like appflowy on it. Then share it with a small team to manage projects. Could I interview you? I'm looking for advice.

[@StephenBlum]

Yes absolutely let me know what you have in mind 😄

[@HowWebsite]

@@StephenBlum Wow! Sweet! I'm not sure what the best way is to share what I would like to do.... I'll message you on LinkedIn and we will go from there.

[@StephenBlum]

@@HowWebsite sounds great! www.linkedin.com/in/stephenlb/

[@anonstrider9136]

Close your eyes and he's Willy Wonka

[@StephenBlum]

Yes can see that 🍫💝 and hear it 😄

[@HomeEngineer-wm5fg]

So what does "Better-SQLite" do.....better?

[@StephenBlum]

Good question! 😄 One of my favorite advantages of SQLite is the ability to make multiple SQL calls with nearly zero latency impact. SQLite is located in your applications memory space. PostgreSQL and MySQL are further away from your application code on another computer.

[@HappyCheeryChap]

​@@StephenBlum on another computer? Only if that's where you put them. Which you generally wouldn't for small projects anyway. Weird thing to say. Seems you're taking about things you don't have any experience with.

[@StephenBlum]

​@@HappyCheeryChap good catch! 😄 Yes this is a point you make. And I skipped past this entirely. Often the details are critical. And you found a critical detail 🙌 When building access to SQLite on a remote system, you also have to build in a web service which listens and binds on a host port that is accessible to the calling client. Thank you for mentioning! This is important. I like SQLite because you get excellent speed and you get to build how it is accessed. Which does require extra effort.