3 ms·

I've actually worked on a large complex postgrest-based backend and the cons are all based on practical considerations imo: - the dev workflow on a db-as-cod

I've actually worked on a large complex postgrest-based backend and the cons are all based on practical considerations imo: - the dev workflow on a db-as-codebase system is less familiar, less well understood, with tooling general several years behind "normal" code work. - branching and deployments similarly are just different in ways it's hard to prepare for, leading to low confidence in the deployed system. - testing and debugging: pgtap has different constraints than normal unit testing, debugging sql functions is tricky and awkward. again the tooling is missing or far behind. - in most profitable applications I've seen, the DB is the single largest cost and the most likely to become a bottleneck you can't loosen by throwing money at servers. having all your logic in there won't *necessarily* make this worse but it certainly won't make it better. DBAs have dealt with all of these things for decades and they have skills and tools and mental models for them. But devs and DBAs practice different disciplines with different goals, and not everything crosses over easily. Engineers working on a system like this from either side will end up acquiring a degree of competence even expertise in the other one. Making them desirable for other employers and difficult to replace. Overall I don't strictly prefer this approach, but it definitely has under appreciated strengths and should probably be used more. It's hard to say how it could end up if more resources were put into actually developing the tooling necessary to back it up.

taffer 4y ago
> having all your logic in there won't necessarily make this worse but it certainly won't make it better. Logic is a very broad term, and as long as you're talking about number crunching / machine learning, I'd agree. But most web or LOB applications have pretty simple logic. According to Michael Stonbraker[1], a typical OLTP DBMS spends only 4% of its processing time doing useful work, which includes any kind of business logic, among other things. The rest is spent on housekeeping tasks such as context switching and transaction management. The more business logic you move out of the database, i.e. to the middle tier, the more roundtrips you need per transaction. During roundtrips, transactions can't do any meaningful work, which means more idle transactions, larger connection pool, more locking, and context switches. In other words, for typical OLTP workloads, each transaction should ideally occur in a single roundtrip, which requires the logic to reside within the DBMS. [1] https://blog.jooq.org/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ https://blog.jooq.org/mit-prof-michael-stonebraker-the-tradi...

taffer 4y ago

> having all your logic in there won't necessarily make this worse but it certainly won't make it better. Logic is a very broad term, and as long as you're talking about number crunching / machine learning, I'd agree. But most web or LOB applications have pretty simple logic. According to Michael Stonbraker[1], a typical OLTP DBMS spends only 4% of its processing time doing useful work, which includes any kind of business logic, among other things. The rest is spent on housekeeping tasks such as context switching and transaction management. The more business logic you move out of the database, i.e. to the middle tier, the more roundtrips you need per transaction. During roundtrips, transactions can't do any meaningful work, which means more idle transactions, larger connection pool, more locking, and context switches. In other words, for typical OLTP workloads, each transaction should ideally occur in a single roundtrip, which requires the logic to reside within the DBMS. [1] https://blog.jooq.org/mit-prof-michael-stonebraker-the-traditional-rdbms-wisdom-is-all-wrong/ https://blog.jooq.org/mit-prof-michael-stonebraker-the-tradi...