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

SQLite Is Serverless

sqlite.org

Post reply on HN
Print view View original

111–120 of 453 posts

Re: SQLite Is Serverless

#111

Post by bsenftner »

throw0101a wrote: post #90
Gys wrote: post #64

The main problem that I see for using sqlite is exactly for it being 'Classic Serverless'. Because how does one keep an up te date backup? Deploying to Heroku, Dokku, AWS Lambda and such means the sqlite file will be lost on a crash or new deploy. Even a VM can crash. Export to S3 on every write? Maybe if changes do not happen often, so only for specific use cases (and actually I think you should just generate static…

> Because how does one keep an up te date backup? https://www.sqlite.org/backup.html There's a ".backup" command: * https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_... Alternatively, given that it's ACID, you could just take a snapshot of the file system/volume in question, and do a recovery on restore. Edit: SQLite also has WAL files, so presumably one could just use tar/rsync to create the backup, and o…

Ding! correct answer. In addition, the backups can run in the background, simultaneous to active use in other threads. When the backup is initiated, it is a transaction, so it maintains knowledge of what portion of the database is new since the backup began and does not include that data. Which is what you want for backup integrity.

Re: SQLite Is Serverless

#113

Post by ComodoHacker »

lxgr wrote: post #36
oldgradstudent wrote: post #23

> Of those that are serverless, SQLite is the only one known to this author that allows multiple applications to access the same database at the same time. IIRC, MS Access allowed that, which explained a lot of its popularity.

Good point. Berkeley DB also supports multiple processes accessing a database concurrently, as far as I know. I was wondering if the authors were referring to SQL-like databases, but MS Access seems to be one?

Yes, MS Access supports both SQL and non-SQL API, and not mentioning it isn't very professional from the author.

Re: SQLite Is Serverless

#114

Post by bsenftner »

bob1029 wrote: post #69
geokon wrote: post #57

Earlier quoted context omitted.

will you store large objects like images or audio file directly in SQL?

In most cases I will. Pulling a blob out of a row is a lot faster than opening an additional file handle. There aren't really any downsides to this either unless you are running table scans. SQLite does support indexes (even full-text) and they do work miracles so do use them when necessary. I would go so far as to argue that SQLite could be used to store all of the assets for any large piece of software (I.e. a AAA…

Can confirm SQlite is used in AAA game engines as well as VFX production pipelines. I know because I put it there.

Re: SQLite Is Serverless

#116

Post by pdimitar »

dana321 wrote: post #91

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a web crawler in go and used sqlite as the backend. As soon as i connect using the command line interface, it slows down significantly. Just something to bear in mind if you want to use it with multiple processes!

Centralise your writes to be only done through a single thread/process and then you can read from as many threads/processes you like with no noticeable difference in performance (at least for the several hobby dataset importing projects I tried).

Re: SQLite Is Serverless

#117

Post by slfnflctd »

paulie_a wrote: post #104
tenant wrote: post #81

Earlier quoted context omitted.

Access is actually designed to work in a multiuser situation over a LAN for concurrent read and write. SQLite isn't afaik. As long as the LAN was cabled I never saw any issues. The only reason it was necessary to move to a server type database was because people were insisting on wifi networking.

It might have been designed for it. It was poorly designed for it. It was a clustfuck of corruption when actually utilized

You basically had to engineer around the corruption risks for anything actually in production. Data redundancy, old-school 'Save' buttons (your work isn't properly saved until you click it, because it sends copies of that work to multiple backends) and isolating users as much as possible to their own 'shards' was part of how I saw it kludged through in the real world.

There was still a lot of weird behavior, though, and while you could reduce data loss you couldn't eliminate it.

Re: SQLite Is Serverless

#118

Post by pilif »

pdimitar wrote: post #116
dana321 wrote: post #91

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a web crawler in go and used sqlite as the backend. As soon as i connect using the command line interface, it slows down significantly. Just something to bear in mind if you want to use it with multiple processes!

Centralise your writes to be only done through a single thread/process and then you can read from as many threads/processes you like with no noticeable difference in performance (at least for the several hobby dataset importing projects I tried).

«just write your own server on top of serverless embedded SQLite to get to acceptable performance without needing a client/server database»

(Honestly, once you are at the point where concurrency causes performance issues with SQLite, you are better off moving to databases designed to handle concurrency rather than trying to cobble together your own workaround - you have reached the point where the drawback of SQLite‘s architecture outweigh its advantages and the advantages of other databases architectures outweigh their drawbacks)

Re: SQLite Is Serverless

#119

Post by bob1029 »

dana321 wrote: post #91

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a web crawler in go and used sqlite as the backend. As soon as i connect using the command line interface, it slows down significantly. Just something to bear in mind if you want to use it with multiple processes!

Multiple processes is an instant anti-pattern for a single SQLite database. I would stop and reconsider your approach before trying to build this solution using it. The way I see it there are 3 options:

1) Implement another process which will have exclusive ownership of the shared SQLite database, and then use some IPC scheme to delegate database operations from multiple processes.

2) Give each process its own copy of a SQLite db if there is no effective shared state between these processes (I.e. you are just map-reducing web crawler results). Upon completion of each process you could aggregate each into a final combined db.

3) Use a hosted database solution such as Postgres.

The bigger question for me would be what are you going to do with this data once you collect it. If you plan on having another series of processes that then use the SQLite db to provide reporting views or execute business logic, I think a hosted solution might be a better option. If scalability is a serious concern, option 2 is probably your best bet.

Post reply on HN