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

161–170 of 207 posts

Re: Postgres scaling advice

#161

Post by ldng »

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'm curious, will he answered given his vested interest in "exabyte-scale analytical database" ? :-)

And laurenz_albe has a vested interest in PG as he's a contributor.

So I, too, would love more concrete details.

Re: Postgres scaling advice

#162

Post by 1ris »

objektif wrote: post #126
Silhouette wrote: post #37

Earlier quoted context omitted.

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

Can you please name a few?

As I'm not a K8s user take this with salt.

Heavily depends on your exact situation. Keeping your source code in git and let it build on a CI server was almost always enough for me. If your build server runs windows this is usually just fine.

C and C++ compilers on Linux have this IMHO very unpleasant property that the operating system wants to manage your development environment, so that the build output is a function not only of the repo, but also of things not under your control. I have little experience with that (I'm mostly a desktop developer, and these desktop applications are for windows), but so far simply not installing any -dev packages seems to have eliminate that problem. Put the library source code in the repo, use a git submodule, use a per repo package manager like nuget or cargo, whatever, just make sure your build input is in the repo and only there. For other programming languages this is generally not a issue. Thankfully no Linux distribution I'm aware of tried to sneak in their version of log4net, so far. Same seems true for every other languages I'm aware of, so this is a non-issue for any other language than C and C++.

Automated tests can run in a chroot to make sure all runtime requirements are contained in your build artefact. On Windows running them in a clean VM might be a good idea, but keeping the machine clean seems enough. Don't give people the password to that machine, so they are not tempted to "fix" problems by installing software there, fiddling with system settings, creating "magic" files, but by chaing the code in the repository.

I used repository singular, but this works for both monorepo and polyrepo.

Re: Postgres scaling advice

#163

Post by icebraining »

h0l0cube wrote: post #25
gowld wrote: post #17

Earlier quoted context omitted.

Kubernetes is a container system, mostly orthogonal to 3- or 4-tier application design.

And typically a single DO droplet would suffice for a toy project or POC, for which Ansible is probably the more expedient option. But maybe they're not in a rush, and learning K8s is just another feather in their cap .

Ah, but is it? I'm not sure. One difference between these platforms is that they're very optimized for the "typical app deployment" process, whereas Ansible and such are more generic.

Just getting a simple deployment script in Ansible requires programming a bunch of steps - copying archive, verifying it, unpacking it, then atomically configuring the system to use the new version, finally cleaning it up - whereas with k8s you just let it know you want to Deploy something and it does.

Of course, that's only true if you already know k8s, but then again if you work with both toy and larger projects, chances are you'll have to, sooner or later.

Re: Postgres scaling advice

#164

Post by barrkel »

btilly wrote: post #100
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…

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance im…

What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows?

Either you're going to do 10s of millions of fetches and a whole lot of code, or you can push some of the work to the database and perform updates that involve joins. Those queries may take minutes to complete; but the SQL will be pretty short.

Big queries that fetch millions of rows isn't solely the preserve of reporting. Some applications - business SaaS in particular - has a different user:row-count distribution to consumer businesses; instead of a handful of rows for every consumer, with each consumer doing something simple, a business SaaS may have tens of users who each individually manipulate millions of rows every day.

Re: Postgres scaling advice

#165

Post by barrkel »

ajsharp wrote: post #123
btilly wrote: post #100

Earlier quoted context omitted.

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance im…

My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.

The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows.

You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow.

No simple rules. It depends on the application.

(Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)

Re: Postgres scaling advice

#166

Post by bobthebuilders »

brasetvik wrote: post #116
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…

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

Nice deepfake. It's so surprising how far the field has gone. I re never seeing the fake Nixon moon speech and being blown away

Re: Postgres scaling advice

#167

Post by syspec »

brasetvik wrote: post #116
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…

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

murdered by words

Re: Postgres scaling advice

#168

Post by mulmen »

btilly wrote: post #100
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…

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance im…

#2, so much #2. And also #3.

One of my earliest and still one of my favorite career achievements was optimizing a stored procedure in Oracle. Our application called the procedure and depending on the result dropped a character from the input and called the procedure again.

This was the highest load on our database. We were negotiating a new license to scale up the Oracle database. Estimates were in the six figure range.

I simply reproduced that modification/retry logic inside the procedure. The application always got the desired result on the first call over the network. No application side code change needed at all.

IIRC my optimization reduced load by ~an order of magnitude. We no longer needed the additional Oracle capacity. Saved a multiple of my salary in an afternoon, just looking for a hot spot. I think my boss bought me a sandwich. It was a good day.

Re: Postgres scaling advice

#169

Post by btilly »

barrkel wrote: post #164
btilly wrote: post #100

Earlier quoted context omitted.

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance im…

What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows? Either you're going to do 10s of millions of fetches and a whole lot of code, or you can push some of the work to the database and perform updates that involve joins. Those queries may take minutes to complete; but the SQL will be pretty short. Big queries that fetch millions of rows isn't solely…

What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows?

Know your problem, then set things up appropriately.

I was addressing someone whose problem looked like a reporting query about a consumer application that was affecting the responsiveness of their consumer application. For that case separate the transactional and reporting database and tune each appropriately. That means, for example, different numbers of connections, different memory per connection, different amounts of temporary table space, and so on.

The problem that you're describing is much like the one I'm currently facing, ingesting time series from a busy factory floor, then doing various kinds of analytics. For that case, you can simply use one database, optimize reasonably, tune appropriately for the workload, and set appropriate expectations on responsiveness.

In all these cases you don't need a distributed system. Not unless your requirements are a lot harsher than what has been described so far.

Re: Postgres scaling advice

#170

Post by btilly »

barrkel wrote: post #165
ajsharp wrote: post #123

Earlier quoted context omitted.

My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.

The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster ov…

Every system has ways that it can fall down hard.

Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables.

(This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on Postgres.)

Post reply on HN