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

SQLite as a Document Database (2020)

dgl.cx

Post reply on HN
Print view View original

41–50 of 63 posts

Re: SQLite as a Document Database (2020)

#41

Post by bitwize »

WhitneyLand wrote: post #14

Why do people say document database when they really just mean json database?

1) The internal or wire representation of data from document DBs isn't necessarily JSON, though it's normally converted to such.

2) "Document" has undergone a bit of semantic drift thanks to HTML and XML. In an informational context it means "structured, hierarchical unit of data containing mostly text". The data from forms, invoices, and the like needs to be collected and stored, even if it isn't properly normalized and relationalized (or is en route to being such) so a "document database" is thought to be suited to this task

I dunno, whatever, I'm in the "just fucking use postgres until you can justify why you shouldn't" camp.

Re: SQLite as a Document Database (2020)

#42

Post by bitwize »

SOLAR_FIELDS wrote: post #34
QuantumNomad_ wrote: post #19

Earlier quoted context omitted.

The 1st edition CouchDB book from 2010 explained it like this: > We write software to improve our lives and the lives of others. Usually this involves taking some mundane information—such as contacts, invoices, or receipts—and manipulating it using a computer application. CouchDB is a great fit for common applications like this because it embraces the natural idea of evolving, self-contained documents as the very cor…

The thing that people always miss about this takeaway is that while it is a truism, most data is actually inherently relational. Even in your example given, the individual components that are made to assemble that document are better represented as relational datastores

In the real world of business, generally you want to store these pieces of information together to establish a historical record, not have references or links to other tables, etc. Links and relations are fragile, as anyone who's clicked through to a 404 can attest. Standalone documents last as long as the media that stores them.

Re: SQLite as a Document Database (2020)

#44

Post by inigyou »

mathnode wrote: post #33
inigyou wrote: post #21

Earlier quoted context omitted.

Postgres does even better and it's available right now. No virtual column needed. CREATE TABLE t1 (data JSONB); INSERT INTO t1 VALUES ('{"column1":1234}'); CREATE INDEX t1column1 ON t1(data->'column1'); SELECT * FROM t1 WHERE data1->'column1' = '1234'; // not sure about data type

I would not call an index format which gets slower with growth "better". And that vacuum issue on JSONB scales with your data size.

What do you mean slower with growth? What vacuum issue?

Re: SQLite as a Document Database (2020)

#45

Post by somat »

SOLAR_FIELDS wrote: post #34
QuantumNomad_ wrote: post #19

Earlier quoted context omitted.

The 1st edition CouchDB book from 2010 explained it like this: > We write software to improve our lives and the lives of others. Usually this involves taking some mundane information—such as contacts, invoices, or receipts—and manipulating it using a computer application. CouchDB is a great fit for common applications like this because it embraces the natural idea of evolving, self-contained documents as the very cor…

The thing that people always miss about this takeaway is that while it is a truism, most data is actually inherently relational. Even in your example given, the individual components that are made to assemble that document are better represented as relational datastores

I would argue most data is inherently(naively?) hierarchical(the document), relational structured data is a clever but unintuitive mechanism to introduce powerful analytic opportunities to a set of data.

Basically a document is a report, a large disjoint volume of information on a subject, I consider this the natural form of data because this is how it is collected and how most people think about it. relational is sort of like storing that data as vertical slices through your stack of reports. Not natural at all but much nicer for analysis across the data set.

Re: SQLite as a Document Database (2020)

#46

Post by flomo »

Calavar wrote: post #26
WhitneyLand wrote: post #14

Why do people say document database when they really just mean json database?

It's a MongoDBism. The MongoDB community used document to mean the nonrelational equivalent of a row in a relational database. But over time there was definitional shift, and now it means a JSON blob, even if that blob is in a relational database.

It's a much older term, eg. Lotus Notes was described as a document database.

Re: SQLite as a Document Database (2020)

#48

Post by sgarland »

bitwize wrote: post #42
SOLAR_FIELDS wrote: post #34

Earlier quoted context omitted.

The thing that people always miss about this takeaway is that while it is a truism, most data is actually inherently relational. Even in your example given, the individual components that are made to assemble that document are better represented as relational datastores

In the real world of business, generally you want to store these pieces of information together to establish a historical record, not have references or links to other tables, etc. Links and relations are fragile, as anyone who's clicked through to a 404 can attest. Standalone documents last as long as the media that stores them.

Immutable records which are retained for legal purposes should be in object storage, not a database.

Re: SQLite as a Document Database (2020)

#50

Post by fipar »

SOLAR_FIELDS wrote: post #34
QuantumNomad_ wrote: post #19

Earlier quoted context omitted.

The 1st edition CouchDB book from 2010 explained it like this: > We write software to improve our lives and the lives of others. Usually this involves taking some mundane information—such as contacts, invoices, or receipts—and manipulating it using a computer application. CouchDB is a great fit for common applications like this because it embraces the natural idea of evolving, self-contained documents as the very cor…

The thing that people always miss about this takeaway is that while it is a truism, most data is actually inherently relational. Even in your example given, the individual components that are made to assemble that document are better represented as relational datastores

The "relational" in relational databases is not about department number in employees referencing departments.

What the model calls relations are sets of n-tuples where each attribute value has a domain.

SQL databases call their version of relations tables, and, in that view, a database with a single table is still relational.

Now I don't think SQL databases are relational but the analogy still holds (I'd just say that a relational database can have just a single relation)

If you meant it this way and I misunderstood you, apologies, though I'm not sure I'd say most data is actually inherently relational (even though I do think the relational model is the best one we have so far for databases).

However, if you meant that most data has relationships (as the ones we enforce with foreign keys in sql databases) then I agree with you, and I think using database management systems that don't have good support for representing this type of relationships between data entities will only work in niche cases and will eventually cause more trouble than benefits in general-purpose use cases.

Post reply on HN