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

31–40 of 67 posts

Re: Updating a 50 terabyte PostgreSQL database (2018)

#31

Post by Kranar »

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…

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.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#32

Post by CubsFan1060 »

hawk_ wrote: post #18
jfbaro wrote: post #9

Thanks for sharing this. Really interesting. But a basic question, why not upgrading to PG 13 instead? I am curious about the reasons for staying on an older version of PostgreSQL.

Interesting. Does PG13 let then do this without downtime?

Not from 9.4, but now there is a path using logical replication between versions: https://www.percona.com/blog/2019/04/04/replication-between-...

Re: Updating a 50 terabyte PostgreSQL database (2018)

#33

Post by [deleted] »

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…

[deleted]

Re: Updating a 50 terabyte PostgreSQL database (2018)

#34

Post by merb »

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…

> Normalization is a performance optimization

that is a stupid statement. because it's way too generic and it depends on the use case. read heavy data that needs a lot of joins are most often denormalized IF updating a lot is not a problem. sometimes you need to create views that pull in different stuff with different queries which would make them not really performant especially not on postgres which is just super slow when it comes to listing/filtering data.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#35

Post by itsthecourier »

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?

It depends on the queries you run. In postgres we use stuff like materialized views, partial indexes, hyperloglog and it you are using citusdb (postgres for adults), you can even have columnar tables to accelerate olap stuff

Re: Updating a 50 terabyte PostgreSQL database (2018)

#36

Post by Something1234 »

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?

count(*) is always going to be slow. They don't store the number of live tuples, just an estimate so it's a full table scan. The secret is to use indexes to get down to a small bit that you care about. If you're filtering on 3 columns, the goal is to get the index to wipe out at least half the results you don't care about and so on and so forth.

A 130M record table with no indexes is going to be crazy slow. Although if all you need are primary key updates, then that's the way to go.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#37

Post by arlattimore »

mulmen wrote: post #25
LoungeFlyZ wrote: post #7

Irony. I think the HN post took down the Slony website.

http://howfuckedismydatabase.com/postgres/slony.php

I've not seen that website before, going back to the home page and clicking through the database options literally had me laughing out loud - thank you :-)

Re: Updating a 50 terabyte PostgreSQL database (2018)

#38

Post by mulmen »

Kranar wrote: post #31
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…

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 could also make it worse if you take it too far. Domain Key normal form could kill you in unique indices alone.

In OLAP different levels of normalization actually helps both read and write performance. Take a look at the star schema to see how that works. In general dimensions as they relate to fact tables are normalized but dimensions themselves are denormalized.

Normalization is a tool like anything else. You can’t make absolute statements about it. The appropriate application will depend on your use case and may change over time.

[1]: https://en.m.wikipedia.org/wiki/MediaWiki

Re: Updating a 50 terabyte PostgreSQL database (2018)

#39

Post by kabes »

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…

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

Re: Updating a 50 terabyte PostgreSQL database (2018)

#40

Post by mulmen »

kabes wrote: post #39
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…

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 one box. But it’s not magic and you pay the price in other areas, such as hot partitions, and implementing join logic in your application.

Also Postgres is the subject at hand and is relational so normalization is an unavoidable design decision.

Said another way normalization is a fundamental concept to modeling data in general. It might even be the fundamental question. You have to balance your requirements to arrive at an answer.

Post reply on HN