91–100 of 125 posts
The real wins for us are multithreaded and multiprocess DB I/O. This is something MySQL can handle, but SQLite really isn't designed to do well or efficiently.
Earlier quoted context omitted.
Just a ballpark: my beets DB is 15 MB for ~1000 albums (well, folders). This post[0] claims 10M albums ever released. So maybe 150 GB upper bound? Assuming my db has been vacuumed recently (~~probably not true~~ Edit: nope, still 15M after vacuum) and ignoring that indexes will scale slightly non-linearly. [0]: https://www.quora.com/How-many-music-albums-are-available-in...
I think 10m is far too low to be the total number of albums. But it would be impossible to actually calculate that figure given the number of producers that have released content outside the scope of any particular authority (eg NIN has released stuff to download only from their site. Aphex Twin uploaded a load of stuff to SoundCloud. Etc). But we digress. My music collection is massive. It's got 30 years of singles…
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.
Earlier quoted context omitted.
Do you store the binary .sqlite in Git or source .sql and build the .sqlite?
Wouldn't tracking the binary with Git make a new copy on every commit?
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?
https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...
Earlier quoted context omitted.
> Why aren't we replacing CSV with it, especially for big data applications? Big data applications tend to use other structured binary formats like parquet and avro, which any big data tool can typically parse.
Parquet is a great columnar format, and Avro is great for schema evolution. But they're not that easy to work with directly. To this day there isn't a decent Parquet viewer to do adhoc data viewing. SQLite on the other hand has a lightweight SQL REPL that can be invoked from the command line. Spark can work with SQLite via JDBC, though obviously it isn't as native as Parquet. Between SQLite and Parquet, I might pick…
SQlite is usually a single file representing a database though, I don't know how it would work with partitioning and stuff, and then how to handle the schema evolving across sqlite files.
FYI: SQLite doesn't have ALTER TABLE.
Earlier quoted context omitted.
> Why? To allow others to impl the data store they want and to avoid having to write blog posts like these. It's not that much real work to quickly pull out an iface on a stable system. Assuming it would be just mostly pass-through to your main impl anyways and this is a common approach in refactorization. You don't need some huge system, just drop it back a layer and abstract it. Often, what you end up learning abou…
> To allow others to impl the data store they want and to avoid having to write blog posts like these. But why? For the longest time MySQL didn't support window function or CTEs. What do you do if you're using those? (I'm not sure of SQLite's window support, but I'm pretty sure it has CTEs.) If you're using postgres, should you not use pg_trgm (trigram indexer to use an index for regex searches on text), postgis (GIS…
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.
Earlier quoted context omitted.
Parquet is a great columnar format, and Avro is great for schema evolution. But they're not that easy to work with directly. To this day there isn't a decent Parquet viewer to do adhoc data viewing. SQLite on the other hand has a lightweight SQL REPL that can be invoked from the command line. Spark can work with SQLite via JDBC, though obviously it isn't as native as Parquet. Between SQLite and Parquet, I might pick…
csv is notoriously bad with spark - in fact you couldn't write to csv for the longest time without external libraries like the databricks one, so I don't contest your last point. SQlite is usually a single file representing a database though, I don't know how it would work with partitioning and stuff, and then how to handle the schema evolving across sqlite files.