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

61–70 of 125 posts

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

#61

Post by Someone1234 »

debaserab2 wrote: post #48
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…

I'd actually argue that CSV's biggest competitor specifically is xlsx. Which is kind of XML...

I'm talking about business to business transactions, as in automated transactions on VANs and similar data interchanges.

The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries which require Windows licenses, and the two formats have a huge amount of overhead compared to XML formats and CSV (which costs money on VANs).

This is a space where plain text files and binary is still king. Formats like EDI are still massive. In many ways CSV and XML are the new kids on the block.

Only end users are sending Excel formatted files (and PDFs) to one another.

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

#62

Post by shabbyrobe »

noir_lord wrote: post #53
benoror wrote: post #45

Earlier quoted context omitted.

Don't underestimate the ability to see right away CSV contents using Excel/Numbers/Google Spreadsheets. Non-coders technical users

I'm a coder and I still find the ability to eyeball a file useful, also csv can be munged about with the shell easily (cut, grep, sed etc) which can be surprisingly useful.

Or Miller: https://github.com/johnkerl/miller

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

#63

Post by rossdavidh »

benoror wrote: post #45
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.

Don't underestimate the ability to see right away CSV contents using Excel/Numbers/Google Spreadsheets. Non-coders technical users

...or even, in most cases, Notepad.

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

#64

Post by WA »

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.

Single process applications if I’m not mistaken. And somewhere in the docs it says: most writes take a few milliseconds at most, so even in a multi-process environment (say 5-10 PHP processes), it should be fine. Haven’t tried this though.

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

#65

Post by ams6110 »

noir_lord wrote: post #53
benoror wrote: post #45

Earlier quoted context omitted.

Don't underestimate the ability to see right away CSV contents using Excel/Numbers/Google Spreadsheets. Non-coders technical users

I'm a coder and I still find the ability to eyeball a file useful, also csv can be munged about with the shell easily (cut, grep, sed etc) which can be surprisingly useful.

csv can also be queried by tools like q[1] if sed, grep, etc. are not quite up to the task.

I find this very useful to quickly summarize, filter, reorder/rename columns etc. in csv files.

[1] https://harelba.github.io/q/

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

#67

Post by jimktrains2 »

kodablah wrote: post #38
Analemma_ wrote: post #33

Earlier quoted context omitted.

Why? I know this is kind of a snarky response, but seriously... why? This would take developer resources away from real feature work, introduce boatloads of complexity, add more points of failure, make testing and validation harder and make it harder to reason about the entire system end-to-end. Not that that isn't sometimes necessary, but you need a compelling reason, not just jumping into it because it sounds like…

> 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 tools), pg-routing (routing tools), ossp_uuid (uuid support), better types (boolean, range, inet, geometric, &c), or features such as RETURNING just because someone doesn't like my choice of database? No, I'm going to do what makes my life easier and my application faster and more featureful.

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

#68

Post by diogofranco »

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.

> 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.

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

#69

Post by wenc »

debaserab2 wrote: post #48
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…

I'd actually argue that CSV's biggest competitor specifically is xlsx. Which is kind of XML...

XLSX is actually a decent format for business users. The only trouble is it has tons of limits [1], most restrictive being the limit of 1,048,576 rows by 16,384 columns.

I'm working with a client who had to split up their data into several XLSX files because they hit a row limit.

They can of course ETL the data into a real SQL database, but ETL tools for end users are still not a common thing plus not all end users have access to a SQL database.

[1] https://support.office.com/en-us/article/excel-specification...

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

#70

Post by makmanalp »

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. Each visualization ends up pulling a large amount of data (sometimes in the 10 thousands of rows) so the network / serde overhead and the administration costs of an external database server adds up, though things have changed recently so perhaps a revisit is in order.

Where it hurts is: The query optimizer sometimes stumbles and does silly things (e.g. not always very smart about column / index selectivity statistics). The data import takes a long time (compared to e.g. postgres' COPY), so that's another pain point.

Post reply on HN