5 ms·

Lesser Known Postgres Features (2021)

103 points by skadamat 3y ago

paulryanrogers 3y ago
> With PostgreSQL 14 released just a few weeks ago... Would benefit from putting "2021" in the title
- deleted 3y ago
  [deleted]
  - deleted 3y ago
    [deleted]
- systems 3y ago
  from https://www.postgresql.org/ https://www.postgresql.org/ Latest Releases 2023-08-10 - PostgreSQL 15.4, 14.9, 13.12, 12.16, 11.21, and PostgreSQL 16 Beta 3 Released!
pvg 3y ago
Discussed at the time, 166 comments https://news.ycombinator.com/item?id=29163319 https://news.ycombinator.com/item?id=29163319
zepearl 3y ago
I'd probably love to have the "REFRESH ON COMMIT"-feature for materialized views, to refresh (only the) impacted rows automatically whenever rows of the source table(s) change :) (the same like in Oracle - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-MATERIALIZED-VIEW.html https://docs.oracle.com/en/database/oracle/oracle-database/1... - but I would not need the fancy "query rewrite" feature that make SQLs use the matView even if they don't explicitly mention it in their SQL)
- sseagull 3y ago
  This is being worked on if I recall: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm
- paulddraper 3y ago
  "Incremental view maintenance" Lots and lots of theory about how to do this correctly. I authored a project to do this with triggers. https://github.com/rivethealth/denorm https://github.com/rivethealth/denorm
  - jjgreen 3y ago
    Nice! I did this "by hand" for a dozen views a few years back, fiddly but rewarding; if I have to do it again ...
- janee 3y ago
  We've had success using DBT incremental models for this. Deletes aren't supported though, but for our use case that's fine and do a nighly refresh on the whole. Otherwise the best alternative would be something like https://materialize.com https://materialize.com
- atomically 3y ago
  Check out https://readyset.io https://readyset.io as well — it’s an incremental view maintenance system
brasetvik 3y ago
Some overlap, but my similar post mentions a few other things too, with a lot of links to sources to learn more :) https://medium.com/cognite/postgres-can-do-that-f221a8046e https://medium.com/cognite/postgres-can-do-that-f221a8046e
zX41ZdbW 3y ago
One of the features I wish Postgres had is "column matchers" from ClickHouse: https://clickhouse.com/docs/en/sql-reference/statements/select#columns-expression https://clickhouse.com/docs/en/sql-reference/statements/sele... SELECT COLUMNS('_id$') FROM table; SELECT * EXCEPT(id) FROM table; SELECT * REPLACE(i + 1 AS i) FROM table; SELECT * APPLY(toString) FROM table;
panzi 3y ago
There are a few nice things in that, like (x, y) IN (SELECT ...), ROW_NUMBER() OVER (PARTITION BY ...), and (start1, end1) OVERLAPS (start2, end2).
globular-toast 3y ago
I discovered `\crosstabview` the other day! It does, of course, use crosstab underneath. It's just some psql magic. One tiny thing I discovered which I use a surprising amount is `table <tablename>;` is equivalent to `select * from <tablename>;`.
magden 3y ago
I bet that the `watch` feature would make to the list if the article was written these days. `watch` is the coolest Postgres feature that I've discovered recently.

103 points by skadamat 3y ago

paulryanrogers 3y ago

> With PostgreSQL 14 released just a few weeks ago... Would benefit from putting "2021" in the title

deleted 3y ago
[deleted]
- deleted 3y ago
  [deleted]
systems 3y ago
from https://www.postgresql.org/ https://www.postgresql.org/ Latest Releases 2023-08-10 - PostgreSQL 15.4, 14.9, 13.12, 12.16, 11.21, and PostgreSQL 16 Beta 3 Released!

pvg 3y ago

Discussed at the time, 166 comments https://news.ycombinator.com/item?id=29163319 https://news.ycombinator.com/item?id=29163319

zepearl 3y ago

I'd probably love to have the "REFRESH ON COMMIT"-feature for materialized views, to refresh (only the) impacted rows automatically whenever rows of the source table(s) change :) (the same like in Oracle - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-MATERIALIZED-VIEW.html https://docs.oracle.com/en/database/oracle/oracle-database/1... - but I would not need the fancy "query rewrite" feature that make SQLs use the matView even if they don't explicitly mention it in their SQL)

sseagull 3y ago
This is being worked on if I recall: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm
paulddraper 3y ago
"Incremental view maintenance" Lots and lots of theory about how to do this correctly. I authored a project to do this with triggers. https://github.com/rivethealth/denorm https://github.com/rivethealth/denorm
- jjgreen 3y ago
  Nice! I did this "by hand" for a dozen views a few years back, fiddly but rewarding; if I have to do it again ...
janee 3y ago
We've had success using DBT incremental models for this. Deletes aren't supported though, but for our use case that's fine and do a nighly refresh on the whole. Otherwise the best alternative would be something like https://materialize.com https://materialize.com
atomically 3y ago
Check out https://readyset.io https://readyset.io as well — it’s an incremental view maintenance system

brasetvik 3y ago

Some overlap, but my similar post mentions a few other things too, with a lot of links to sources to learn more :) https://medium.com/cognite/postgres-can-do-that-f221a8046e https://medium.com/cognite/postgres-can-do-that-f221a8046e

zX41ZdbW 3y ago

One of the features I wish Postgres had is "column matchers" from ClickHouse: https://clickhouse.com/docs/en/sql-reference/statements/select#columns-expression https://clickhouse.com/docs/en/sql-reference/statements/sele... SELECT COLUMNS('_id$') FROM table; SELECT * EXCEPT(id) FROM table; SELECT * REPLACE(i + 1 AS i) FROM table; SELECT * APPLY(toString) FROM table;

panzi 3y ago

There are a few nice things in that, like (x, y) IN (SELECT ...), ROW_NUMBER() OVER (PARTITION BY ...), and (start1, end1) OVERLAPS (start2, end2).

globular-toast 3y ago

I discovered `\crosstabview` the other day! It does, of course, use crosstab underneath. It's just some psql magic. One tiny thing I discovered which I use a surprising amount is `table <tablename>;` is equivalent to `select * from <tablename>;`.

magden 3y ago

I bet that the `watch` feature would make to the list if the article was written these days. `watch` is the coolest Postgres feature that I've discovered recently.