3 ms·

Do you know why the shared database vision didn't work out? Because I still think it would be the best approach for many companies. Most companies are small eno

Do you know why the shared database vision didn't work out? Because I still think it would be the best approach for many companies. Most companies are small enough that they could spend less than $10k/month for an extremely powerful cloud DB. Then you could replace most microservices with views or stored procs. What could be simpler? I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible (compared to popular languages like C#, Python, etc...) But why has no one written a great DB language yet?

zerd 7y ago
I've worked on a service like this. 800k lines of PL/SQL and Java stored procesures (running inside the database, so you could call Java from PL/SQL and vice versa), powered by triggers. * Testing is god-awful. To test a simple thing you had to know how the whole application worked, because there's validation in triggers, which triggers other triggers, which require things to be in a certain state. This made refactoring really hard/risky so it rarely got done. * There's a performance ceiling, and when you hit that, you're done. We did hit a ceiling, did months of performance tuning, then upgraded to the biggest available box at the time, 96 cores, 2TB ram, which helped, but next time the upgrade won't be big enough. You're limited in what one box can do (and due to stored procedures being tied to the transaction there's limits to what you can do concurrently as well)
- projektfu 7y ago
  I think they go too crazy with the stored procedures. I would rather see constraints and views to make the data work. Triggers are useful for simple behind-the-scenes things like logging, or creating updateable views, but their behavior should be kept simple. If they're sending e-mails and launching missiles, it's probably too much. Debugging PL/SQL without the PL/SQL debugger is nearly impossible. Unfortunately a lot of shops cheap out on developer tools after they buy the server licenses. I never liked the idea of Java on the database. The good thing about PL/SQL is that nobody wants to write it so it has a tendency to not be overused by most developers. The performance ceiling probably wouldn't be too low if there weren't too much extra activity with each update. As with all databases, sharding and replication are your friend.
zip1234 7y ago
SQL is actually amazing at some things that are very difficult, verbose, or slow to do in C# or Python.
jdlshore 7y ago
DBAs were a bottleneck. In the best case, you’d throw your application’s data needs over the wall to the DBAs (remember, this was the waterfall era) and hope they’d update the schema for you in a timely manner. In the worst case, the DBAs were petty tyrants who stifled progress. In the worser case, they were incompetent and you ended up with all these applications directly reading and writing the database, making schema changes impossible and coupling applications in obtuse, incredibly difficult to fix ways. In any case, designing a single schema that encompassed all the needs of the organization and could grow and change as the organization did was nearly always too much to ask. This was in the days of enterprise data modeling where people believed there really was just one data model or object model that could represent the whole org, independent of the needs of any given application. I don’t think anybody believes that any more.
cellularmitosis 7y ago
For workloads which are read-heavy, I think a single database can be a great solution -- a small monolith has exclusive write access to the db, and any number of polyglot read-only services are connected to as many read-replicas as are needed for horizontal scaling. For write-heavy workloads, best of luck to you :)
Hendrikto 7y ago
> I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible Where did you get that from?
takeda 7y ago
> I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible (compared to popular languages like C#, Python, etc...) But why has no one written a great DB language yet? SQL is actually hard to compare to programming languages, because in SQL you say what you want, while in iterative language you say how. I only know one language that was competing with SQL (and lost) it is QUEL (originally it was used by Ingress and Postgres). BTW for triggers and stored procedures you actually can use traditional language, I know that PostgreSQL supports Python, you just need to load a proper extension to enable it.
projektfu 7y ago
Probably for the same reasons that waterfall development doesn't work out. This approach requires up-front specification of the data before the application domain is well understood. Any application desiring a migration to a different schema would need to work with the others to do it. Finally, developers bristle at the idea that they have to wait for another team to get their job done. In the absence of a strong company policy, it will inevitably drift toward shipping an application rather than keeping everything together. Perhaps refactoring, had it been better understood around 1970, could have gone a long way toward harmonizing diverse schemas, allowing experimentation with eventual refactoring into the common database. Our current environment makes this impossible. There's no way that Salesforce is going to ship a version that works with your company's database schema. You're going to have to supply that replication yourself. Same for Quickbooks. To get that kind of customization you need to be spending hundreds of thousands for enterprise software.
- default-kramer 7y ago
  I didn't make this clear in my original comment, but I was envisioning that an app like Salesforce would use its own schema, but still live in the same DB with other schemas. I'm going to assume that Salesforce has some notion of an Employee. Salesforce would use its own Employee table by default, but it would provide extension points (views and SPs) that would allow you to read and write Employee data from tables in another schema if you want. This might be preferable to duplicating Employee data. Edit - I just saw that you addressed this in your original post: "nobody offered applications that could be configured to an existing data base"

zerd 7y ago

I've worked on a service like this. 800k lines of PL/SQL and Java stored procesures (running inside the database, so you could call Java from PL/SQL and vice versa), powered by triggers. * Testing is god-awful. To test a simple thing you had to know how the whole application worked, because there's validation in triggers, which triggers other triggers, which require things to be in a certain state. This made refactoring really hard/risky so it rarely got done. * There's a performance ceiling, and when you hit that, you're done. We did hit a ceiling, did months of performance tuning, then upgraded to the biggest available box at the time, 96 cores, 2TB ram, which helped, but next time the upgrade won't be big enough. You're limited in what one box can do (and due to stored procedures being tied to the transaction there's limits to what you can do concurrently as well)

projektfu 7y ago
I think they go too crazy with the stored procedures. I would rather see constraints and views to make the data work. Triggers are useful for simple behind-the-scenes things like logging, or creating updateable views, but their behavior should be kept simple. If they're sending e-mails and launching missiles, it's probably too much. Debugging PL/SQL without the PL/SQL debugger is nearly impossible. Unfortunately a lot of shops cheap out on developer tools after they buy the server licenses. I never liked the idea of Java on the database. The good thing about PL/SQL is that nobody wants to write it so it has a tendency to not be overused by most developers. The performance ceiling probably wouldn't be too low if there weren't too much extra activity with each update. As with all databases, sharding and replication are your friend.

zip1234 7y ago

SQL is actually amazing at some things that are very difficult, verbose, or slow to do in C# or Python.

jdlshore 7y ago

DBAs were a bottleneck. In the best case, you’d throw your application’s data needs over the wall to the DBAs (remember, this was the waterfall era) and hope they’d update the schema for you in a timely manner. In the worst case, the DBAs were petty tyrants who stifled progress. In the worser case, they were incompetent and you ended up with all these applications directly reading and writing the database, making schema changes impossible and coupling applications in obtuse, incredibly difficult to fix ways. In any case, designing a single schema that encompassed all the needs of the organization and could grow and change as the organization did was nearly always too much to ask. This was in the days of enterprise data modeling where people believed there really was just one data model or object model that could represent the whole org, independent of the needs of any given application. I don’t think anybody believes that any more.

cellularmitosis 7y ago

For workloads which are read-heavy, I think a single database can be a great solution -- a small monolith has exclusive write access to the db, and any number of polyglot read-only services are connected to as many read-replicas as are needed for horizontal scaling. For write-heavy workloads, best of luck to you :)

Hendrikto 7y ago

> I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible Where did you get that from?

takeda 7y ago

> I think one reason to avoid this approach is because SQL and other DB languages are pretty terrible (compared to popular languages like C#, Python, etc...) But why has no one written a great DB language yet? SQL is actually hard to compare to programming languages, because in SQL you say what you want, while in iterative language you say how. I only know one language that was competing with SQL (and lost) it is QUEL (originally it was used by Ingress and Postgres). BTW for triggers and stored procedures you actually can use traditional language, I know that PostgreSQL supports Python, you just need to load a proper extension to enable it.

projektfu 7y ago

Probably for the same reasons that waterfall development doesn't work out. This approach requires up-front specification of the data before the application domain is well understood. Any application desiring a migration to a different schema would need to work with the others to do it. Finally, developers bristle at the idea that they have to wait for another team to get their job done. In the absence of a strong company policy, it will inevitably drift toward shipping an application rather than keeping everything together. Perhaps refactoring, had it been better understood around 1970, could have gone a long way toward harmonizing diverse schemas, allowing experimentation with eventual refactoring into the common database. Our current environment makes this impossible. There's no way that Salesforce is going to ship a version that works with your company's database schema. You're going to have to supply that replication yourself. Same for Quickbooks. To get that kind of customization you need to be spending hundreds of thousands for enterprise software.

default-kramer 7y ago
I didn't make this clear in my original comment, but I was envisioning that an app like Salesforce would use its own schema, but still live in the same DB with other schemas. I'm going to assume that Salesforce has some notion of an Employee. Salesforce would use its own Employee table by default, but it would provide extension points (views and SPs) that would allow you to read and write Employee data from tables in another schema if you want. This might be preferable to duplicating Employee data. Edit - I just saw that you addressed this in your original post: "nobody offered applications that could be configured to an existing data base"