14 ms·
PostgreSQL 15
- xnx 4y agoGlad to see all the new regex functions. I recently moved a database from AWS Redshift to Postgres on Heroku and was shocked to see how many functions like regexp_substr() weren't available. Wish this had come sooner so I didn't have to rewrite so many of my queries.
- BasedInfra 4y agoThat’s an interesting change from a analytical to transactional postgres flavour. What workload is on this DB?
- xnx 4y agoThe app is so small that we didn't need a separate database copy just for reporting.
- mullsork 4y agoSQL 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 https://www.postgresql.org/docs/15/sql-merge.html
- chrisjc 4y agoI'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?
- systems 4y agoWell 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, so maybe it is
- andy_ppp 4y agoPostgres’ 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!
- zozbot234 4y agoWell, 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 https://www.postgresql.org/docs/current/sql-merge.html
- KajMagnus 4y agoThat's not a warning, instead, it's how it should work, and how one would want it to work, i.e. that the transaction isolation rules apply. Lack of this, would have warranted a warning.
- cogman10 4y agoAgreed. 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.
- piaste 4y ago> 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 workaround (locks/transactions, triggers, exception handling, etc.) If I'm reading the MERGE docs right, you can handle that case: WHEN MATCHED AND old.pkey = new.pkey THEN UPDATE SET value = new.value WHEN MATCHED AND old.col1 = new.col1 AND old.col2 = new.col2 THEN UPDATE SET reps = reps + 1 WHEN NOT MATCHED THEN INSERT [...]
- ptrwis 4y agoAt least for some cases, there was a workaround by using INSERT ... ON CONFLICT
- jeltz 4y agoI would argue that INSERT ... ON CONFLICT is not just a workaround but the correct solution in most cases. It is very explicit about what you want and makes sure that either it can take the correct locks or it will error out if there is no unique index/primary key that it can use to take the lock. But, yes, MERGE can do more things than INSERT ... ON CONFLICT.
- trollied 4y agoIt kind of did support it before. You could do an INSERT … ON CONFLICT ( keys here) DO UPDATE update query here
- jeltz 4y agoThe reason is that PostgreSQL has INSERT ... ON CONFLICT which is usually what you want, especially since it handles concurrency in the way you usually want. MERGE has more capabilities but not enough of them to make such a complex feature prioritized.
- CWuestefeld 4y agoHmmm. 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 clause to evaluate as true is executed." Anybody know more about this? From lots of experience with SQL Server, I know that over there, MERGE is not more efficient, it's just syntactic sugar - and in fact it's buggy syntactic sugar, as there are some conditions where it doesn't handle concurrency properly.
- deleted 4y ago[deleted]
- jabiko 4y agoSection 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-READ-COMMITTED https://www.postgresql.org/docs/15/transaction-iso.html#XACT...
- singingfish 4y agoI'm currently neck deep in a decent sized oracle to postgres project, and MERGE INTO saved me many many hours
- hardwaresofton 4y agojsonlog looks pretty neat! Structured logging is going to make a lot of tooling much easier to write
- jeff-davis 4y agoCSV logging is also available: https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG https://www.postgresql.org/docs/current/runtime-config-loggi...
- praveenweb 4y agoMERGE feature is interesting. But specifically on the revoking CREATE permissions for the public (or default) schema, this is a step in the right direction. Some of the defaults in Postgres can be more secure. For example, the first time I use a POSTGRES_PASSWORD to configure a password, changing this password involves more steps than just changing the values of the ENV, because it doesn't take the changed value there after. Structured logging with JSON is going to improve a lot of debugging, again a great productive change. Also, any idea when the docker image for Postgres 15 will be available?
- andy_ppp 4y agoLooking at the Alpine docker file(s) for Postgres you might be able to use the one for the release candidate and set en environment variable of PGVERSION=15.0 which should use https://ftp.postgresql.org/pub/source/v15.0/ https://ftp.postgresql.org/pub/source/v15.0/ here. You would need to figure out what the package name is on Debian (if it even exists yet?) it's currently set to ENV PG_VERSION 15~rc2-1.pgdg110+1 YMMV.
- janejeon 4y agoI'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...?
- KptMarchewa 4y agoBasically, yes, it will literally just be faster. https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/speeding-up-sort-performance-in-postgres-15/ba-p/3396953 https://techcommunity.microsoft.com/t5/azure-database-for-po...
- janejeon 4y ago:O Thanks for the link!
- johndfsgdgdfg 4y agoOn a sidenote it's amazing to see how much MS is contributing to open source.
- wahnfrieden 4y agoTheir military contributions are also awe inducing
- jeltz 4y agoTo 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.
- chrstr 4y ago> 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?
- cogman10 4y agoCouldn'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 that segment, and then read/update that segment to contain the new value. I'd assume that postgres is doing a fairly similar trick, The only additional synchronization would be on a linked list of found values. In that case, you could either lock the list and update as new values come in, you could sort those values after the fact, or you could employ a lock free algorithm to add nodes to the list (see lock free queue implementations).
- tpetry 4y agoMost 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.
- jeltz 4y agoYou are correct: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=22c4e88ebff408acd52e212543a77158bde59e69 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- chrstr 4y agoThanks! 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=src/backend/optimizer/plan/planner.c;h=468105d91ea71dc195aae1ed12ab3a2044128ca2;hb=refs/heads/REL_15_STABLE#l4420 https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
- mastax 4y agoPostGIS 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/ https://postgis.net/2022/08/27/postgis-3.3.0-released/
- mastax 4y agoAs 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.
- perrygeo 4y ago"PostGIS in Action" is a good option. Also check out https://locatepress.com/ 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.
- hellcow 4y ago> 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.
- brailsafe 4y agoWhat might you use it for? I love Postgres and am always looking for inspiration
- nrmitchi 4y agoI'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.
- hellcow 4y agoExactly 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.
- no_wizard 4y agoI 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
- throw0101a 4y agoIs 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.
- _bohm 4y agoI don't know much about Galera, but Patroni may be of interest to you
- nijave 4y agoGalera 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
- 1500100900 4y agoNot for free (have to pay EnterpriseDB for that). Every free option here is basically "glue pieces together to build your own HA".
- Ankhers 4y agoI am not sure exactly what Galera does, but you may want to look into Citus (https://www.citusdata.com/ https://www.citusdata.com/).
- InitEnabler 4y agoCitus 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.
- morley 4y agoI 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.)
- ianbutler 4y agoI don't think I've worked in an environment where we've upgraded for every release. New projects may start on that newer version, but generally speaking for older projects there's a cost calculation done for the newer features versus the lift required to do the upgrade and ensure it doesn't introduce any regressions. Someone already mentioned the view permissions shift here which looks at caller permissions versus the view creator permissions, that will be compelling in a lot of cases so like this is something that I'd probably raise internally for my team for a few of the apps we maintain and then have a back and forth with the principals, if we like it then go through our current list of work with a PM and our manager and see if it makes sense to do now with the current pipeline of work etc.
- wahnfrieden 4y ago.1+ releases might be best with pg for quality assurance
- dewey 4y agoThis 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.
- brunooliv 4y agoCould 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?
- elchief 4y agoit'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
- NegativeLatency 4y agoWorks well for bulk operations where you're loading data in on a lower frequency.
- gen220 4y agoThe 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 running those operations lean.
- nicoburns 4y agoYou can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...
- gen220 4y agoYou 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.
- ppjim 4y agoI 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.
- bumblebritches5 4y ago
- praveenweb 4y agoInterestingly, there are no breaking changes that were required to be addressed by Hasura GraphQL Engine to support Postgres 15. Hasura is fully compatible with this release, with the potential of adding the MERGE command via the GraphQL API soon. Excited about the incremental performance improvements and making more secure defaults by revoking CREATE permission for public schema for non-superusers.
- jabl 4y agoWhat's the status of zheap? https://wiki.postgresql.org/wiki/Zheap https://wiki.postgresql.org/wiki/Zheap seems to claim it has been rebased on top of 14.1, but generally progress seems slow? Also https://cybertec-postgresql.github.io/zheap/ https://cybertec-postgresql.github.io/zheap/
- CodeIsTheEnd 4y agoThis 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), ); CREATE TABLE posts ( tenant_id int REFERENCES tenants ON DELETE CASCADE, id serial, author_id int, PRIMARY KEY (tenant_id, id), FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL ); This schema has a problem. When you delete a user, it will try to set both the tenant_id and author_id columns on the posts table to NULL: INSERT INTO tenants VALUES (1); INSERT INTO users VALUES (1, 101); INSERT INTO posts VALUES (1, 201, 101); DELETE FROM users WHERE id = 101; ERROR: null value in column "tenant_id" violates not-null constraint DETAIL: Failing row contains (null, 201, null). When we delete a user, we really only want to clear the author_id column in the posts table, and we want to leave the tenant_id column untouched. The feature I added is a small syntax extension to support doing exactly this. You can provide an explicit column list to the ON DELETE SET NULL / ON DELETE SET DEFAULT actions: CREATE TABLE posts ( tenant_id int REFERENCES tenants ON DELETE CASCADE, id serial, author_id int, PRIMARY KEY (tenant_id, id), FOREIGN KEY (tenant_id, author_id) -- Clear only author_id, not tenant_id REFERENCES users ON DELETE SET NULL (author_id) -- ^^^^^^^^^^^ ); I initially encountered this problem while converting a database to use composite primary keys in preparation for migrating to Citus [2], and it required adding custom triggers for every single foreign key we created. Now it can be handled entirely by Postgres! [1]: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3DnjbSMxf%2BrbDHpx%3DW%3DB7AEaMKn8dWn9OZJY7w%40mail.gmail.com https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3D... [2]: https://www.citusdata.com/ https://www.citusdata.com/
- solidr53 4y agoThanks for your work on that, very useful.
- wharfjumper 4y agoNice, thanks!
- systemvoltage 4y agoJust some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version. Looks like that's going to take 2-3 days. Sources are available but that's not something most people want to delve in with make files and dependencies. Something like postgres is huge. Right now, if you go to downloads and expect postgresql-15 available, it is not; lot of people on IRC and elsewhere on Twitter are confused where to download postgresql-15. I know that takes time, so the PR release should just be delayed until apt sources are ready. May be also docker repositories.
- anarazel 4y ago> Just some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version. Normally that's the case - we "wrap" the release on Monday so that packagers have time till Thursday to get packages ready. Looks like something didn't quite work out this time. Looking into what went wrong. Part of it is that a list of supported versions on the windows, macos download pages weren't updated, despite the 15 being available. But unfortunately the Debian / Ubuntu packages are indeed not yet ready. > May be also docker repositories postgresql.org doesn't currently provide docker containers to my knowledge.
- systemvoltage 4y agoNo worries, thanks for looking into it.
- deleted 4y ago[deleted]
- jfbaro 4y agoCongrats PG team and community