22 ms·

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery

by benkant 11y ago

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language elsewhere in your code in a language that makes impedance stark. PostgreSQL is advanced technology. Whatever you might consider doing in your CRUD software, PostgreSQL has a neat solution. You can extend SQL, add new types, use PL/SQL in a bunch of different languages, background workers, triggers, constraints, permissions. Obviously there are limits but you don't reinvent web servers because Apache doesn't transcode video on the fly. Well, you do if you're whoever makes Rubby on Rails. The argument that you don't want to write any code that locks you to a database is some stunning lack of awareness, as you decide to lock yourself into the tsunami of unpredictability that is web frameworks to ward off the evil of being locked into a 20 year database product built on some pretty sound theoretical foundations. Web developers really took the whole "let's make more work for ourselves" idea and ran with it all the way to the bank. You'd have to pay me a million dollars a year to do web development.

kijin 11y ago
> Why people in the web world don't use stored procedures and constraints is a mystery to me. You can blame MySQL 4.1 for that :( Most people who call themselves "web developers" haven't even heard of PostgreSQL, or even if they've heard of it, have no use for it because their usual clients are stuck with MySQL-only web hosts who have only just managed to upgrade to PHP 5.3.
- ozzie80 11y ago
  Most people, really?
  - chx 11y ago
    Yes. HN is a bubble. There are ~700 PHP questions on SO a day and ~150 node.js. This is just one pair of numbers, you can mine your own whatever you like but you'll realize there are massive amounts of "web developers" with a ... low amount knowledge.
    - adamors 11y ago
      Thinking most web developers are still shipping PHP 5.3 apps on shared hosts is also a very outdated view.
      - kijin 11y ago
        The latest version of WordPress is still compatible with PHP 5.2.4 and above, so anyone who builds a WordPress site is effectively shipping a PHP 5.2 app.
        spdionis 11y ago
        They do recommend php 5.4 though, and afaik they do try to push the community to upgrading.
        kijin 11y ago
        It still means they can't depend on any feature introduced in PHP 5.3 or later. No closures, no namespaces. Ditto for any theme or plugin that tries to be compatible with all versions of PHP that WordPress itself supports.
      - greghinch 11y ago
        Globally, it's not.
      - _lce0 11y ago
        While I do agree with you, I want to make a distinction between shipping and building an application. IMHO the term "developer" should not be applied to those that can just ship but rather those who can also build. It doesn't matter if they are web, desktop, nor low-systems developer actually Most auto-called web developers are just "web masters"
- mark_sz 11y ago
  I'm guessing you are not developer, because that kind of comment wouldn't come from someone who's thinking logically. We don't need another flame war here. And yes, most of the web is build on Wordpress/PHP - but you don't need to be a developer to install Wordpress.
  - kijin 11y ago
    What does logic have to do with it? I'm just stating what I believe to be a fact: that the majority of web developers in this world never think of PostgreSQL as an option. I don't care whether that's a logical thing for them to think. It's just a fact, whether I like it or not. If you think I'm wrong about the facts, please feel free to open a phone book in any part of the world other than the Bay Area, call up a decent sample of people who self-identify as web developers, and find out what percentage of them have ever heard of, let alone used, PostgreSQL.
    - GrinningFool 11y ago
      So you start off ok here: I'm just stating what I believe to be a fact... But then you move on to say: It's just a fact, whether I like it or not. So which is it? Do you believe it to be a fact, or is it a fact? And if it is, where's your evidence? (I happen to agree with your opinion, but the semantics here bug me.)
      - kijin 11y ago
        Sorry for the loose use of language. Everything that follows the colon after "I believe to be a fact", until the end of that paragraph, is the content of what I believe, including the statememt "It's just a fact." I believe that it's a fact. Anecdotal evidence: I've interacted with dozens of other people who call themselves web developers over the years, and most of them (outside of Silicon Valley) have never used PostgreSQL, nor any advanced features of SQL in any other RDBMS. Objective evidence: the large market share of WordPress, Drupal, and other content management systems that don't use any advanced database features; as well as the large market share of frameworks such as Rails, Django, and Laravel that encourage developers to stick with the ORM and not care about advanced database features.
devnonymous 11y ago
...and it's not just databases. OS capabilities (eg: vm tuning, bumping up the default sysctl limits, etc) are ignored and the problems arising of such disregard are then dealt with by adding layers to the application like distributed caches and other ^scaling^ solutions.
adamors 11y ago
For one, stored procedures are hard to test, debug, maintain and check into source control. But don't let that get in your way of ignorantly generalising about web developers.
- bni 11y ago
  "hard to test, debug, maintain and check into source control." Why? I have never had problem with any of these. SPs is just imperative code like any other imperative code.
  - adamors 11y ago
    Well, you cannot isolate the code, you cannot unit test it, you cannot use a debugger, cannot set breakpoint, have stack traces etc. You are tied to the database at all times. In my experience, every stored procedure that is larger than 2-3 lines is a headache.
    - bni 11y ago
      All of those are no problem with the right tools. Any database IDE can debug SPs. You can unit test SPs like any other code just use a testrunner.
      - glogla 11y ago
        Yeah. I suspect that lot of hate of logic in DB is because of bad Oracle setups many years ago, kind of like lot of people think SQL is useless because MySQL is.
    - arethuza 11y ago
      I'm not a huge fan of stored procedures - but I'm pretty sure you can debug stored procedures in SQL Server pretty easily from Visual Studio - I think you can "step into" the call to SPs while debugging client code.
- glogla 11y ago
  Stored procedures may be all of those things, but they don't have to - it's just that most of the time, developers don't really care, so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Here is interesting talk about database migrations and stored procedures and unit tests: http://www.pgcon.org/2013/schedule/events/615.en.html http://www.pgcon.org/2013/schedule/events/615.en.html Also, DB procedures are not easy to "debug" in the traditional way, but SQL client is basically the first REPL every programmer becomes familiar with. You can easily step stored procedure by running it's commands one by one, unless it's fancy Oracle forall loop with cursor or something (and the cursor select can still be selected as normal). Also, databases tend to have more strong data types than programming languages in general so putting constraints in DB means, that bad data are not savable in the system.
batou 11y ago
That is quite frankly horse poop. We use stored procedures. Not by choice; this is a legacy we're stuck with. It is nothing but an unadulterated disaster of a technology regardless of what you use it for. I'm talking 45,000 stored procedures here. 2000 tables. TiB of data. 50,000 requests/second across SOAP/web/desktop etc. It's hell. Problems with stored procedures: 1) Performance. More code running in the hard to scale-out black box. You're just hanging yourself with hardware and/or license costs in the long run. 2) Maintenance. All database objects are stateful i.e. they have to be loaded to work. The sheer complexity of managing that on top of the table state results in massive cost. Add to that tooling, version control costs as well. Have you tried merging a stored procedure in a language which has no compiler and very loose verification? 3) Orthoganality. Nothing inside the relational model matches the logical concepts in your application. Think of transactions, domain modelling etc. 4) Duplication. You still have to write something in the application to talk to every single one of those stored procedures and map it to and from parameters and back to collections. 5) Transaction scoping. Do you know how expensive it is to introduce distributed transactions? Well it's a ton more cash when EVERYTHING is inside that black box. 6) Lock in. Your stored procedures aren't portable. Good luck trying to shift vendor in the future when you hit a brick wall. Now I know it's popular to bash on Rails and I wouldn't use it personally but there are people using the same model on top of other platforms, like us. Sorry but databases are just a hole to put your shit in when you want it out of memory. If you start investing too much in all of the specific functionality you're hanging yourself.
- spdionis 11y ago
  You had me until your last sentence. Database are very important to (and very good at) store your data. And data is important (duh). All the issues you described above are related to working with the data, which should not happen in the database.
  - batou 11y ago
    That's a poor argument because we're not discussing the arbitraty boundary of storing versus working with data as the lines between those are very blurred. This is even more the case when you use stored procedures which work with data close to the storage. While we're on this subject, RDBMS are no better at storing data than any other technology out there[1]. In fact when you start thinking abstractly like this, other tech such as Riak makes sense for a lot of workloads. The only real benefits of RDBMS' are fixed schema, fungibility of staff, the ability to issue completely random queries and get a result in a reasonable amount of time and the proliferation of ORMs. [1] Caveated on insane design decisions like MyISAM storage engine and MongoDB as a whole.
ilitirit 11y ago
I find it strange when people adhere to these extremely dogmatic ideas about stored procedures. They appear to be either on one end of the scale or the other, ie. they either put all their logic in to Stored Procedures, or refuse to use them at all. Of course, the reality is that people who use them reasonably do exist and are probably in the majority. You just rarely hear them talk about it because I suspect that they hold the same views about Stored Procedures, Constraints and any other DBMS feature as they do with any other software development tool ie. Use the right one for the job.
- glogla 11y ago
  Yeah. Lot of the time, for a CRUD app, the who layer between rendering and data storage doesn't really do much besides validations, and those can be in database, so whole middle layer can be unnecessary. Sometimes, the logic has to be in database, because it is single point of truth and because many application servers are hard to synchronize with regards to "you get max 3 attempts at login" or "you have to have enough balance to do bank transfer". Sometimes, lot of your logic is in database, because database can do a lot of things in really fast and practical way, like aggregation and reporting and various data exploration tasks. Sometimes, the database is just dumb store of object oriented data. It depends on the application.
3pt14159 11y ago
You are speaking from ignorance with the voice of authority. I worked on a rails app that handled a billion requests per day. The problem isn't performance of the web framework, those are easy to load balance and split into C or cache when you need it. The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored procedures would restrain you from doing all three. And I know, I worked on a codebase in 1999 that did this because of the "performance gains". It ended up bricking the project due to inability to iterate.
- tomchristie 11y ago
  "The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored procedures would restrain you from doing all three." Perfectly expressed.
- wslh 11y ago
  If Facebook uses MySQL and PHP there is some truth in the comment.
  - jeletonskelly 11y ago
    To say that Facebook uses PHP and MySQL is to leave out the truth, honestly. They are a part of the stack, yes, but they aren't what makes the application scale to billions of requests. It would be like saying the local coffee shops website using Wordpress with a MySQL backend is using the same tech as Facebook. It's laughable.
    - wslh 11y ago
      They choose MySQL vs a lot of other alternatives for some reason and this reasoning can be applied to your use case. > They are a part of the stack, yes, but they aren't what makes the application scale to billions of requests. These are not just part of the stack, these are critical components within the stack.
    - 11y ago
framp 11y ago
I don't think using stored procedures should be the focus of the project. PostgREST is great because it lets you kickstart a CRUD application with ease. I'm mainly a node.js developer nowadays and I'm using some frameworks to kickstart APIs for my clients - and then I jump in and add features. What I really want is a solution to build a API server which deals with authentication, exposing my models through REST and other boring and repetitive stuff. In this way I don't have to focus on everything, but just on the specific problem I'm solving. I don't think there is a valid solution out there right now. That's why I'm contributing to PostgREST and I hope to see even more features coming out of it (eg: better authentication, maybe with 3rd party logins).
- kaikokaur 11y ago
  You may want to see http://www.zazler.com/ http://www.zazler.com/ or https://www.npmjs.com/package/zazler https://www.npmjs.com/package/zazler
  - framp 11y ago
    I'm using hapi + sequelize or loopback actually. Zazler looks interesting, thanks for the link!
jasonlotito 11y ago
> Why people in the web world don't use stored procedures and constraints is a mystery to me. We do. At least some of us, and honestly, it's not something I think about as being exceptional. I don't always use them, but I much prefer having a nice API of SPs to use rather than having to have custom SQL all over the place. DRY applies to writing queries just as much.
fweespeech 11y ago
> This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. Regardless of the other points people brought up... Sharding a database with stored procedures and constraints as you advise is a nightmare because you now have a completely separate deployment process [deploying stored procedures, if you think this doesn't require a deployment process across a sharded infrastructure...I have no words]. Using an internal web framework is much, much easier than maintaining two separate deployment processes. Especially when one of those processes has to take down nodes to avoid some shards having different stored procedures than other shards.
ecopoesis 11y ago
Because databases are a very poor fit for APIs? This is one of my biggest problems with high holy REST: it generally just means reimplementing your SQL API in HTTP semantics. APIs should be about encapsulating business logic. Databases should be about storing data in a reliable, predictable way.
- benkant 11y ago
  That's what views are for.
psaintla 11y ago
Around 2005 I worked for a fairly large company that did exactly what you're suggesting with Postgresql and it was a complete disaster. Have you ever tried implementing sharding with all of your business logic in stored procedures? Have you ever tried hiring people who understand pl/sql and WANT to work with it? I have done both and it is a nightmare, once you get to the point of having to shard data you end up in one of two places: 1.) The sprocs become insanely complex because they have to be shard aware. 2.) You slowly start moving more of your code that was in sprocs to your application so now you've got two problems. As for hiring, put out an ad for an engineer with pl/sql knowledge better yet put out an ad for someone who wants to learn and use pl/sql. Good luck finding enough of those people to get any significant work done.

by benkant 11y ago

kijin 11y ago

> Why people in the web world don't use stored procedures and constraints is a mystery to me. You can blame MySQL 4.1 for that :( Most people who call themselves "web developers" haven't even heard of PostgreSQL, or even if they've heard of it, have no use for it because their usual clients are stuck with MySQL-only web hosts who have only just managed to upgrade to PHP 5.3.

ozzie80 11y ago
Most people, really?
- chx 11y ago
  Yes. HN is a bubble. There are ~700 PHP questions on SO a day and ~150 node.js. This is just one pair of numbers, you can mine your own whatever you like but you'll realize there are massive amounts of "web developers" with a ... low amount knowledge.
  - adamors 11y ago
    Thinking most web developers are still shipping PHP 5.3 apps on shared hosts is also a very outdated view.
    - kijin 11y ago
      The latest version of WordPress is still compatible with PHP 5.2.4 and above, so anyone who builds a WordPress site is effectively shipping a PHP 5.2 app.
      - spdionis 11y ago
        They do recommend php 5.4 though, and afaik they do try to push the community to upgrading.
        kijin 11y ago
        It still means they can't depend on any feature introduced in PHP 5.3 or later. No closures, no namespaces. Ditto for any theme or plugin that tries to be compatible with all versions of PHP that WordPress itself supports.
    - greghinch 11y ago
      Globally, it's not.
    - _lce0 11y ago
      While I do agree with you, I want to make a distinction between shipping and building an application. IMHO the term "developer" should not be applied to those that can just ship but rather those who can also build. It doesn't matter if they are web, desktop, nor low-systems developer actually Most auto-called web developers are just "web masters"
mark_sz 11y ago
I'm guessing you are not developer, because that kind of comment wouldn't come from someone who's thinking logically. We don't need another flame war here. And yes, most of the web is build on Wordpress/PHP - but you don't need to be a developer to install Wordpress.
- kijin 11y ago
  What does logic have to do with it? I'm just stating what I believe to be a fact: that the majority of web developers in this world never think of PostgreSQL as an option. I don't care whether that's a logical thing for them to think. It's just a fact, whether I like it or not. If you think I'm wrong about the facts, please feel free to open a phone book in any part of the world other than the Bay Area, call up a decent sample of people who self-identify as web developers, and find out what percentage of them have ever heard of, let alone used, PostgreSQL.
  - GrinningFool 11y ago
    So you start off ok here: I'm just stating what I believe to be a fact... But then you move on to say: It's just a fact, whether I like it or not. So which is it? Do you believe it to be a fact, or is it a fact? And if it is, where's your evidence? (I happen to agree with your opinion, but the semantics here bug me.)
    - kijin 11y ago
      Sorry for the loose use of language. Everything that follows the colon after "I believe to be a fact", until the end of that paragraph, is the content of what I believe, including the statememt "It's just a fact." I believe that it's a fact. Anecdotal evidence: I've interacted with dozens of other people who call themselves web developers over the years, and most of them (outside of Silicon Valley) have never used PostgreSQL, nor any advanced features of SQL in any other RDBMS. Objective evidence: the large market share of WordPress, Drupal, and other content management systems that don't use any advanced database features; as well as the large market share of frameworks such as Rails, Django, and Laravel that encourage developers to stick with the ORM and not care about advanced database features.

devnonymous 11y ago

...and it's not just databases. OS capabilities (eg: vm tuning, bumping up the default sysctl limits, etc) are ignored and the problems arising of such disregard are then dealt with by adding layers to the application like distributed caches and other ^scaling^ solutions.

adamors 11y ago

For one, stored procedures are hard to test, debug, maintain and check into source control. But don't let that get in your way of ignorantly generalising about web developers.

bni 11y ago
"hard to test, debug, maintain and check into source control." Why? I have never had problem with any of these. SPs is just imperative code like any other imperative code.
- adamors 11y ago
  Well, you cannot isolate the code, you cannot unit test it, you cannot use a debugger, cannot set breakpoint, have stack traces etc. You are tied to the database at all times. In my experience, every stored procedure that is larger than 2-3 lines is a headache.
  - bni 11y ago
    All of those are no problem with the right tools. Any database IDE can debug SPs. You can unit test SPs like any other code just use a testrunner.
    - glogla 11y ago
      Yeah. I suspect that lot of hate of logic in DB is because of bad Oracle setups many years ago, kind of like lot of people think SQL is useless because MySQL is.
  - arethuza 11y ago
    I'm not a huge fan of stored procedures - but I'm pretty sure you can debug stored procedures in SQL Server pretty easily from Visual Studio - I think you can "step into" the call to SPs while debugging client code.
glogla 11y ago
Stored procedures may be all of those things, but they don't have to - it's just that most of the time, developers don't really care, so they have fancy versioning, deployment and continuous integration for all their code, except for stored procedures. Here is interesting talk about database migrations and stored procedures and unit tests: http://www.pgcon.org/2013/schedule/events/615.en.html http://www.pgcon.org/2013/schedule/events/615.en.html Also, DB procedures are not easy to "debug" in the traditional way, but SQL client is basically the first REPL every programmer becomes familiar with. You can easily step stored procedure by running it's commands one by one, unless it's fancy Oracle forall loop with cursor or something (and the cursor select can still be selected as normal). Also, databases tend to have more strong data types than programming languages in general so putting constraints in DB means, that bad data are not savable in the system.

batou 11y ago

That is quite frankly horse poop. We use stored procedures. Not by choice; this is a legacy we're stuck with. It is nothing but an unadulterated disaster of a technology regardless of what you use it for. I'm talking 45,000 stored procedures here. 2000 tables. TiB of data. 50,000 requests/second across SOAP/web/desktop etc. It's hell. Problems with stored procedures: 1) Performance. More code running in the hard to scale-out black box. You're just hanging yourself with hardware and/or license costs in the long run. 2) Maintenance. All database objects are stateful i.e. they have to be loaded to work. The sheer complexity of managing that on top of the table state results in massive cost. Add to that tooling, version control costs as well. Have you tried merging a stored procedure in a language which has no compiler and very loose verification? 3) Orthoganality. Nothing inside the relational model matches the logical concepts in your application. Think of transactions, domain modelling etc. 4) Duplication. You still have to write something in the application to talk to every single one of those stored procedures and map it to and from parameters and back to collections. 5) Transaction scoping. Do you know how expensive it is to introduce distributed transactions? Well it's a ton more cash when EVERYTHING is inside that black box. 6) Lock in. Your stored procedures aren't portable. Good luck trying to shift vendor in the future when you hit a brick wall. Now I know it's popular to bash on Rails and I wouldn't use it personally but there are people using the same model on top of other platforms, like us. Sorry but databases are just a hole to put your shit in when you want it out of memory. If you start investing too much in all of the specific functionality you're hanging yourself.

spdionis 11y ago
You had me until your last sentence. Database are very important to (and very good at) store your data. And data is important (duh). All the issues you described above are related to working with the data, which should not happen in the database.
- batou 11y ago
  That's a poor argument because we're not discussing the arbitraty boundary of storing versus working with data as the lines between those are very blurred. This is even more the case when you use stored procedures which work with data close to the storage. While we're on this subject, RDBMS are no better at storing data than any other technology out there[1]. In fact when you start thinking abstractly like this, other tech such as Riak makes sense for a lot of workloads. The only real benefits of RDBMS' are fixed schema, fungibility of staff, the ability to issue completely random queries and get a result in a reasonable amount of time and the proliferation of ORMs. [1] Caveated on insane design decisions like MyISAM storage engine and MongoDB as a whole.

ilitirit 11y ago

I find it strange when people adhere to these extremely dogmatic ideas about stored procedures. They appear to be either on one end of the scale or the other, ie. they either put all their logic in to Stored Procedures, or refuse to use them at all. Of course, the reality is that people who use them reasonably do exist and are probably in the majority. You just rarely hear them talk about it because I suspect that they hold the same views about Stored Procedures, Constraints and any other DBMS feature as they do with any other software development tool ie. Use the right one for the job.

glogla 11y ago
Yeah. Lot of the time, for a CRUD app, the who layer between rendering and data storage doesn't really do much besides validations, and those can be in database, so whole middle layer can be unnecessary. Sometimes, the logic has to be in database, because it is single point of truth and because many application servers are hard to synchronize with regards to "you get max 3 attempts at login" or "you have to have enough balance to do bank transfer". Sometimes, lot of your logic is in database, because database can do a lot of things in really fast and practical way, like aggregation and reporting and various data exploration tasks. Sometimes, the database is just dumb store of object oriented data. It depends on the application.

3pt14159 11y ago

You are speaking from ignorance with the voice of authority. I worked on a rails app that handled a billion requests per day. The problem isn't performance of the web framework, those are easy to load balance and split into C or cache when you need it. The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored procedures would restrain you from doing all three. And I know, I worked on a codebase in 1999 that did this because of the "performance gains". It ended up bricking the project due to inability to iterate.

tomchristie 11y ago
"The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored procedures would restrain you from doing all three." Perfectly expressed.
wslh 11y ago
If Facebook uses MySQL and PHP there is some truth in the comment.
- jeletonskelly 11y ago
  To say that Facebook uses PHP and MySQL is to leave out the truth, honestly. They are a part of the stack, yes, but they aren't what makes the application scale to billions of requests. It would be like saying the local coffee shops website using Wordpress with a MySQL backend is using the same tech as Facebook. It's laughable.
  - wslh 11y ago
    They choose MySQL vs a lot of other alternatives for some reason and this reasoning can be applied to your use case. > They are a part of the stack, yes, but they aren't what makes the application scale to billions of requests. These are not just part of the stack, these are critical components within the stack.
  - 11y ago

framp 11y ago

I don't think using stored procedures should be the focus of the project. PostgREST is great because it lets you kickstart a CRUD application with ease. I'm mainly a node.js developer nowadays and I'm using some frameworks to kickstart APIs for my clients - and then I jump in and add features. What I really want is a solution to build a API server which deals with authentication, exposing my models through REST and other boring and repetitive stuff. In this way I don't have to focus on everything, but just on the specific problem I'm solving. I don't think there is a valid solution out there right now. That's why I'm contributing to PostgREST and I hope to see even more features coming out of it (eg: better authentication, maybe with 3rd party logins).

kaikokaur 11y ago
You may want to see http://www.zazler.com/ http://www.zazler.com/ or https://www.npmjs.com/package/zazler https://www.npmjs.com/package/zazler
- framp 11y ago
  I'm using hapi + sequelize or loopback actually. Zazler looks interesting, thanks for the link!

jasonlotito 11y ago

> Why people in the web world don't use stored procedures and constraints is a mystery to me. We do. At least some of us, and honestly, it's not something I think about as being exceptional. I don't always use them, but I much prefer having a nice API of SPs to use rather than having to have custom SQL all over the place. DRY applies to writing queries just as much.

fweespeech 11y ago

> This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. Regardless of the other points people brought up... Sharding a database with stored procedures and constraints as you advise is a nightmare because you now have a completely separate deployment process [deploying stored procedures, if you think this doesn't require a deployment process across a sharded infrastructure...I have no words]. Using an internal web framework is much, much easier than maintaining two separate deployment processes. Especially when one of those processes has to take down nodes to avoid some shards having different stored procedures than other shards.

ecopoesis 11y ago

Because databases are a very poor fit for APIs? This is one of my biggest problems with high holy REST: it generally just means reimplementing your SQL API in HTTP semantics. APIs should be about encapsulating business logic. Databases should be about storing data in a reliable, predictable way.

benkant 11y ago
That's what views are for.

psaintla 11y ago

Around 2005 I worked for a fairly large company that did exactly what you're suggesting with Postgresql and it was a complete disaster. Have you ever tried implementing sharding with all of your business logic in stored procedures? Have you ever tried hiring people who understand pl/sql and WANT to work with it? I have done both and it is a nightmare, once you get to the point of having to shard data you end up in one of two places: 1.) The sprocs become insanely complex because they have to be shard aware. 2.) You slowly start moving more of your code that was in sprocs to your application so now you've got two problems. As for hiring, put out an ad for an engineer with pl/sql knowledge better yet put out an ad for someone who wants to learn and use pl/sql. Good luck finding enough of those people to get any significant work done.