Postgres scaling advice • Hacker News Board

Re: Postgres scaling advice

#181

Post by simcop2387 » Wed, Jan 27, 2021, 3:31 AM UTC

jeffbee wrote: post #152

btilly wrote: post #148

Earlier quoted context omitted.

I've seen https://www.postgresql.org/docs/12/sql-createindex.html#SQL-... work well in practice on busy transactional databases. I'd be interested in knowing about cases where it doesn't work well. My experience on systems without indexes differs strongly from yours. Yes, they can work well. But if you have multiple use cases for how your data is being queried, they push you into keeping multiple copies of your data.…

Just from personal experience, if they can build gmail on top of a database (bigtable) having neither indexes nor consistency, then probably it will also be suitable for the purposes of my far smaller, much less demanding products. On the other hand I've seen, and am currently suffering through, products that have desperate performance problems with trivial amounts (tens of GB) of data in relational databases with in…

That's not exactly true, bigtable still has some indexes. You're just restricted to one per table and only on the row key. Instead you spend time finding the right row keys for each value that you're going to want to look up. Picking bad keys will still lead you to have terrible performance, since you'll be looking up the wrong thing to get what you actually want.

Re: Postgres scaling advice

#182

Post by Shorn » Wed, Jan 27, 2021, 4:52 AM UTC

mmacedo_en wrote: post #94

ahachete wrote: post #29

Earlier quoted context omitted.

As this is a public reference: GitLab's Postgres cluster handles peaks of 300K tps, where the master node alone supports around 60K-80K. And this is not in-memory (datasize in the order of 8TB, RAM 600GB). https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ And there's still room for vertical scaling. Disclaimer: we provide Postgres support for GitLab.

HN is missing an user follow and user tagging button :-)

I wish HN published the user comments/subscriptions pages as RSS.

For example, I "follow" a few users on StackOverflow by adding the feed that SO makes available linked from their profile. Super useful.

Re: Postgres scaling advice

#183

Post by smolder » Wed, Jan 27, 2021, 5:38 AM UTC

> A single node instance is literally bullet-proof

That is exactly the wrong way to use that word.

Re: Postgres scaling advice

#184

Post by kikimora » Wed, Jan 27, 2021, 7:09 AM UTC

srcreigh wrote: post #143

jandrewrogers wrote: post #99

Earlier quoted context omitted.

It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it ou…

Naive question. Wouldn't sampling a power law dataset be straightforward? The idea is there's only a few outlier values, and the rest are uncommon. This distribution seems extremely common. Ie column with mostly NULL values and the rest somewhat unique non null strings. I'm curious what data you saw and why the sampling didn't work?

I think the problem is PG does not know underlying data distribution and use one sampling strategy which works for normally distributed data.

Re: Postgres scaling advice

#185

Post by kitd » Wed, Jan 27, 2021, 7:28 AM UTC

mvanbaak wrote: post #66

kitd wrote: post #33

Earlier quoted context omitted.

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

> There is benefit in having established platforms for running your code You do know k8s is very new, there's a constant stream of changes and updates to it, etc etc? it's not established. It's known, but that's it.

Well, firstly, the core components of k8s are pretty well understood now, and the APIs to them are not just going to change overnight.

Secondly, it doesn't really matter how old the platform is. As I said, it comes down to how familiar the operations team are with it. If you are running an on-premise or hybrid cloud, ops will want something familiar over all environments. They're not going to be happy with k8s on one, ansible on another, etc.

Re: Postgres scaling advice

#186

Post by pas » Wed, Jan 27, 2021, 9:57 AM UTC

majewsky wrote: post #111

pas wrote: post #96

Earlier quoted context omitted.

1.0 was released in 2015. There are stable LTS vendors for it. It's pretty established. And much saner than cobbling together Ansible/Puppet/Chef playbooks for everything.

Saying that 2021's Kubernetes is established because 1.0 was released in 2015 is like saying that 1991's Linux is stable because Unix had existed for 20 years at that point. Kubernetes 1.0 and 1.20 share the same name, design principles and a certain amount of API compatibility, but it's impossible to take a nontrivial application running on 1.20 and just `kubectl apply` it on 1.0. Too much has changed. Kubernetes is…

Of course 1.20 has a myriad of additional featues. But 1.0 concepts are in 1.20, the fundamentals are stable. Schedule and run containers, expose them to the external network via a load balancer (or node port).

The declarative aspect is stable. Yes, many people are writing insane go programs to emit templated ksonnet or whatever that itself has a lot of bash embedded, but that's the equivalent of putting too much bash into the aforementioned configuration/orchestration playbooks.

Re: Postgres scaling advice

#187

Post by mmacedo_en » Wed, Jan 27, 2021, 1:08 PM UTC

CharlesW wrote: post #90

mmacedo_en wrote: post #88

Earlier quoted context omitted.

I've seen queries running for 1 minute 2 minutes raising user complaints. Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. Before thinking about distributed systems, there is an entire database optimization toolkit to make use of: primary key review, secondary index creation, profiling, view or stored procedure creation, temporary tables, memory tables a…

> Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. This is exactly what the parent comment said: "you should try to build your application so queries and transactions are very short". If you're claiming that the parent is incorrect about "sometimes, the only answer is to distribute the queries across other boxes", my guess is that probably don't work at…

I'm referring to parent's point "Sometimes you just have to do joins across large tables.". Indexes and query hints can make a world of difference once you profile how the query is being executed.

Another option is to create a stored procedure which first extracts a smaller amount of data from first table to a temporary table and then join it to the other large table.

Measuring and testing different optimization methods takes a lot of effort and time. We should only do that when faced with a real problem of slowness.

"The first rule of optimization is: Don't do it. The second rule of optimization (for experts only) is: Don't do it yet. Measure twice, optimize once."

Re: Postgres scaling advice

#188

Post by mmacedo_en » Wed, Jan 27, 2021, 1:13 PM UTC

ahachete wrote: post #149

mmacedo_en wrote: post #94

Earlier quoted context omitted.

HN is missing an user follow and user tagging button :-)

If that's for me, I really appreciate that :) There's obviously that option on Twitter (same username as here), if you want to follow there ;)

Did it already :-) Good idea putting twitter link on your profile

Re: Postgres scaling advice

#189

Post by nwsm » Wed, Jan 27, 2021, 3:39 PM UTC

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…

The author says this in the first paragraph.

Re: Postgres scaling advice

#190

Post by scndalousarbite » Wed, Jan 27, 2021, 4:32 PM UTC

jayd16 wrote: post #98

ccmcarey wrote: post #34

Earlier quoted context omitted.

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…

Agreed. What is it that people are doing (or not doing) where a simple managed k8s cluster is more work than the minimum way to do this? Are teams not even setting up automated builds and just sshing into a box?

k8s is not simple to learn for legacy admin teams, even those who have some container experience. It is simple to kubespray a semi-working cluster on-prem or cloud provider k8s your way along but if you need to actually learn how to deal with multiple ingress controllers, a service mesh, need multiple storage providers, affinity, gpu accelerated apps, secure k8s and the other problems solved in the legacy world (in myriad ways) k8s can be regarded as a disruptive interruption in your ability to operate. All this being said I lead both IOT edge(via k3s) and science (cloud & air gapped 'vanilla' k8s) platform teams and appreciate the chance to sell k8s and make easy money.