3 ms·

You can keep your sql in version control and use a script to reconcile the current database schema with the contents of a repository easily enough. Then you ca

scarface74 8y ago
You can keep your sql in version control and use a script to reconcile the current database schema with the contents of a repository easily enough. Then you can track, git blame and in some cases, easily revert. Check out Alembic on this front, it's pretty useful. And what happens when I need to rollback? I have to rollback the stored procedure code separately. What about regression testing? If you're going back and changing old versions of a stored procedure, you're doing it wrong. The only case where this would be true is a breaking schema change, and then application layer code would break too, so the point is moot. It would be one change in one module (in process) or one microservice (out of process). Siloing your data makes it less useful (or rather, it increases the cost of using it) and less discoverable. Additionally, if you have some sort of security model in place on your data that becomes exponentially harder to manage in the silo'd case. For reporting/analytics that would be a separate longer running process anyway and wouldn’t be a part of your OLTP system anyway and you probably wouldn’t be using stored procs. You would be using some type of reporting system. As for scaling - read scaling databases is REALLY easy. I admit that write scaling is more challenging, but stored procedures are read-dominant in general. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings. Yes, throwing up read replicas is just a matter of throwing money at the problem - and I’m not morally opposed to using money as an optimization technique when necessary. But you can still get more granular scalability with app servers. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings. And that’s where I come in :)

scarface74 8y ago

You can keep your sql in version control and use a script to reconcile the current database schema with the contents of a repository easily enough. Then you can track, git blame and in some cases, easily revert. Check out Alembic on this front, it's pretty useful. And what happens when I need to rollback? I have to rollback the stored procedure code separately. What about regression testing? If you're going back and changing old versions of a stored procedure, you're doing it wrong. The only case where this would be true is a breaking schema change, and then application layer code would break too, so the point is moot. It would be one change in one module (in process) or one microservice (out of process). Siloing your data makes it less useful (or rather, it increases the cost of using it) and less discoverable. Additionally, if you have some sort of security model in place on your data that becomes exponentially harder to manage in the silo'd case. For reporting/analytics that would be a separate longer running process anyway and wouldn’t be a part of your OLTP system anyway and you probably wouldn’t be using stored procs. You would be using some type of reporting system. As for scaling - read scaling databases is REALLY easy. I admit that write scaling is more challenging, but stored procedures are read-dominant in general. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings. Yes, throwing up read replicas is just a matter of throwing money at the problem - and I’m not morally opposed to using money as an optimization technique when necessary. But you can still get more granular scalability with app servers. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings. And that’s where I come in :)