magden

I’ve always thought that "Just Use Postgres" would make an excellent title and topic for a book. And we’ve partnered with Manning to bring it to life.

Looking forward to your feedback on the TOC and chapters that have already been released. The book is current in the Manning Early Access Program (MEAP), which lets read it while I continue to write the chapters.

Cool, works with my YugabyteDB deployment using the provided scripts for Postgres.

1. Nope, I don't think that CRDB is generally faster than Yugabyte. Vendors can't say otherwise in their own benchmarks :)

3. That code must be the tooling around the db and various smart drivers (that extend default drivers of Postgres for various programming languages). Overall, the core database engine of Yugabyte is written in C.

In light of the recent license changes by CockroachDB, their competitor (YugabyteDB) took another approach that seems to work well.

Here is a detailed article covering various design patterns for multi-region deployments: https://www.yugabyte.com/blog/multi-region-database-deployme...

(I'm the author of the article)

There are several design patterns depending on whether you need to optimize the latency for reads, writes, or both.

If it's all abour reads, then you can use follower reads (as shown in the article) or read replicas. If you also need writes, then a geo-partitioned deployment or standalone database instances for each region would be good starting points.

CloudnativePg scales vertically. Distributed Postgres assumes you can data and read-write workload both horizontally and vertically.

It was great to see the article about distributed Postgres by the Crunchy Data team trending on HN. Judging by the ongoing discussion, many got interested in the whys and hows of distributed Postgres.

I published the following guide to distributed Postgres at the end of 2023. It explains three solutions from the Postgres ecosystem that turn Postgres into a distributed database.

Should be useful for many. Curious to hear your feedback.

> You handwaved this away, saying you can just store an entire table on a single node, but that defeats many of the benefits of these sharded SQL databases.

I just clarified one-liners listed under the closing "Cons" section. My intention was not to say that the author is utterly wrong. Marco is a recognized expert in the Postgres community. It only feels like he was too opinionated about distributed SQL DBs while wearing his Citus hat.

> Also, before attacking the author's biases, it seems fair to disclose that you appear to work at Yugabyte.

I'm sorry if I sounded biased in my response. I'm with the YugabyteDB team right now, but that's not my first and I bet not the last database company. Thus, when I respond on my personal accounts, I try to be as objective as possible and don't bother mentioning my current employment.

Anyway, I'm very positive to see that this article got traction on HN. As a Postgres community member, I truly love what's happening with the database and its ecosystem. The healthy competition within the Postgres ecosystem is a big driver for the database growth that's becoming the Linux of databases.

There are good and solid HA options in the Postgres ecosystem that are fully open. You can use Patroni for standard Postgres or YugabyteDB that is fault-tolerant by design.

Specifics are here: https://www.yugabyte.com/postgresql/postgresql-high-availabi...

You can achieve RPO=0 with Postgres using synchronous logical replication. You would need to replicate to 2+ standbys because if there is only one standby and it goes down then the primary will stuck. During the failover you would need to have Patroni or comparable tool, but I don’t know what’s the RTO.

But once you outgrow the primary/standbys severs storage or compute capacity you would need to scale to larger machines that can incur downtimes. With distributed Postgres such as YugabyteDB this is not gonna happen because you can scale horizontally

Scalability is not the only reason for jumping on a distributed Postgres version.

Some apps might do just 1000 ops/second but still run on a distributed database for high availability or data locality reasons. For instance, shared-nothing databases usually guarantee RPO=0 (no data loss, recovery point objective) with RTO (recovery time objective) measured in seconds for zone and region-level outages. As for data locality, think automatic data placement/pinning to regions/data centers for data regulatory and low latency reasons (serve read/write requests equally fast for folks living in NYC, London, Tokyo).

First time seeing someone call Spanner, CockroachDB, and YugabyteDB a "distributed key-value store with SQL" :)

The cons of the mentioned distributed shared-nothing SQL databases are questionable:

- "Key-value store" is in fact an LSM-tree-based document store that supports column-level versioning (Postgres supports row-level versioning only).

- "Many internal operations incur high latency." - I guess this conclusion is based on the referenced Gigaom benchmark that was paid for by Microsoft to compare apples to oranges.

- "No local joins in current implementations." (YugabyteDB certainly has colocated tables that store a whole table on a single node. CockroachDB and Spanner might do this as well.)

- "Not actually PostgreSQL..." - There is only one 100% compatible database with Postgres...It's Postgres itself. Citus, CockroachDB, Aurora, Alloy, YugabyteDB, and others can be classified as "not actually Postgres."

- "And less mature and optimized." - Well, both CockroachDB and YugabyteDB are on Gartner's Magic Quadrant of the top 20 cloud databases. Toys don't get there.

It feels like the author joined Crunchy to work on their own distributed version of Postgres. Good move for Crunchy, good luck!

I don't think we need specialized databases for vectors. Relational databases can easily be expanded by vector data types and operations. They will eventually catch up by supporting what was once a unique feature of the new system: https://medium.com/@magda7817/two-things-to-keep-in-mind-bef...

Nice, thanks for catching the issue! https://github.com/dmagda/pg-compute-node/issues/5

This extension is primaraly for those who avoid using database functions at all because of not-the-best dev experience. Once your plv8 function is ready & tested, then it's totally fine to create it once and execute using a DB driver. However, while in development you might need to change the function implementation several times (or you might need to update it after going to prod) and, personally, it's easier to do this from within your IDE.

Anyway, those who prefer create functions manually can take advantage of the MANUAL deployment mode: https://github.com/dmagda/pg-compute-node/blob/main/compute/...

Thank you for feedback. Sharing a bit more details to make things clearer.

Upon a function invocation, PgCompute does the following:

1. Constructs a function signature (name + arguments) and checks if the function was invoked before. (there is a deploymentTable object that tracks functions that were already sent and executed on the database: https://github.com/dmagda/pg-compute-node/blob/main/compute/...)

2. If the function already exists on the database and its implementation is NOT changed, then PgCompute just sends the function signature to the database for the execution using the `select + schema + "." + funcName + "(" + argsStr + ") statement`.

3. If the function has never been executed or its implementation is changed, then the function is first deployed/created on the database end and then executed. JS allows you to capture the function body as text and send it over whenever you like. PgCompute uses the `create or replace function " + this.#schema + "." + funcName + "()...` statemet to deploy the function.

The transitively invoked functions should work if you execute functionA first via PgCompute and then execute functionB that calls functionA. But I still need to check this.

As for the scope of supported JS APIs, presently the functions have to use the APIs that are allowed/supported by plv8.

PgCompute uses the plv8 extension on the database end. Basically, PgCompute will be transferring the function logic to the database where plv8 will get it executed. This function transfer happens only once and then PgCompute just passes the method signature for the execution.

I think that the perf impact should be negligable but you're right that the next reasonable step is to do some perf testing.

Nope, nobody uses it in prod yet. I've just released the first version.

Yeap, that was the main reason for building this client-side extension/module - so that we, developers, can execute the functions from the application source and our preferred IDEs.

Hey folks, I'm the one who created this extension. Looking forward to your feedback, thoughts and suggestions.

I bet that the `watch` feature would make to the list if the article was written these days.

`watch` is the coolest Postgres feature that I've discovered recently.

It should be helpful for maintenance and DDL tasks. Such as I need to build a secondary index over a large table and do something once the index is ready. Start the index creation with pg_later, check the status later and do what you need to do next. Probably, they can support triggers so that once a task is completed I can be notified.

Another reason why sharding is not always the best for use cases when you need to re-shard frequently. They should take a look at distributed Postgres dbs like Yugabyte.

When discussing GCP, the latency between AZs within the same region is approximately 5 ms. Thus, if you have a 3-node database cluster spanning 3 AZs (all within the same region), a transaction can be committed in the 5-10ms range using Raft. CockroachDB should handle this seamlessly.

However, if you're considering a multi-region setup, the latency will depend on the distance between the regions.That's why usually you define a preferred region (that stores primary copy of the records) or deploy in a geo-partitioned mode (when data is automatically pinned to configured regions).

I can only concur that multi-region apps are becoming the new normal.

Deploying app instances across distant locations was never an issue. However, databases used to be the bottleneck. I'm glad to see that changing, thanks to CockroachDB and YugabyteDB.

My favorite multi-region deployment mode is geo-partitioned deployment. This is when a database automatically pins user data to specific locations, ensuring low latency for both reads and writes, regardless of user location. One-minute demo how it works: https://www.youtube.com/watch?v=9ESTXEa9QZY&list=PL8Z3vt4qJT...

This is the right move for Scylla. Overall, looks similar to YugabyteDB that distirbutes data by sharding tables into tablets as well. The cluster monitors the cluster size (number of nodes) and the size of each tablet (data volume), and adds new tablets or re-shards large ones automatically:https://docs.yugabyte.com/preview/architecture/docdb-shardin...