PostgreSQL 15 • Hacker News Board

Re: PostgreSQL 15

#11

Post by chrstr » Thu, Oct 13, 2022, 2:19 PM UTC

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

Re: PostgreSQL 15

#12

Post by andy_ppp » Thu, Oct 13, 2022, 2:19 PM UTC

systems wrote: post #8

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?

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!

Re: PostgreSQL 15

#13

Post by KptMarchewa » Thu, Oct 13, 2022, 2:21 PM UTC

janejeon wrote: post #10

I'm a little bit confused on the "sorting perf improvements" bit. Does that mean that if I have a query with a `SORT BY`, it will literally "just be faster"? Surely that sounds too good to be true...?

Basically, yes, it will literally just be faster.

https://techcommunity.microsoft.com/t5/azure-database-for-po...

Re: PostgreSQL 15

#14

Post by janejeon » Thu, Oct 13, 2022, 2:22 PM UTC

KptMarchewa wrote: post #13

janejeon wrote: post #10

I'm a little bit confused on the "sorting perf improvements" bit. Does that mean that if I have a query with a `SORT BY`, it will literally "just be faster"? Surely that sounds too good to be true...?

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

:O

Thanks for the link!

Re: PostgreSQL 15

#15

Post by johndfsgdgdfg » Thu, Oct 13, 2022, 2:29 PM UTC

KptMarchewa wrote: post #13

janejeon wrote: post #10

I'm a little bit confused on the "sorting perf improvements" bit. Does that mean that if I have a query with a `SORT BY`, it will literally "just be faster"? Surely that sounds too good to be true...?

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.

Re: PostgreSQL 15

#16

Post by mastax » Thu, Oct 13, 2022, 2:33 PM UTC

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/

Re: PostgreSQL 15

#17

Post by ptrwis » Thu, Oct 13, 2022, 2:34 PM UTC

chrisjc wrote: post #6

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

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?

At least for some cases, there was a workaround by using INSERT ... ON CONFLICT

Re: PostgreSQL 15

#18

Post by trollied » Thu, Oct 13, 2022, 2:36 PM UTC

chrisjc wrote: post #6

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

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

Re: PostgreSQL 15

#19

Post by hellcow » Thu, Oct 13, 2022, 2:40 PM UTC

> 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 upgrading from Postgres 13 to 15.

Re: PostgreSQL 15

#20

Post by mastax » Thu, Oct 13, 2022, 2:45 PM UTC

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 working well, too many unfamiliar terms and missing fundamentals. If you have any recommendations for books or docs I'd love to hear them.