franckpachot

Or how to transform the open-source, no-vendor community PostgreSQL into a proprietary database, where each managed service comes with its own unique syntax, features, and benchmark claims. People go to PostgreSQL for freedom, not to bind their application to one extension with one owner and one maintainer.

And:

> The instance was assigned a public IP

I think that without a private link, the managed services that run in different accounts (Neon, Crunchy, Clickhouse if not BYOL) may be in a different data center than the client's. And this is just default pgbench where most of the time is spent on client-server roundtrips

"dollar quoting" is the PostgreSQL way to quote strings with quotes, avoiding double quoting or escape characters. I like to use the tagged version of it, like $sql$ SELECT ... $sql$ to describe what is inside.

Beyond the hype, the PostgreSQL community is aware of the lack of "batteries-included" HA. This discussion on the idea of a Built-in Raft replication mentions MongoDB as:

>> "God Send". Everything just worked. Replication was as reliable as one could imagine. It outlives several hardware incidents without manual intervention. It allowed cluster maintenance (software and hardware upgrades) without application downtime. I really dream PostgreSQL will be as reliable as MongoDB without need of external services.

https://www.postgresql.org/message-id/0e01fb4d-f8ea-4ca9-8c9...

Be sure to read the Муths and Truths about Synchronous Replication in PostgreSQL (by the author of Patroni) before considering those solutions as cloud-native high availability: https://www.postgresql.eu/events/pgconfde2025/sessions/sessi...

CloudNativePG is automation around PostgreSQL, not "batteries included", and not the idea of Kubernetes where pods can die or spawn without impacting the availability. Unfortunately, naming it Cloud Native doesn't transform a monolithic database to an elastic cluster

It's largely cultural. In the SQL world, people are used to accepting the absence of real HA (resilience to failure, where transactions continue without interruption) and instead rely on fast DR (stop the service, recover, check for data loss, start the service). In practice, this means that all connections are rolled back, clients must reconnect to a replica known to be in synchronous commit, and everything restarts with a cold cache.

Yet they still call it HA because there's nothing else. Even a planned shutdown of the primary to patch the OS results in downtime, as all connections are terminated. The situation is even worse for major database upgrades: stop the application, upgrade the database, deploy a new release of the app because some features are not compatible between versions, test, re-analyze the tables, reopen the database, and only then can users resume work.

Everything in SQL/RDBMS was thought for a single-node instance, not including replicas. It's not HA because there can be only one read-write instance at a time. They even claim to be more ACID than MongoDB, but the ACID properties are guaranteed only on a single node.

One exception is Oracle RAC, but PostgreSQL has nothing like that. Some forks, like YugabyteDB, provide real HA with most PostgreSQL features.

About the hype: many applications that run on PostgreSQL accept hours of downtime, planned or unplanned. Those who run larger, more critical applications on PostgreSQL are big companies with many expert DBAs who can handle the complexity of database automation. And use logical replication for upgrades. But no solution offers both low operational complexity and high availability that can be comparable to MongoDB

What about "cannot scale without downtime"? While all databases can scale vertically, increasing or decreasing CPU or memory resources requires a restart, which leads to downtime. All sessions must disconnect, the system restarts, and users reconnect and execute their first queries with a cold cache. This is far from ideal, especially when workload spikes—the very situation where scaling up is needed. Cloud-native databases that promote scalability typically scale horizontally without downtime. This involves adding or removing nodes and automatically resharding data without stopping the application. Such elasticity is key to the cost efficiency in the cloud: it lets you scale resources up or down with the workload, avoiding excess provisioning.

What is the null join behavior that cause you problem?

The companies that attempt to replace PostgreSQL do so not to replace PostgreSQL itself, but to replace Oracle.

Can you provide more details? Inserting with unique indexes do not lock the table. Case statements are ok in where clause, use expression indexes to index it

A Data Council talk on the modern SQL vs NoSQL

The Write-Ahead Logging (WAL) is single-threaded and maintains consistency at a specific point in time in each instance. However, there can be anomalies between two instances. This behavior is expected because the RDS Multi-AZ cluster does not wait for changes to be applied in the shared buffers. It only waits for the WAL to sync. This is similar to the behavior of PostgreSQL when synchronous_commit is set to on. Nothing unexpected.

Do you declare your Foreign Key in your SQL database? What about MongoDB?

Are NULLs treated as distinct or duplicates in your UNIQUE INDEX? It depends SQL != NoSQL != some specific implementations Which option offers the best developer experience? Comments welcome

YugabyteDB supports much more than basic things. I've been a 3+ years dev advocate for Yugabyte, and I've always seen triggers. LISTEN/NOTIFY is not yet there (it is an anti-pattern for horizontal scalability, but we will add it as some frameworks use it). Not yet 100% compatible, but there's no Distributed SQL with more PG compatibility. Many (Spanner, CRDB, DSQL) are only wire protocol + dialect. YugabyteDB runs Postgres code and provides the same behavior (locks, isolation levels, datatype arithmetic...)

Index creation should not be controlled by statement timeout, but backfill_index_client_rpc_timeout_ms which defaults to 24 hours. May have been lower in old versions

It depends on the use cases and performance goals. You may want to distribute the rows that you insert, and then a random UUID makes sense. However, it is too much distributed for B-Tree indexes and the problem is not only cache but the amount of modifications due to leaf block splits. This includes MySQL which stores the primary key in a B-Tree index. Other use cases may benefit from colocating the rows that are inserted together. Think of timeseries, or simply an order entry where you query the recent orders. A sequence makes sense there, to have a good correlation between the index (on time) and the primary key. This avoids too many random reads with low cache hits.

It is wrong to think that distributed databases do not need sequences. YugabyteDB allows it. With YugabyteDB you use hash sharding to distribute them to a small number of hash ranges, so that they don0t go all at the same place, but are not scattered across the whole database. CockroachDB and Spanner doesn't have hash sharding and that's why they do not recommend sequences. There are also use cases where range sharding on the sequence is good when you don't need to distribute the data ingest, but benefit from their colocation when querying.

1. Regarding performance, I recently did a simple test. CockroachDB uses a considerable number of CPU instructions compared to YugabyteDB: https://dev.to/yugabyte/comparing-sql-engines-by-cpu-instruc...

Writing a database from scratch is not easy. YugabyteDB uses some PostgreSQL, Kudu, and RocksDB code that has been heavily optimized before. Those are good codebases, and only some parts need to be enhanced to make them distributed.

2. Their Go version of RocksDB, Peeble, seems less efficient. They did it for a good reason. They didn't have the C++ skills to enhance RocksDB itself.

3. The repo holds more than the database.

C: is the SQL layer, based on PostgreSQL

C++: the transactional distributed storage, heavily modified Kudu and RocksDB

Java: some regression tests, the managed service automation, sample applications

TS: the Graphical User Interface

Python: some tooling to build the releases, some support tools

The database itself is C and C++

YugabyteDB is and will always be Apache2. It is PostgreSQL compatible (the query layer is a fork of PostgreSQL) so the migration from CockroachDB, which implements a subset of PostgreSQL features, is easy.

What I do not understand is they say "we explored CockroachDB, TiDB, Spanner, and Vitess". Those are not compatible with PostgreSQL beyond the protocol and migration would require massive rewrites and tests to get the same behavior. YugabyteDB is using PostgreSQL for the SQL processing, to provide same features and behavior and distributes with a Spanner-like architecture. I'm not saying that there's no risk and no efforts, but they are limited. And testing is easy as you don't have to change the application code. I don't understand why they didn't spend a few days on a proof of concept with YugabyteDB and explored only the solutions where application cannot work as-is.

Maybe it is not the planner. On difference between those other databases and PostgreSQL is that their plan do not depend on how freshly the table was vacuumed. The cost of your "correct index" may becomes worse when the rows are updated until they are vacuumed.

In all databases you will avoid bad plans (and the unpredictable performance related to plan changes) by providing the right index. You have two selective filters: WHERE and LIMIT so the right index have both

If changing random_page_cost from 4 to 2 makes a difference, then probably there are no good indexes. The choice between Seq Scan and Index Scan should be obvious without depending on small adjustments or one day, with slightly different data distribution the plan will flip to a bad one

Even if bugs are fixed instantly, nobody will apply a patch in production withiut previous testing. Changing system-wide behavior to fix a single query may make things worse. Hints are the only way to fix at the scope of one statement with the guarantee that it doesn't break others

All those methods are try and guess. With hints you can have a scientific approach to understand why the bad plan has been chosen and find the right plan. Then, you can address the root cause. join_collapse_limit=1 may set the join order but not the join direction, so that's not enough if cardinality is misestimated. And pg_hint_plan can set this parameter for one statement if that's what you want, better than setting for the transaction

(YugabyteDB Developer Advocate here)

@flagged24 If you can send me more info about your migration problems, I would love to look at it (fpachot@yugabyte.com). The postgres-compatibility, performance, and YB Voyager are improving from feedback.

The default parameters may not be the best to try an existing app. Here is a docker image I've made with the best defaults for a quick start: https://github.com/FranckPachot/yb-pglike to check the compatibility, and then look at more tuning.

@gunapologist99 I'm not a big fan of benchmarks, especially on products with fast evolution. The best is to test with something that is similar to your app and open an issue (github, forum, slack) if it is slow to be sure it's not a configuration issue, or bug recently fixed.

Franck

You have a misconception about CockroachDB and YugabyteDB architecture. It is not 2-way replication. It writes to a Raft group. The transaction can be picked up by any node because the transaction intents are also replicated. The main difference with RAC is that the current version of a row is read/write on the Raft Leader, replicated to followers (that can be elected new leader). In RAC, the block with current version of a row is moved back and forth between the instance. That's possible only with reliable, short distance, dedicated network. Distributed SQL can scale out to multiple availability zones. Not RAC

Referring to Distributed SQL as a key-value store is like defining monolithic databases as a block storage. It reduces it to an internal structure. What makes it a database is what is on top: ACID transaction, full SQL features, relational tables, JSON document, foreign keys,... which are not available when sharding is done on top of SQL

Sharding above is not transparent (the application has to do it) Sharding below is what Distributed SQL does (what they call distributed key-value storage, ignoring that the SQL transactions are also distributed)