14 ms·

I think VoltDB could be huge, but the utter reliance I stored procedures is misguided in my opinion. The reasoning behind it is really to optimize database acce

by batbomb 13y ago

I think VoltDB could be huge, but the utter reliance I stored procedures is misguided in my opinion. The reasoning behind it is really to optimize database access at the database level, including targeting features like JIT compilation and leveraging zero-copy, but it's so hard to get this all to work right, unit test, debug, etc... On the other hand, his "NewSQL" approach can probably become a good replacement for many use cases where MongoDB would otherwise be used.

falcolas 13y ago
I have a separate problem with stored procedures - it's pushing more and more work into the single hardest piece of software to scale - your database.
- jhugg 13y ago
  Apologies for the curt response, but VoltDB is an existence proof that this isn't necessarily true.
- gaius 13y ago
  It depends what you mean by scale. A serious corporate database might have a hundred applications connecting to it, written in a dozen different languages, and the guys who wrote the oldest one retired before the current guys were out of college. How many times do you want to re-implement the same validation logic? How many apps do you want to have to change and test when something in the business changes? How many great engineers can you hire when you mention that legacy maintenance is a big part of the job? For the 99.999% of people who will never need to worry about Google or Facebook's "scale", stored procedures are absolutely the only sane way to build large systems.
- y0ghur7_xxx 13y ago
  > pushing more and more work into the single hardest piece of software to scale - your database. I never understood this argument. Maybe you can elaborate a bit? Almost all our apps use sp for data lifting work (most of the so called "business logic"), and we never got scaling problems. Well, we don't have that much users or data either, but how many users/data do you have that your stored procs could not keep up with? What numbers are we talking about? What makes the db slow is throwing bad sql at it, not Stored Procs.
  - falcolas 13y ago
    Terabytes and 5-6 thousand queries per second. > What makes the db slow is throwing bad sql at it, not Stored Procs Absolute statements are absolutely wrong. :) Particularly with DBs like PostgreSQL where you can (and people do) run Python scripts as stored procs. Great way to slow down your DB.
    - y0ghur7_xxx 13y ago
      > Terabytes and 5-6 thousand queries per second. That is not much info :) Just for reference: a simple query by pk index on my veryveryvery low end pc¹ on a table with 1mil rows takes about 0.04 ms (so says pgsql analyze). That is 25000 queries per second. What type of app are we talking here where the db needs to scale? Something like reddit? Stackoverflow? Just curious. Never saw such a beast where a DB server needs that much power. Where I work we have 200 apps on the same DB server, and it's idling most of the time. ¹i5 CPU 2.40GHz with 2GB of RAM > run Python scripts as stored procs. Great way to slow down your DB. There is more than one way to shoot yourself in the foot :)
jhugg 13y ago
VoltDB currently requires stored procedures for multi-statement transactions, but you can send single-statement ad-hoc queries to it just fine (JDBC/Native Clients/HTTP-JSON). Our SQL has gotten significantly more sophisticated lately and many kinds of interesting queries and updates can be performed with one statement. Stored procs are used to allow rich transactions without using client-side transaction control. For all of the concerns about stored procs (of which some are valid), the fundamental issue is that external transaction control is performance's nemesis. It's not a coincidence that few No/NewSQL systems support external transaction control, and those that do don't publish many benchmarks using that feature.
- batbomb 13y ago
  I suppose it depends heavily on application, but I don't think external transaction control is necessarily tied to performance. However, that being said, it's usually up to the user to come up with some sort of managed transaction on the application/middleware side which usually needs to be coupled with some sort of database locking mechanism. In reality, that's really hard to get right, even if you "get" multithreading. Again, I think VoltDB is going places. I haven't been able to actually dig into it personally due to time constraints, and when I realized Command Logging was only available in the Enterprise Edition that effectively killed a few use cases for me. One of the uses I hope to investigate is to use it as a nice materialized-view playground for bridging data on-demand from various databases, in coordination with Presto: https://www.facebook.com/notes/facebook-engineering/presto-interacting-with-petabytes-of-data-at-facebook/10151786197628920 https://www.facebook.com/notes/facebook-engineering/presto-i... In any case, it's a shame it's rarely discussed on HN.

by batbomb 13y ago

falcolas 13y ago

I have a separate problem with stored procedures - it's pushing more and more work into the single hardest piece of software to scale - your database.

jhugg 13y ago
Apologies for the curt response, but VoltDB is an existence proof that this isn't necessarily true.
gaius 13y ago
It depends what you mean by scale. A serious corporate database might have a hundred applications connecting to it, written in a dozen different languages, and the guys who wrote the oldest one retired before the current guys were out of college. How many times do you want to re-implement the same validation logic? How many apps do you want to have to change and test when something in the business changes? How many great engineers can you hire when you mention that legacy maintenance is a big part of the job? For the 99.999% of people who will never need to worry about Google or Facebook's "scale", stored procedures are absolutely the only sane way to build large systems.
y0ghur7_xxx 13y ago
> pushing more and more work into the single hardest piece of software to scale - your database. I never understood this argument. Maybe you can elaborate a bit? Almost all our apps use sp for data lifting work (most of the so called "business logic"), and we never got scaling problems. Well, we don't have that much users or data either, but how many users/data do you have that your stored procs could not keep up with? What numbers are we talking about? What makes the db slow is throwing bad sql at it, not Stored Procs.
- falcolas 13y ago
  Terabytes and 5-6 thousand queries per second. > What makes the db slow is throwing bad sql at it, not Stored Procs Absolute statements are absolutely wrong. :) Particularly with DBs like PostgreSQL where you can (and people do) run Python scripts as stored procs. Great way to slow down your DB.
  - y0ghur7_xxx 13y ago
    > Terabytes and 5-6 thousand queries per second. That is not much info :) Just for reference: a simple query by pk index on my veryveryvery low end pc¹ on a table with 1mil rows takes about 0.04 ms (so says pgsql analyze). That is 25000 queries per second. What type of app are we talking here where the db needs to scale? Something like reddit? Stackoverflow? Just curious. Never saw such a beast where a DB server needs that much power. Where I work we have 200 apps on the same DB server, and it's idling most of the time. ¹i5 CPU 2.40GHz with 2GB of RAM > run Python scripts as stored procs. Great way to slow down your DB. There is more than one way to shoot yourself in the foot :)

jhugg 13y ago

VoltDB currently requires stored procedures for multi-statement transactions, but you can send single-statement ad-hoc queries to it just fine (JDBC/Native Clients/HTTP-JSON). Our SQL has gotten significantly more sophisticated lately and many kinds of interesting queries and updates can be performed with one statement. Stored procs are used to allow rich transactions without using client-side transaction control. For all of the concerns about stored procs (of which some are valid), the fundamental issue is that external transaction control is performance's nemesis. It's not a coincidence that few No/NewSQL systems support external transaction control, and those that do don't publish many benchmarks using that feature.

batbomb 13y ago
I suppose it depends heavily on application, but I don't think external transaction control is necessarily tied to performance. However, that being said, it's usually up to the user to come up with some sort of managed transaction on the application/middleware side which usually needs to be coupled with some sort of database locking mechanism. In reality, that's really hard to get right, even if you "get" multithreading. Again, I think VoltDB is going places. I haven't been able to actually dig into it personally due to time constraints, and when I realized Command Logging was only available in the Enterprise Edition that effectively killed a few use cases for me. One of the uses I hope to investigate is to use it as a nice materialized-view playground for bridging data on-demand from various databases, in coordination with Presto: https://www.facebook.com/notes/facebook-engineering/presto-interacting-with-petabytes-of-data-at-facebook/10151786197628920 https://www.facebook.com/notes/facebook-engineering/presto-i... In any case, it's a shame it's rarely discussed on HN.