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

201–207 of 207 posts

Re: Postgres scaling advice

#201

Post by barrkel »

mulmen wrote: post #172
btilly wrote: post #170

Earlier quoted context omitted.

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…

VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?

AFAIK most of the Postgres bits of Redshift have been rewritten except for the SQL parser and client protocol.

Re: Postgres scaling advice

#202

Post by barrkel »

scndalousarbite wrote: post #192
barrkel wrote: post #127

Earlier quoted context omitted.

Kubernetes is for when you need to allocate CPU like you allocate RAM, and you don't want to be tied to a higher level API sold by a vendor.

Like sched_setaffinity didn't exist and cgroups can't be used outside some container env?

Service and cluster autoscaling. Automatically allocating and provisioning new nodes on compute demand, and releasing them when done.

It can be done without k8s, but without something similar (e.g. Mesos) you're coding to cloud vendor APIs. K8s is like a cloud operating system, it gives you portable APIs for allocating compute and other cloud resources.

Re: Postgres scaling advice

#203

Post by mulmen »

barrkel wrote: post #200
mulmen wrote: post #171

Earlier quoted context omitted.

My relational experience is largely in the analytics world. Can you give me a (simplified/obfuscated) example of this type of workload? Why do the rows need to be updated together or so frequently? My imagination is failing me but this sounds like an interesting problem.

Most recently I was chief architect at London-based fintech SaaS company that does reconciliations. Recs, as they're known, are in practice a big diff, or join (in the relational sense), between two sets of tabular data. The clients are financial organizations, and the records are things like trades, positions, transactions of various kinds. Larger organizations perform millions of these a day, and for regulatory and…

Thanks for taking the time to explain that. Food for thought.

Re: Postgres scaling advice

#204

Post by barrkel »

mulmen wrote: post #203
barrkel wrote: post #200

Earlier quoted context omitted.

Most recently I was chief architect at London-based fintech SaaS company that does reconciliations. Recs, as they're known, are in practice a big diff, or join (in the relational sense), between two sets of tabular data. The clients are financial organizations, and the records are things like trades, positions, transactions of various kinds. Larger organizations perform millions of these a day, and for regulatory and…

Thanks for taking the time to explain that. Food for thought.

MySQL was the chosen store, chosen primarily for its (async) replication story which was better than Postgres back in 2012. Though I'm not sure it wouldn't still be a better choice today with the update load, I've had problems in personal toy (~20 million row) projects with slow bulk update speed in Postgres.

The last thing I did before I left was build out a mechanism for mirroring a denormalized copy of recent data in ClickHouse, partitioned by time (for matched) and type (for partials / unmatched). CH, being analytics oriented, works much better for the ad-hoc end user queries, which filter and sort on a handful of arbitrary columns - ideal for a columnar store. Interactive updates can be handled by CH's versioned merge tree table engine, and batch updates by rewriting the relevant partition from the source of truth in MySQL.

I chose CH primarily because it scaled down well for a columnar store - much better than anything in the Hadoop space. The conservatism of the market meant you couldn't just throw random cloud provider data mechanisms at it, nor a whole lot of fancy big stuff.

Re: Postgres scaling advice

#205

Post by collyw »

riku_iki wrote: post #175
pas wrote: post #93

Earlier quoted context omitted.

Monzo (UK bank) has 1600+ microservices, but mandates a common framework/library and uses Cassandra. (Which is basically a shared nothing, bring your own schema "database".) So it makes sense to combine advantages of different approaches.

using non-ACID db for financial services probably requires lots of trickery.

it is web scale though

Re: Postgres scaling advice

#206

Post by scndalousarbite »

barrkel wrote: post #202
scndalousarbite wrote: post #192

Earlier quoted context omitted.

Like sched_setaffinity didn't exist and cgroups can't be used outside some container env?

Service and cluster autoscaling. Automatically allocating and provisioning new nodes on compute demand, and releasing them when done. It can be done without k8s, but without something similar (e.g. Mesos) you're coding to cloud vendor APIs. K8s is like a cloud operating system, it gives you portable APIs for allocating compute and other cloud resources.

qemu/kvm and libvirt as an example api to address your resource idiom + a load balancer/rev proxy API. You are missing my point afaict.

Re: Postgres scaling advice

#207

Post by scndalousarbite »

pas wrote: post #194
scndalousarbite wrote: post #191

Earlier quoted context omitted.

Playbooks are terrible. They are a replacement for expert knowledge of platform tooling. There is no replacement for expertise and knowledge of the platform. Serious problems are always reduced to understanding the platform, not the playbook. Ansible and the python ecosystem are especially broken. I will _never_ use another playbook to replace mature ssh driven deployments.

Yep, agreed. I found that the active control loops (coupled with the forgiving "just crashloop until our dependencies are up" approach) that k8s provides/promotes are the only sane way to ensure complex deployments. (The concepts could be used to create a new config management platform, but it would be really hard, as most of the building blocks are not idempotent, and making them such usually requires wrapping them…

Hmm. The expectations and engineering of a platform like kubernetes and 'fail till right' require a lot of alignment work to get to the point where 'fail till right' works.

There is no silver bullet. Automation and self healing is a selling point but when it hits engineering it usually is a dud in terms of incorporation in existing environments.

The real novelty would be to generate a declarative description from the customer and provide an in place deployment solution via k8s. That would be the ultimate replacement solution.

Post reply on HN