4 ms·

Author here. > If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language

Author here. > If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. Yeah, but I don't consider this a bad thing. IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. > If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else... I'm not sure I follow. The application I'm using this in has > 1 billion rows and we frequently re-compute the state of all our entities across the entire data set in batches after we make changes to our logic. Having the code that does this in the database avoids having to move large amounts of data between our db and the application.

bluejekyll 9y ago
> IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. Very likely true. But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this. > The application I'm using this in has > 1 billion rows and we frequently re-compute the state of all our entities across the entire data set in batches after we make changes to our logic. That is very impressive. Mind sharing the rate of change across all of those rows? Also, I'm curious. Is this all that DB instance does? Or is it responsible for other data as well?
- cwyers 9y ago
  The only thing worse than paying through the nose for Oracle is paying through the nose for Oracle and treating it like a really expensive version of MySQL/Postgres. If you're paying for it, might as well get some value for your money and use those features.
- felixge 9y ago
  > But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this. I hear you. Picking a database is major decision and shouldn't be made lightly. That being said, I hear a lot of companies are successfully migrating from Oracle to Postgres these days. And in fact, this application was actually migrated from Couchbase to Postgres, but that's a story for another day perhaps :). > That is very impressive. Mind sharing the rate of change across all of those rows? Nowadays its about 5 million inserts / day with peak rates of 150 inserts / sec. Not too crazy, but it adds up over time :). > Also, I'm curious. Is this all that DB instance does? Or is it responsible for other data as well? It does a few other things, but this is the main workload.
cryptonector 9y ago
Well, this is what the SQL standard is for: to reduce the amount of work to be done in migrating between databases. That said, I much prefer to put all this logic into SQL, and to use PostgreSQL, than the alternatives. There's any number of reasons for this: - direct access to the DB is not dangerous if the logic needed to keep it consistent... is in the DB - you don't have to replicate this logic if you add new front-ends - SQL is very expressive, so you'll end up having less code using SQL than anything else -- this also means that RDBMS migration costs need not be as high as one might think, since there will be less SQL than the equivalent for the front-end - updating business logic is easier to do atomically
- felixge 9y ago
  Yeah, the idea of having a SQL standard is great. But in practice a "standard" is somewhat meaningless without rigorous compliance testing, and unfortunately SQL hasn't had this for 20 years [1]. But I'll take SQL over any of poor reinvention of relational algebra any day, so it's still better than the alternatives by far :). [1] http://www.techrepublic.com/article/is-sql-a-standard-anymore/ http://www.techrepublic.com/article/is-sql-a-standard-anymor...
jerrysievert 9y ago
I might add that it's also fairly easy these days to unit test in the database itself, especially in pg, so moving logic down is a perfectly acceptable practice.

bluejekyll 9y ago

> IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. Very likely true. But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this. > The application I'm using this in has > 1 billion rows and we frequently re-compute the state of all our entities across the entire data set in batches after we make changes to our logic. That is very impressive. Mind sharing the rate of change across all of those rows? Also, I'm curious. Is this all that DB instance does? Or is it responsible for other data as well?

cwyers 9y ago
The only thing worse than paying through the nose for Oracle is paying through the nose for Oracle and treating it like a really expensive version of MySQL/Postgres. If you're paying for it, might as well get some value for your money and use those features.
felixge 9y ago
> But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this. I hear you. Picking a database is major decision and shouldn't be made lightly. That being said, I hear a lot of companies are successfully migrating from Oracle to Postgres these days. And in fact, this application was actually migrated from Couchbase to Postgres, but that's a story for another day perhaps :). > That is very impressive. Mind sharing the rate of change across all of those rows? Nowadays its about 5 million inserts / day with peak rates of 150 inserts / sec. Not too crazy, but it adds up over time :). > Also, I'm curious. Is this all that DB instance does? Or is it responsible for other data as well? It does a few other things, but this is the main workload.

cryptonector 9y ago

Well, this is what the SQL standard is for: to reduce the amount of work to be done in migrating between databases. That said, I much prefer to put all this logic into SQL, and to use PostgreSQL, than the alternatives. There's any number of reasons for this: - direct access to the DB is not dangerous if the logic needed to keep it consistent... is in the DB - you don't have to replicate this logic if you add new front-ends - SQL is very expressive, so you'll end up having less code using SQL than anything else -- this also means that RDBMS migration costs need not be as high as one might think, since there will be less SQL than the equivalent for the front-end - updating business logic is easier to do atomically

felixge 9y ago
Yeah, the idea of having a SQL standard is great. But in practice a "standard" is somewhat meaningless without rigorous compliance testing, and unfortunately SQL hasn't had this for 20 years [1]. But I'll take SQL over any of poor reinvention of relational algebra any day, so it's still better than the alternatives by far :). [1] http://www.techrepublic.com/article/is-sql-a-standard-anymore/ http://www.techrepublic.com/article/is-sql-a-standard-anymor...

jerrysievert 9y ago

I might add that it's also fairly easy these days to unit test in the database itself, especially in pg, so moving logic down is a perfectly acceptable practice.