> - Let's say we want to change the database from postgres to Oracle in the future. How do I go about doing it?
If by “database” you mean “storage backend” (either in whole or in part), then the answer is oracle_fdw.
If you mean the API implementation, then, just as if you wanted to use a different language/platform when it wasn't implemented via Postgres originally, it's a complete reimplementation of that layer.
But, really, changing DBs isn't a root need, it's a solution, and unless we know the actual problem, we can't determine a solution (and “switch DBs to Oracle” likely isn't the best solution.
> How about complex logic that needs to be done in a declarative language such as SQL?
Complex logic is often more clearly expressed in a declarative language.
OTOH, to the extent there is a need for procedural/imperative logic, Postgres supports a variety of procedural languages, including Python.
> How about version control and updating views, with traceability?
There are a number of variations on approaches for this with database schemas in devops pipelines. Whether your schema is just base tables or includes views/triggers/etc. doesn't really make any difference here.
> Do you think SQL is more readable for logic code than say Python?
I think most appropriate of SQL, pl/pgSQL, and Python for each component is more readable than just-Python
> You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.
That’s...exactly what views were designed for. For a long time the implementations in most RDBMSs weren't fairly limited, but that's not really the case now.
> This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.
Honestly, LOB apps in an enterprise environment is probably where this approach is most valuable. It might not be right for your core application in a startup where you are aiming for hockey stick growth. At least, from the complaints I've heard about horizontally scaling Postgres, I'd assume that.
I love this book and it explicitly addresses a lot of painpoints in designing complex systems. For example, business counterparts might request writing to CSV instead of database. You want to decouple domain models from RDBMS.
If by “database” you mean “storage backend” (either in whole or in part), then the answer is oracle_fdw.
If you mean the API implementation, then, just as if you wanted to use a different language/platform when it wasn't implemented via Postgres originally, it's a complete reimplementation of that layer.
But, really, changing DBs isn't a root need, it's a solution, and unless we know the actual problem, we can't determine a solution (and “switch DBs to Oracle” likely isn't the best solution.
> How about complex logic that needs to be done in a declarative language such as SQL?
Complex logic is often more clearly expressed in a declarative language.
OTOH, to the extent there is a need for procedural/imperative logic, Postgres supports a variety of procedural languages, including Python.
> How about version control and updating views, with traceability?
There are a number of variations on approaches for this with database schemas in devops pipelines. Whether your schema is just base tables or includes views/triggers/etc. doesn't really make any difference here.
> Do you think SQL is more readable for logic code than say Python?
I think most appropriate of SQL, pl/pgSQL, and Python for each component is more readable than just-Python
> You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.
That’s...exactly what views were designed for. For a long time the implementations in most RDBMSs weren't fairly limited, but that's not really the case now.
> This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.
Honestly, LOB apps in an enterprise environment is probably where this approach is most valuable. It might not be right for your core application in a startup where you are aiming for hockey stick growth. At least, from the complaints I've heard about horizontally scaling Postgres, I'd assume that.