I'm looking for advice on the best database to use in Azure for making changes to a production application, particularly one that can't afford any downtime, like a payment system. Typically, I think it's best to avoid using "ALTER TABLE" commands and instead create new tables or use metadata/jsonb to allow for flexibility in what we can add. I hear about Cosmos DB but I'm hesitant to use it for financial transactions. From what I've read, Postgres seems strong because it supposedly handles non-blocking additions well and even has a better jsonb implementation compared to SQL Server. I've also come across SQL Server Managed Instance with the ONLINE option for altering tables and Azure Hyperscale. However, overall, it seems like Azure SQL is the least advantageous in this respect. What are your thoughts?
5 Answers
It sounds like you're focused on a zero-downtime setup. If that's truly your goal, maybe consider that schema change locks are only part of the equation. If you can afford a brief maintenance window every now and then, that would alleviate a lot of your worries. You could also think about using something like an outbox pattern where your payment app sends updates to a service bus and the worker processes them at their own pace without affecting the payment flow.
In my experience, a typical ALTER TABLE can often finish in under a second. If you pick a quiet time, you might be able to do a live migration without causing disruptions. But remember, for large operations like what Stripe manages, you’ll definitely need a thorough migration plan.
Actually, Postgres and SQL Server don’t differ much when it comes to adding nullable columns. Both will still require a brief lock for those operations, even though they do aim for a fast metadata update. There really isn’t a relational database that can completely avoid blocking during schema alterations. But from what I hear, some aspects of Postgres might make it feel less obstructive—especially if you avoid using default values for added columns.
I brought this up because when I asked an AI, it indicated that Postgres is far superior in handling this, claiming that adding a nullable column is just a metadata operation with minimal blocking effects, even on large tables. Curious about any insights on that!
Honestly, if you're unsure, it might be worthwhile to consult your lead developer or architect. They could offer you tailored advice based on your existing infrastructure, especially since navigating legacy systems can be tricky.
I get that, but asking for help is always a good first step. Let's all support each other and provide useful pointers instead of shutting others down!
This is quite a broad question. To give you the best advice, I’d need to know more about your specific use case. How much data are we dealing with? Is your workload write-heavy or read-heavy? What are your latency requirements? Are you focusing on transactionality, or is this about setting up a new staging database for ETL? You might also want to consider Azure Data Explorer (Kusto) since it’s designed for fast queries with an append-only data structure. Plus, check out "Designing Data-Intensive Applications" for more insights that could help!
I'm thinking more along the lines of when a company like Stripe needs to add a column for new features on transactions. It seems like the best solution might be to create a new table instead of modifying the existing one.
But remember, payment calls must be handled quickly—ideally within 1.5 seconds. So even if there’s an option for downtime, it isn't ideal for your case. I just think it might be worth considering the possibility of never altering tables.