10 ms·

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the w

by makach 7y ago

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.

ako 7y ago
No, putting data logic in a service is putting logic in the wrong layer. Data logic should live in the data layer.
perlgeek 7y ago
It's super helpful for database migrations. Say you suddenly don't have data for a column anymore, because the API that you read from has stopped providing it. Now you can add a computed column that uses a heuristic as an interim until you have time to remove all the reads from that column. Or say that you have an object with a separate display name, and product management decides that the display name now can't be chosen freely anymore, but must be generated from name and title. A computed column can do that trick pretty well.
magicalhippo 7y ago
It's great for speeding up queries. Optimizations frequently require giving up on absolute purity. We use computed columns a lot for speeding up specific, frequently used queries. One example is where we need to store a special identifier code. The identifier code is required by law to have a certain format, and part of it is the date the code was generated. Users frequently want to view items with codes generated on a specific day. For making presentation and reporting easier, as well as significantly faster, we added a computed date column that extracts the date from the code and indexed it. Keeping an extra field in sync in code would inevitably have lead to bugs, especially as we have several different codebases which could update the id code.
pnako 7y ago
If your database contains objects like "customer ID", "address" and the like, you already have business considerations (if not logic) inside it. In fact, businesses using databases for business stuff is probably 95% of DB use cases. If it's something that's not application-specific and fits nicely into the query language (like in the example given) it makes sense to have it inside the DB, because for queries involving sorting, limiting, etc. you can do all that stuff inside the DB and return the few values that interest you, instead of transferring all the data and having to write code to do that inside your app.
- eternalban 7y ago
  Arguably, a robust RDBMS with user defined functions (such as PostgreSQL) are the ideal place for core domain semantics. What is missing is a modern tool chain that addresses the painpoints of such a development model.
dragonwriter 7y ago
> But after contemplating it, is this really necessary? No, you could always do the materialized equivalent via triggers, so it's not necessary for correctness. And since this feature is limited to the materialized form, too, it doesn't offer much change other than simpler expression of what is going on (which, to be fair, is a big win.) > I fear for putting business logic and meanings into the wrong the layer If you don't have some mechanism for calculated columns, you are forced to put domain logic that belongs in the DB in the app layer, which is especially problematic in the (increasingly out of fashion, apparently) circumstance where there are multiple consumers of a DB, as it promotes inconsistency.
- petereisentraut 7y ago
  This is also much faster than the equivalent using a PL/pgSQL trigger.
stupidcar 7y ago
Why do you think placing business logic into the database system is a layering violation? Just because most developers use their DB as a dumb store doesn't mean it needs to be. There are also plenty of successful software systems that place the majority of their business logic and use a generic programming language and runtime only for the presentation layer. If you're comfortable fully exploiting the capabilities of your DB, then the intelligent combination of a relational model with custom data types, constraints, triggers, views and stored procedures can make the DB the perfect place to implement business logic.
- tyingq 7y ago
  It could just be something more pedestrian like not yet being able to handle database changes well for deployments. Things like blue/green, canary, reverting, etc. It's a bit of work to get that functioning well.
- another-one-off 7y ago
  All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. If it doesn't make sense to compartmentalise data and logic, why compartmentalise anywhere? Do the whole project in one big file. Of all the surprises a project is going to face 'oh, this data is useful for [new thing]' is one of the most likely. And everyone expects to find a boundary drawn there because it is such an obvious place to draw one; so it saves on confusion. Putting complex business logic in the database is opening up all sorts of interesting new ways for data to be unavailable, corrupted or complicated to access. It is easy to imagine it working out well for simple requirements where there just needs to be something that works. PostgreSQL is a piece of software that takes data and enforces the relational data model on it. Great idea. But the relational model of data is really only tuned to relational algebra. Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL. Relational databases already have great integration with every other programming language in current use.
  - taffer 7y ago
    > All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data. > Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL. Say you wrote your code in PHP, now you want to migrate to Ruby or NodeJS. You can't, you have to rewrite everything. How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.
philshem 7y ago
As an analyst, either for reporting or machine learning tasks, it scratches my itch. I'm often creating wide, flat views with extended attributes. Many columns are nearly identical, for example, year as an integer, year a date type, etc. This is trivial with views, but ... For fast query performance, I usually materialize those views. But that's a multi-step and often manual process, requiring schedules or triggers. This feature combines both of those worlds, with fewer total database objects. Especially with ALTER TABLE.
asah 7y ago
Putting business logic in the database has been a boon to large companies for decades - while the development process can be trickier, it helps ensure that every team is using the same logic (incl version) to access the data. With PostgreSQL, you can define business logic in everyday languages, including JavaScript (plv8).
scarlac 7y ago
I agree that it should be used sparingly. But for more than 5 years I've wished I had a computed column feature because you are often thrown into code bases that need refactoring and refactoring 100% in 1 commit is not always the safest way to go. Sometimes you want to migrate a codebase in several steps. Computed/virtual columns are an absolutely fantastic feature and it allows refactoring and migrations at your own pace, or as a fallback.
michaelper22 7y ago
Here's one of my current use cases: We have a folder tree table in our (formerly nested sets, then closure table, now materialized path), and we cache a materialized version of the path with the folder names. There are some places in the app where you can look up a node by its full path, and this column should conceptually be unique. Obviously an unindexed VARCHAR(2048) is bad to filter on, but MySQL indexes can only cover the first 767 bytes (we used utf8mb4). So we have another column, PathHash CHAR(40) AS (SHA1(path)) VIRTUAL, and then a unique index on that.
dahdum 7y ago
You’d hate my work, we shoehorn everything we can into the database, with a nice simple API layer of versioned stored procedures in front. It’s easy to test and enforce the business logic, and then it can be easily utilized across web, mobile apps, custom apps, and third parties. Works great if you’re sure you won’t ever need to scale huge horizontally. One of my RoR apps connects to a schema of views, with instead of triggers calling procedures for updates. Something like 30 normalized tables are rolled up into 5 denormalized views, leaving hardly any ActiveRecord woes and still excellent performance.

by makach 7y ago

ako 7y ago

No, putting data logic in a service is putting logic in the wrong layer. Data logic should live in the data layer.

perlgeek 7y ago

It's super helpful for database migrations. Say you suddenly don't have data for a column anymore, because the API that you read from has stopped providing it. Now you can add a computed column that uses a heuristic as an interim until you have time to remove all the reads from that column. Or say that you have an object with a separate display name, and product management decides that the display name now can't be chosen freely anymore, but must be generated from name and title. A computed column can do that trick pretty well.

magicalhippo 7y ago

It's great for speeding up queries. Optimizations frequently require giving up on absolute purity. We use computed columns a lot for speeding up specific, frequently used queries. One example is where we need to store a special identifier code. The identifier code is required by law to have a certain format, and part of it is the date the code was generated. Users frequently want to view items with codes generated on a specific day. For making presentation and reporting easier, as well as significantly faster, we added a computed date column that extracts the date from the code and indexed it. Keeping an extra field in sync in code would inevitably have lead to bugs, especially as we have several different codebases which could update the id code.

pnako 7y ago

If your database contains objects like "customer ID", "address" and the like, you already have business considerations (if not logic) inside it. In fact, businesses using databases for business stuff is probably 95% of DB use cases. If it's something that's not application-specific and fits nicely into the query language (like in the example given) it makes sense to have it inside the DB, because for queries involving sorting, limiting, etc. you can do all that stuff inside the DB and return the few values that interest you, instead of transferring all the data and having to write code to do that inside your app.

eternalban 7y ago
Arguably, a robust RDBMS with user defined functions (such as PostgreSQL) are the ideal place for core domain semantics. What is missing is a modern tool chain that addresses the painpoints of such a development model.

dragonwriter 7y ago

> But after contemplating it, is this really necessary? No, you could always do the materialized equivalent via triggers, so it's not necessary for correctness. And since this feature is limited to the materialized form, too, it doesn't offer much change other than simpler expression of what is going on (which, to be fair, is a big win.) > I fear for putting business logic and meanings into the wrong the layer If you don't have some mechanism for calculated columns, you are forced to put domain logic that belongs in the DB in the app layer, which is especially problematic in the (increasingly out of fashion, apparently) circumstance where there are multiple consumers of a DB, as it promotes inconsistency.

petereisentraut 7y ago
This is also much faster than the equivalent using a PL/pgSQL trigger.

stupidcar 7y ago

Why do you think placing business logic into the database system is a layering violation? Just because most developers use their DB as a dumb store doesn't mean it needs to be. There are also plenty of successful software systems that place the majority of their business logic and use a generic programming language and runtime only for the presentation layer. If you're comfortable fully exploiting the capabilities of your DB, then the intelligent combination of a relational model with custom data types, constraints, triggers, views and stored procedures can make the DB the perfect place to implement business logic.

tyingq 7y ago
It could just be something more pedestrian like not yet being able to handle database changes well for deployments. Things like blue/green, canary, reverting, etc. It's a bit of work to get that functioning well.
another-one-off 7y ago
All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. If it doesn't make sense to compartmentalise data and logic, why compartmentalise anywhere? Do the whole project in one big file. Of all the surprises a project is going to face 'oh, this data is useful for [new thing]' is one of the most likely. And everyone expects to find a boundary drawn there because it is such an obvious place to draw one; so it saves on confusion. Putting complex business logic in the database is opening up all sorts of interesting new ways for data to be unavailable, corrupted or complicated to access. It is easy to imagine it working out well for simple requirements where there just needs to be something that works. PostgreSQL is a piece of software that takes data and enforces the relational data model on it. Great idea. But the relational model of data is really only tuned to relational algebra. Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL. Relational databases already have great integration with every other programming language in current use.
- taffer 7y ago
  > All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data. > Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL. Say you wrote your code in PHP, now you want to migrate to Ruby or NodeJS. You can't, you have to rewrite everything. How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.

philshem 7y ago

As an analyst, either for reporting or machine learning tasks, it scratches my itch. I'm often creating wide, flat views with extended attributes. Many columns are nearly identical, for example, year as an integer, year a date type, etc. This is trivial with views, but ... For fast query performance, I usually materialize those views. But that's a multi-step and often manual process, requiring schedules or triggers. This feature combines both of those worlds, with fewer total database objects. Especially with ALTER TABLE.

asah 7y ago

Putting business logic in the database has been a boon to large companies for decades - while the development process can be trickier, it helps ensure that every team is using the same logic (incl version) to access the data. With PostgreSQL, you can define business logic in everyday languages, including JavaScript (plv8).

scarlac 7y ago

I agree that it should be used sparingly. But for more than 5 years I've wished I had a computed column feature because you are often thrown into code bases that need refactoring and refactoring 100% in 1 commit is not always the safest way to go. Sometimes you want to migrate a codebase in several steps. Computed/virtual columns are an absolutely fantastic feature and it allows refactoring and migrations at your own pace, or as a fallback.

michaelper22 7y ago

Here's one of my current use cases: We have a folder tree table in our (formerly nested sets, then closure table, now materialized path), and we cache a materialized version of the path with the folder names. There are some places in the app where you can look up a node by its full path, and this column should conceptually be unique. Obviously an unindexed VARCHAR(2048) is bad to filter on, but MySQL indexes can only cover the first 767 bytes (we used utf8mb4). So we have another column, PathHash CHAR(40) AS (SHA1(path)) VIRTUAL, and then a unique index on that.

dahdum 7y ago

You’d hate my work, we shoehorn everything we can into the database, with a nice simple API layer of versioned stored procedures in front. It’s easy to test and enforce the business logic, and then it can be easily utilized across web, mobile apps, custom apps, and third parties. Works great if you’re sure you won’t ever need to scale huge horizontally. One of my RoR apps connects to a schema of views, with instead of triggers calling procedures for updates. Something like 30 normalized tables are rolled up into 5 denormalized views, leaving hardly any ActiveRecord woes and still excellent performance.