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

191–199 of 199 posts

Re: New In Postgres 12: Generated Columns

#191

Post by zapov »

SigmundA wrote: post #152
zapov wrote: post #118

Earlier quoted context omitted.

Postgres is object-relational database, so it can certainly be used in such a way (and I use it like that most of the time). But this is quite a niche usage of PG and there are little to zero tools supporting it.

Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.

The impedance mismatch is not about having to translate, but being unable to translate. An yes, that problem exist with relational-only database, but not with PG where you can create composite types, can have arrays of composite types as a column and all other goodies which come with that.

I personally do not find useful to execute .NET code in PG, but technically you could do that.

As long as you can translate LINQ expressions to PG as you can most of them with Revenj you can avoid the pitfalls of mainstream ORMs.

Re: New In Postgres 12: Generated Columns

#192

Post by spacemanmatt »

Buttons840 wrote: post #140
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…

For me, it's because I've never studied my database the way I do my programming language. I'll read a thousand pages to master a new language, and I'll take it all to heart. Meanwhile, I begrudgingly read a few pages about PostgreSQL (let's say) and move on to the next task ASAP. PostgreSQL has great documentation, and I will give it a serious read one day.

Spending a few professional years using/learning PostgreSQL hard have paid off like crazy for me. It's a great server, doing my own devops for it is not much burden, and it doesn't hurt to have it splashed all over my resume.

Re: New In Postgres 12: Generated Columns

#193

Post by icebraining »

durkie wrote: post #149
icebraining wrote: post #127

Earlier quoted context omitted.

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.

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.

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.

Re: New In Postgres 12: Generated Columns

#194

Post by tmd83 »

_jal wrote: post #114
misterdoubt wrote: post #71

Earlier quoted context omitted.

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

I so very wish this convention would die. Also, leading a new line with the comma.

I actually like them both a lot for someone who is a developer who has spend hours fixing/optimizing large queries. As someone mentioned in another comment good syntax highlighting is often not available for sql (if any) so having the keywords separated is very helpful. When fixing and improving things I mostly care about 10% of the query, the JOINS and WHERE clause and CAPS keyword make those much easier to grasp. And the comma at the line start is very helpful (even though it looks odd for me) when you are adding/removing columns and sections on the fly. If the comma is at the start of the line you can simply comment, if it's at line end you need to edit two lines when removing a particular line from the query.

Re: New In Postgres 12: Generated Columns

#195

Post by SigmundA »

zapov wrote: post #191
SigmundA wrote: post #152

Earlier quoted context omitted.

Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.

The impedance mismatch is not about having to translate, but being unable to translate. An yes, that problem exist with relational-only database, but not with PG where you can create composite types, can have arrays of composite types as a column and all other goodies which come with that. I personally do not find useful to execute .NET code in PG, but technically you could do that. As long as you can translate LINQ…

Not sure I have seen a type or construct that could not be translated one way or another. The mismatch is the need for translation. Table per hierarchy, table per subclass etc.. References vs foreign keys. Instance identity vs primary key.

The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use.

The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.

Re: New In Postgres 12: Generated Columns

#196

Post by pmart123 »

icebraining wrote: post #193
durkie wrote: post #149

Earlier quoted context omitted.

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.

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.

I believe so. I would say it’s more useful for rollups or aggregations where real-time isn’t necessary.

Re: New In Postgres 12: Generated Columns

#197

Post by adamcharnock »

shrumm wrote: post #105
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 think you need a visual model that works for you to approach it since they're entirely different beasts. You'll hit a wall very soon if you approach learning SQL in terms of C++/C#. CSS is a similar phenomenon, you can't approach it as code. The box model is a nice way to get the basics and manipulate the DOM. Similarly, I think in terms of tables (rows and columns) to visualize SQL operations that I need to do. Ev…

I sometimes mention declarative vs imperative when talking about software development, especially to people who are coming from a HTML/CSS background. I'll give some details below in case it is useful.

Declarative languages encompass things like HTML, CSS, and SQL. Here we say what should happen, but not specifically how it should happen.

Imperative languages are C, Python, Java, JS etc. Here we give the precise steps which describe how something should work, and there is an absence of describing what the problem actually is.

Declarative languages give us a lot of power to solve a very narrow range of tasks.

Imperative languages give us comparatively less power, but allow us to tackle a very wide range of tasks.

For example: We could certainly write a Python program to sort, filter, group, and join two lists of data. However, the equivalent SQL code would be much shorter and – all things being equal – much more readable.

Conversely, SQL would be a non-starter for writing a web service or an image processing library. SQL does one thing really well, and that thing is data wrangling.

Re: New In Postgres 12: Generated Columns

#198

Post by durkie »

icebraining wrote: post #193
durkie wrote: post #149

Earlier quoted context omitted.

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.

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.

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.

Re: New In Postgres 12: Generated Columns

#199

Post by pizza234 »

magicalhippo wrote: post #185
pizza234 wrote: post #179

Earlier quoted context omitted.

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…

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…

Agreed, I didn't want to convey that it's not verbose, either :-)
Post reply on HN