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

PostgreSQL 15

postgresql.org

Post reply on HN
Print view View original

121–130 of 132 posts

Re: PostgreSQL 15

#121

Post by gen220 »

nicoburns wrote: post #101
gen220 wrote: post #85

Earlier quoted context omitted.

The operation it's replacing is something like "SELECT, followed by UPDATE/INSERT". Implicit in that sequence is transmitting the selected rows over the network, and buffering the rows in-memory on the client side. With MERGE, you eliminate the network stress, and push the burden of managing the rows in-memory onto the postgres server. That's quite nice if you have beefy operations and want to keep the services/jobs…

You can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...

You can do them individually yes, but you can’t do INSERT and UPDATE from the same SELECT CTE.

Before, you’d have to either load the data in the client side or duplicate the CTE across two statements in a transaction.

Re: PostgreSQL 15

#122

Post by nicoburns »

gen220 wrote: post #121
nicoburns wrote: post #101

Earlier quoted context omitted.

You can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...

You can do them individually yes, but you can’t do INSERT and UPDATE from the same SELECT CTE. Before, you’d have to either load the data in the client side or duplicate the CTE across two statements in a transaction.

You can, like this:

    WITH results as (
      SELECT ...
    ),

    inserted_rows AS (
      INSERT ...
      SELECT * FROM results
      ...
    ),

    deleted_rows AS (
      DELETE ...
      USING results
      ... 
    )

    UPDATE ...
    FROM results

Re: PostgreSQL 15

#123

Post by tomputer »

morley wrote: post #25

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it's highly contextual and depends on how easy it is to do so, but I've heard about places that never upgrade until it's a huge problem for them to do so (in order to avoid an even worse problem.)

Where I work we've recently upgraded many PostgreSQL servers from 9.x/10.x/11.x to 14.x.

A very cool PostgreSQL feature is that, if you install a second instance on the same server, it will by default use a different path and port to run the second instance.

  root@example:~# pg_lsclusters
  Ver Cluster Port Status Owner    Data directory              Log file
  11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
  14  main    5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
Now you can dump and import databases on the second instance and test your applications with the new version. If it all works as expected, use pg_upgrade[1] to migrate everything to the new instance and switch the ports after the migration.

I have only done this migration process on Debian systems but in my experience it worked fantastic!

[1] https://www.postgresql.org/docs/current/pgupgrade.html

Re: PostgreSQL 15

#124

Post by gen220 »

nicoburns wrote: post #122
gen220 wrote: post #121

Earlier quoted context omitted.

You can do them individually yes, but you can’t do INSERT and UPDATE from the same SELECT CTE. Before, you’d have to either load the data in the client side or duplicate the CTE across two statements in a transaction.

You can, like this: WITH results as ( SELECT ... ), inserted_rows AS ( INSERT ... SELECT * FROM results ... ), deleted_rows AS ( DELETE ... USING results ... ) UPDATE ... FROM results

Woah! I stand corrected, thanks.

It seems you’re knowledgeable on this, do you know if MERGE useful beyond expressivity?

Re: PostgreSQL 15

#125

Post by InitEnabler »

Ankhers wrote: post #77
throw0101a wrote: post #22

Is there anything like Galera for PostreSQL? I find it very convenient for small-scale HA and redundancy and it's quite easy to get going.

I am not sure exactly what Galera does, but you may want to look into Citus ( https://www.citusdata.com/ ).

Citus is great, however do note that reading the docs you do have to change your scheme to use Citus. So it's not really "drop-in" par say.

Re: PostgreSQL 15

#126

Post by nijave »

morley wrote: post #25

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it's highly contextual and depends on how easy it is to do so, but I've heard about places that never upgrade until it's a huge problem for them to do so (in order to avoid an even worse problem.)

Depends on the app but usually don't major version upgrade unless the current version is losing support or there's a compelling new feature. I wouldn't run on the latest until a few point releases are out (say .3 or .4).

I haven't worked on anything recently with a good performance testing system so that weighs in a bit, too (there's not a high degree of confidence the app won't hit a weird perf regression or edge case)

If it's using a managed service, that weighs in, too. Something like AWS RDS Postgres usually has a 6 month lag + give it some additional time to work out any AWS control plane "surprises"

Re: PostgreSQL 15

#127

Post by nijave »

_bohm wrote: post #31
throw0101a wrote: post #22

Is there anything like Galera for PostreSQL? I find it very convenient for small-scale HA and redundancy and it's quite easy to get going.

I don't know much about Galera, but Patroni may be of interest to you

Galera is multi-master but I'm not sure that's as important with Postgres (it has good baseline performance & can fail over quickly).

Patroni is great for managing active-hot standby clusters

Re: PostgreSQL 15

#128

Post by alvherre »

frant-hartm wrote: post #96
thewataccount wrote: post #89

Earlier quoted context omitted.

I manage two separate projects at my work, I would never upgrade to a .0 update, but we do every update it either when there's features we want, over every ~2~3 years for the general improvements. We use django and some other stuff that has very decent support for postgres, and in my experience postgres updates rarely have breaking changes that require much if any changes for us. This also keeps us from being "trappe…

Note that vendors know this and some release few patch versions quickly with only few patches to make people move to newest version (and get the community test it by using it). No idea if Postgres is one of these.

Postgres has a strict schedule of quarterly bugfix releases, and only deviates from that when serious bugs occur (very rare). So, no.

Re: PostgreSQL 15

#129

Post by alvherre »

tinus_hn wrote: post #120
jeltz wrote: post #98

Earlier quoted context omitted.

Jeff is right, PostgreSQL has released a new major yearly the last almost 25 years.

That would only be possible if they started with version -10!

Before version 10, the versioning scheme was different, and the first number was increased about once every five years: for example, 9.6 and 9.5 were consecutive yearly major releases. Version 7.0 was released around year 2000.

Re: PostgreSQL 15

#130

Post by googl-free »

CodeIsTheEnd wrote: post #88

This release includes a feature I added [1] to support partial foreign key updates in referential integrity triggers! This is useful for schemas that use a denormalized tenant id across multiple tables, as might be common in a multi-tenant application: CREATE TABLE tenants (id serial PRIMARY KEY); CREATE TABLE users ( tenant_id int REFERENCES tenants ON DELETE CASCADE, id serial, PRIMARY KEY (tenant_id, id), ); CREAT…

Wait, did we also get trailing comma support in column lists?
Post reply on HN