3 ms·

Has anybody used Sqlite in a server side, production level capacity.... I know it works great on embeded cases, but how about server side with a decent user lo

Has anybody used Sqlite in a server side, production level capacity.... I know it works great on embeded cases, but how about server side with a decent user load?

sk0g 6y ago
SQLite is for use cases where a dedicated DB is overkill/impossible. Considering the limitations it has, it probably isn't usable for most production DBs.
- marvel_boy 6y ago
  Not true. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
  - sk0g 6y ago
    Some of the limitations make it a no-go for me at least [0] [0] https://www.sqlite.org/omitted.html https://www.sqlite.org/omitted.html
sterlind 6y ago
I was about to ask what the use-case would be, since I assumed server SQL databases would use Paxos or Raft or something, but apparently I was wrong! I guess durability is done through persisted volumes, storing tables in files and being deliberate about journal structure and fsync? In that kind of world I guess sqlite is as good a choice as any if you don't need a SQL endpoint.
kinkora 6y ago
Pieter Levels (levelsio) of Nomadlist famously gets mocked for using a single index.php and sqlite but does 5 figure MRR. https://nomadlist.com/open https://nomadlist.com/open He used to get a lot of flack for it and i never understood why since you use whatever gets the job done. Too many people doing the "gatekeeper" thing.
arendtio 6y ago
SQLite is totally capable of delivering a good performance. The main problem is that you have to take care of efficiently opening and closing the file yourself. Naively doing it for every user/transaction will quickly put an end to your scalability plans and in fact, it is something you get with a DB server applications for free. That said, it's strength is its simplicity and not its scalability. So if you plan to run a database server cluster, you probably want to look for other technologies. But as long as you are on a single machine, your SQLite performance problems are probably a case of 'you are doing it wrong' ;-) So when you are talking about server-side, you probably don't want to limit your technology choice to a database engine that can not be clustered. However, if you are offering software which should be simple to install on a server (e.g. Nextcloud), providing an SQLite option is probably a good idea, because nobody has to care about DB-Server administration and connection setup.
karl42 6y ago
If you do mostly reads, it works great. I use it on http://www.wikdict.com http://www.wikdict.com for years without problems. I for e have very much traffic, though.
sradman 6y ago
There has always been a case for server-side embedded databases for app servers. It can be an in-process library like SQLite or Apache Derby, but databases like SQL Anywhere and SQL Server also have local IPC protocols that achieve the same effect. App Embedded databases are not the main use case for non-SQL transactional engines like Berkeley DB and Microsoft ESE (used for ActiveDirectory and Exchange Server). SQLite is a better server-side engine than the critics think but it is ill suited with the default options. WAL mode and Shared Cache mode must be enabled for decent performance. The popular runtime libraries for SQLite don’t expose the required C API calls nor even the enhanced functionality of the FILE URL optional connection string. Even when configured properly, concurrency in SQLite is limited for apps that have a hot row or page. Row level locking or snapshot isolation are sometimes a huge help but fast local reads go a long way in alleviating the need. The main obstacle, however, is that the popular web frameworks assume a wire protocol SQL engine running on a separate tier like Heroku’s 12-Factor App architecture.

Has anybody used Sqlite in a server side, production level capacity.... I know it works great on embeded cases, but how about server side with a decent user load?

sk0g 6y ago

SQLite is for use cases where a dedicated DB is overkill/impossible. Considering the limitations it has, it probably isn't usable for most production DBs.

marvel_boy 6y ago
Not true. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- sk0g 6y ago
  Some of the limitations make it a no-go for me at least [0] [0] https://www.sqlite.org/omitted.html https://www.sqlite.org/omitted.html

sterlind 6y ago

I was about to ask what the use-case would be, since I assumed server SQL databases would use Paxos or Raft or something, but apparently I was wrong! I guess durability is done through persisted volumes, storing tables in files and being deliberate about journal structure and fsync? In that kind of world I guess sqlite is as good a choice as any if you don't need a SQL endpoint.

kinkora 6y ago

Pieter Levels (levelsio) of Nomadlist famously gets mocked for using a single index.php and sqlite but does 5 figure MRR. https://nomadlist.com/open https://nomadlist.com/open He used to get a lot of flack for it and i never understood why since you use whatever gets the job done. Too many people doing the "gatekeeper" thing.

arendtio 6y ago

SQLite is totally capable of delivering a good performance. The main problem is that you have to take care of efficiently opening and closing the file yourself. Naively doing it for every user/transaction will quickly put an end to your scalability plans and in fact, it is something you get with a DB server applications for free. That said, it's strength is its simplicity and not its scalability. So if you plan to run a database server cluster, you probably want to look for other technologies. But as long as you are on a single machine, your SQLite performance problems are probably a case of 'you are doing it wrong' ;-) So when you are talking about server-side, you probably don't want to limit your technology choice to a database engine that can not be clustered. However, if you are offering software which should be simple to install on a server (e.g. Nextcloud), providing an SQLite option is probably a good idea, because nobody has to care about DB-Server administration and connection setup.

karl42 6y ago

If you do mostly reads, it works great. I use it on http://www.wikdict.com http://www.wikdict.com for years without problems. I for e have very much traffic, though.

sradman 6y ago

There has always been a case for server-side embedded databases for app servers. It can be an in-process library like SQLite or Apache Derby, but databases like SQL Anywhere and SQL Server also have local IPC protocols that achieve the same effect. App Embedded databases are not the main use case for non-SQL transactional engines like Berkeley DB and Microsoft ESE (used for ActiveDirectory and Exchange Server). SQLite is a better server-side engine than the critics think but it is ill suited with the default options. WAL mode and Shared Cache mode must be enabled for decent performance. The popular runtime libraries for SQLite don’t expose the required C API calls nor even the enhanced functionality of the FILE URL optional connection string. Even when configured properly, concurrency in SQLite is limited for apps that have a hot row or page. Row level locking or snapshot isolation are sometimes a huge help but fast local reads go a long way in alleviating the need. The main obstacle, however, is that the popular web frameworks assume a wire protocol SQL engine running on a separate tier like Heroku’s 12-Factor App architecture.