Postgres scaling advice
cybertec-postgresql.com
Postgres scaling advice
1–10 of 207 posts
Re: Postgres scaling advice
#2When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?
Re: Postgres scaling advice
#3One point I found very interesting was the following paragraph:
> For example, on my (pretty average) workstation, I can do ca. 25k simple read transactions per 1 CPU core on an “in memory” pgbench dataset…with the default config for Postgres v13!
In my own very unscientific experiments I never got values as high as that, but in the area of around 4k transactions per second total on multiple cores. Of course I'm comparing very different things, I was looking at more than just the database and there are lots of other aspects I probably did in a different way.
I find this interesting as it probably means that my bottleneck wasn't entirely where I thought it was. I have to play around with that some time again.
If I wanted to find out the baseline Postgres performance on my hardware for trivial read queries like looking up individual rows by primary key with a database that fits entirely in RAM, how would I do that? I know there is pgbench, but that performs more complex queries as far as I understand.
Re: Postgres scaling advice
#4This was an interesting read for a database novice. It seems like a lot of the quoted stats are about in memory datasets - is that realistic?
Re: Postgres scaling advice
#5One point I found very interesting was the following paragraph: > For example, on my (pretty average) workstation, I can do ca. 25k simple read transactions per 1 CPU core on an “in memory” pgbench dataset…with the default config for Postgres v13! In my own very unscientific experiments I never got values as high as that, but in the area of around 4k transactions per second total on multiple cores. Of course I'm comp…
You can give pgbench files to have it run the query you want (see -f flag)
Re: Postgres scaling advice
#6When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?
pg_auto_failover is good https://www.citusdata.com/blog/2019/05/30/introducing-pg-aut...
Disclosure: I work for Citus
Re: Postgres scaling advice
#7When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?
In 2016 I was expecting some sane defaults like MySQL would have arrived by 2018 or 2019.
Looks like 2021 isn't that much different to 2016. There are work being done, but doesn't seems to be anywhere close to the level of MySQL.
Re: Postgres scaling advice
#8I wonder when using a distributed database (like CockroachDB) will be the default for new applications. Right now it seems that they are less feature and harder to set up than traditional RDBMSes but I can only assume that this gap will narrow and at some point in the future things will be "scalable by default". (Of course no DB is going to prevent all ways to shoot yourself in the foot)
Re: Postgres scaling advice
#9When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?
Use built-in physical replication, it works and it's good.
Patroni is widely used for automatic failover, at least where you don't want a possibility of a split brain.
Re: Postgres scaling advice
#10I wonder when using a distributed database (like CockroachDB) will be the default for new applications. Right now it seems that they are less feature and harder to set up than traditional RDBMSes but I can only assume that this gap will narrow and at some point in the future things will be "scalable by default". (Of course no DB is going to prevent all ways to shoot yourself in the foot)
This question is similar to asking on a car forum when using a 40 foot lorry will be the default starter car for everyone. The answer is "probably never" because while it does offer superior cargo transport scalability, the tradeoffs are not worth it for the vast majority of users. The question is posed like distributed databases have no disadvantages over non-distributed databases, but that is simply not the case. Clustering introduces all sort of problems, from network splits to slow(er) joins across nodes.