Updating a 50 terabyte PostgreSQL database (2018) | Hacker News Reader

Updating a 50 terabyte PostgreSQL database (2018)

medium.com

187 pointswhitepoplar77 comments

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?

Also very confused by this. I was waiting for a part that explained how data was being accessed and managed during that window.

I wouldn't really want a different, likely subpar system responsible for queuing and serving requests during that window.

I can only think they are directing it to a different cluster.

Maybe their concept of downtime is purely related on the transactions log side.

Assuming they're like my bank: they do maintenance like these every X months, and during that time frame ATMs and E-Banking are offline, but transactions keep on working without interruptions.

Not sure if this is the case though

They mentioned this: > One other detail to note is that we built our software architecture in such a way that we can stop traffic to our PostgreSQL databases, queue the transactions,and run a PostgreSQL update without affecting payments acceptance.

And how can you at a bank run a transaction if you don't know if there is money or not in the account ???

1) Risk management. Accept some losses if necessary.

2) Overdraft. Take the money, putting the account below zero, and fine the customer for letting their account go below zero.

Just make sure you can cache the last known state of the database and use a separate queue for all non-database-applied transactions. This way you can do database maintenance or suffer database outages without affecting your service.

I heard that around 10 years ago, in The Netherlands a major bank still only wrote transactions to their system in a batch once a day. And all transactions of _today_ showing up in their e-banking applications came from their queue and caching systems on the side of their big database. It's weird but also has some benefits

One benefit to the bank is that they can apply the transactions in whatever order they want to your account. Banks like to apply the largest debit first, so that maybe all of the smaller ones will cause an overdraft, thus maximizing the overdraft fees they can charge. The banks say they do this so your mortgage payment for example doesn't bounce, but it doesn't hurt that they make a ton of money from it - not that a bank would ever do anything just for the money.

While processing a payment a psp/acquirer is mostly receiving money, so no need to check if there is balance available. Only for cases like refunds and card payouts you need to know if a merchant has sufficient balance to process the request.

Naively thinking I would assume they're only queueing the updates, but running the read requests against the slaves in that timeframe.

This would mean that there is probably a possibility for people to "overspend" money they don't have, because a recent transaction wasn't yet applied to their account when doing the next transaction quickly afterwards, but if the timeframe is sufficiently short, that might be a risk they're simply willing to tolerate and mitigate by organizational means.

Alternatively one could also integrate some kind of quick-and-dirty solution specifically to catch cases like that, by tracking "temporary balances" in an intermediate layer and denying payments if they are likely to exceed the actual account balance. Such mechanisms wouldn't have to replicate the actual business logic exactly, but just roughly, in order to provide meaningful protection against exploitation of this temporary update situation.

> This would mean that there is probably a possibility for people to "overspend" money they don't have...

They are a payments processor, not an issuer. That means they are on the merchant's side of the transaction, not the purchaser's. Their role in authorization is simply routing. The issuer is responsible for performing the authorization. The only balance they need to track is how much money they owe the merchant.

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

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.

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

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

There's no way to upgrade from one Postgres version that keeps the database up and responding to arbitrary queries for the duration.

> I guess they are caching account balances and limits in some in-memory DB. This is a common design choice for payment processing systems.

MariaDB has offered online schema changes in InnoDB for several years now with continous improvement.

Their (currently commercial only) engine, Xpand (previously known as Clustrix), offers distributed SQL as well as online schema changes.

No need for an unncessary database migration if you are already on LAMP and the like to get these fancy features. However handling any kind of change should be handled with care, especially on such a large dataset. Test your rollback procedures!

Disclaimer, I work for MariaDB.

This is about an online database version upgrade not a schema upgrade / migration.

> Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet.

Surely you could just set up a logically-replicated standby server running the new PG version and then hot-failover to the standby, turning it into the new master. That should preserve availability throughout the upgrade.

That's how they did it in 2015. Read the article if you want to know why they decided to change this in 2018.

Changing master for upgrades works great if youre not at their scale though.

Based on their numbers, they should be archiving their historical data in parquet format partitioned by YYYYMMDD onto something like Clickhouse. This way, they can run a lean Postgres instance(s) at all times yet still get benefits of real-time reporting. Based on their use case, they can retain up-to 30 days of data in Postgres and offload the rest onto Clickhouse.

You assume too much. For example, you assume that analytics aren't already run on a separate data warehouse.

Ease of accessing older data is an important aspect of database holding transaction (not meaning transactional db). Wouldn't you want to check your transactions on bank page that are older than 30 days?

All modern columnnar/analytical databases have no problem in pulling back older data; in fact, that's the point of running large scale analytics.

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-02-01' 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.

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 solution. I strongly believe that mission critical transactional databases (postgres in this case) MUST be run very lean to keep their app running at hyper speeds at all times. 50TB overhead seems very inefficient when you take into account the the low cost solutions available in this day and age.

Based on my personal experience of achieving 94% compression on 2TB of data using snappy parquet file format, they could be looking at a final dataset size of 3.5TB on Clickhouse.

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

Also, fuck off with that condescension.

> fuck off with that condescension

Please follow the site guidelines, regardless of whether or not someone else has broken them. Otherwise we just get a downward spiral.

https://news.ycombinator.com/newsguidelines.html

Sorry, I get way too heated when I see this type of condescending argumentation.

Yes, I experience that too and I bet most people do. One way I often explain this that seems to work well is this:

https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que...

> Your comment clearly illustrates that you have no working knowledge

Please omit swipes like that from your comments here. The rest of your comment would be find without that bit.

https://news.ycombinator.com/newsguidelines.html

I would probably implement an "archive" system that stores static data that is mostly accessed by "sum" functions - In order to keep the main database relatively small. If the data is immutable/static replication and caching becomes much easier.

I'm not sure what you mean by sum functions, but Postgres natively support foreign tables, and I'm aware of one bank that uses this for older, immutable data.

With "sum" functions, I mean that for money transactions the data will likely be stored in columns and the most common operation will likely be to sum each column. So you could store an "archive" with all the columns/transactions, then store just the sum of each column in the "hot" database. If the data is static/immutable you have a lot of options when it comes to caching and optimizing different queries.

Another strategy would also be to "shard" the database... I guess storing everything in the same database is the most simple solution, but problems will arise when you have to replicate/recover (an arbitrary) 100+ TB of data. Just copying it over a 100Gbit link will take 3 hours.

Vertical scaling like this will have quite a few physical limits, eg max cores (is it 64?). I would think that they may be fast approaching these limits?. For a an scalable enterprise solution would it not be better, and also more cost-efficient, to horizontally scale. Shard the tables over many nodes.

You can buy boxes with 30x more RAM without going super boutique. You can buy quad-socket server motherboards, so you can have 64x4 cores.

For the vast majority of businesses, vertical scaling is quite feasible.

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.

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

Am I the only one who is shocked to discover that payments (even very small sums) are stored for years on end, maybe indefinite??

Simple deleting a row that is 366 days old is not an option to keep the PostgreSQL DB relatively small?

Thats not shocking. Its probably due to regulatory laws that transactions are saved that long.

But they don't need to be in the active DB right? Auditing and reporting can be done on an immutable database, while transactions are processed on a smaller active one.

It’s a regulatory requirement keep it for 5-7 years e.g for disputes etc

Just to add to this: GDPR does not apply here.

Yups, that's correct

That's a bad way of putting it: GDPR obviously applies, but it permits storing data that needs to be stored due to other legal obligations. If "GDPR didn't apply", it would also loose all the other protections GDPR provides, which it doesn't.

It's worth noting that it's not necessarily specific to bank payments, depending on your jurisdiction it may be that general accounting laws that require every company to store payment history for at least 5 years, for example.

To add on that, for some types of transaction the regulatory requirements are much, much longer - I was working in a jurisdiction where data of housing loan repayments had to be stored, by law, for 70 years after the end of the loan; so for a 30-year mortgage you'd have to be prepared to store every repayment for 100 years; and information on salaries calculated and paid has to be stored for 75 years (IMHO to resolve retirement-related disputes where it matters where you worked decades ago), passing on to national archive if the company is dissolved.

(2018)

Added above. Thanks!

> As PostgreSQL options were not suitable for the next upgrade, in parallel we considered other possibilities. Our storage devices were able to make instant snapshots and also make them available on remote storage devices over the network, within a much smaller timeframe

ZFS?

It might be NetApp.

Ah thanks -- I was wondering why they wouldn't mention it if it was ZFS, would have made a good addition to the article. I guess they didn't say because it was something proprietary?

No idea, I actually came to the comments for the same reason: looking for details about the storage.

In my opinion it's either zfs or netapp. Zfs can replicate datasets via zfs send, netapp has a snapmirror functionality that does basically the same.

Also, iirc, netapp is contributor to freebsd, so it might be zfs anyway underneath.

I wouldn't be surprised. Last time I had the pleasure to create a snapshot for a volume in a netapp it kinda felt like creating a zfs snapshot, in term of speed and ease.

I thought of netapp since they're running fancy 768gb boxes. If they spend money well on their hardware, they probably spend money well on their storage too.

Yeah zfs send (and a bunch of other features/architecture choices) have been one of the biggest reasons I'm interested in the zfs ecosystem and kind of keep a lookout for it/finding ways to work it into infrastructure that I build. I know that Hardware RAID is what the big boys use, but assuming the performance hit is OK, the feature set of ZFS with a properly tuned database installation like a match made in heaven, was hoping to have them comment...

I'd recommend you give a look at TrueNAS/FreeNAS, it's basically an open source NetApp alternative, still based on FreeBSD.

Their enterprise version also has HA and other goodies.

Reply on news.ycombinator.com