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?
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.
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
[1] http://www.techrepublic.com/article/is-sql-a-standard-anymor...
> 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.