Show HN: ScratchDB – Open-Source Snowflake on ClickHouse (github.com)

You can use it for logs. I have a basic example here: https://scratchdb.com/blog/fly-logs-to-clickhouse/

It's just storing data in a database so it persists as long as you want. For the hosted version you just pay per GB of compressed data, so if you have tons of logs then you can keep them, or you can clear out old data to save on disk space.

I haven't seen TiDB before - thanks for sharing. I honestly haven't spent tons of much time analyzing other databases (there are so many!) but I haven't seen much out there which tries to be "Firebase for OLAP" - that is, make it so easy that a developer who maybe doesn't have time to learn Clickhouse can be effective with it. That's the most important goal of this project: lower the barrier for people who need to use a columnar data structure to be able to do so [without thinking too much about it] and get back to solving their engineering problems.

TiFlash is designed for TiDB users and seamlessly moving between inserting row data and querying column storage.

Good to meet you! I hadn't seen graphjson before, I'll check that out. I'm also a fan of tinybird, and I think we have similar goals of wanting to make it easier for people to adopt OLAP.

On the technical side, I've made different design decisions when it comes to ingesting and processing data. For example, after a table is created, you can post new JSON, with different columns, and we ingest it without needing a manual migration step. I also have a different treatment for JSON arrays, where we can split them into multiple Clickhouse rows (rather than using a clickhouse array.)

Philosophically, I think there is a lot of room for open-source software that also has an amazing UI and developer experience. I've been writing OSS for years and basically think this is the best way to build successful developer tools.

> how you see your project differs from GraphJSON (https://www.graphjson.com/) and Tinybird (https://www.tinybird.co/)?

Not OP, but neither of these appear to be open source?

I don't bill separately for the compute vs storage the way bigtable does. The pricing per GB of data is inclusive of compute. The goal is for pricing to be modeled similarly to DynamoDB - just pay for what you use. The other way I charge is by query wall time - so a 30s query will cost you more than a 500ms one.

I haven't used bigtable but it seems like the minimum charge is on the order of $300 before you have any data. With ScratchDB, the minimum charge is $10 for 30 GB.

Additionally, on average, data has a 25% compression ratio. So if your 1 TB of data only takes up 250GB, you only pay for that.

Bigtable isn't OLAP, so you would not use them for the same data. This competes more directly with GCP's BigQuery.

Finally, I'm interested in pricing feedback! The goal is to be able to sustain the development of this, so I want to do what makes sense.

Thank you - really excited about what you're building too!

This is good feedback on writing a clear message - I appreciate it.

The goal of this project is to build a superb developer experience around analytical databases. I think that is one of Snowflake's (many) value propositions. It is also a goal for users to be able to have full control of their data, how it is processed, and be able to make economical use of their compute.

This project does not have a goal of matching anyone else's features. But of course, as we grow, we'll end up building things which are important to enterprises.

This is a really good question. My snarky answer is "the way we ingest JSON doesn't require 50 pages and n configuration settings to explain."

Here's a more factual one:

- We don't use tuples for nested JSON. We just use underscores to show parent_child relationships in keys.

- We don't use arrays. Instead, we make it easy to split into multiple rows so you can use regular SQL.

I haven't directly compared Clickhouse's all of different JSON treatments compared to what I've implemented, but my goal was to build something that you could run and would "just work the way it should."

  {
    "some_key": "1"
    "some": {
      "key": "2"
    }
  }

Schema inference for nested JSON objects is a feature of ClickHouse 23.9. I've made a video about it: https://www.youtube.com/watch?v=yS8YU-rBpMM&t=1846s

Great question. Each new table is given a __row_id column which ScratchDB populates and that is the default primary key.

Then, as data is added, I look at the cardinality of each column and set an ORDER BY appropriately. The goal (implementation in progress, not launched yet) is for ScratchDB to do this automatically - we should be able to automatically look at both the data in your table and queries and analytically come up with an appropriate sorting key.

Currently no, but I’m open to the idea of being able to set this. Perhaps the ability to set it on the fly (or better yet, inform the user of which type of compression to use based on their actual data!) would be useful. Happy to discuss in a gh issue too.

Yes! Have an issue for that https://github.com/scratchdata/ScratchDB/issues/19

What would you want it to look like?

Really interesting question(s)! Will try to answer the gist.

First, the system is designed for users to be able to drop in whatever hardware they want. So your questions are around my initial deployment options. These will certainly change in the future.

I chose Hetzner because of cost. I will probably end up using other providers in the future but Hetzner let me begin this experiment without burning my runway.

I use metal servers for Clickhouse, and small cloud boxes and LBs for the API. It happens I'm using US-based cloud servers because that's where my users are.

I'm using B2 + SQS because I did not want to take on sysadmin for those components. They are not performance-sensitive. Using B2 at $0.006/GB vs managing minio on an SX server at $0.001/GB was acceptable to me :)

Why use regular servers for the API instead of fly.io? Because the API writes data to disk, and then bulk loads it to clickhouse. This means I needed durable and reliable disk, which you can only have with actual VMs. I didn't want the process to be randomly SIGKILL'd since the process shuts down safely to avoid data loss, and I didn't want the risk of ephemeral storage. So to control all this I have to run the HTTP servers myself.

I might experiment with a PaaS for this, but it was easy enough to just set up an init script to run the daemon.

re: negotiating power - that conversation only happens when I have volume, at which point I will be able to negotiate with any provider.

re: points of failure/latency - this will continually change: the deployment strategy for today will be different than when I'm managing 100s of TB of data across thousands of users. The main focus is to make the system flexible to handle different topologies and to be able to change providers by updating a configuration.

Thank you!

Yes! I should set up confirmation emails - I'm reaching out to the folks who have signed up so you should hear back soon! If I miss it (apologies!) then feel free to email directly.