Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why PostgreSQL High Availability Matters and How to Achieve It

yugabyte.com

Post reply on HN
Print view View original

71–80 of 86 posts

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#71

Post by bob1029 »

roncesvalles wrote: post #42
endisneigh wrote: post #38

It’s amazing that this isn’t a solved problem, but we have all of this crazy language model stuff. Unfortunately Spanner isn’t open source. Yugabyte and Citus are close but have annoying issues. Cockroach isn’t 100% compatible (and has its own issues) and things like FoundationDB which are truly HA and comparable to Spanner in terms of consistency and fault tolerance are not easily plugged into Postgres as the underl…

Honestly, just use Spanner. It's the perfect database*. If you need to trade freshness for savings, put a cache in front of it. If I were building a new startup in 2023, I would need a mountain of evidence against using Spanner. It's ugly that it locks you into GCP but hey an iPhone locks you into Apple's ecosystem, that's just the price you pay to get good things. * unless you need timeseries, columnar, FTS, geospat…

> perfect

Doesn't spanner introduce new (very unlikely) failure modes that other databases are not impacted by? The reliance on an external consistency model feels to me like a complete outsourcing of liability that warrants thorough investigation.

Hypothetically, if GPS went down for a prolonged period and/or a bug was found in the TrueTime system, what would happen to the consistency model around Spanner?

I feel like some applications and customers would much rather wait for a synchronous acknowledgement from the actual, live system. An extra 150ms when you are confirming a 6-figure wire transfer could easily be framed as a good thing in most circles.

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#72

Post by audioheavy »

tmpz22 wrote: post #67
brigadier132 wrote: post #50

Earlier quoted context omitted.

I hate this tired argument. If I'm building a startup I'm building it with the intention to hit scale. If I can use cockroach or yugabyte and get 90% of the benefits of postgres without significant additional cost I'm going to use it. I've been at multiple startups that have had trouble scaling their database and the cost to switch database technologies when you hit scale is massive. And worst of all, the limitations…

Different teams have different abilities. You can't really make general rules around any of this. Should you use spanner as a default selection for your company's database needs? Maybe.

both good points, including the devs having different abilities and using a ready-made scalable solution. We're living in the world of having something working that you can test in a few days shrug

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#73

Post by Randis »

franckpachot wrote: post #28
I_am_tiberius wrote: post #16

I wished AWS, Google and others would also add a Citus solution. I fear that once migrated to Citus, we face the full price pressure from the Azure monopoly.

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.

CockroachDB is available in the Azure/AWS/GCP marketplaces all 3 as managed service or SH option.

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#74

Post by hot_gril »

emptysea wrote: post #51
hot_gril wrote: post #41

Great summary. Multi-node relational DBs are very much double-edged swords. This article mentions the potential data loss during failover events. I've complained in the past that Heroku Postgres advertises HA like a strict improvement and leaves the actual failover mechanism in the very fine print; the standby master is async, so you can lose data. There's no free lunch here, you either tolerate some data loss or tol…

I’m not really familiar with the trade offs of modes with Postgres but why isn’t Serializable necessary?

The default read-committed is good enough for most use cases, explained in https://www.postgresql.org/docs/current/transaction-iso.html (search "it is just right for simpler cases"). If somehow you've designed a schema that causes race conditions in the default mode, you're probably better off changing your design than you are switching modes.

Serializable mode uses some kind of optimistic concurrency, where your transaction might get halfway through then fail because of a conflict with another one, which also fails. Then you have to do retry + random backoff on the client side. Spanner does something similar. Problem with Postgres serializable mode is it's slow and won't scale well if many readers/writers are touching the same data.

I get it if this answer isn't satisfying. Partial ACID is worrisome, and full ACID is expensive.

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#75

Post by franckpachot »

endisneigh wrote: post #59
franckpachot wrote: post #47

Earlier quoted context omitted.

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?

This is true, but the article is talking about HA and Citus is HA, no?

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

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#76

Post by hot_gril »

hot_gril wrote: post #41

Great summary. Multi-node relational DBs are very much double-edged swords. This article mentions the potential data loss during failover events. I've complained in the past that Heroku Postgres advertises HA like a strict improvement and leaves the actual failover mechanism in the very fine print; the standby master is async, so you can lose data. There's no free lunch here, you either tolerate some data loss or tol…

Forgot to add, any Spanner query that I expect to be reasonably fast for realtime usage has to be very simple. In Postgres I often get away with much more complex ones.

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#77

Post by ddorian43 »

roncesvalles wrote: post #63
endisneigh wrote: post #45

Earlier quoted context omitted.

Spanner is ridiculously expensive. Though I get your point, it’s not like an iPhone. An iPhone is not actually that expensive compared to top end android phones. Spanner is very expensive even compared to hosted cockroach and RDS (Aurora). That being said I’m sure if you're enterprise it's substantially cheaper. Spanner generally is about twice the cost as cockroach which seems cheap but the gap in nominal cost only…

The thing is, there is nothing else like Spanner, unless you want to trust a startup with your database, which opens up Cockroach, Yuga, and TiDB. Databases like RDS don't compare because they don't offer the same guarantees. Nothing even in AWS' and Azure's portfolios is like Spanner. Back of napkin math I did a couple of months ago: Spanner costs about $1/month for 2 writes/second and 10 reads/second. The minimum i…

You forgot storage costs and especially bandwidth costs and lockin costs and probably range-queries costs.

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#78

Post by urza »

merb wrote: post #37

one of the solutions which made it pretty simple for us to run postgresql in a ha environment (mostly in k8s, but works standalone as well) is zalandos patroni: https://github.com/zalando/patroni it's really solid and worked for us for a few years already. (it also comes with a haproxy config to have a single leader connection) or for k8s their operator: https://github.com/zalando/postgres-operator (docker image: htt…

How does patroni can do some self healing after primary db gets down and then up again? Or is manual intervention required? How does it look?

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#79

Post by franckpachot »

hot_gril wrote: post #48
azurelake wrote: post #46

Earlier quoted context omitted.

> You can't combine two indexes to filter on two cols; you need one composite index Can you expand on how you perceive traditional RDMSs to be different for this case?

CREATE TABLE foo (id bigserial, bar int, baz int); CREATE INDEX foobar ON foo(bar); CREATE INDEX foobaz ON foo(baz); SELECT \* FROM foo WHERE bar = 2 AND baz = 4; Postgres (and I think MySQL) will use both indexes in the above query*. Spanner can only use one index, which will be slow if there are many non-matching bar=2 or baz=4 rows. So Spanner needs CREATE INDEX foobarbaz ON foo(bar, baz); Which Postgres could als…

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...

Re: Why PostgreSQL High Availability Matters and How to Achieve It

#80

Post by panda888888 »

endisneigh wrote: post #38

It’s amazing that this isn’t a solved problem, but we have all of this crazy language model stuff. Unfortunately Spanner isn’t open source. Yugabyte and Citus are close but have annoying issues. Cockroach isn’t 100% compatible (and has its own issues) and things like FoundationDB which are truly HA and comparable to Spanner in terms of consistency and fault tolerance are not easily plugged into Postgres as the underl…

Out of curiosity, what's wrong with cockroach?
Post reply on HN