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

We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

beets.io

Post reply on HN
Print view View original

81–90 of 125 posts

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#81

Post by ufo »

jstimpfle wrote: post #49
batmansmk wrote: post #30

We use SQLite in an app with about 1,000 active users. It took us: - 0h to manage backups ("cp"), - 0h to manage seeds and tests fixtures ("cp"), - 0h to configure and secure (void), - 0h to write the deployment scripts (void), - 0h monitoring/watchdog jobs (void), - 1h to rsync for failover ("rsync") My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, w…

> 0h to manage backups ("cp") Are you aware that's unsafe? To make a safe backup use sqlite3's ".dump" command (or filesystem snapshotting, but I've had bad experiences with that, at least on btrfs).

I'm not very familiar with the details here. What are the problems with cp?

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#82

Post by Someone1234 »

infogulch wrote: post #76
Someone1234 wrote: post #47

Earlier quoted context omitted.

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simpl…

> CSV's biggest strength is that it is easy to parse correctly (goes on to say a bunch of applications don't parse it correctly; lets add mssql to the pile)

Please review the comment rules here:

https://news.ycombinator.com/newsguidelines.html

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#83

Post by Sohcahtoa82 »

jimktrains2 wrote: post #34
batmansmk wrote: post #30

We use SQLite in an app with about 1,000 active users. It took us: - 0h to manage backups ("cp"), - 0h to manage seeds and tests fixtures ("cp"), - 0h to configure and secure (void), - 0h to write the deployment scripts (void), - 0h monitoring/watchdog jobs (void), - 1h to rsync for failover ("rsync") My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, w…

Do you have a write-heavy workload? How do you handle contention? Do you have multiple servers? How do you handle real-time filesystem sync? If you only have one server, how do you handle its inevitable failure? Do you loose any data collected between last backup and failure? (I'm not saying SQLite isn't good, because it's f-ing amazing. I'm just not sold on it as a multi-process, multi-user database.) (If you have a…

> I'm just not sold on it as a multi-process, multi-user database.

That's fine. It was never designed to be a multi-process, multi-user database. Even the authors don't pretend it works in that use case:

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

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#84

Post by dagss »

kodablah wrote: post #59
mamcx wrote: post #55

Earlier quoted context omitted.

This was for long time the "correct" way to think about this. Let me show you why is insane. Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#? A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away? Why think is nut to code most or all the code on it?

> Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#? Why would I do such a thing? I rarely ever promote abstraction for abstraction's sake and will in almost all cases disagree with premature abstraction. There are levels and for simple CRUD things, a thin contract over your DB is a reasonable tradeoff. > A RDBMS is even more important that the glue code. Why hide i…

In many projects the database is way more important than the "backend" or what have you. Business logic in SQL (DB table schemas is 80% of business logic anyway...); Java or Python or whatever is just some glue to connect user interface or API to database.

(I think "glue code" in parent referred to "what is not the UI or DB" BTW, i.e. the "Java app".)

When the database is the most fundamental thing, then trying to abstract away the database can be a bit like trying to abstract away the programming language you are using.

I have code where I would much rather rewrite the "backend" in another language than swap out the database.

I think it was a very good analogy to say that abstracting over database is a bit like abstracting over what programming language you are using.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#85

Post by wenc »

rhizome wrote: post #74
wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

SQLite has no type safety on its fields; CSV is just text. You can create an integer field and save the value "wenc" in a SQLite record without it complaining, something it calls "dynamic typing."

Good point. Type affinity via dynamic typing [1] isn't strong typing. But I think it's still slightly better than CSV's no-typing.

[1] https://www.sqlite.org/faq.html#q3

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#86

Post by octorian »

laumars wrote: post #19

> The idea is that a more complicated DBMS should be faster, especially for huge music libraries. How "huge" are they talking about? I built a tool that imports Apache logs into sqlite for quick analysis and that easily handles several million records.

For single-threaded access, its possible that a more complicated DBMS will actually be slower than SQLite. (Or similar in performance.)

Of course once your application needs to do multithreaded DB access (especially if writes are involved), then you're far better off ditching SQLite. Then again, this is probably not a very common use case for a typical desktop application.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#87

Post by jstimpfle »

ufo wrote: post #81
jstimpfle wrote: post #49

Earlier quoted context omitted.

> 0h to manage backups ("cp") Are you aware that's unsafe? To make a safe backup use sqlite3's ".dump" command (or filesystem snapshotting, but I've had bad experiences with that, at least on btrfs).

I'm not very familiar with the details here. What are the problems with cp?

cp does not make atomic snapshots. It copies by reading (usually sequentially) chunk by chunk from the source file, and writing these chunks to the destination file. This takes time. If the database has writes at the time of backup, the backup might be invalid (it contains some old parts and some new parts).

(Unless you use e.g. the --reflink option of GNU cp, in which case it makes atomic snapshots on filesystems that support it).

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#88

Post by neves »

wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

Because it is human readable. I can inspect it without any tool installed. I can grep it. I can awk it. I can understand it.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#89

Post by [deleted] »

jacquesm wrote: post #51
Boulth wrote: post #42

Earlier quoted context omitted.

> Do you have a write-heavy workload? How do you handle contention? I'd also be interested in that. Last time I wanted to use Sqlite opening twice the same file for writing either would not succeed or could time out.

That's because SQLite is meant for single user applications. If you try to open the file the second time it will wait to acquire the lock.

[deleted]

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#90

Post by infogulch »

makmanalp wrote: post #70

We use sqlite in data-heavy visualization website ( http://atlas.cid.harvard.edu/ ): We currently have an approximately 22GB sqlite file with the two largest tables going up to 150 million rows each. It still works just fine. The limiting factor seems to be the I/O throughput of the instance and disk of the server (EBS volumes). Read-only workloads, most of which eventually get cached by the webserver, but still. Eac…

You can help the optimizer stumbling by running ANALYZE [1] with representative data present, and turning off auto-analyze. This was intentionally designed as a feature so you could set up the statistics for a db, empty it, deploy it out to the end user and know that the optimizer isn't gonna go off and do something funky.

[1]: https://www.sqlite.org/lang_analyze.html

Post reply on HN