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

51–60 of 86 posts

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

#51

Post by emptysea »

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?

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

#52

Post by gen220 »

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…

Doesn't Spanner depend on access to globally-synchronized custom atomic clock hardware? i.e. even if it were open source, it wouldn't really be valuable unless you're a data center operator.

As I understand it, it's not a solved problem because there is no silver bullet, but rather trade-offs in every direction, and which solution works for you (including Spanner) is heavily dependent on your use case.

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

#53

Post by sunderous »

iotapi322 wrote: post #29

I just implemented master with read replica with the bitnami postgres-repmgr image. It's not perfect, but it works and running my own instances in aws instead of rds is going to save me close to 80% when i also add in purchasing a savings plan. By setting this up I've learned more about postgres than i ever ever wanted too. lol

Just a heads up, we were using the bitnami charts in our first attempt at an HA postgres instance in our k8s cluster, and we're currently figuring out how to move away from them because they had so many issues.

Granted, we might've messed something up and there are lots of factors, but you should manually check your individual nodes every so often to make sure none of them are going out of sync. Need to connect to each one directly, can't rely on what the bouncer/pgpool/loadbalancer is showing you to check the data is the same. It happened repeatedly to us, and wasn't obvious from any of our monitoring. In the end we had to scale down to 1 node while we sort out moving to a different operator.

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

#54

Post by JoshGlazebrook »

Somewhat related, but I'm looking at hosting solutions for a hobby SaaS I'm building, and I would like to use google cloud run, but the cloud sql pricing seems extremely expensive if you want HA and are just starting up. They also don't seem to offer any cloud sql as part of their free like like AWS does with RDS, it just seems very odd.

I looked at Planetscale thinking I might be willing to just use MySQL, but even if I were to take a risk with their confusing pricing it turns out there are no foreign keys.

And there is no equivalent of Cloud run on AWS.

It just seems like there is a gap here in when it comes to managed databases, not just GCP laking any kind of "free tier" option, but just a lack of players in the space between free and established business pricing for HA.

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

#55

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…

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;

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

#56

Post by samlambert »

JoshGlazebrook wrote: post #54

Somewhat related, but I'm looking at hosting solutions for a hobby SaaS I'm building, and I would like to use google cloud run, but the cloud sql pricing seems extremely expensive if you want HA and are just starting up. They also don't seem to offer any cloud sql as part of their free like like AWS does with RDS, it just seems very odd. I looked at Planetscale thinking I might be willing to just use MySQL, but even…

We have FKs and new pricing coming soon.

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

#57

Post by endisneigh »

JoshGlazebrook wrote: post #54

Somewhat related, but I'm looking at hosting solutions for a hobby SaaS I'm building, and I would like to use google cloud run, but the cloud sql pricing seems extremely expensive if you want HA and are just starting up. They also don't seem to offer any cloud sql as part of their free like like AWS does with RDS, it just seems very odd. I looked at Planetscale thinking I might be willing to just use MySQL, but even…

> It just seems like there is a gap here in when it comes to managed databases, not just GCP laking any kind of "free tier" option, but just a lack of players in the space between free and established business pricing for HA.

Use serverless cockroachdb. Yugabyte also has a free tier. As does Fauna and Planetscale.

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

#58

Post by znpy »

I_am_tiberius wrote: post #25
benglish11 wrote: post #23

Earlier quoted context omitted.

Citus is owned by Microsoft as well. https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquir...

Yes, the company, but the plugin is some sort of open source.

Open source stuff are still subject to ownership via copyright.

The license allows you to do many things, but not everything.

The copyright owner on the other had, can do everything.

That’s why many projects nowadays require you to assign copyright to the team before accepting a contribution.

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

#59

Post by endisneigh »

franckpachot wrote: post #47
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…

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?

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

#60

Post by endisneigh »

gen220 wrote: post #52
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…

Doesn't Spanner depend on access to globally-synchronized custom atomic clock hardware? i.e. even if it were open source, it wouldn't really be valuable unless you're a data center operator. As I understand it, it's not a solved problem because there is no silver bullet, but rather trade-offs in every direction, and which solution works for you (including Spanner) is heavily dependent on your use case.

the clocks help but are not strictly required. FoundationDB also achieves external consistency without the clocks, but has limitations, such as a 5 second window for a transaction, due to optimistic concurrency.

the clocks remove such limitations.

Post reply on HN