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

What’s New in PostgreSQL 18 – a Developer’s Perspective

bytebase.com

Post reply on HN
Print view View original

21–29 of 29 posts

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#21

Post by IgorPartola »

During the past 15 years I have been using Postgres quite a bit. I have also almost exclusively have been accessing databases using an ORM. Love them or hate them, they do provide value for a lot of common cases and speed up development and debugability, or at least they did for me and I know they remain very popular.

This all meant that as databases like Postgres keep adding cool new features they mostly go unused because an ORM just doesn’t let you pierce that layer of abstraction except dropping to pure SQL which is typically seen as a code smell and an annoyance to everyone involved.

So on the one hand I love that Postgres is getting amazing new features, not to mention all the cool extensions. On the other hand I and I suspect many others are essentially locked out of them and since most ORMa try to serve multiple databases they typically only include the most common denominator features. As I get more experienced I both see why RDBMS is the right choice most times AND see the appeal of an object store instead of a row store.

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#23

Post by michaelsalim »

JoelJacobson wrote: post #2

It made me happy to see the pg_get_acl() function that I was involved in adding, is appreciated by users. I think there is still much improvement in the space of querying privileges. I think most users would probably struggle to come up with the query from the article: postgres=# SELECT (pg_identify_object(s.classid,s.objid,s.objsubid)).*, pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl FROM pg_catalog.pg_…

Yes. I was looking for something like this. And I've had people ask me about this before too

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#24

Post by seabombs »

IgorPartola wrote: post #21

During the past 15 years I have been using Postgres quite a bit. I have also almost exclusively have been accessing databases using an ORM. Love them or hate them, they do provide value for a lot of common cases and speed up development and debugability, or at least they did for me and I know they remain very popular. This all meant that as databases like Postgres keep adding cool new features they mostly go unused b…

It depends on the ORM, I know SQLAlchemy and Django ORM have some Postgres specific features (e.g. full text search/indexing). Some also let you extend the ORM to add your own features, or at least write raw SQL.

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#25

Post by d4mi3n »

JoelJacobson wrote: post #2

It made me happy to see the pg_get_acl() function that I was involved in adding, is appreciated by users. I think there is still much improvement in the space of querying privileges. I think most users would probably struggle to come up with the query from the article: postgres=# SELECT (pg_identify_object(s.classid,s.objid,s.objsubid)).*, pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl FROM pg_catalog.pg_…

Absolutely. A lot of data security risk is gauged by who has access to what, and the sad fact is that many teams don’t use row or column level security for ergonomic reasons. Features like this would do a lot to make these features easier to reason about, understand, and verify.

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#26

Post by JakaJancar »

One obvious thing I still can’t believe pg doesn’t have is the ability to define triggers at the database or schema level. I must have written code to mass generate DROP/CREATE TRIGGER probably 5 times (yes I know you can reuse the trigger procedure itself). And then you need to remember to re-run whenever tables are added/removed.

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#27

Post by IgorPartola »

seabombs wrote: post #24
IgorPartola wrote: post #21

During the past 15 years I have been using Postgres quite a bit. I have also almost exclusively have been accessing databases using an ORM. Love them or hate them, they do provide value for a lot of common cases and speed up development and debugability, or at least they did for me and I know they remain very popular. This all meant that as databases like Postgres keep adding cool new features they mostly go unused b…

It depends on the ORM, I know SQLAlchemy and Django ORM have some Postgres specific features (e.g. full text search/indexing). Some also let you extend the ORM to add your own features, or at least write raw SQL.

Yeah I mostly use Django ORM and TortoiseORM these days and have used SQLAlchemy in the past. FTS is nice but things like locks, pubsub, stored procedures, triggers, views/materialized views, and many others are not supported out of the box and for some of these I don’t even see where they could be hooked up. And this is before you get into things like replication and multi-server clusters.

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#28

Post by tianzhou »

JoelJacobson wrote: post #2

It made me happy to see the pg_get_acl() function that I was involved in adding, is appreciated by users. I think there is still much improvement in the space of querying privileges. I think most users would probably struggle to come up with the query from the article: postgres=# SELECT (pg_identify_object(s.classid,s.objid,s.objsubid)).*, pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl FROM pg_catalog.pg_…

Author here — I nearly overlooked this in the changelog. Definitely my second favorite feature (uuidv7() is tough to beat)

Re: What’s New in PostgreSQL 18 – a Developer’s Perspective

#29

Post by evanelias »

jimktrains2 wrote: post #17
tczMUFlmoNk wrote: post #8

Earlier quoted context omitted.

> I imagine the computed column could be indexed or materialized if needed. The article mentions that "you cannot create indexes on VIRTUAL generated columns".

Since you can index expressions I wonder if that's because you essentially emhave to store the value in the index anyway and that wouldn't be expected for a virtual column?

I don't really understand why this restriction would be necessary. Wouldn't the computed value just get stored in the index btree keys, exactly the same as an indexed expression?

For comparison, MySQL permits indexing of virtual columns, and indexes on expressions share the same underlying implementation to support this. In other words, in MySQL an index on an expression is literally just an index on an internally-hidden virtual column.

Post reply on HN