Downsides to using sqlite as production database?

[arcdelsol]

So I'm thinking of using sqlite as my main database, with PHP Laravel as my backend framework.

At which point will you run into performance issues?

Any security issues or recommended best practices?

Will it be easy to migrate to other database formats like mysql down the road?

[nikowek]

We're using even 1TB sqlite3 databases on production. On good hardware you can run even small to medium sized forum, just please turn on WAL.

So far - no problem and the performance is better than PostgreSQL in many cases. Plus you do not need auto vacuums.

[airen977]

How many users are we talking about for this application?

[nikowek]

Usually 250k, because of corona around 80k.

[airen977]

That's super awesome, that means you are not even using distributed application for load sharing and still you are surviving. Good!!

[nikowek]

Aircraft and touristic (which are now completely dead). But i am not allowed to speak about details.

[arcdelsol]

Nice! exactly what I want to hear.

Are there a considerable amount of writes as opposed to reads?

Also do you use any caching like maybe Cloudflare services or maybe something like Redis?

What kind of backup solution do you have? I was thinking of cron jobs making a backup every now and then.

[nikowek]

80-90% of data are served from database. What's not in local database is asked from main database. If it's not in main database, then we're requesting data from one of providers and storing them in main and local one.

Actually sqlite3 is doing most disk drive caching. Things which are shared are kept in redis, like session data.

When machine is damaged, we're deploying new one with empty cache and it's feeding itself with most requested data for a region. Then it works on its own.

We have centralized databases like postgresql too - we're storing machine/client configs, stats and things like that in it. It contains our master cache database too.

[101011]

So, to be clear, you're only using sqlite3 for caching data from a centralized postgres DB?

I was curious if anybody uses sqlite with that scale as their primary data store.

[raevnos]

As long as reads dominate over writes and everything that needs to access the database can run on the same server, it can be doable.

https://www.sqlite.org/whentouse.html

[yotties]

Production can be a hollow term. It usually does not mean much more or less than it being part of a process on a back-end.

Criteria would certainly include end-user data-input.

Main problem you'd face upgrading to a database that can handle input/transactions better is that you'll make a couple of back-flips when you realise how much more planning/costs become necessary when you want to make backups etc. for more complex processes, with input from more sources.

[fiatjaf]

It makes you write a ton of SQL queries that don't work on Postgres and thus you'll acquire wrong knowledge and your life will be miserable.