Hacker News Reader

Comment by Animats | Hacker News Reader

What database are they using? Their description reads like they used some NoSQL database, and then they needed to do an ALTER TABLE.

Also, how many merchants can they have? I have a database of all US businesses on a desktop machine and a server. It's a few gigabytes. There are about 20 million business entities in the US, and some of them may not be Stripe customers.

View on HN

This can happen even with something as robust as postgres. If you have a huge table undergoing lots of read/write activity, the locking needed to modify the table schema can take (essentially) infinite time, and/or slow your writes to a crawl and/or spike memory usage on your DB such that you no longer have faith in process completion.

Example from my past: setting a default column value on a huge table undergoing hundreds of writes per second. Oops.

This doesnt really affect any advanced database that has online alters and workload management. It would just take longer but wouldn't affect any normal transactions. It's a solved problem unless the database just doesn't support it.

AFAIK, all of the common open-source databases have this problem. Postgres is better than mysql, but it still needs to do extensive locking when you alter a column.

Under heavy read/write loads and/or with large tables, this can blow up in your face.

IIRC, this isn't true for Postgres. Many common schema alterations in Posgres are lock-free (non-blocking). In MySQL, these alterations used to be locking (ie. O(n) in number rows)... but perhaps it has gotten better? (Wouldn't know; don't use MySQL.) I did a quick cursory google search to find some substantiation:

- http://www.estelnetcomputing.com/index.php?/archives/12-Lock...

- http://stackoverflow.com/a/6542757

I ran into this problem with MySQL. We have a table of user submissions that is millions of rows but the amount of time it was taking to migrate the test server was unacceptable downtime in production. We ended up using https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc... to do an online update.

The relevant bit from your first link: "postgres will update the table structure and there is no locking unless you do something that needs to alter existing rows."

So, altering a column (e.g. adding a default) will trigger the problem. Adding a new column or table will not.

They used Mongo, but I'm not sure this is still the case. I guess they use multiple datastores across the company services.

I thought the same thing, how many can they be? Even considering Stripe operates globally, I'd say it's a number in the tens of millions.

Edit: lots of typos

They released this tool a couple of years ago https://stripe.com/blog/announcing-mosql

In the post they wrote

"Here at Stripe, we use a number of different database technologies for both internal- and external-facing services. Over time, we've found ourselves with growing amounts of data in MongoDB that we would like to be able to analyze using SQL. [...] MoSQL does an initial import of your MongoDB collections into a PostgreSQL database, and then continues running, applying any changes to the MongoDB server in near-real-time to the PostgreSQL mirror".

So they could have been run that migration on MongoDB.

I wonder if whatever they're doing in Mongo was really not doable or viable in Postgres?

Reply on news.ycombinator.com