Because you mentioned Xata (I'm the author of that blog post, thanks for mentioning it), this is pretty similar to what we do at the high level, but we built our own storage system rather than relying on Ceph. The reason is scalability to many volumes and to lesser degree performance.
I'd say Homescale is closer to Xata than Planetscale, tbh :)
> - Is ~300x faster than Postgres on analytical workloads. Right now it's 2x slower than Clickhouse on clickbench and I think it's possible to get faster than Clickhouse
That sounds like you are storing the data in a columnar format? Or do you do both row and columnar?
In a somewhat similar (yet also quite different) effort, I've been working on δx, a Postgres extension that compresses the data in a columnar format stored in normal Postgres tables (so replication, crash recovery, pg_dump, etc. still work normally). https://github.com/xataio/deltax
Yep! The new version of pgrust supports batch based execution and a columnar format. I'm curious how you got δx to perform that well? From what I've seen a columnar layout only gets you part of the way and really good parallelism and really fast hash tables seem to make up a significant portion of why Clickhouse is faster.
Yeah, spent a lot of time on parallelism, vectorizing, pipelining, filter push-downs, bloom filters, all the tricks out there. It's really fun to make pretty steady progress on this.
pg_mooncake (now effectively abandoned due to being acquired by Databricks, but still up at https://github.com/Mooncake-Labs/pg_mooncake) pulled the DuckDB engine into Postgres wholesale, if I remember right.
pg_lake also uses DuckDB but keeps it external, routing through Postgres and managing Iceberg tables (but not the data itself) there (https://github.com/Snowflake-Labs/pg_lake).
Both of these were neck and neck with ClickHouse last time I tried them.
Actually δx is faster than the "duckdb embedded in postgres" options: https://benchmark.clickhouse.com/#system=+_etx|_b|_i)|dula|pnc&type=-&machine=-6t|ca2|6ax|g4e|6ale|3al&cluster_size=-&opensource=-&hardware=+c&tuned=+n&metric=combined&queries=-
Plus all the normal Postgres features work as expected: physical/logical replication, crash recovery, pg_dump/pg_restore, etc.
That isn't what the data shows, but we don't need to discuss it further. My reply was to the person interested in learning from other Postgres OLAP designs. This stuff is all pretty immature though and I wouldn't actually build on it outside of some very narrow, well-understood workloads.
As small piece of feedback, I think it would be really good if the tools list would include license information, and make it easy to filter for open source tools/licenses. IMHO source-available extensions break part of what is attractive to the Postgres ecosystem, which is that you can move from one provider to another without vendor lock-in.
A bit of a shameless plug, but I've been working on this for a few months in the form of an extension: https://github.com/xataio/deltax
When I started I thought there's too much inherent overhead in using Postgres tables for storage and using the Postgres executor, so figured it would be pretty cool to match Timescale in performance. I didn't think it would be possible to get close to dedicated analytical DBs. But as the project progressed and the performance got better and better, I'm now firmly in the camp of doing analytics with Postgres + an extension.
I have been working on another PG extension for timeseries (https://github.com/xataio/deltax) for a few months, and trying to make it score as good as possible on ClickBench.
This is a project that is simply lot of fun to work on. There are many tricks that can be used to speed-up analytics, besides just type-aware compression:
* for each segment you will keep things like max/min/sum, number of distinct values, bloom filters, etc. For a good amount of common queries, you can answer them just based on this metadata, so you don't need to decompress the columns at all.
* for text column, you compress them differently based on cardinality. Low cardinality (think labels or similar) is dictionary based compression. High cardinality is LZ4.
* Generally the smaller the data on disk, the higher the cold runs performance. This is because you need less IO to load it in memory. I have discovered that on top of the type-aware compression, it's worth doing another round of LZ4. There's also some research that it's sometimes worth doing multiple passes of LZ4.
* Partition and segment pruning. If you can tell from the metadata or bloom filters that the filter doesn't match a partition or segment, you skip the whole thing.
* Push down of filters in the decompression layer. Depending on the compression algorithm, while you decompress you can also filter out the values that you don't need. This avoids passing data and allocating memory for elements that will be later discarded anyway.
* Organization of data on disk is more important than almost anything else. Of course, that's the main point of columnar storage, but there are level of details on how to organize the data so that IO is minimized during queries. I have tried 3-4 different layouts before settling on one.
* For top N type of queries, which are really common in analytics, you want to stop the reading from disk / decompressed as soon as you have enough data to guarantee that you have a correct top N to satisfy the query.
* Parallelize everything: at least ClickBench runs on instances with a lot of CPU cores, so you need to parallelize every step of the way. This is done differently depending on the query type. For example for top N, each worker can take a subset of the segments and get the top N from each of them. Then you combine those in a single result.
I work a lot with databases, but only a couple times have I really messed with time series stuff. I've enjoyed reading your list of tricks and it's gotten me interested in playing around with writing my own toy database to just experiment with some of these ideas.
What is your idea on monetizing it? Besides Timescale the other providers have found issue on monetization AFAIK. And they all restrict the S3 offloading for Enterprise accounts.
For context, I'm CTO at Xata where we offer a Postgres service that differentiates via other ways (very fast CoW branching, scale-to-zero, anonymization, etc). We often recommend people keeping production where it is (RDS, Cloud SQL, on-prem, etc.) and use Xata only for staging + branching.
We can't offer Timescale because of the license (except for the version without compression), which blocks some potential customers from using us.
So there is currently no monetization plan beyond reducing this blocker for us. An explicit goal of DeltaX is to be pure OSS, offered on Xata, and also hopefully on all other Postgres providers. Without making any hard promises for the long-term future, I will say I care deeply about it being something that reduces lock-in in the Postgres ecosystem, rather than increasing it.
This is Tudor from Xata. You can think of Xata as an open-source, self-hosted, alternative to Aurora/Neon. Highlight features are:
- Fast copy-on-write branching.
- Automatic scale-to-zero and wake-up on new connections.
- 100% Vanilla Postgres. We run upstream Postgres, no modifications.
- Production grade: high availability, read replicas, automatic failover/switchover, upgrades, backups with PITR, IP filtering, etc.
You can self-host it, or you can use our [cloud service](https://xata.io).
Background story: we exist as a company for almost 5 years, offered a Postgres service from the start, and have launched several different products and open source projects here on HN before, including pgroll and pgstream. About a year and half ago, we’ve started to rearchitect our core platform from scratch. It is running in production for almost an year now, and it’s serving customers of all sizes, including many multi-TB databases.
One of our goals in designing the new platform was to make it cloud independent and with a careful selection of dependencies. Part of the reason was for us to be able to offer it in any cloud, and the other part is the subject of the announcement today: we wanted to have it open source and self-hostable.
Use cases: We think Xata OSS is appropriate for two use cases:
- get fast your preview / testing / dev / ephemeral environments with realistic data. We think for many companies this is a better alternative to seed or synthetic data, and allows you to catch more classes of bugs. Combined with anonymization, especially in the world of coding agents, this is an important safety and productivity enabler.
- offer an internal PGaaS. The alternative we usually see at customers is that they use a Kubernetes operator to achieve this. But there’s more to a Postgres platform than just the operator. Xata is more opinionated and comes with APIs and CLI.
Technical details: We wanted from the start to offer CoW branching and vanilla Postgres. This basically meant that we wanted to do CoW at the storage layer, under Postgres. We’ve have tested a bunch of storage system for performance and reliability and ultimately landed on using OpenEBS. OpenEBS is an umbrella project for more storage engines for Kubernetes, and the one that we use is the replicated storage engine (aka Mayastor).
Small side note on separation of storage from compute: since the introduction of PlanetScale Metal, there has been a lot of discussion about the performance of local storage. We had these discussions internally as well, and what’s nice about OpenEBS is that it actually supports both: there are local storage engines and over-the-network storage engines. For our purpose of running CoW branches, however, the advantages of the separation are pretty clear: it allows spreading the compute to multiple nodes, while keeping the storage volumes colocated, which is needed for CoW.
So for now the Xata platform is focused on this, but it’s entirely possible to run Xata with local storage: basically a storage-class change away.
Another small side note: while Mayastor is serving us well, and it’s what we recommend for OSS installations, we have been working on our own storage engine in parallel (called Xatastor). It is the key to having sub-second branching and wake-up times and we’ll release it in a couple of weeks.
For the compute layer, we are building on top of CloudNativePG. It’s a stable and battle-tested operator covering all the production great concerns. We did add quite a lot of services around it, though: our custom SQL gateway, a “branch” operator, control plane and authentication services, etc.
The end result is what we think is an opinionated but flexible Postgres platform. More high level and easier to use than a K8s operator, and with a lot of battery included goodies.
We did consider arguments in both directions (e.g. easier to recreate the code, agents can understand better how it works), but I honestly think the security argument goes for open source: the OSS projects will get more scrutiny faster, which means bugs won't linger around.
Time will tell, I am in the open source camp, though.
Just wanted to appreciate the open-source work by Xata. I’ve been eyeing pgroll [1] for schema migrations after Liquibase license shenanigans (the only barrier for me is json-based migration instead of sql-based migrations)
I'd say Homescale is closer to Xata than Planetscale, tbh :)
reply