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

41–50 of 67 posts

Re: Updating a 50 terabyte PostgreSQL database (2018)

#41

Post by golergka »

ok_dad wrote: post #26
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?

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?

Re: Updating a 50 terabyte PostgreSQL database (2018)

#42

Post by wruza »

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.

Some time ago when I studied bitcoin ledger structure, I was confused about how it does sum up all transactions to get the balance so quickly, like we in accounting usually do, with the help of some period caching, which is another point of failure and maintenance. Bitcoin is a large enough database to not do that easily. Few docs later I realized that it doesn't do this:

  mining -> A 1
  A -> B 0.2
  (sum(A) == 0.8)
And instead it does this:

  mining -> A 1
  A -> (B 0.2; A 0.8)
  (last(A) == 0.8)
No sums required, all balances are checked by essentially "lookup the latest appearance in a db", where lookup also involves some merkle trees optimization, which I didn't understand enough to remember.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#43

Post by Kranar »

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…

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

Normalization has very little to do with optimization and it's one of the least cited reasons (if cited at all), for doing it.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#44

Post by samhw »

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 quite follow you here. It does something distinct from either a fold or caching the current total? (Do you mean that each transaction encodes the resultant balance?)

Re: Updating a 50 terabyte PostgreSQL database (2018)

#45

Post by ipaddr »

mulmen wrote: post #27
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?

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.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#46

Post by duskwuff »

aantix wrote: post #24
GordonS wrote: post #14

Earlier quoted context omitted.

50TB is big. Bigger is possible I'm sure, but I'd guess 99.something% of all PG databases are less than 50TB. If someone here commented they had a 2PB database, I guarantee someone else here would be like "pfft, that's not big"...

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.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#47

Post by barrkel »

ok_dad wrote: post #26
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?

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.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#49

Post by lukeasrodgers »

Gigachad wrote: post #30
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?

How are people dealing with databases this large? At work we have a mysql db with a table that has 130M records in it and a count(*) on the table takes 100 seconds. Anything but a simple look up by id is almost unworkable. I assumed this was normal because its too big. But am I missing something here? Are SQL databases capable of actually working fast at 50TB?

Even at the 130M rows range, you should still be able to take advantage of indexes for fast queries beyond just the primary key. It's been a while since I used mysql, but around 2010 I was working on mysql 5.something and we had several >100M row tables that could still serve indexed queries very quickly (sub ms, or couple ms, iirc). If you are not able to do this, I suggest looking into mysql config and adding/tuning indexes. But yes count(*) will be slow, I'm not aware of good workarounds for that other than caching or using table stats with postgres (if you don't need perfect accuracy) - not sure if mysql supports similar.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#50

Post by [deleted] »

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.

[deleted]
Post reply on HN