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

Postgres scaling advice

cybertec-postgresql.com

Post reply on HN
Print view View original

31–40 of 207 posts

Re: Postgres scaling advice

#31

Post by mumblemumble »

jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

It's resumé-driven development, and it's also entertainment-driven development. Bringing in new technologies gives you a chance to play with a new toy. That's an effective way to make your job more interesting when the thing you're supposed to be working on is boring. Which, in business applications, is more often than not the case.

Re: Postgres scaling advice

#32

Post by avereveard »

jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

Kubernetes isn't only about scaling. The repeatability of deployment process is a great asset to have as well.

Re: Postgres scaling advice

#33

Post by kitd »

jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

I see people talking about using Kubernetes for internal applications and I just can't figure out why.

There is benefit in having established platforms for running your code, and this is especially true for large orgs where the people who run the systems are an entirely different group from those that developed or assembled it. And people (+ their skills) are what cost the most money in any business.

It's true that many/most systems don't require a full Kubernetes stack (for instance), but if a critical mass of the business IT is going that way, doing the same with your own makes sense from an economies of scale PoV.

Re: Postgres scaling advice

#34

Post by ccmcarey »

jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

I think it's easier to run a small k8s cluster than it is to attempt to recreate a lot of the functionality provided manually, especially if you're running in a cloud where the control plane is handled for you.

It provides unified secrets management, automatic service discovery and traffic routing, controllable deployments, resource quotas, incredibly easy monitoring (with something like a prometheus operator).

Being able to have your entire prod environment defined in declarative yml is just so much better than running something like a mishmash of ansible playbooks.

If your application runs on a single host and you don't really care about SLAs or zero downtime deploys, sure, use some adhoc deploy scripts or docker compose. Any more than that, and I think k8s pays for itself.

Re: Postgres scaling advice

#35

Post by Silhouette »

jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

Very many successful applications can indeed run on a single DB server (modulo redundancy in case of failures). Vertical scaling isn't trendy, but it is effective, until it's not.

I have yet to encounter a real situation where it suddenly became impossible to run a production DB on a single, high-spec server, without knowing far enough in advance to plan a careful migration to a horizontally scaled system if and when it was necessary.

Re: Postgres scaling advice

#36

Post by WJW »

kevincox wrote: post #19
WJW wrote: post #10

Earlier quoted context omitted.

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

I don't think it is quite the same. - Switching from a car to a van to a lorry is fairly low cost. You don't need to recreate your product (probably). - You don't need to run distributed databases in a cluster to start. But I think most importantly the decrease in dev speed and performance is an investment in future scalability. And I only imagine that this different will shrink over time to where for example a 1 nod…

Making investments in future scalability at the cost of dev speed and performance is exactly the wrong tradeoff given that 90% of startups fail. At the start, when you have very little income, you want to allow for as much speed and flexibility as possible so that you can get to product/market fit ASAP. By the time your company gets big enough that plain MySQL/Postgres can't handle the load any longer, you will have more than enough money to afford a few experts that can help you migrate.

Re: Postgres scaling advice

#37

Post by Silhouette »

avereveard wrote: post #32
jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

Kubernetes isn't only about scaling. The repeatability of deployment process is a great asset to have as well.

But there are much simpler ways than K8s to achieve automated/repeatable deployments, if that is your goal.

Re: Postgres scaling advice

#38

Post by kryptiskt »

kevincox wrote: post #8

I 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)

Why would it ever be default? Very few applications need a distributed database. These days you can get a single machine with hundreds of terabytes of storage and terabytes of RAM. Add a spare machine for failover and you have a reliable setup for any but the biggest tasks. And the tasks that that setup isn't sufficient for will certainly demand more thought than a cookie-cutter setup of some random distributed DB.

Re: Postgres scaling advice

#39

Post by jandrewrogers »

kevincox wrote: post #8

I 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)

What we need is a database that can both scale-up and scale-out. Most distributed databases offer poor efficiency and performance on a per node basis, which has a high operational cost. This is why people avoid using distributed databases unless they need it. A scale-up database can serve as much workload as pretty large scale-out database in practice. This discontinuity creates the market for scale-up systems.

There is literally nothing preventing distributed databases from having excellent scale-up performance too. Unfortunately, people who design distributed databases have a strong bias toward unnecessarily throwing hardware at performance and scalability problems. This is partly because very few people know how to design a modern scale-up database; making something (trivially) distributed is easier.

Re: Postgres scaling advice

#40

Post by StreamBright »

>> A single PostgreSQL instance can easily do hundreds of thousands of transactions per second

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! With some tuning (by the way, tuning reads is much harder in Postgres than tuning writes!) I was able to increase it to ~32k TPS per core, meaning: a top-notch, dedicated hardware server can do about 1 million short reads! With reads, you can also usually employ replicas – so multiply that by 10 if needed! You then need to somehow solve the query routing problem, but there are tools for that. In some cases, the new standard LibPQ connection string syntax (target_session_attrs) can be used – with some shuffling. By the way, Postgres doesn’t limit the number of replicas, though I personally have never witnessed more than 10 replicas. With some cascading, I’m sure you could run dozens without bigger issues.

This sort of hand-wavy "benchmark" is not really good for anybody other then the author's satisfaction. Real world scenarios are not like that.

Post reply on HN