Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite the only database you will ever need in most cases (2021)

unixsheikh.com

Post reply on HN
Print view View original

371–378 of 378 posts

Re: SQLite the only database you will ever need in most cases (2021)

#371

Post by yencabulator »

cldellow wrote: post #51
bravura wrote: post #35

I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that. Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything? Paging fly.io founders etc! If I have a persistent volume can my fly.io apps us…

IMO, the config you need is: 1) When you open the database: pragma journal_mode = wal; pragma synchronous = normal; 2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`. 3) Don't have long-running transactions. 4) Have some process to do backups. (3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite syst…

    pragma journal_mode = wal;
    pragma synchronous = normal;
I think it's worth noting that this combination loses committed transactions on kernel crash / power failure / etc. That is, your app replied "OK" to the client, but the data still gets lost.

> A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.

https://www.sqlite.org/pragma.html#pragma_synchronous

Re: SQLite the only database you will ever need in most cases (2021)

#372

Post by chasil »

srcreigh wrote: post #324
chasil wrote: post #295

Earlier quoted context omitted.

To optimize the writes, you must look at inotify. The sqlite commit is a specific pattern, it is either write or close/write. Using inotify events can see these faster than random waits.

Respect. I’m curious what use case for >500k writes/second or 5k write txns/second you’re implementing? I know nothing about inotify and random waits. What else can you tell me?

In SQLite's C API, there is a busy handler that you can set that accepts a pointer to a function. This is where inotify would be very practical:

https://sqlite.org/c3ref/busy_handler.html

The normal operation of the busy handler is to accept a maximum timeout (also implement at the SQL level with a PRAGMA). Setting this at the C API also sets a dedicated handler.

https://sqlite.org/c3ref/busy_timeout.html

The inotify interface (which is specific to Linux) allows the kernel to alert a process when writes or close/writes occur (among other filesystem activities). The arrival of such events is a more efficient way to check if the lock has been released, rather than a wait/retry of limited duration.

Let's look at this from the shell:

  # session 1:
  $ sqlite3 test.db
  SQLite version 3.34.1 2021-01-20 14:10:07
  Enter ".help" for usage hints.
  sqlite> create table foo(bar);
  sqlite> .quit

  # session 2:
  $ inotifywait -m test.db  
  Setting up watches.
  Watches established.

  # session 1:
  $ sqlite3 test.db
  SQLite version 3.34.1 2021-01-20 14:10:07
  Enter ".help" for usage hints.
  sqlite> insert into foo values('hello, world!');
  sqlite> begin transaction;
  sqlite> insert into foo values('so long, world!');
  sqlite> delete from foo;
  sqlite> commit;
 sqlite> .quit

  # session 2 output:
  Setting up watches.
  Watches established.
  test.db OPEN 
  test.db ACCESS 
  test.db CLOSE_NOWRITE,CLOSE 
  test.db OPEN 
  test.db ACCESS 
  test.db ACCESS 
  test.db ACCESS 
  test.db ACCESS 
  test.db MODIFY 
  test.db ACCESS 
  test.db MODIFY 
  test.db CLOSE_WRITE,CLOSE
"Path units" under systemd also use this interface.

Re: SQLite the only database you will ever need in most cases (2021)

#374

Post by chillfox »

maxthegeek1 wrote: post #360
chillfox wrote: post #289

Earlier quoted context omitted.

Correct, but in the context of deploys I would hope they are not random failures, but rather planned events that you do when you have updates to... deploy.

Yes, but you also don't want to have to deploy in the middle of the night in order to avoid downtime during peak hours. Not only is that a pain for whoever is monitoring the deployment, but now if the deployment breaks something you're going to have to go wake up all of the relevant stakeholders, if you even know who they are. Not to say late night deployments are never justified, but definitely not something devs wa…

There’s alternatives like making rollbacks really easy, and then automating it.

Re: SQLite the only database you will ever need in most cases (2021)

#375

Post by necovek »

berdon wrote: post #151
JohnBooty wrote: post #134

Earlier quoted context omitted.

You don't need to be a full-blown sysadmin to know how to do basic deployments, and learning these things will make you a better developer. Yes. True. However, it is also true that we have a limited amount of time per week and a limited number of weeks on Earth. Time spent learning sysadmin-y stuff is less time spent mastering developer-y stuff. Think about what it means to be a "full stack" engineer in 2023: - Unix…

Fwiw, I’ve used all of those in my career, extensively here and there. I’ve written many thousands of lines of bash scripts. Even written my own php-for-bash-script style code tags that support arbitrary shells. I’ve written my own log based distributed kv store, gone down the YouTube trail of writing my own db, gone through the angular and react iterations, deep dove in docker, docker compose, k8s, crds and custom h…

> I also have a wife and a 2.5 year old.

You did all of these in the last 2.5 years? If not, that part seems a bit irrelevant.

But regardless of the response, some kids are way easier than others (easy kids are those who like to eat and like to sleep, imho), and some parents have more help or stricter separation of parental duties.

Re: SQLite the only database you will ever need in most cases (2021)

#376

Post by necovek »

elteto wrote: post #194
mjmsmith wrote: post #160

Earlier quoted context omitted.

Maybe he wants to spend more of his time with his wife and floating point child.

My floating point children give me so much grief, they are all so irrational.

If they are proper floating point, they must be rational!

Re: SQLite the only database you will ever need in most cases (2021)

#377

Post by elteto »

necovek wrote: post #376
elteto wrote: post #194

Earlier quoted context omitted.

My floating point children give me so much grief, they are all so irrational.

If they are proper floating point, they must be rational!

They’ll never be their true selves.

Re: SQLite the only database you will ever need in most cases (2021)

#378

Post by albert_e »

drexlspivey wrote: post #322
albert_e wrote: post #259

AWS has a habit of taking a open source project and creating a "managed service" offering of it. Is it possible to offer SQLite as a managed / serverless offering? A light weight and cheap relational data store that we just consumer using an API

Cloudflare D1 does that https://blog.cloudflare.com/introducing-d1/

sounds interesting- thanks for the pointer

I am not familiar with cloudlflare and if it has a free tier like AWS

will explore

Post reply on HN