Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New In Postgres 12: Generated Columns

pgdash.io

Post reply on HN
Print view View original

71–80 of 199 posts

Re: New In Postgres 12: Generated Columns

#71

Post by misterdoubt »

Ididntdothis wrote: post #31
dewey wrote: post #15

Earlier quoted context omitted.

I found this book to be very helpful, I'm not done yet but so far I'm really enjoying it. "Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity…

I think it’s not only the procedural vs declarative difference but also just the plain syntax. To me SQL is just hard on the eyes. It feels a little like FORTRAN in the good old days.

Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.

Re: New In Postgres 12: Generated Columns

#72

Post by magicalhippo »

makach wrote: post #3

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.

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.

Re: New In Postgres 12: Generated Columns

#73

Post by wiz21c »

krn wrote: post #62
sorokod wrote: post #32

Earlier quoted context omitted.

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

> Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting. The trick is to keep declarative business logic in the database layer, and imperative business logic in the application layer. This allows a large team of developers to move quickly without breaking things.

that's very well put. Thx!

Re: New In Postgres 12: Generated Columns

#74

Post by taffer »

another-one-off wrote: post #63
stupidcar wrote: post #13

Earlier quoted context omitted.

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…

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 dra…

> 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.

Re: New In Postgres 12: Generated Columns

#75

Post by philliphaydon »

Erwin wrote: post #70

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 fu…

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.

Re: New In Postgres 12: Generated Columns

#76

Post by noir_lord »

dewey wrote: post #15
Ididntdothis wrote: post #4

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 he…

I found this book to be very helpful, I'm not done yet but so far I'm really enjoying it. "Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity…

Book looks interesting but the reviews are a bit of a mixed bag mostly about copy editing, has that been your experience?

Re: New In Postgres 12: Generated Columns

#77

Post by FroshKiller »

redact207 wrote: post #2

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 a…

You would only get more data if you queried for *, and you should know better than to do that.

Re: New In Postgres 12: Generated Columns

#78

Post by FroshKiller »

Ididntdothis wrote: post #5
redact207 wrote: post #2

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 a…

Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?

If not a performance benefit, at least minuscule energy savings.

Re: New In Postgres 12: Generated Columns

#79

Post by [deleted] »

Erwin wrote: post #70

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 fu…

[deleted]
Post reply on HN