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

Zero-latency SQLite storage in every Durable Object

simonwillison.net

Post reply on HN
Print view View original

41–50 of 108 posts

Re: Zero-latency SQLite storage in every Durable Object

#41

Post by yen223 »

adhamsalama wrote: post #40
yen223 wrote: post #34

Earlier quoted context omitted.

I did, twice. The second time, we had a reporting system that eventually stored billions of rows per day in a Postgres database. Processing times got so bad that we decided to migrate to Clickhouse, resulting in a substantial boost to query times. I maintain that we haven't exhausted all available optimisations for Postgres, but I cannot deny that the migration made sense in the long run - OLTP vs OLAP and all that.…

Well, this isn't specific to Postgres, is it? If you were storing billions of rows per day in MySQL, SQL Server, or Oracle, it still wouldn't be able to handle it, would it?

That's right. The key difference is using row-based vs column-based databases (i.e. OLTP vs OLAP). Any good database person should be cringing at the thought of using Postgres (or MySQL, Oracle, Sql Server, etc) for pulling reporting data.

That said, no regrets using Postgres there. If we started with Clickhouse the project could have not launched as quickly as it did, and that would have given us more problems.

Re: Zero-latency SQLite storage in every Durable Object

#42

Post by crabmusket »

adhamsalama wrote: post #38
simonw wrote: post #35

Earlier quoted context omitted.

That makes a lot of sense to me. One of my strongest hints that a non-relational data store might be a good idea is "grows by billions of rows a day".

Isn't Clickhouse relational?

It does allow you to query with SQL, but it's meant for OLAP workloads, not OLTP. Its internal architecture and storage is different to what you'd usually think of as a relational database, like Postgres. See https://clickhouse.com/docs/en/concepts/why-clickhouse-is-so...

The term "relational" is overloaded. Sometimes it means "you can use SQL" and sometimes it means "OLTP with data stored in an AoS btree".

(And sometimes, a pet peeve of mine, it means "data with relationships" which is based on misunderstanding the term "relation". If someone asks you if "your data is relational" they are suffering from this confusion.)

Re: Zero-latency SQLite storage in every Durable Object

#43

Post by yen223 »

adhamsalama wrote: post #38
simonw wrote: post #35

Earlier quoted context omitted.

That makes a lot of sense to me. One of my strongest hints that a non-relational data store might be a good idea is "grows by billions of rows a day".

Isn't Clickhouse relational?

Clickhouse is a SQL database, so I guess it is?

(Strictly speaking since a "relation" in the original Codd-paper sense is a table, anything with tables is relational. I don't know if that's what people mean by "relational", plus I don't know what counts as "non-relational" in that sense)

Re: Zero-latency SQLite storage in every Durable Object

#44

Post by attilakun »

braden-lk wrote: post #36

Durable objects seem so cool but the pricing always scares me. (Specifically, having to worry about getting hibernation right.) They’d be a great fit for our yjs document based strategy, but while everything in prod still works on plain ol redis and Postgres, it’s hard to justify an exploration.

Does CloudFlare have proper spending caps? If they have, I'd be open to try DOs but if they don't, it's a non-starter for an indie dev as I can't risk bankruptcy due to a bad for loop.

Re: Zero-latency SQLite storage in every Durable Object

#45

Post by xarope »

yen223 wrote: post #34
simonw wrote: post #23

Earlier quoted context omitted.

Have you worked on any examples of projects that started on PostgreSQL and ended up needing to migrate to something specialized?

I did, twice. The second time, we had a reporting system that eventually stored billions of rows per day in a Postgres database. Processing times got so bad that we decided to migrate to Clickhouse, resulting in a substantial boost to query times. I maintain that we haven't exhausted all available optimisations for Postgres, but I cannot deny that the migration made sense in the long run - OLTP vs OLAP and all that.…

Right, OLTP vs OLAP are very different workloads (using the car analogy, that would be like using a ferrari to tow a trailer, and an F250 to... oh wait, an F250 can do anything!).

But seriously though, even if you use postgres, as a former DBA (DB2 and Oracle) I would have tuned the OLTP database very differently to the OLAP database, and I don't mean just indexes, but even during ETL from OLTP->OLAP you might decide to de-normalize columns on the OLAP side simply to speed up queries (OLAP databases are the sort of database you were warned about, where indexes can be 10x the data size)

Re: Zero-latency SQLite storage in every Durable Object

#46

Post by simonw »

adhamsalama wrote: post #38
simonw wrote: post #35

Earlier quoted context omitted.

That makes a lot of sense to me. One of my strongest hints that a non-relational data store might be a good idea is "grows by billions of rows a day".

Isn't Clickhouse relational?

Kind of? By "relational" there I meant "traditional relational databases like MySQL and PostgreSQL that are optimized for transactions and aren't designed for large scale analytics".

Re: Zero-latency SQLite storage in every Durable Object

#48

Post by jchanimal »

crabmusket wrote: post #28
klabb3 wrote: post #25

Earlier quoted context omitted.

Databases is an extremely slow-maturing area, similar to programming languages, but are all deviations from Postgres shiny and hipster? The idea of colocating data and behavior is really a quantifiable reduction in complexity. It removes latency and bandwidth concerns, which means both operational concerns and development concerns (famously the impact of the N+1 problem is greatly reduced). You can absolutely argue t…

> That gives noSQL nightmares - breaking all kinds of important invariants of relational dbs IMO Durable Objects map well to use cases where there actually are documents. Think of Figma. There is a ton of data that lives inside the literal Figma document. It would be awful to have a relational table for like "shapes" with one row per rectangle across Figma's entire customer base. That's just not an appropriate use of…

We are wrestling with questions like this on the new document database we’re building. A database should correspond to some administrative domain object.

Today in Fireproof a database is a unit of sharing, but we are working toward a broader model where a database corresponds to an individual application’s state. So one database is all the shared documents not just a single unit of sharing.

These small changes early on can have big impact later. If you’re interested in these sort of design questions, the Fireproof Discord is where we are hashing out the v0.20 api.

(I was an early contributor to Apache CouchDB. Damien Katz, creator of CouchDB, is helping with engineering and raised these questions recently, along with other team members.)

Re: Zero-latency SQLite storage in every Durable Object

#49

Post by [deleted] »

emadda wrote: post #21

Some other interesting points: - The write api is sync, but it has a hidden async await: when you do your next output with a response, if the write fails the runtime will replace the response with a http failure. This allows the runtime to auto-batch writes and optimistically assume they will succeed, without the user explicitly handling the errors or awaits. - There are no read transactions, which would be useful to…

[deleted]
Post reply on HN