HN user
http://twitter.com/tudor_g
CTO at https://www.xata.io/
tudor.g@gmail.com
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 :)
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.
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.
- 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
It is currently about 30-40% slower than ClickHouse (single node, ofc). The PR to add it to clickbench was just accepted, so you can see the comparison here: https://benchmark.clickhouse.com/#system=+liH|_etx|gQ|saB&ty...
This is why I think sites like this one should show license information and make it simple to filter by OSS license.
Otherwise it's the same trap, just one level deeper.
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.
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.
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.
Hi HN,
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.
Let us know if you have any questions!
It's funny that this news showed up just as we (Xata) have gone the other direction, citing also changes due to AI: https://xata.io/blog/open-source-postgres-branching-copy-on-...
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.
This sounds cool, adding to my list of things to try out.
One note, this shouldn't be confused with https://github.com/xataio/pgstream which does logical replication/CDC with DDL changes.
The Amp editor extensions will self-destruct on March 5 at 8pm Pacific Time. Time to switch to the Amp CLI.
That's an interesting way to tackle the removal of features.
The Postgres extension model to capture the metrics (we also experimented with eBPF, but it causes too many kernel-user space context switches when you can do the same in an extension without them), and a small sidecar to push the metrics out via a standardized protocol like OTEL.
The extension model is great, but it doesn't work with existing postgres providers (RDS, Aurora, etc.). Unless one such extension becomes standard enough that all providers will support it. That would be ideal, IMO.
To be clear, I don't mean pg_stat_statements, that is standard enough, but an extension that pushes the actual queries in real-time.
If it's a network hop, then adds milliseconds, and not microseconds.
Are you talking about connection establishing time or for query delay? I think it should normally be under a millisecond for the later.
Even then, though, it needs to run on the server so it's hard to guarantee to not impact performance and availability. There are many Postgres/Mysql proxies used for connection pooling and such, so at least we understand their impact pretty well (and it tends to be minimal).
Others have mentioned similar solutions but I’d like to add one: a database solution with CoW branching and PII anonymisation solves the db part in a safe way.
Disclaimer: I work at Xata.io, which provides these features. We have a recent blog post with a demo of this: https://xata.io/blog/database-branching-for-ai-coding-agents
This is really cool and I love to see the interest in fast clones / branching here.
We've built Xata with this idea of using copy-on-write database branching for staging and testing setups, where you need to use testing data that's close to the real data. On top of just branching, we also do things like anonymization and scale-to-zero, so the dev branches are often really cheap. Check it out at https://xata.io/
The source database can't have any active connections during cloning. This is a PostgreSQL limitation, not a filesystem one. For production use, this usually means you create a dedicated template database rather than cloning your live database directly.
This is a key limitation to be aware of. A way to workaround it could be to use pgstream (https://github.com/xataio/pgstream) to copy from the production database to a production replica. Pgstream can also do anonymization on the way, this is what we use at Xata.
Thanks for the report!
Lots of good improvements, my favorites are Oauth, NOT NULL constraint with NOT VALID, uuidv7, RETURNING contains old/new. And I think the async IO will bring performance benefits, although maybe not so much immediately.
Great to see pgstream on HN!
Besides DDL changes, pgstream can also do on-the-fly data anonymization and data masking. It can stream to other stores, like Elasticsearch, but the current main focus is on PG to PG replication with DDL changes and anonymization.
We've been using it in Xata to power our `xata clone` functionality, which creates a "staging replica" on the Xata platform, with anonymized data that closely resembles production. Then one gets fast copy-on-write branching from this anonymized staging replica. This is great for creating dev branches and ephemeral environments.
Check out Xata.io. A `xata.micro` instance is ~$9 per month (+storage). It’s in private beta but I can give you access if you are interested.
Ah yes, it was a typo, sorry.
For a tooling solution for this problem, and many others, pgroll (https://github.com/xataio/pgroll) automates the steps from the blog post in a single higher-level operation. It can do things like adding a hidden column, backfill it with data, then adds the constraint, and only then expose it in the new schema.
That is correct, for non-volatile default values Postgres is quick, which means that it is generally a safe operation.
Also interesting, `now()` is non-volatile because it's defined as "start of the transaction". So if you add a column with `DEFAULT now()` all rows will get the same value. But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time. A bit of a subtle gotcha.
[author] My point was that using the default config for the other providers while you control yours is a weakness in the methodology, and might influence the results.
Note that when strictly limiting to 4 CPU it's still faster.
Another way to mitigate this is to make the agents always work only with a copy of the data that is anonymized. Assuming the anonymisation step removes / replaces all sensitive data, then whatever the AI agent does, it won't be disastrous.
The anonymization can be done by pgstream or pg_anonymizer. In combination with copy-on-write branching, you can create a safe environments on the fly for AI agents that get access to data relevant for production, but not quite production data.
On the Xata platform we actually do CoW snapshots and branching at the block device level, which works great.
However we are developing pgstream in order to bring in data and sync it from other Postgres providers. pgstream can also do anonymisation and in the future subsetting. Basically this means that no matter which Postgres service you are using (RDS, CloudSQL, etc) you can get still use Xata for staging and dev branches.
Answering on behalf of Xata, it is orthogonal. I'm curious to try out Oriole on our platform when I get some time.
I think you are describing Xata Agent :) https://github.com/xataio/agent It watches metrics/logs, queries pg_stat_stetements for slow queries, the sends slack notification with suggestions, including indexes, but can be more varied than that.
We use event triggers both in pgroll [0] and pgstream [1] to capture DDL changes. It's a feature of pgroll that it captures the schema migration history, regardless if you do the changes via pgroll or with another tool. And pgstream uses it to put the schema changes in the logical replication stream.
One thing to be aware is that on many Postgres DBaaS EVENT TRIGGERS are not allowed, because they generally require superuser. But RDS and Aurora do support them, we (Xata) support them of course, and I think Supabase is working on adding support for them.