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

81–86 of 86 posts

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

#81

Post by hot_gril »

franckpachot wrote: post #79
hot_gril wrote: post #48

Earlier quoted context omitted.

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

Yeah, I rarely use composite indexes in Postgres. Only if I want to squeeze a little more performance out of a frequent combo query.

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

#82

Post by 5Qn8mNbc2FNCiVV »

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…

If it's a hobby SaaS you are best of just using docker-compose on some VM and pgBackRest. If you have multiple hobby projects, use different databases in the same instance. You can always pull out a single one when issues arise.

Make your life easy, tons of applications are running for years even without (good) backups

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

#83

Post by merb »

urza wrote: post #78
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?

manual intervention is not required. Patroni will just use a Standby as the new master. If the old master will be alive again it will be started as a backup. Of course there might be dataloss if it was used with async replication.

Patroni has great docs for this here: https://patroni.readthedocs.io/en/latest/replication_modes.h... (Actually this is more or less a thing to do with Postgres)

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

#84

Post by hot_gril »

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.

Some general rules make sense for common use cases. Like, I'll say that a relational DBMS is generally what you want rather than a graph one. For a single-node DB, I'll always use Postgres if I have the choice. For a webserver, can't really go wrong with NodeJS, though you might have reasons to use something else (e.g. cost to run).

But sharding is too use-case specific to prescribe something broadly like Spanner.

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

#85

Post by [deleted] »

bob1029 wrote: post #71
roncesvalles wrote: post #42

Earlier quoted context omitted.

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 li…

[deleted]

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

#86

Post by roncesvalles »

bob1029 wrote: post #71
roncesvalles wrote: post #42

Earlier quoted context omitted.

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 li…

Spanner offers both linearizability and serializability. They use the non-standard term "external consistency" to describe the union of those two properties.

>Doesn't spanner introduce new (very unlikely) failure modes that other databases are not impacted by?

Curious if you could go into that.

Post reply on HN