franckpachot

Aurora writes each block to 6 replicas, and waits for a quorum of 4 to acknowledge. This has small impact on commit time which you can see as IO:XactSync (https://dev.to/aws-heroes/aws-aurora-io-xactsync-is-not-a-po...) but provides good HA

All benchmarks are biased because nobody does the same level of config tuning on different databases. But this GigaOm/Microsoft one is a joke. They run Citus with no replication on 12x16+1x32 vCores and the others on 14x16 vCPU (so 14x8 cores) with replication factor 3.

but large cache is expensive in the cloud and you cannot scale up/down without downtime

I mentioned Neon because it is open source and the article says "Not OSS" for "DBMS-optimized cloud storage". Think of it as open-source Aurora https://neon.tech/

In the case of YugabyteDB, here is how we avoid "excessive number" of networks hops

- true Index Only Scan. PostgreSQL doesn't store the MVCC visibility in indexes and have to look at the table even in case of Index Only Scan. YugabyteDB has a different implementation of MVCC with no bloat, no vacuum and true Index Only Scan. Here is an example: https://dev.to/yugabyte/boosts-secondary-index-queries-with-... This is also used for reference table (duplicate covering indexes in each regions)

- Batching reads and writes. It is not a problem to add 10ms because you join two tables or check a foreign key. What would be problematic is doing that for each rows. YugabyteDB batches the read/write operations as much as possible. Here are two examples: https://dev.to/franckpachot/series/25365

- Pushdowns to avoid sending rows that are discarded later. Each node can apply PostgreSQL expressions to offload filtering to the storage nodes. Examples: https://dev.to/yugabyte/yugabytedb-predicate-push-down-pbb

- Loose index scan. With YugabyteDB LSM-Tree indexes, one index scan can read multiple ranges, which avoids multiple roundtrips. An example: https://dev.to/yugabyte/select-distinct-pushdown-to-do-a-loo...

- Locality of transaction table. If a transaction touches to only one node, or zone, or region, a local transaction table is used, and is promoted to the right level depending on what the transaction reads and writes.

Most of the times when I've seen people asking to store tables together, it was premature optimization, based on opinions rather than facts. When they try (with the right indexes of course) they appreciate that the distribution is an implementation detail that the application doesn't have to know. Of course, there are more and more optimizations in each release. If you have a PostgreSQL application and see low performance, please open a git issue.

I'm also working for Yugabyte as Developer Advocate. I don't always feel the need to precise it as I'm writing about facts, not marketing opinions, and who pays my salary has no influence on the response time I see in execution plans ;)

The article is a marketing promotion for Citus. They ignore Neon, a major innovation in distributed cloud storage solutions. They reduce Distributed SQL to key-value stores, when it is actually the only distributed architecture that preserves all SQL features (ACID, consistent reads, referential integrity, global indexes).

And the only fact about performance is a benchmark comparing elastic and resilient distributed SQL to non-HA Citus running on larger machines.

The "disappointing" benchmark mentioned in the article is a shame for GigaOm who published it and for Microsoft who paid for it. They compare Citus with no HA to CockroachDB and YugabyteDB with replication factor 3 Multi-AZ, resilient to data center failure. And they run Citus on 16 cores (=32 vCPU) and the others on 16 vCPU. But your point about "beefy machine" shows the real advantages of Distributed SQL. PostgreSQL and Citus needs downtime to save cost if you don't need that beefy machine all days all year. Scale up and down is downtime, as well as upgrades. Distributed SQL offers elasticity (no downtime to resize the cluster) and high availability (no downtime on failure or maintenance)

It is in the Wiki: https://wiki.postgresql.org/wiki/Serializable Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

I guess it is the same for all MVCC databases. They don't want to acquire a read lock just in case another transaction is in Serializable

What is monolithic in traditional SQL databases when compared to Distributed SQL?

Interesting, that's the one I don't really agree with. Bottleneck, yes, because it is a monolithic stream for the whole database. But corruption?

Yes, everything Yugabyte does is open source

This is incredibly awesome. When Oracle started to talk about Javascript in the database (around 2016), with GraalVM and MLE, it was clear for me that the goal was not to deploy stored procedures in Javascript but, at some point have the JS function called in the application and transparently run close to data. They will probably go there but that will take time.

I'm so happy to see this idea in Open Source with PostgreSQL! The best of both worlds: defined the business logic in the app but run it close to data for performance.

Yes, CockroachDB and YugabyteDB do more than sharding, they are distributed SQL (difference here: https://dev.to/yugabyte/partitioning-vs-sharding-what-about-...). YugabyteDB is open source and has more postgres features as its query layer is a fork of postgres, but good to test both

Looking at the execution plan, IN() or =ANY() seems faster: https://dbfiddle.uk/zIk9IrEN

OR reads the same number of buffers, but with a long execution plan

The power of SQL is that it is structured, based on mathematic definitions. Once written and tested, the surface of possible bug or wrong result is very limited. LLMs are based on language interpretation and do not have this precision

I'm not sure those are great recommendations. First, the range scan on Tid is there only since PostgreSQL 14: https://commitfest.postgresql.org/32/2924/ And the CTID changes on an active database: https://twitter.com/FranckPachot/status/1691021274829426688?...

With an index on User (createdAt, id) and one on ContactMethod ( primary,ContactMethod,userId), it should be fast (check that the the execution plan starts with User). Except if lot of recent users have no phones, but that will not be better in a single table (except if columnar storage)

Many decision trees I've seen when NoSQL purpose-built were becoming popular are obsolete now that the main SQL databases have structures, APIs and optimisations for non-relational models: JSONB, Text Search, Timeseries, Documents... Today, the differentiator on architecture goals: converged database vs. multiple services

I used your example to show how the 2 indexes solution is better on PostgreSQL, but the one composite index is the best one for YugabyteDB (PostgreSQL on a Spanner-like architecture): https://dev.to/yugabyte/one-fat-index-or-two-indexes-on-each...

by itself, Citus is just sharding to multiple databases. Then each of this database can be protected like any database with a standby

Bitmap Scan relies on shared buffers. Distributed SQL cannot share the buffers between nodes. BitmapAnd is nice but expensive. I prefer a compound index on foo(bar, baz) but PostgreSQL needs an additional index if there are query with condition on baz only. YugabyteDB, thanks to hybrid scan, can use this single index also for SELECT * FROM foo WHERE baz = 4;

Citus is not close to Spanner. No global secondary indexes, no cross-shard ACID (the commit status is eventually consistent). No auto-resharding. Yugabyte and Cockroach have a spanner-like architecture, but different open-source model and postgres-compatibility. What are those annoying issues?

23c Free is the same as the previous Oracle XE. Free (really, no risk, can be deployed anywhere) but limited in resources. The risk is to develop your application with all features and then have to buy all options for production

On self-hosted you can use the same as what cloud vendors are doing. Patroni or pg_auto_failover to manage single-primary + replicas. Maybe Neon to run an Aurora-like. CitusData... Azure did a lot around to get elasticity. Not easy to do the same. YugabyteDB on Kubernetes can be a cloud-native self-hosted solution

Google has Spanner. AWS is working on something similar. And both have YugabyteDB in their marketplace. Those are Distributed SQL (Global ACID), not Citus. For DataWarehouse which doesn't need ACID, there are other services.

“Multi-AZ” in Amazon RDS and how it may differ from High Availability: https://dev.to/aws-heroes/multi-az-in-amazon-rds-and-how-it-...

And this is wrong. Oracle stores the physical address in the index. The ROWID is the relative file number (in the tablespace) + block offset in the file + an index in the block row directory. The difference is that Oracle rows usually don't move and are updated in place. Because old version diff goes to undo segments.

YugabyteDB (open source) uses the Postgres code, to provide all PostgreSQL features, plugged on top of a Spanner-like distributed storage and transactions to scale horizontally: https://docs.yugabyte.com/preview/architecture/layered-archi...