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

61–70 of 132 posts

Re: PostgreSQL 15

#61

Post by 86____ »

jeltz wrote: post #52
jacooper wrote: post #45

Earlier quoted context omitted.

If only they made the upgrade automatic. I use it in a container mainly, and MariaDB has an option to enable auto upgrades, but postgres requires you to manually upgrade every time.

For small databases with lax uptime requirements I find it pretty easy to upgrade. Just stop the database, run pg_upgrade_cluster (a Debian tool) and create a new replica. For large databases where you care about uptime upgrading is usually a huge project anyway, since you have to make sure that nothing breaks due to the new version (check query plans, check if the new version changed anything, etc). You also probabl…

I'd genuinely love to read about that if you're able to share more?

Re: PostgreSQL 15

#62

Post by jeff-davis »

film42 wrote: post #42
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.)

Corruption of database storage is the biggest fear so most upgrades are done cautiously. Historically postgres versions were released much much slower so the upgrade cadence was slower too. That said, we've been upgrading more frequently, especially since a lot of performance features are being released. So far our upgrades have been executed flawlessly for our cluster of postgres servers (~30 postgres chains each ma…

Postgres has released a new major version each year for a long time.

The only change is that versions used to be X.Y.Z, and now it just used X.Y. Now, X gets incremented for every major version, so it seems like it's moving faster.

Re: PostgreSQL 15

#63

Post by mwigdahl »

cogman10 wrote: post #23
systems wrote: post #8

Earlier quoted context omitted.

Well MS SQL Merge statement is not very good, and I personally avoid it, and most places I worked in recommend to avoid it, except in the simplest scenarios From the docs "At scale, MERGE may introduce complicated concurrency issues or require advanced troubleshooting. As such, plan to thoroughly test any MERGE statement before deploying to production." I dont know if its better in PQSQL , but they took their time, s…

Agreed. The amount of deadlocks merged causes in MS SQL is pretty insane. We tried to use it for "upsert" type capabilities and even that would cause weird deadlocks. Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better.

Agreed, another vote here from painful experience to avoid MERGE in SQL Server like the plague.

Re: PostgreSQL 15

#64

Post by riku_iki »

chrstr wrote: post #11

> Queries using SELECT DISTINCT can now be executed in parallel. This sounds quite interesting, but I would assume it does not always work? I didn't see this mentioned in the linked documentation, does someone know when/how the parallel distinct works?

"select distinct" is likely now syntax sugar around "select ... group by 1", which worked in parallel for a while.

Re: PostgreSQL 15

#65

Post by chrstr »

jeltz wrote: post #43
tpetry wrote: post #35

Earlier quoted context omitted.

Most parallel operations in PG are implemented by simple merge the dataset, work independently and merge the results. I expect the new distinct to behave the same and not work on a shared data structure.

You are correct: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Thanks! May be helpful to include this in the documentation, since I guess it will then often depend on the numDistinctRows estimate [1] if the parallel plan is used.

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...

Re: PostgreSQL 15

#66

Post by hellcow »

nrmitchi wrote: post #60
brailsafe wrote: post #54

Earlier quoted context omitted.

What might you use it for? I love Postgres and am always looking for inspiration

I'm not doing this, but it would be very useful when using row level security in a multi-tenant application. You can create a single view for "all active orders" (or whatever, just an example) and querying that view from different users would now give you the correct (user limited) results. It sounds like previously this was not the case.

Exactly right. We can isolate customers from one another with policies on the table, so once the policy is in place, it's actually impossible for us to write code that exposes data from one customer to any others. But if you created a view on that table, querying the view would expose all the underlying data in the table, effectively removing the policy.

Previously the only way I found to get around this was to define a function with security_invoker, then create a view based on that function. But this change removes the need for this extra function, and you can create views that use row-level security directly.

Re: PostgreSQL 15

#67

Post by jeff-davis »

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

The version support is five years: https://www.postgresql.org/support/versioning/

As long as you stay well ahead of that, you are fine.

However, I'd strongly recommend testing your applications with new versions as they come out, or in beta (even better). You may learn about new features, find bugs in your application, find bugs in your migration processes, etc. If you bring any problems to the community right away, then by the time you actually want to upgrade, the problems may already be solved in the newer release.

Re: PostgreSQL 15

#68

Post by jtc331 »

piaste wrote: post #37
cogman10 wrote: post #23

Earlier quoted context omitted.

Agreed. The amount of deadlocks merged causes in MS SQL is pretty insane. We tried to use it for "upsert" type capabilities and even that would cause weird deadlocks. Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better.

> Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better. INSERT... ON CONFLICT is awesome, but it has some limitations. The one I ran into most commonly is that it can only handle exactly one unique constraint on the target table. So if you have both a PK and another unique index, you need to choose which one gets the simple 'on conflict' and which one gets a hacky…

MERGE does not have locking built in in the way that ON CONFLICT does, so it handles more cases and fewer cases all at the same time.

Re: PostgreSQL 15

#69

Post by jtc331 »

jeltz wrote: post #52
jacooper wrote: post #45

Earlier quoted context omitted.

If only they made the upgrade automatic. I use it in a container mainly, and MariaDB has an option to enable auto upgrades, but postgres requires you to manually upgrade every time.

For small databases with lax uptime requirements I find it pretty easy to upgrade. Just stop the database, run pg_upgrade_cluster (a Debian tool) and create a new replica. For large databases where you care about uptime upgrading is usually a huge project anyway, since you have to make sure that nothing breaks due to the new version (check query plans, check if the new version changed anything, etc). You also probabl…

Isn’t keeping a logical slot alive holding a snapshot?

Re: PostgreSQL 15

#70

Post by samus »

bitwrangler wrote: post #56
jeltz wrote: post #39

Earlier quoted context omitted.

No, but as a PostgreSQL fan I upgrade the databases every major release for my side projects. I always wait until .1 or .2 is released though.

I've heard a saying "just say no to dot oh" -- encouraging one to skip the .0 release and wait until .1 or .2 before doing the upgrade.

This will stop working when not enough people adopt .0 versions anymore, because errors from regular usage will not pop up. .0 will become the new beta release that nobody cares about.
Post reply on HN