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

Updating a 50 terabyte PostgreSQL database (2018)

adyen.com

Post reply on HN
Print view View original

51–60 of 67 posts

Re: Updating a 50 terabyte PostgreSQL database (2018)

#51

Post by [deleted] »

golergka wrote: post #41
ok_dad wrote: post #26

Earlier quoted context omitted.

Around ~2005 I took a tour of the [a well known government organization] and they were bragging about several-PB-sized databases at the time. Interestingly, there was a TON of server racks there in a bomb-proof building with tons of security, and they were all IBM servers (a supercomputer maybe?), if I remember correctly. Also, there was one small server rack that was painted differently from the rest (it looked like…

Was that a three letter US government agency?

[deleted]

Re: Updating a 50 terabyte PostgreSQL database (2018)

#52

Post by ok_dad »

barrkel wrote: post #47
ok_dad wrote: post #26

Earlier quoted context omitted.

Around ~2005 I took a tour of the [a well known government organization] and they were bragging about several-PB-sized databases at the time. Interestingly, there was a TON of server racks there in a bomb-proof building with tons of security, and they were all IBM servers (a supercomputer maybe?), if I remember correctly. Also, there was one small server rack that was painted differently from the rest (it looked like…

This comment contains no information other than an ego boost for yourself, AFAICT.

I need every ego boost I can get these days, friend. Either way, I was intending to tell a story directly relevant to the OP about how there were very large databases even back then. Interestingly, the same size databases are probably run on much less hardware today.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#53

Post by AlisdairO »

mulmen wrote: post #40
kabes wrote: post #39

Earlier quoted context omitted.

Aren't a lot of nosql database, especially document databases like mongo based around the idea that denormalization is a performance optimization (for read-heavy load)?

Well, no. Normalization benefits DynamoDB too, if you understand the nature of the database. It’s all a spectrum. In the last year my team did a lot of iterating on our DynamoDB schema and eventually just re-discovered normalization. From what I can tell the benefit of databases like DynamoDB is that you can shard your workload over many hosts mostly transparently. So you get the benefit of more resources than fit in…

Particularly in the dynamo case, you're working outside of a common buffer pool. One of the key benefits of normalization in a typical db is that you can fit more stuff into memory if you normalize - dynamo renders that point largely moot.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#54

Post by hsbauauvhabzb »

paozac wrote: post #13

50TB is not so big these days. I read that in 2008 (!) Yahoo had a 2+ PB PG database. What is the largest you know of, 14 years later?

Security and incident response systems ingesting log files from other systems can get big, add in ‘must store for $x years’ compliance fuzz and you might hit some big numbers

Re: Updating a 50 terabyte PostgreSQL database (2018)

#55

Post by dikei »

znpy wrote: post #29
rattray wrote: post #17

Earlier quoted context omitted.

Yeah, they architect their application to accept DB downtime – but I'm sure their services are still degraded to some degree or another during this, and they aren't clear how much total DB downtime they need for this (and how that time scales across various axes). Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" which…

> Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" HA is one of those things where MySQL wins hands down, sadly. Sadly in the sense that PostgreSQL HA still looks like a couple of hacks held together with duct tape, at least when compared to MySQL solutions. The Percona MySQL distribution has multi-master HA (Percona…

Only people who haven't run MySQL multi-master HA at scale praise it. In reality, it's a world of pain, where one single seemingly innocent query can take down the entire cluster.

Speaking from operation experience with MariaDB Galera and Percona Xtradb Cluster (which is also Galera).

Re: Updating a 50 terabyte PostgreSQL database (2018)

#56

Post by throwdbaaway »

dikei wrote: post #55
znpy wrote: post #29

Earlier quoted context omitted.

> Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" HA is one of those things where MySQL wins hands down, sadly. Sadly in the sense that PostgreSQL HA still looks like a couple of hacks held together with duct tape, at least when compared to MySQL solutions. The Percona MySQL distribution has multi-master HA (Percona…

Only people who haven't run MySQL multi-master HA at scale praise it. In reality, it's a world of pain, where one single seemingly innocent query can take down the entire cluster. Speaking from operation experience with MariaDB Galera and Percona Xtradb Cluster (which is also Galera).

In practice, you should typically only send write traffic to 1 galera node, and only fall back to "multi-master" for a split-second when promoting another galera node as the new writer node, either due to scheduled maintenance or unexpected downtime on the old writer node. This setup works great when being fronted by a fleet of stateless proxysql nodes. The whole DBA team can sleep well, without having to worry about whether the automated failover logic would actually work or not.

Also note that galera is just a set of API (https://github.com/codership/wsrep-API), which can be implemented against other databases to bring the same replication capability.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#57

Post by dachryn »

ipaddr wrote: post #45
mulmen wrote: post #27

Earlier quoted context omitted.

My only experience with databases of that size is for data analysis so yeah, constraints are relaxed. But even at that point ideas like normalization are critical to extracting performance out of large datasets. Normalization is a performance optimization. Denormalization is a development shortcut. Neither is right or wrong but I would be surprised if a 50TB OLTP database wasn’t already highly normalized. If it isn’t…

You have that backwards. Denormalization is a performance optimization. By duplicating data you reduce the need for costly joins at the expense of data consistency.

both of you can be wrong, but in practice its more like: finding the right level of normalization is a performance optimization.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#58

Post by mulmen »

Kranar wrote: post #43
mulmen wrote: post #38

Earlier quoted context omitted.

There are performance benefits to normalization at scale. Take a look at the 2005 schema changes at Wikipedia [1] for a real-world example. > Normalizing data can improve write performance but will make reads slower, so if you have a write intensive database then you will see some performance gains. In OLTP this might be true, based on access pattern. Normalization may improve both read and write performance. It coul…

>Normalization is a tool like anything else. You can’t make absolute statements about it. That's exactly my point. I specifically said that normalization is independent of optimizations, it might help, it might not. It was specifically in response to your absolute statement about normalization being critical to performance, and I quote: >Normalization is a performance optimization. Emphasis taken directly from your q…

In data(bases) performance and normalization are intimately related.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#59

Post by dikei »

throwdbaaway wrote: post #56
dikei wrote: post #55

Earlier quoted context omitted.

Only people who haven't run MySQL multi-master HA at scale praise it. In reality, it's a world of pain, where one single seemingly innocent query can take down the entire cluster. Speaking from operation experience with MariaDB Galera and Percona Xtradb Cluster (which is also Galera).

In practice, you should typically only send write traffic to 1 galera node, and only fall back to "multi-master" for a split-second when promoting another galera node as the new writer node, either due to scheduled maintenance or unexpected downtime on the old writer node. This setup works great when being fronted by a fleet of stateless proxysql nodes. The whole DBA team can sleep well, without having to worry about…

> In practice, you should typically only send write traffic to 1 galera node

Yes, we did that too, it's the only way to reduce the deadlock and certification failure with Galera. However, it won't prevent a single `ALTER TABLE` to lock up the entire cluster. You can use `pt-online-schema-change` to workaround that, but it's not perfect.

We've switched to simple Asynchronous Replication with Openark Orchestrator for the majority of our clusters and the DBA team is much happier now.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#60

Post by idiocrat »

leifg wrote: post #19

One of the things I always wonder with giant relational database is. How much of the "typical relational stuff" are they actually using? Do they have constraints on rows? Are they using views or do they just denormalize and duplicate? Do they use joins at all? Are they even doing more than 1 thing in a transaction?

One question to ask is if they truly need all the 50 TB data in one database instance at once.

Some old inactive data can be moved into partitions, then detached and moved to a different DB instance, as an archiving step.

It reads like their processed payments (5000 tx/second) are very suitable for daily, monthly, yearly archiving.

For analytics, a summary stab can be kept in place of the detailed (archived) transactions.

For statutory reporting, the slow archives can be accessed on demand.

Post reply on HN