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

71–80 of 132 posts

Re: PostgreSQL 15

#71

Post by fdr »

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

I don't unless I want to use something. The main consideration is hedging against an unusual problem in a new version vs. my software.

This may depend on your situation. For example, if you were running a large IT org with a few staff members with a lot of installed software, the cycle to process and check correct operation of each software under your management may be long enough that you'd start soon: by the time you are working on the last few systems under your responsibility, EOL for that Postgres version may be in sight.

You may also be working on a very new software project, where bugs are of lower consequence and you'd like to forestall the friction of an upgrade motivated by EOL. Or, you may find yourself in a lull in useful development and this is a good way to get ahead of maintenance, effectively reducing pressure in the future. In this case, I'd also consider upgrading...after a few months. Even Postgres has somewhat iffy .0 releases.

My own experience is in vertically integrated teams where we control all the software, so we typically upgrade every second or third version, leaving over a year to resolve issues before EOL of the version we're on.

Re: PostgreSQL 15

#72

Post by no_wizard »

hellcow wrote: post #19

> PostgreSQL 15 lets users create views that query data using the permissions of the caller, not the view creator. This option, called security_invoker, adds an additional layer of protection to ensure that view callers have the correct permissions for working with the underlying data. Thank you, kind friends. This is a huge QOL improvement when using row-level security with views and is the top reason I'll be upgrad…

I suspect this will make Supabase very happy! They really believe in row level security as a major line of defense so I imagine this makes it even better

Re: PostgreSQL 15

#73

Post by perrygeo »

mastax wrote: post #20
mastax wrote: post #16

PostGIS 3.3.0 mentioned another improvement in this release: "This version of PostGIS can utilize the faster GiST building support API introduced in PostgreSQL 15." https://postgis.net/2022/08/27/postgis-3.3.0-released/

As an aside, I've been trying to learn basic GIS with PostGIS and QGIS and it's been quite frustrating. I had a dataset of roads which were broken up into short segments, which I wanted to merge back together based on a key. Theoretically that's a single simple operation, in practice it was getting hung up on something I couldn't understand and it took all afternoon. My usual practice of JIT doc reading wasn't workin…

"PostGIS in Action" is a good option. Also check out https://locatepress.com/ which has a few more books in this niche.

> a dataset of roads which were broken up into short segments, which I wanted to merge back together based on a key. Theoretically that's a single simple operation

PostGIS provides the ST_MakeLine aggregate function for this, but you need to write the query such that the GROUP BY query retains the correct order. Creating a new line segment out of many line segments effectively means breaking the lines into their constituent points and then creating a new linestring based on the points. For things like GPS data, you can order by timestamp. But for other cases? You've got to write your aggregate query carefully so that adjacent line segments are actually meant to be merged.

Re: PostgreSQL 15

#75

Post by ppjim »

I hope that someday it will become as popular as MySql. Although I see complicated, since many companies use other alternatives and in my experience it is complicated to make the migration when you have many years using the same technologies.

Re: PostgreSQL 15

#76

Post by anarazel »

jtc331 wrote: post #69
jeltz wrote: post #52

Earlier quoted context omitted.

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?

Not normally, no. A real snapshot is held during the initial creation of the logical slot. After that the slot's "catalog xmin" prevents old catalog (but not user table!) row versions from being removed, but that's somewhat different from a snapshot.

Re: PostgreSQL 15

#78

Post by elchief »

brunooliv wrote: post #58

Could someone give some examples on their own domains where the MERGE command is a huge QOL improvement over what's currently available? I see a lot of people being so very happy in the comments, and, well, I've tried to think long and hard about how to apply it to my current domain but was a bit at a loss... Maybe some practical examples can help?

it's common to use it to load Slowly Changing Dimensions in data warehouses, at least in other systems, so it's nice to have the same-ish syntax in PG

Re: PostgreSQL 15

#79

Post by Beltalowda »

samus wrote: post #70
bitwrangler wrote: post #56

Earlier quoted context omitted.

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.

I typically update to .0 on my dev machine ASAP, and if there are no problems after a bit I'll update the server to .0 (or usually .1, since that'll usually be out by then).

I think that's a nice compromise; although not a "real" test as the load is a lot less. Also things may work in dev but not production, but unless you explicitly use new features that's rare.

For larger setups you can us canary servers and whatnot, but I prefer to work at small companies and keep my sanity.

Re: PostgreSQL 15

#80

Post by phamilton »

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

We try to upgrade at least once a year, during our slow season (ed-tech, so summer time is We use AWS Aurora with Postgres and we autoscale our cluster. Because most upgrades have a few perf improvements we have found that we save a bit of money every time we upgrade, either in reduced CPU (which translates to running fewer instances) or in better memory efficiency (which boosts buffer_cache_hit_ratio and therefore reduces IO).
Post reply on HN