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

111–120 of 207 posts

Re: Postgres scaling advice

#111

Post by majewsky »

pas wrote: post #96
mvanbaak wrote: post #66

Earlier quoted context omitted.

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

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 just now entering the realm of "becoming stable". Maybe in five years or so it'll finally be boring (in the best sense of the word) like Postgres.

Re: Postgres scaling advice

#112

Post by vlovich123 »

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’m not saying in all companies, but as you grow you have lots of different teams with different needs. So then you spin up a tools team to manage the engineering infrastructure since you can’t do it as-hoc anymore (CI, source control, etc). So to make that team more efficient, you let them force one size fits all solutions. While this may feel constraining for a given problem domain, it actually makes engineers more portable between projects within the company which is valuable. Thus having one DB or cloud thing that’s supported for all teams for all applications is valuable even if sometimes it isn’t necessarily the absolute best fit (and the complexity is similarly reduced as good companies will ensure there’s tooling to make those complex things easy to configure in consistent ways). Your tools team and the project team will work together to smooth out any friction points. Why? Because for larger numbers of engineers collaborating this is an efficient organization that takes advantage of specialization. A generalist may know a bit about everything (useful when starting) but a domain expert will be far more equipped to develop solutions (better results when you have the headcount).

Re: Postgres scaling advice

#113

Post by wejick »

codyb wrote: post #83
evanelias wrote: post #74

Earlier quoted context omitted.

Once your data grows very large, a successfully-implemented sharding solution actually improves availability, rather than reducing it. With a huge monolithic database, a failure causes downtime for your entire product/company. Replica cloning and backups are slow. Major version upgrades are stressful because it's all-or-nothing. With a sharded environment, a single shard failure only impacts a portion of your userbas…

Wouldn’t sharding generally result in overlap between the key ranges so that a database shard going down doesn’t have to result in any downtime? Then your issue is replication of writes I suppose. Probably depends on use case what configuration you choose.

Honestly I never found the case when this happens, data always falls into 1 shard according to the key. Then comes the concept of shard replica where the shard can live in several nodes and form a redundancy.

However I'm noy sure how usually it's being setup on Postgres

Re: Postgres scaling advice

#114

Post by brandmeyer »

SPBS wrote: post #91

If you are going to allocate sharded databases per client with identical schemas, might as well give each of them an sqlite database? Since you're massively cutting down the writes if it's one database per customer.

And then just one query comes along where you need to make an update that should be globally visible, and not just visible to the shard.

I can see why you would stick with full Postgres for as long as practicable.

Re: Postgres scaling advice

#115

Post by majewsky »

mmacedo_en wrote: post #68

I'm building an app using Postgres for the first time. Naturally I was a bit worried about performance and scaling if the not launched yet app becomes a major success. I began simulating a heavy use scenario. 100k users creating 10 records daily for three years straight. 100000 x 10 x 365 x 3 ~= 1 billion rows or about 200 GB with a record's length of 200 bytes. This is peanuts for modern databases and hardware. Seem…

You classifying 11 writes per second as "heavy use" reminds me of how people on average completely underestimate how fast computers actually are (when they're not bogged down by crappy programs).

Re: Postgres scaling advice

#116

Post by brasetvik »

jandrewrogers wrote: post #99
laurenz_albe wrote: post #53

Earlier quoted context omitted.

This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number of "most common values" and histogram buckets scales up to 10000, which should be good even for large tables. While I'll readily admit that not all aspects of cross-column dependencies are dealt with, and cross-table distributions are not considered, that has n…

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…

> I know how to read the source. You aren't as familiar with PostgreSQL as you think you are.

Oh, maybe you have read some of Laurenz Albe's many contributions to Postgres, then. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search...

Re: Postgres scaling advice

#117

Post by jeffbee »

mmacedo_en wrote: post #88
ccleve wrote: post #81

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries and transactions are very short. That solves a great many problems. But sometimes you can't. Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. That's a huge burden on…

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…

Indexes are not free, they take up space and they make mutations more costly. Also, building the index may not even be possible while your application is running, because postgresql and other RDBMS have inadequate facilities for throttling index construction such that it doesn't harm the online workload. You might have to build indexes at midnight on Sundays, or even take your whole system offline. It can be a nightmare.

This isn't just a problem for SQL databases. Terrible-but-popular NoSQL systems like MongoDB also rely heavily on indexes while providing zero or few safety features that will prevent the index build from wrecking the online workload.

I personally prefer databases that simply do not have indexes, like bigtable, because they require more forethought from data and application architects, leading to fundamentally better systems.

Re: Postgres scaling advice

#118

Post by corty »

vlovich123 wrote: post #109
corty wrote: post #60

Earlier quoted context omitted.

Having a Dockerfile that copies a few binary blobs into an age-old distro image isn't an improvement, it's a huge liability. And most of that stuff that no one knows anything about anymore is like that. Same as with an old VM or PM. I'd rather have that old crap as a physical machine. Why? Because the hardware lifetime "naturally" limits the lifetime of such applications. If the hardware dies, it forces a decision to…

Set up your docker file to be part of your CI so that your binary blobs are built from source with regularity? That’s typically the solution I’ve seen work well. Manually maintained stuff (especially for stuff that may not be the thing everyone is primarily doing) generally doesn’t scale well without automation (speaking as someone who’s seen organizations grow). This is also true of “getting started” guides. Can’t t…

Yes, of course. That would be ideal. That's what we do for everything we can control.

But as someone in the IT dept., far too often you get some container that either was built by someone who long left the company or an external consultant who got paid to never return. Sourcecode is usually unavailable, and if it is available, will only build on that one laptop that the consultant used. The IT department gets left with the instruction "run it, it was expensive" and "no, you don't get any budget to fix it". That results in the aforementioned containers of doom...

Yes, I'm bitter and cynical. Yes, I'm leaving as soon as I can :)

Re: Postgres scaling advice

#119

Post by giantrobot »

corty wrote: post #60
zdragnar wrote: post #43

Earlier quoted context omitted.

In big enough organizations, it is very easy to lose track of who owns what, especially when it is those little ad-hoc internal tools. Manually managing the infrastructure for them is a recipe for them to become permanently enshrined in the wasteland of "services we think we use, but do not maintain because we don't remember who needed it or put it up or how to configure it". K8s isn't the only answer, but if you are…

Having a Dockerfile that copies a few binary blobs into an age-old distro image isn't an improvement, it's a huge liability. And most of that stuff that no one knows anything about anymore is like that. Same as with an old VM or PM. I'd rather have that old crap as a physical machine. Why? Because the hardware lifetime "naturally" limits the lifetime of such applications. If the hardware dies, it forces a decision to…

In theory that works. In practice it rarely does. Docker et al gained popularity because they made it way more practical for projects to be managed as the world works rather than as it should be, for good or ill. Before Docker it was moving old applications to VMs and before that it was running them in chroot horror shows.

Re: Postgres scaling advice

#120

Post by zinclozenge »

evanelias wrote: post #107
codyb wrote: post #83

Earlier quoted context omitted.

Wouldn’t sharding generally result in overlap between the key ranges so that a database shard going down doesn’t have to result in any downtime? Then your issue is replication of writes I suppose. Probably depends on use case what configuration you choose.

With a typical sharded relational database setup, each sharding key value maps to exactly one shard. There should be replicas of that shard, which can be promoted in case of a master failure. But in rare cases all replicas may also be degraded or inconsistent and therefore non-promotable. When this happens to a giant monolithic non-sharded database, the impact is far more catastrophic than when it happens to a single…

Most, if not all, commercial newsql distributed databases do range based splitting of the data, with each range managed by a raft group. Raft groups get migrated between nodes to avoid hot spots, among other scheduling criteria. TiDB does this for sure, I'd be surprised if CockroachDB (and yugabyte and dgraph) doesn't do it.
Post reply on HN