17 ms·

New In Postgres 12: Generated Columns

516 points by craigkerstiens 7y ago

ako 7y ago
Seems like a slightly more convenient way than using a trigger to achieve the same...
aidos 7y ago
This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query. It’s doable, but the onus is on everyone who queries to understand this.
- ddorian43 7y ago
  Make a function ?
  - aidos 7y ago
    Do you mean an application layer function, or a dB one? And if a dB one, you also need to create an index to go with it. And then there are dragons that you don’t need in life. If an application one, then people need to know to call it, so, yeah. I know there are things you can do, but having better tools available is always a win (especially when they remove later maintenance from the equation).
    - ddorian43 7y ago
      In DB. You can keep it with migrations. You can have a functional index without creating a new column.
  - ako 7y ago
    Make a trigger
    - grzm 7y ago
      From the article: > ” Such functionality was earlier usually achieved with triggers, but with generated columns this becomes much more elegant and cleaner.”
      - ako 7y ago
        "much more elegant and cleaner" is a bit of an overstatement in my opinion...
        grzm 7y ago
        Sounds like the generated column implementation is faster than a PL/pgSQL trigger. https://news.ycombinator.com/item?id=21134706 https://news.ycombinator.com/item?id=21134706 I don’t have a link at hand from a discussion on the mailing list, but I’ll take Peter’s word for it.
        mobilemidget 7y ago
        I miss the why its more elegant and cleaner too. "Indexes: Generated columns can be used in indexes, but cannot be used as a partition key for partitioned tables." I also hope it can be used for a partition key in the future.
- AhtiK 7y ago
  This JSON plucking is also much easier now with the json path expressions coming up in v12!
  - deleted 7y ago
    [deleted]
  - rattray 7y ago
    Can anyone share a link?
    - onion2k 7y ago
      https://www.postgresql.org/docs/12/functions-json.html https://www.postgresql.org/docs/12/functions-json.html
- jitans 7y ago
  Why your application doesn't use a view? Or even better a table function?
  - icebraining 7y ago
    At least in PG, a regular view must calculate everything at read time, which might mean a lot of duplicated calculations, and a materialized view has to be refreshed "manually", it doesn't auto-update piecemeal when the underlying data changes.
    - durkie 7y ago
      I've had pretty good luck with using table triggers to update materialized views and make everything "automatic". A little more work up front, but pretty easy to forget about it once it's in place.
      - icebraining 7y ago
        A REFRESH of a materialized view still requires full re-computation of all values, no? It's better than regular views if you have more reads than writes, but still quite wasteful.
        pmart123 7y ago
        I believe so. I would say it’s more useful for rollups or aggregations where real-time isn’t necessary.
        durkie 7y ago
        You are right. I've been dealing with this personally with PostGIS and storing large geometries. Ideally you split the geometries up in to many smaller geometries (using a built-in function), and it's much faster to calculate intersections, contained lengths, etc using those instead. Many places online recommend storing this collection of divided geometries as a materialized view, but I recently had to move it to a separate real table because inserting a single new record would take 15 minutes to update the view (on an RDS 4xlarge database). It could at least update concurrently, so other reads didn't block, but now that the the divided geometries are stored in a separate table I can add new records in under 5 seconds usually.
- purplerabbit 7y ago
  I've been excited about this idea of json-blob-field-to-computed-column for a solid year... I'm so stoked that it's coming to fruition! Relating json/xml API response blobs to queryable columns is a huge and inefficient burden which I hope that these featuers can help to alleviate.
tirumaraiselvan 7y ago
Why do they call it Generated Columns and not Computed Columns like most other databases already do?
- t0astbread 7y ago
  I guess every column is "computed" if you think about it? It's nitpicky and I don't know the official reason though.
- PeterisP 7y ago
  IMHO "computed columns" generally implies columns that are computed on-demand when read, as opposed to this case when they're computed when the row is altered and persisted in the table.
- fowl2 7y ago
  Possibly because they're closer to "persisted calculated columns".
yourad_io 7y ago
I wonder if a generated column can be referenced as a foreign key. Weirdness wouod probably ensue, so I'd guess... No?
- mslot 7y ago
  Yes. A generated column can be included in a foreign key and be referenced by a foreign key. Nothing too weird happens. It's basically like having every update and insert specify the column value.
Erwin 7y ago
An alternate way of doing this is to pass the entire current table row to a function which can be done easily: if you have a table "purchase" PG also creates a "purchase" type, so if you have this function: CREATE FUNCTION vat(a purchase) RETURNS numeric AS 'SELECT a.value * .25' LANGUAGE 'sql' Then you can run: SELECT value, vat(purchase.*) FROM purchase; And so be able to use every purchase column within the SQL function to do your calculation. There are two interesting shortcuts: You can call just vat(purchase) because the type is an alias for the current table row. That alias is very confusing and this is not recommended (try select table from table!) There's also a method like shortcut which the documentation calls "functional notation": SELECT value, purchase.vat FROM purchase; This calls your VAT() function passing it the entire purchase row, see https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-USAGE https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-US... I don't actually know if PG can correctly inline the necessary code in here -- it is better able to do it if you use "SQL" function certainly.
- philliphaydon 7y ago
  This isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday) You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is stored + a period of time which it must be completed by. Calculating that column in sql before doing a where filter is crazy slow when you’re looking at millions and millions of records. But pre-calculating it and storing it, and then adding an index on top of it, is insanely fast. This is currently done in code. But if I moved this to a computed column then I can Ensure the result is always up to date if the period changes and avoid code being written to accidentally forget to update this value. There are use cases for computer columns. As there are for functions. And doing it in code. This feature in pg12 mainly gives us the ability to index the value which we couldn’t do before.
  - taffer 7y ago
    I'm not sure if I understand you correctly, but logically the WHERE clause happens before the SELECT clause[1], so it only calculates this value for the rows you're interested in. It is also possible to index functions without generated columns. [1] https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
redact207 7y ago
As a developer I always hated this feature in other rdbms. The idea of your app saving a row and retrieving it only to have more data in it. IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing. It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions. Perhaps there are legitimate uses of this, maybe for DBs that aren't just repositories for apps?
- Ididntdothis 7y ago
  Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
  - luismedel 7y ago
    I'm not a db-engine expert, but if I'd have to write this functionality myself, I'd perform the computation only when any of the involved columns change, not on every read. EDIT: sorry, I read your comment on the opposite way. I see we are saying the exact same thing :)
  - redact207 7y ago
    Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
    - filleduchaos 7y ago
      And if another consumer of the DB doesn't do the calculation or, even worse, does the wrong calculation?
    - baq 7y ago
      what if you have 50 apps talking to the same DB?
    - yourad_io 7y ago
      So long as everyone, everywhere, always remembers to calculate it in the same way for all inserts and updates. This seems a lot more robust to me.
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.
Ididntdothis 7y ago
I tend to follow the Postgres releases and I am always impressed by the cool things SQL databases can do. But for programmers like me who are used to code in C++/C# I always find the transition from these languages to SQL too harsh. Especially if you don’t have to do SQL daily it’s really hard to remember the syntax and read complex SQL code. Also the transition from SQL results to typed languages is tedious. ORMs help a little but they have their own set of problems. Not sure what I am trying to say other than that I wish it was easier for regular programmers to use advanced SQL features...
- magicalhippo 7y ago
  SQL is a bit verbose at times, but it's not much different from going to other programming languages. In fact I'd say it's easier given that it's so verbose. I have no idea what all those modifier symbols do in Rust, say, but I find "case when InvoiceNo is null then" or "select Name, list(distinct Title) as Titles from" pretty transparent in comparison. The biggest mental difference I think is that you're dealing with a different data model. You don't have a bunch of individual entities floating around. You got rows in tables, which you mostly process in bulk. As such it's more like an Excel spreadsheet.
  - pizza234 7y ago
    What specifically do you find verbose? The only thing I find redundant (based on the queries I handle) is that theoretically join arguments could be deduced from foreign keys (maybe extending this behavior, having joins automatically deduced). I also wonder if the perceived verbosity is caused by some programmers cramming logic into the queries, rather than keeping them simple and processing the results in the client language.
    - magicalhippo 7y ago
      It's verbose compared to a lot of other languages simply by using words rather than symbols or abbreviations for most things. Though I tried to convey that I did not think this was a bad thing. The only case where things get really verbose for me is when I need to do subqueries for columns. Often I might need to run basically the same subquery but return aggregates over different fields, ie sum of net weight, sum of gross weight, sum of value. For performance reasons I can't write that as a join, at least on our SQL server. I agree there should be a shortcut syntax for joining using foreign keys ("join X on foreign key"?) though.
hestefisk 7y ago
This is really nice. Acts almost as a materialised view.
- yourad_io 7y ago
  I think those have to be explicitly refreshed, no?
  - yourad_io 7y ago
    Ah - they're are two types of generated columns > There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). https://www.postgresql.org/docs/12/ddl-generated-columns.html https://www.postgresql.org/docs/12/ddl-generated-columns.htm...
Mister_Snuggles 7y ago
The first database I used professionally was called UniVerse[0], currently owned by Rocket Software. It's a Pick-style, non-relational database. In the data dictionary for a file you could create I-descriptors, which were computed columns much like this feature allows. The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. An INVOICES file, for example, would have fields like CUSTOMER.NAME, CUSTOMER.ADDRESS, etc, (note that the '.' is just another character in the field name, it doesn't actually mean anything to the database) which would pull the relevant information from the customer file or call a subroutine to find the relevant information (e.g., in a history file). The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch. It also leads to some interesting gotchas. I don't have a good reference handy for UniVerse's I-descriptors, but the System Description document[1] has a section on it. It had a certain elegance that I miss in modern SQL databases. On the flip side, modern SQL databases are so much more powerful. [0] https://www.rocketsoftware.com/products/rocket-universe-0/rocket-universe https://www.rocketsoftware.com/products/rocket-universe-0/ro... [1] https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Funiverse%2Fprevious%20versions%2Fv11.1.15%2Funiverse_systemdescriptionuserguide_v11115.pdf https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Fu...
- uhoh-itsmaciek 7y ago
  Is that different from adding such columns in a view enriching the table?
  - Mister_Snuggles 7y ago
    For most things no, but there are some things you can do that are difficult or impossible to do in an SQL view. I-Descriptors can call subroutines, which opens them up to doing anything that the underlying UniVerse BASIC runtime can do. I once made an I-Descriptor which called a subroutine to feed the postal code into a web service that returned the geographic location of that postal code. It also implemented caching so it would only call the service for a postal code once. You could simply list out the file and find customers in a particular geographic region just using the query language. I'm sure this is do-able using something like PL/Python, but I suspect it would be much more difficult to do it all in-database than how I did it in UniVerse. Whether doing it this way is a good idea is, of course, up for debate. In a modern application I wouldn't dream of doing this on the fly in a view. Just because I could build it the way I did doesn't mean it was a good idea.
laxentasken 7y ago
Looking through the docs I cannot find the limitations for what you can generate (e.g. only simple column * column or more "advanced" statements like you can use in a update statement)?
picduniya 7y ago
Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.
stockkid 7y ago
This is indeed very nice for maintaining things such as indices for full text search. Solutions based on triggers never felt easier to maintain because triggers were one step removed from the table itself.
hultner 7y ago
I've been using this for a while in a hobby project, great feature which makes for neat declarative schemas. Unfortunately some tooling is choking on this, for instance datagrip crashes om introspection, but hey that's what we can expect for being early adopters. :-)
deleted 7y ago
[deleted]
72deluxe 7y ago
Fascinating reading and looks very useful. I will try to get away from SQL Server for a bit to look at postgresql again. Thanks
jitans 7y ago
This is one of those features to not use ever, unless you have another problem that is an application touching directly a table instead of a view.
- icebraining 7y ago
  Why? This does something a (postgres) view cannot. It's useful even if you have a view in front of the table.
est 7y ago
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... I am a bit surprised nobody mentioned MySQL 5.7 had Generated Columns yet. Any difference between these two?
- atombender 7y ago
  I've not studied the documentation in detail, but MySQL looks pretty good here. It allows partitioning on a generated column, and Postgres currently does not.
iblaine 7y ago
1% of the people that want this feature are surely thrilled. It is a cool feature. The other 99% of us are looking at this like a potential land mine that will show up unsuspectingly when trying to refactor old code, or migrating from postgres.
- pythonwutang 7y ago
  I doubt 99% of the community is so humorously pessimistic. This functionality can be easily replicated in several databases (with triggers and the like). Generated columns l make a common pattern simpler and more explicit. Maybe it’s not a useful feature for everyone but it’s certainly not some instant tech debt like you’re implying.
- dragonwriter 7y ago
  Generated/computed columns are a fairly common RDBMS feature; MySQL has it since 5.7; SQL Server, Oracle, and DB2 have them. So, Postgres having them makes migrating into Postgres easier and migrating out no more difficult except maybe if you are migrating to SQLite or a nonrelational store.
mopierotti 7y ago
This seems like a cool feature, but would I be correct in thinking that: 1) If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? (https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional...) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expression you're using is, because you're saving time by precomputing the column rather than computing at query time. Edit: I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases.
- combatentropy 7y ago
  > If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? Good point! > I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases. You can do that also with a database view or function, which is my preference. I prefer my tables to be fully normalized. Any computation or processing, I try to keep in views. This just helps my mind. Tables = hard data. Views = processed data. But maybe I'm just set in my ways. Calculated columns are in the SQL standard, after all, and have been implemented in other databases for some time. In special cases (heavy calculation + heavy reads) of course this feature makes a bit more sense.
  - eropple 7y ago
    I think your approach makes more sense when thinking about databases directly. If you're using an ORM, you can use a view but it's kind of awkward. I see this as being more useful in that ORM universe--it just ends up being a read-only field on your model.
    - combatentropy 7y ago
      Can you tell me why? Not only can you select from a view, but at least in Postgres you can also insert, update, and delete against one too. I don't use an ORM. But however you specify a table name in the application code, I imagine just specifying the name of a view instead. The ORM selects, inserts, updates, or deletes with the view as the target, instead of a table. It would not know it was not a table.

ako 7y ago

Seems like a slightly more convenient way than using a trigger to achieve the same...

This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query. It’s doable, but the onus is on everyone who queries to understand this.

ddorian43 7y ago
Make a function ?
- aidos 7y ago
  Do you mean an application layer function, or a dB one? And if a dB one, you also need to create an index to go with it. And then there are dragons that you don’t need in life. If an application one, then people need to know to call it, so, yeah. I know there are things you can do, but having better tools available is always a win (especially when they remove later maintenance from the equation).
  - ddorian43 7y ago
    In DB. You can keep it with migrations. You can have a functional index without creating a new column.
- ako 7y ago
  Make a trigger
  - grzm 7y ago
    From the article: > ” Such functionality was earlier usually achieved with triggers, but with generated columns this becomes much more elegant and cleaner.”
    - ako 7y ago
      "much more elegant and cleaner" is a bit of an overstatement in my opinion...
      - grzm 7y ago
        Sounds like the generated column implementation is faster than a PL/pgSQL trigger. https://news.ycombinator.com/item?id=21134706 https://news.ycombinator.com/item?id=21134706 I don’t have a link at hand from a discussion on the mailing list, but I’ll take Peter’s word for it.
      - mobilemidget 7y ago
        I miss the why its more elegant and cleaner too. "Indexes: Generated columns can be used in indexes, but cannot be used as a partition key for partitioned tables." I also hope it can be used for a partition key in the future.
AhtiK 7y ago
This JSON plucking is also much easier now with the json path expressions coming up in v12!
- deleted 7y ago
  [deleted]
- rattray 7y ago
  Can anyone share a link?
  - onion2k 7y ago
    https://www.postgresql.org/docs/12/functions-json.html https://www.postgresql.org/docs/12/functions-json.html
jitans 7y ago
Why your application doesn't use a view? Or even better a table function?
- icebraining 7y ago
  At least in PG, a regular view must calculate everything at read time, which might mean a lot of duplicated calculations, and a materialized view has to be refreshed "manually", it doesn't auto-update piecemeal when the underlying data changes.
  - durkie 7y ago
    I've had pretty good luck with using table triggers to update materialized views and make everything "automatic". A little more work up front, but pretty easy to forget about it once it's in place.
    - icebraining 7y ago
      A REFRESH of a materialized view still requires full re-computation of all values, no? It's better than regular views if you have more reads than writes, but still quite wasteful.
      - pmart123 7y ago
        I believe so. I would say it’s more useful for rollups or aggregations where real-time isn’t necessary.
      - durkie 7y ago
        You are right. I've been dealing with this personally with PostGIS and storing large geometries. Ideally you split the geometries up in to many smaller geometries (using a built-in function), and it's much faster to calculate intersections, contained lengths, etc using those instead. Many places online recommend storing this collection of divided geometries as a materialized view, but I recently had to move it to a separate real table because inserting a single new record would take 15 minutes to update the view (on an RDS 4xlarge database). It could at least update concurrently, so other reads didn't block, but now that the the divided geometries are stored in a separate table I can add new records in under 5 seconds usually.
purplerabbit 7y ago
I've been excited about this idea of json-blob-field-to-computed-column for a solid year... I'm so stoked that it's coming to fruition! Relating json/xml API response blobs to queryable columns is a huge and inefficient burden which I hope that these featuers can help to alleviate.

tirumaraiselvan 7y ago

Why do they call it Generated Columns and not Computed Columns like most other databases already do?

t0astbread 7y ago
I guess every column is "computed" if you think about it? It's nitpicky and I don't know the official reason though.
PeterisP 7y ago
IMHO "computed columns" generally implies columns that are computed on-demand when read, as opposed to this case when they're computed when the row is altered and persisted in the table.
fowl2 7y ago
Possibly because they're closer to "persisted calculated columns".

yourad_io 7y ago

I wonder if a generated column can be referenced as a foreign key. Weirdness wouod probably ensue, so I'd guess... No?

mslot 7y ago
Yes. A generated column can be included in a foreign key and be referenced by a foreign key. Nothing too weird happens. It's basically like having every update and insert specify the column value.

Erwin 7y ago

An alternate way of doing this is to pass the entire current table row to a function which can be done easily: if you have a table "purchase" PG also creates a "purchase" type, so if you have this function: CREATE FUNCTION vat(a purchase) RETURNS numeric AS 'SELECT a.value * .25' LANGUAGE 'sql' Then you can run: SELECT value, vat(purchase.*) FROM purchase; And so be able to use every purchase column within the SQL function to do your calculation. There are two interesting shortcuts: You can call just vat(purchase) because the type is an alias for the current table row. That alias is very confusing and this is not recommended (try select table from table!) There's also a method like shortcut which the documentation calls "functional notation": SELECT value, purchase.vat FROM purchase; This calls your VAT() function passing it the entire purchase row, see https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-USAGE https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-US... I don't actually know if PG can correctly inline the necessary code in here -- it is better able to do it if you use "SQL" function certainly.

philliphaydon 7y ago
This isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday) You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is stored + a period of time which it must be completed by. Calculating that column in sql before doing a where filter is crazy slow when you’re looking at millions and millions of records. But pre-calculating it and storing it, and then adding an index on top of it, is insanely fast. This is currently done in code. But if I moved this to a computed column then I can Ensure the result is always up to date if the period changes and avoid code being written to accidentally forget to update this value. There are use cases for computer columns. As there are for functions. And doing it in code. This feature in pg12 mainly gives us the ability to index the value which we couldn’t do before.
- taffer 7y ago
  I'm not sure if I understand you correctly, but logically the WHERE clause happens before the SELECT clause[1], so it only calculates this value for the rows you're interested in. It is also possible to index functions without generated columns. [1] https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...

redact207 7y ago

As a developer I always hated this feature in other rdbms. The idea of your app saving a row and retrieving it only to have more data in it. IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing. It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions. Perhaps there are legitimate uses of this, maybe for DBs that aren't just repositories for apps?

Ididntdothis 7y ago
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
- luismedel 7y ago
  I'm not a db-engine expert, but if I'd have to write this functionality myself, I'd perform the computation only when any of the involved columns change, not on every read. EDIT: sorry, I read your comment on the opposite way. I see we are saying the exact same thing :)
- redact207 7y ago
  Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
  - filleduchaos 7y ago
    And if another consumer of the DB doesn't do the calculation or, even worse, does the wrong calculation?
  - baq 7y ago
    what if you have 50 apps talking to the same DB?
  - yourad_io 7y ago
    So long as everyone, everywhere, always remembers to calculate it in the same way for all inserts and updates. This seems a lot more robust to me.

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.

Ididntdothis 7y ago

I tend to follow the Postgres releases and I am always impressed by the cool things SQL databases can do. But for programmers like me who are used to code in C++/C# I always find the transition from these languages to SQL too harsh. Especially if you don’t have to do SQL daily it’s really hard to remember the syntax and read complex SQL code. Also the transition from SQL results to typed languages is tedious. ORMs help a little but they have their own set of problems. Not sure what I am trying to say other than that I wish it was easier for regular programmers to use advanced SQL features...

magicalhippo 7y ago
SQL is a bit verbose at times, but it's not much different from going to other programming languages. In fact I'd say it's easier given that it's so verbose. I have no idea what all those modifier symbols do in Rust, say, but I find "case when InvoiceNo is null then" or "select Name, list(distinct Title) as Titles from" pretty transparent in comparison. The biggest mental difference I think is that you're dealing with a different data model. You don't have a bunch of individual entities floating around. You got rows in tables, which you mostly process in bulk. As such it's more like an Excel spreadsheet.
- pizza234 7y ago
  What specifically do you find verbose? The only thing I find redundant (based on the queries I handle) is that theoretically join arguments could be deduced from foreign keys (maybe extending this behavior, having joins automatically deduced). I also wonder if the perceived verbosity is caused by some programmers cramming logic into the queries, rather than keeping them simple and processing the results in the client language.
  - magicalhippo 7y ago
    It's verbose compared to a lot of other languages simply by using words rather than symbols or abbreviations for most things. Though I tried to convey that I did not think this was a bad thing. The only case where things get really verbose for me is when I need to do subqueries for columns. Often I might need to run basically the same subquery but return aggregates over different fields, ie sum of net weight, sum of gross weight, sum of value. For performance reasons I can't write that as a join, at least on our SQL server. I agree there should be a shortcut syntax for joining using foreign keys ("join X on foreign key"?) though.

hestefisk 7y ago

This is really nice. Acts almost as a materialised view.

yourad_io 7y ago
I think those have to be explicitly refreshed, no?
- yourad_io 7y ago
  Ah - they're are two types of generated columns > There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). https://www.postgresql.org/docs/12/ddl-generated-columns.html https://www.postgresql.org/docs/12/ddl-generated-columns.htm...

Mister_Snuggles 7y ago

The first database I used professionally was called UniVerse[0], currently owned by Rocket Software. It's a Pick-style, non-relational database. In the data dictionary for a file you could create I-descriptors, which were computed columns much like this feature allows. The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. An INVOICES file, for example, would have fields like CUSTOMER.NAME, CUSTOMER.ADDRESS, etc, (note that the '.' is just another character in the field name, it doesn't actually mean anything to the database) which would pull the relevant information from the customer file or call a subroutine to find the relevant information (e.g., in a history file). The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch. It also leads to some interesting gotchas. I don't have a good reference handy for UniVerse's I-descriptors, but the System Description document[1] has a section on it. It had a certain elegance that I miss in modern SQL databases. On the flip side, modern SQL databases are so much more powerful. [0] https://www.rocketsoftware.com/products/rocket-universe-0/rocket-universe https://www.rocketsoftware.com/products/rocket-universe-0/ro... [1] https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Funiverse%2Fprevious%20versions%2Fv11.1.15%2Funiverse_systemdescriptionuserguide_v11115.pdf https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Fu...

uhoh-itsmaciek 7y ago
Is that different from adding such columns in a view enriching the table?
- Mister_Snuggles 7y ago
  For most things no, but there are some things you can do that are difficult or impossible to do in an SQL view. I-Descriptors can call subroutines, which opens them up to doing anything that the underlying UniVerse BASIC runtime can do. I once made an I-Descriptor which called a subroutine to feed the postal code into a web service that returned the geographic location of that postal code. It also implemented caching so it would only call the service for a postal code once. You could simply list out the file and find customers in a particular geographic region just using the query language. I'm sure this is do-able using something like PL/Python, but I suspect it would be much more difficult to do it all in-database than how I did it in UniVerse. Whether doing it this way is a good idea is, of course, up for debate. In a modern application I wouldn't dream of doing this on the fly in a view. Just because I could build it the way I did doesn't mean it was a good idea.

laxentasken 7y ago

Looking through the docs I cannot find the limitations for what you can generate (e.g. only simple column * column or more "advanced" statements like you can use in a update statement)?

picduniya 7y ago

Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.

stockkid 7y ago

This is indeed very nice for maintaining things such as indices for full text search. Solutions based on triggers never felt easier to maintain because triggers were one step removed from the table itself.

hultner 7y ago

I've been using this for a while in a hobby project, great feature which makes for neat declarative schemas. Unfortunately some tooling is choking on this, for instance datagrip crashes om introspection, but hey that's what we can expect for being early adopters. :-)

deleted 7y ago

[deleted]

72deluxe 7y ago

Fascinating reading and looks very useful. I will try to get away from SQL Server for a bit to look at postgresql again. Thanks

jitans 7y ago

This is one of those features to not use ever, unless you have another problem that is an application touching directly a table instead of a view.

icebraining 7y ago
Why? This does something a (postgres) view cannot. It's useful even if you have a view in front of the table.

est 7y ago

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... I am a bit surprised nobody mentioned MySQL 5.7 had Generated Columns yet. Any difference between these two?

atombender 7y ago
I've not studied the documentation in detail, but MySQL looks pretty good here. It allows partitioning on a generated column, and Postgres currently does not.

iblaine 7y ago

1% of the people that want this feature are surely thrilled. It is a cool feature. The other 99% of us are looking at this like a potential land mine that will show up unsuspectingly when trying to refactor old code, or migrating from postgres.

pythonwutang 7y ago
I doubt 99% of the community is so humorously pessimistic. This functionality can be easily replicated in several databases (with triggers and the like). Generated columns l make a common pattern simpler and more explicit. Maybe it’s not a useful feature for everyone but it’s certainly not some instant tech debt like you’re implying.
dragonwriter 7y ago
Generated/computed columns are a fairly common RDBMS feature; MySQL has it since 5.7; SQL Server, Oracle, and DB2 have them. So, Postgres having them makes migrating into Postgres easier and migrating out no more difficult except maybe if you are migrating to SQLite or a nonrelational store.

mopierotti 7y ago

This seems like a cool feature, but would I be correct in thinking that: 1) If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? (https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional...) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expression you're using is, because you're saving time by precomputing the column rather than computing at query time. Edit: I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases.

combatentropy 7y ago
> If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? Good point! > I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases. You can do that also with a database view or function, which is my preference. I prefer my tables to be fully normalized. Any computation or processing, I try to keep in views. This just helps my mind. Tables = hard data. Views = processed data. But maybe I'm just set in my ways. Calculated columns are in the SQL standard, after all, and have been implemented in other databases for some time. In special cases (heavy calculation + heavy reads) of course this feature makes a bit more sense.
- eropple 7y ago
  I think your approach makes more sense when thinking about databases directly. If you're using an ORM, you can use a view but it's kind of awkward. I see this as being more useful in that ORM universe--it just ends up being a read-only field on your model.
  - combatentropy 7y ago
    Can you tell me why? Not only can you select from a view, but at least in Postgres you can also insert, update, and delete against one too. I don't use an ORM. But however you specify a table name in the application code, I imagine just specifying the name of a view instead. The ORM selects, inserts, updates, or deletes with the view as the target, instead of a table. It would not know it was not a table.