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

41–50 of 132 posts

Re: PostgreSQL 15

#41

Post by zozbot234 »

andy_ppp wrote: post #12
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…

Postgres’ history usually suggests they don’t ship broken database features which is why most of us reach for it as a first option when choosing a database. The MSSQL warning sounds bad enough that I’d never use this feature!

Well, the Postgres docs about MERGE include a similar warning: "When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see [Concurrency control / Transaction isolation] for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable." https://www.postgresql.org/docs/current/sql-merge.html

Re: PostgreSQL 15

#42

Post by film42 »

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 made up of 4x large dedicated servers).

Re: PostgreSQL 15

#43

Post by jeltz »

tpetry wrote: post #35
cogman10 wrote: post #27

Earlier quoted context omitted.

Couldn't tell you the when, but I can tell you the how is likely how you'd expect. Generally speaking, to do distinct you need a dictionary to look up previously seen values. To do it in parallel you need to make that dictionary thread safe. For Java, such a thread safe dictionary is made by segmenting the table and synchronizing on the segments. So you'd hash your values, figure out which segment that targets, lock…

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

Re: PostgreSQL 15

#44

Post by dewey »

aprdm wrote: post #36
dewey wrote: post #30

Earlier quoted context omitted.

This probably depends on your database, but with Postgres we usually don't stay on the old version for too long as the updates are usually very smooth and painless.

How do you upgrade and how much data do you have ?

Mostly in the 1TB range and usually by doing the replication / promotion dance. I know it gets more complicated if you have larger ones or with zero write downtime but in our case that's usually acceptable.

Re: PostgreSQL 15

#45

Post by jacooper »

jeltz wrote: post #39
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.)

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.

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.

Re: PostgreSQL 15

#46

Post by ellisv »

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

Our current policy is to update to the N-1 release. So we're currently running v13 since v14 was the latest but will soon upgrade to v14 since v15 is now the latest.

Re: PostgreSQL 15

#47

Post by briffle »

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

It takes us a few months to upgrade, with planning, testing, co-ordinating downtime, etc. We upgrade every other year, to keep us from falling too far back.

Very excited for this year, as we'll be using logical replication to migrate, which should make this much less risky. (since the old systems are untouched).

In the past, we used pg_upgrade for in place upgrades, and even though we took snapshots before, still seemed a bit stressfull when it happened.

Re: PostgreSQL 15

#48

Post by jeltz »

johndfsgdgdfg wrote: post #15
KptMarchewa wrote: post #13

Earlier quoted context omitted.

Basically, yes, it will literally just be faster. https://techcommunity.microsoft.com/t5/azure-database-for-po...

On a sidenote it's amazing to see how much MS is contributing to open source.

To give credit where credit is due this was not just a Microsoft contribution. The four mentioned contributors were all from different companies: Microsoft, Dalibo, Greenplum and EnterpriseDB. Microsoft employs some of the core contributors of PostgreSQL, but many patches come out of cross company collaboration.

Re: PostgreSQL 15

#49

Post by LunaSea »

trollied wrote: post #18
chrisjc wrote: post #6

Earlier quoted context omitted.

I'm actually surprised to hear that MERGE is only now available on Postgres. I'm now interesting in hearing about other standard (what I have come to expect as standard) SQL that's not or only now available on Postgres?

It kind of did support it before. You could do an INSERT … ON CONFLICT ( keys here ) DO UPDATE update query here

This only works if you reference a combination of columns that has been declared as unique which is not always something you want or can do.

Re: PostgreSQL 15

#50

Post by jabiko »

CWuestefeld wrote: post #7
mullsork wrote: post #3

SQL MERGE looks great! I hope I remember it when the time comes, instead of writing 3 separate queries. edit: Postgres docs on MERGE: https://www.postgresql.org/docs/15/sql-merge.html

Hmmm. The doc kinda suggests that this might be more efficient than doing it with separate commands: "First, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first cla…

Section 13.2. Transaction Isolation" has some additional information regarding the behavior of MERGE. Just CTRL-F and search for "MERGE" on https://www.postgresql.org/docs/15/transaction-iso.html#XACT...
Post reply on HN