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.

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.

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

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.

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

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.

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.

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.

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.

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

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.