111–120 of 132 posts
> 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…
This 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), ); CREAT…
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.)
My experience is mostly that you either upgrade because it has a feature you need (then you try it out fast), if it has some performance or other nice thing (then you go for the .1) or you don't really care then you upgrade when your current version goes EOL or if you upgrade your underlying server OS (if we assume something like Ubuntu LTS, and they'd go with 14 instead of 12 or something).
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…
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.
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.
This 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), ); CREAT…
This 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), ); CREAT…
What happens if someone deletes a tenant?
Earlier quoted context omitted.
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.
Earlier quoted context omitted.
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…
Jeff is right, PostgreSQL has released a new major yearly the last almost 25 years.