I'm curious why the test needed so many router hosts:
512 shards, each with one Postgres primary each on an r8g.16xlarge
480 Neki routers, each on its own 8xlarge instance
That's ~250K queries/sec per router which seems lowish for this type of workload? The routers won't be doing very much (parse query, route it to proper shard?).
that's over 13k queries/sec per router core, about 50% of it is spent doing syscalls, the other 50%: parsing, doing grpc, tls, go gc, resolving the shards, waiting for the responses
neki is still in platform preview, this experimentation allowed us to collect profiles at such scale and ship some nice optimizations, more are coming
yeah, this is a definitely a "best case" workload for a sharded database. Single row reads on the key used to shard with no hotspots (no shard to shard network traffic at all).
The intro section is a good summary of why distributed SQL databases (Spanner, roach, Yugabyte, TiDB) haven't taken off in the market in the same way as say distributed data warehouses have (Snowflake, Databricks, FabricDW, Clickhouse, etc.).
I would add a few other things to the list of scaling problems. Some SQL features are hard to scale out (auto_increment/serial columns, unique secondary keys, foreign keys, etc.). Some SQL query operators are hard to scale out for OLTP queries that want low latency and high throughput (DISTINCT, LIMIT/TOP-N, non-collocated joins). I take it SpacetimeDB is a nosql database, so these problems are less important to them?
As to how spacetimedb plans to scale out, I didn't follow it fully. It's hard to take the spacetimedb folks seriously (see: https://strn.cat/posts/spacetime/).
I'm a cofounder of SpacetimeDB (and the author of OPs article). The https://strn.cat/posts/spacetime/ article has several substantial errors. I've spoken with Vicent directly about them.
Most notably, almost the entire commentary about durability is incorrect. SpacetimeDB does not acknowledge anything before data is fully persisted to disk, even though he claims it does. Clients CAN chose to listen before that, but you can do the same thing in Postgres if you want.
There is no 50 ms delay to writing to disk. The article is mostly nonsense.
He spent 15 minutes looking at our code (by his own admission), having never written a database storage engine before AFAIK, and made a pronouncement that SpacetimeDB wasn't a good database. Crazy stuff.
We originally did MVCC and it was actually worse performance (in our implementation, I grant), but that's what OPs article is about. We spent a lot of money finding out that a lock is more performant.
Calling it a "hashtable" is something that only someone who hasn't built a DB engine would do. It's incredibly naive. It discounts the complexity of execution, atomicity, durability, constraint validation, migrations, query planning, incremental query evaluation, down to zero. It really makes it sound like he has absolutely no idea what he's talking about.
Fresh reader here. I am very interested to learn more about your sentence
> We spent a lot of money finding out that a lock is more performant.
I want to hear about that journey.
> Besides, it's a btree.
I guess it's not a hash table, but I think the point of Vincent's post is still worth exploring. You and he both say that essentially a key/value store is mutexed, and the user's code runs inside that mutex. That is fascinating. Why would that be better? Clearly it is, or you wouldn't have spent the money. What controls did you put in place to ensure user code didn't blow up the performance, or did you even feel the need for such controls? Is WASM VM execution fast enough for this? Is there even a market for that? Who pays to put their code inside that mutex and why? I want to know more.
I don't have the whole story for you, but the key is that SpacetimeDB transactions are not interactive. The TigerBeetle team talks about this a lot as well.
The TL;DR is that because you're not holding locks across the network (as is the case in Postgres), your server code can complete transactions in single digit microseconds, rather than milliseconds. And the practical effect is you can do many more transactions per second as a result.
Would it be correct to say that this is an example of the “Actually Serial Execution” strategy for implementing serializable transactions in the terminology of “Designing Data Intensive Applications”?
(I mention this mainly as a keyword that people can look up for more information)
I know people aren't going to put more than a few minutes into verification, so that's why I suggested it. I'm not really sure what else to do. It really is all there in the code.
I've pushed teams to choose Spanner over using Postgres (when already in the GCP ecosystem). It's not really more expensive when you sit down and do the math, you save untold hours of maintenance over the lifespan of the app, and it actually scales without fuss.
Is it really not more expensive? I haven't operated a large scale Spanner cluster, but the numbers would suggest it's quite a bit more expensive. I suppose it's workload dependent is what you're saying?
I ran some rough numbers a while ago and I found single-region GCP Spanner to be the most cost-effective relational DB option (out of the ones I looked it) if you don't mind that it can't scale to zero (min $65/mo), if synchronous replication for durability is a must and you need serializability. It's more cost effective than any Aurora offering including DSQL (which isn't serializable afaik but even then).
I think one of the maybe less talked about benefits of distributed SQL is support for nearly transparent rolling upgrades of the database with very little impact to a running workload. Spanner is best in class at this.
Major version upgrades, HA, multi-master, sharding and georeplication are all stories that are not as out-of-the box simple as they should be at this point, IMHO. Then you layer on all the ways devs tend to abuse postgres (stored procedures, pubsub systems, re-indexing hot tables, etc) that will need hours and hours of debate + meetings + committees + design reviews + more meetings to settle. When all along the team probably could have just written the data to something like Firestore and been, like, totally fine?
Very cool! pg_duckdb itself is missing fully integrated storage - it can query data stored externally (say in S3) in delta/iceberg formats, but it can't write out data in those formats via transactional writes to PG tables (insert\update\deletes). pg_mooncacke is one neat way of solving that problem. It lets you have a columnstore table in Postgres that can do both reads and writes as if it's any other PG table and have the storage format be an open format like delta/iceberg/etc with that data persisted to blob store (like most cloud DWs would do anyways).
Had a similar thought. Azure Postgres has something similar to pg_parquet (pg_azure_storage), but we're looking into replacing it with pg_duckdb assuming the extension continues to mature.
It would be great if the Postgres community could get behind one good opensource extension for the various columnstore data use cases (querying data stored in an open columnstore format - delta, iceberg, etc. being one of them). pg_duckdb seems to have the best chance at being the goto extension for this.
I was just wondering if pg_parquet could be combined with pg_azure_storage to write Parquet files to Azure Storage.
I had problems with pg_azure_storage in the past, because the roles pg_read_server_files and pg_write_server_files are unassignable on Azure PostgreSQL databases which makes the use of `COPY {FROM,TO}` impossible.
It would not be safe to let any user access object storage. Therefore, pg_parquet has two roles called parquet_object_store_read and parquet_object_store_write that give permission to COPY FROM/TO object storage (but not local file system).
In pg_azure_storage there is a comparable azure_storage_admin role that needs to be granted to users that need Azure Blob Storage permission.
Maybe good to mention torn pages somewhere too? Both MySQL and Postgres jump through some hoops to both detect them and repair them [1][2]. So, even the scenario in the post where fsync is used to harden writes, the database still needs to handle torn pages (or requires using a file system \ storage that guarantees atomic page writes at the page size the database is using as several managed\cloud databases do).
Thanks Adam! I think torn writes would still be caught via checksums, no? Although that may be later than you'd wish.
I'm not confident but from reading that page it seems that for Postgres at least, if it did do checksums it might not need to count on page-level atomic writes?
Checksums can detect a torn page, but not always repair them. It's likely a good part of the database page is gone (i.e., an amount of data that matches the disk / file system atomic write unit size is probably missing). Torn page writes are a pretty common scenario too, so databases need to be able to fully recover from them - not just detect them and report a corruption (ie., just pull the power plug from the machine during a heavy write workload and you're likely to get one - it doesn't require a solar ray to flip a bit :) ).
That's fair. In the post I did mention disk redundancy (and I guess I only implied recovery) as one additional level for safety. Which I think is what you're getting at too.
Disk redundancy won't help guarantee torn page protection if the writes across the redundant disks are not coordinated to have one start after the other finishes such that there is always have one copy of the page that is not currently being written. So writing to a RAID1 array won't help here without knowledge about how that raid1's writes work.
I'm not sure why you're getting downvoted. I think the pressure to appease Gartner usually starts when companies bring in CEOs whose primary background is enterprise sales. They tend to over value magic quadrant positioning (in my view).
For enterprise sales, the Gartner MQ and Forrester Wave are pretty big deals. (As is how they talk about you in client inquiries generally.) Maybe enterprise buyers place more stock in how the big analyst firms view a vendor than they should but it's generally the reality.
Control theory is also used by databases (probably not as often as it should be). It's great for "self tuning" [1], for example tuning the various cache sizes a database has to maximize throughput under changing workload conditions. Its definitely worth spending the time to understand PID controllers if your an engineer working on databases.
512 shards, each with one Postgres primary each on an r8g.16xlarge
480 Neki routers, each on its own 8xlarge instance
That's ~250K queries/sec per router which seems lowish for this type of workload? The routers won't be doing very much (parse query, route it to proper shard?).