SQLite vs PostgreSQL or MySQL

Поділитися
Вставка
  • Опубліковано 1 лис 2024

КОМЕНТАРІ • 46

  • @KentCDodds-vids
    @KentCDodds-vids 2 місяці тому +3

    Oh hi 👋

    • @StephenBlum
      @StephenBlum  2 місяці тому +1

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

  • @BrunoBernard-kn6vt
    @BrunoBernard-kn6vt 7 місяців тому +13

    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
      @StephenBlum  7 місяців тому +2

      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
      @codecaine 3 місяці тому

      It is already recommended :)

    • @KikkerFish
      @KikkerFish 22 дні тому +1

      Most web apps never reach a million users because the vast majority of companies don’t have a million customers.

    • @GuillermoArellano
      @GuillermoArellano 10 днів тому +1

      @@KikkerFishYES, exactly what you said.

  • @thunder____
    @thunder____ 4 місяці тому +13

    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
      @StephenBlum  4 місяці тому +1

      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
      @anze3db 4 місяці тому +4

      @@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
      @StephenBlum  4 місяці тому +1

      @@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
      @bryanstark324 4 місяці тому +1

      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
      @StephenBlum  4 місяці тому +1

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

  • @apex-lazer
    @apex-lazer 5 днів тому +1

    Bro, this was so informative thank you so much.

    • @StephenBlum
      @StephenBlum  5 днів тому

      Glad it was helpful! DB technologies have a lot of variety. The tradeoffs can help you decide the best option for your use case 😊🎉

  • @denysolleik9896
    @denysolleik9896 Рік тому +7

    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
      @StephenBlum  Рік тому +1

      @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
      @yjawhar 10 місяців тому +3

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

    • @StephenBlum
      @StephenBlum  10 місяців тому +2

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

    • @StephenBlum
      @StephenBlum  10 місяців тому +1

      @denysolleik9896 SQLite added JSON support 🎉

    • @LARathbone
      @LARathbone 5 місяців тому +1

      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?

  • @HowWebsite
    @HowWebsite 8 місяців тому +1

    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
      @StephenBlum  8 місяців тому +1

      Yes absolutely let me know what you have in mind 😄

    • @HowWebsite
      @HowWebsite 8 місяців тому +1

      @@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
      @StephenBlum  8 місяців тому

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

  • @JamesJosephFinn
    @JamesJosephFinn Місяць тому +1

    Spock has a UA-cam Channel?! Live long and prosper 🖖🏼

    • @StephenBlum
      @StephenBlum  Місяць тому

      star trek ❤ live long and prosper! 🖖😀

  • @j3553hh
    @j3553hh 6 місяців тому +1

    Very helpful video. Thanks! Keep at it.

    • @StephenBlum
      @StephenBlum  6 місяців тому

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

  • @codecaine
    @codecaine 3 місяці тому +3

    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
      @StephenBlum  3 місяці тому +1

      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! 🚀🐍

    • @kowaihana
      @kowaihana 4 дні тому

      i wish python added braces it'd be so cool

  • @gernot-starke
    @gernot-starke 14 днів тому +1

    sqlite has a limit on 281 terabytes; so no exabyte....

    • @StephenBlum
      @StephenBlum  12 днів тому

      281 terabytes is a lot! and you are right that 281 isn't exabyte range for a single DB. Good distinction thank you!

  • @HomeEngineer-wm5fg
    @HomeEngineer-wm5fg 9 місяців тому +1

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

    • @StephenBlum
      @StephenBlum  9 місяців тому +2

      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
      @HappyCheeryChap 4 місяці тому +1

      ​@@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
      @StephenBlum  4 місяці тому +1

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

  • @anonstrider9136
    @anonstrider9136 2 місяці тому +1

    Close your eyes and he's Willy Wonka

    • @StephenBlum
      @StephenBlum  2 місяці тому

      Yes can see that 🍫💝 and hear it 😄