Updating a 50 terabyte PostgreSQL database (2018) • Hacker News Board

Re: Updating a 50 terabyte PostgreSQL database (2018)

#61

Post by wruza » Fri, Jan 14, 2022, 9:34 AM UTC

samhw wrote: post #44

wruza wrote: post #42

While others saying 50T DB is relatively normal, personally I can't imagine myself dealing with that sort of data which is always online. If I were them, I'd ask myself, could we archive this data into split chunks? Is this archive even required to be online? What is the size of a subset that has actuality? And so on. Of course they have answers to that and they are generally "no", but my imagination just stops here.…

> I was confused about how it does sum up all transactions to get the balance so quickly, like we in accounting usually do For what it's worth, in a system design context, this is called event sourcing (in more general terms it's just a fold, though the idea of event sourcing doesn't preclude caching). I worked at a bank for a few years and this was how we calculated balances too. > And instead it does this: I don't…

Not the entire balance, because a single wallet may have many "inputs", but that's just an implementation detail, as far as I understand it.

https://en.bitcoin.it/wiki/Transaction

If the input is worth 50 BTC but you only want to send 25 BTC, Bitcoin will create two outputs worth 25 BTC: one to the destination, and one back to you (known as "change", though you send it to yourself). Any input bitcoins not redeemed in an output is considered a transaction fee; whoever generates the block can claim it by inserting it into the coinbase transaction of that block.

I.e. any "input" is spent completely by a transaction and this makes it irrelevant to future calculations.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#62

Post by blacklight » Fri, Jan 14, 2022, 9:42 AM UTC

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?

As a former Adyen employee, I can confirm that they use the relational features a lot. It's a ledger with millions (probably billions by now) of financial records, and it's heavily optimized for flexible querying. You have raw tables of transactions that are linked to many other tables (accounts, exchange rates, risk, transaction accept/capture/settle records, batches etc.), and you can ideally query the state of any transaction by just joining those tables on the db. What they also have is plenty of aggregate tables materialized by consumers and compactors, which are used a lot for reporting and auditing - for instance, daily transacted amount broken down by account and currency, or monthly revenue reports broken down by marketplace and currency. Additionally, being a financial institution they are also compelled to hold these records for several years, so there aren't many options for reducing the size of the db by aggregating/moving old records.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#63

Post by funcDropShadow » Fri, Jan 14, 2022, 2:30 PM UTC

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.

Denormalization is only a performance optimization if it matches exactly to your queries. If your data is denormalized in some way, but later on you discover you want to query it differently, it may be way more expensive to query it then. Even if you know beforehand that you want to access the data in multiple different ways and you denormalize it for all those ways, it might be faster to use normalization because of better memory-locality. So, there are a lot of tradeoffs.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#64

Post by natmaka » Fri, Jan 14, 2022, 3:26 PM UTC

mulmen wrote: post #38

Kranar wrote: post #31

Earlier quoted context omitted.

Normalization is pretty independent of optimization strategy and generally that's not presented as one of its advantages. 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.

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…

Denormalization adds to the volume of data to read and write, and often reduces the global hit ratio of caches.

On most servers the most plentiful and cheap resource is the CPU (not the storage!), and an adequate level of normalization loads CPU while relieving storage.

Normalization often adds storage 'seeks', and therefore doesn't costs much on some storage (SSD...).

Re: Updating a 50 terabyte PostgreSQL database (2018)

#65

Post by znpy » Fri, Jan 14, 2022, 5:08 PM UTC

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

> Only people who haven't run MySQL multi-master HA at scale praise it.

This statement (and the following phrases) is so vague that the only sensible thing that I can reply is that you're probably using it wrong, and that you should go back to the fine manual.

We run (several) Percona XtraDB Cluster (PXC) and even though there is some occasional crash (usually a node that goes into OOM) it's very stable. We never had, so far, a whole cluster go down. In the worst case a single node was still up, and was operative while we recovered the other nodes.

Of course stability doesn't come from for free: we spent some time using PMM (Percona Monitoring&Management) to find bottlenecks, and we keep PMM around for developers to troubleshoot performance issues on their own.

At the same time we just don't grant risky permissions to developers. One of the riskiest grants when dealing with PXC is ALTER TABLE, which requires the appropriate tooling (pt-online-schema change from the percona toolkit) and also requires following a procedure. Need an ALTER? Open a ticket. Ez.

What can I say?

If PXC doesn't work well for you, you're probably using it wrong.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#66

Post by mulmen » Fri, Jan 14, 2022, 8:51 PM UTC

AlisdairO wrote: post #53

mulmen wrote: post #40

Earlier quoted context omitted.

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.

That's only one benefit of normalization.

In our case many of our objects had redundant data (aka denormalized) so updates required multiple calls to the DynamoDB service. By normalizing we saw throughput gains in our application and reduced service calls by taking fewer trips. Additionally we had conflated a couple of our domain-specific concepts in the data model and by splitting what was actually two independent entities that had been modeled as one we reduced the absolute record count.

I describe these optimizations as "making the data smaller" and "normalization".

Re: Updating a 50 terabyte PostgreSQL database (2018)

#67

Post by Groxx » Sat, Jan 15, 2022, 3:48 AM UTC

duskwuff wrote: post #46

aantix wrote: post #24

Earlier quoted context omitted.

With 50TB, and if you were doing a full text search, wouldn't the entirety of the index have to be held in memory?

No. Full-text indexes exist.

You can also do an incremental/streaming search. Lots of ways to avoid loading it all into memory at once, yeah.