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

Updating a 50 terabyte PostgreSQL database (2018)

medium.com

Post reply on HN
Print view View original

51–60 of 81 posts

Re: Updating a 50 terabyte PostgreSQL database (2018)

#51

Post by KptMarchewa »

manigandham wrote: post #49
KptMarchewa wrote: post #47

Earlier quoted context omitted.

I agree, but analytics is orthogonal topic. Unless you want to use columnar database to retrieve 15 transaction that particular user did month ago...

There is nothing wrong with that use case. It works just fine. It may not have the same latency as an OLTP database but there are many factors (indexing, sort/partition keys, compression, etc) that can return similar performance for those queries.

Just to clarify what you advocate for: you want to run this type of queries on analytical databases (like Snowflake, Redshift etc) to display 10 rows on frontend device?

    select * from transactions where user_id='asdf' and completed_date = '2021-01-01'

Re: Updating a 50 terabyte PostgreSQL database (2018)

#52

Post by manigandham »

KptMarchewa wrote: post #51
manigandham wrote: post #49

Earlier quoted context omitted.

There is nothing wrong with that use case. It works just fine. It may not have the same latency as an OLTP database but there are many factors (indexing, sort/partition keys, compression, etc) that can return similar performance for those queries.

Just to clarify what you advocate for: you want to run this type of queries on analytical databases (like Snowflake, Redshift etc) to display 10 rows on frontend device? select * from transactions where user_id='asdf' and completed_date = '2021-01-01'

Sure. Again the database and data layout make a difference. Snowflake has startup latency since the "warehouse" might not be running but Redshift will do fine, along with many others that are always on.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#53

Post by ComodoHacker »

easton wrote: post #50
ComodoHacker wrote: post #20

Earlier quoted context omitted.

This indeed means downtime on database level. But their system design allows processing transactions with DB down for some time. I guess they are caching account balances and limits in some in-memory DB. This is a common design choice for payment processing systems. Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet. This is one of the few points why commercial DBMSes are often worth their mone…

I wonder what the break even point is for just that one feature. 15 minutes of downtime per year, which you can schedule and maybe work around, or five-six figures to Oracle or Microsoft for them to already have solved this problem, but otherwise you don’t get much else (that you need).

It could be more than one downtime per year, if we take security patches into account.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#54

Post by darksaints »

gdgdgdgbgfff wrote: post #42
noir_lord wrote: post #40

Earlier quoted context omitted.

128x4 (EPYC 7000 series go up to 128) assuming you can get a 4 socket board.

I thought 64 cores was a limit coming from Postgres http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-abo...

I would think it is a bit higher now, as this benchmark is for a pretty old version that's not even supported anymore. In particular, I'm pretty sure that shared memory throughput is a major factor for scaling the number of cores, and there have been quite a few changes to the shared memory subsystem since then. But even then, there probably are some significant overheads that are reached before you get to the limits of single node systems that are available today.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#55

Post by darksaints »

ComodoHacker wrote: post #20
fastest963 wrote: post #3

They're halting database traffic for 15-30 minutes. I'm confused how this won't mean they have downtime? The application can queue transactions but how is the application handling reads after those queued trsnactions and what if 2 queued transactions conflict?

This indeed means downtime on database level. But their system design allows processing transactions with DB down for some time. I guess they are caching account balances and limits in some in-memory DB. This is a common design choice for payment processing systems. Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet. This is one of the few points why commercial DBMSes are often worth their mone…

> Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet. This is one of the few points why commercial DBMSes are often worth their money.

I was about to say the same, as much as it pains me to say it. I wonder if Citus or EnterpriseDB has any solutions to a problem like this? Can Citus run temporarily with heterogeneous nodes? E.g. some nodes upgrading with other nodes operating?

Re: Updating a 50 terabyte PostgreSQL database (2018)

#56

Post by kasey_junk »

gdgdgdgbgfff wrote: post #42
noir_lord wrote: post #40

Earlier quoted context omitted.

128x4 (EPYC 7000 series go up to 128) assuming you can get a 4 socket board.

I thought 64 cores was a limit coming from Postgres http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-abo...

That’s a 10 year old restriction. At least as of Postgres 9.2 you could run more than 64 cores (though Postgres didn’t always use them effectively).

Re: Updating a 50 terabyte PostgreSQL database (2018)

#57

Post by pupdogg »

KptMarchewa wrote: post #48
pupdogg wrote: post #46

Earlier quoted context omitted.

Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case. What I'm suggesting does not limit their customers from searching any historical data. Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested…

>Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested solution. That must be trolling. 1000x faster than single digit millisecond indexed query retrieving 15 rows? The fact that you keep talking about storage size means that you're talking about analytics not transactional needs. >Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format…

Ok tiger! Thank you proving that the more inept you are, the smarter you think you are. Now, go beef with someone else.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#58

Post by darksaints »

ComodoHacker wrote: post #53
easton wrote: post #50

Earlier quoted context omitted.

I wonder what the break even point is for just that one feature. 15 minutes of downtime per year, which you can schedule and maybe work around, or five-six figures to Oracle or Microsoft for them to already have solved this problem, but otherwise you don’t get much else (that you need).

It could be more than one downtime per year, if we take security patches into account.

Security patches are minor upgrades...the upgrades only cause significant downtime if there are large changes in on-disk representation, which shouldn't happen for a security patch.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#59

Post by darksaints »

manigandham wrote: post #52
KptMarchewa wrote: post #51

Earlier quoted context omitted.

Just to clarify what you advocate for: you want to run this type of queries on analytical databases (like Snowflake, Redshift etc) to display 10 rows on frontend device? select * from transactions where user_id='asdf' and completed_date = '2021-01-01'

Sure. Again the database and data layout make a difference. Snowflake has startup latency since the "warehouse" might not be running but Redshift will do fine, along with many others that are always on.

Redshift has some pretty significant and fairly unpredictable per-query overhead. For example, all queries are compiled, then code is distributed to every node in the cluster, then executed and gathered before returning. And most analytics databases use a scheduled execution model, which can delay execution from a couple seconds to several minutes, depending on database loads.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#60

Post by datavinci »

ComodoHacker wrote: post #20
fastest963 wrote: post #3

They're halting database traffic for 15-30 minutes. I'm confused how this won't mean they have downtime? The application can queue transactions but how is the application handling reads after those queued trsnactions and what if 2 queued transactions conflict?

This indeed means downtime on database level. But their system design allows processing transactions with DB down for some time. I guess they are caching account balances and limits in some in-memory DB. This is a common design choice for payment processing systems. Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet. This is one of the few points why commercial DBMSes are often worth their mone…

Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet. -> Hey coould you explain by what u mean here? Thanks.
Post reply on HN