Is 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.
I don't know much about Galera, but Patroni may be of interest to you
PostgreSQL 15
31–40 of 132 posts
Re: PostgreSQL 15
#32I 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.)
In my experience, it’s always been a huge decision to upgrade based on lots of analysis and factors. I have never worked in a company where the team routinely upgrades when a major version comes out, as it generally doesn’t make business sense. Just my 2c…
Re: PostgreSQL 15
#33Re: PostgreSQL 15
#34I 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).
Re: PostgreSQL 15
#35> 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?
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.
Re: PostgreSQL 15
#36I 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.)
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 ?
Re: PostgreSQL 15
#37Earlier 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…
Agreed. 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.
> 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 [...]Re: PostgreSQL 15
#38SQL 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?
The 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.
Re: PostgreSQL 15
#39I 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.
Re: PostgreSQL 15
#40I 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 are heavy on-prem Postgres users. We currently run all major versions from 10-13, and are upgrading some clusters to 14 very soon. Old versions are mostly to support internal tools that we're not upgrading for various reasons, and will go away when the apps that use them do.
We generally stay one major version behind on the main production clusters. The major reason is usually just scheduling - PG upgrades are large projects that touch a lot of things, take a lot of prep and impact every part of our business, so scheduling it is always a fraught negotiation.
If you don't have a lot of complexity, I recommend finding a cadence that doesn't hurt, but sticking with it. Once you get far enough behind, it becomes harder to upgrade, mostly because you also weren't upgrading surrounding tooling, so you end up changing a lot of things at once.
As your environment grows in complexity and use, at some point I think you'll find that nontechnical concerns start to dominate these decisions.