PostgreSQL

16 Oct 2024 22:59

the CTE is a current improvement, the 'in use' version doesn't have it, it's just a left join, I tried the lateral join and the query time increase

PostgreSQL

16 Oct 2024 22:55

well, he's certainly not going to be any less clueless after seeing your answer.

PostgreSQL

16 Oct 2024 22:54

But you are VP, Chief Database Scientist @ EnterpriseDB, PostgreSQL Major Contributor and Committer

PostgreSQL

16 Oct 2024 22:52

TBTH, a classic JOIN (without the CTE) will probably do better. I.e., assuming you have an index on a_id.
If you need those line numbers, you'd have to improve you OVER() part, of course.

You may also want to look into JOIN LATERAL, depending on how many records you pull out of that JOIN, that might be what you're looking for.

PostgreSQL

16 Oct 2024 22:51

I don't think that my answer is unhelpful.

It's just plain my recomendation on most pg setups: use physical backup tools to backup.

No separate bases/schemas/tables. Just a cluster as a whole. Don't mess with that (without exceptional conditions). If you designed your workflow on schema backup — forget this, there is no schema BACKUP. Design something else. Your attempt was wrong, just make this another way.

PostgreSQL

16 Oct 2024 22:50

it's enough to make you crazy. there is absolutely no end to ways to do it wrong. and people don't read the documentation, or they don't understand it, or they don't follow it. in so, so many different ways.

PostgreSQL

16 Oct 2024 22:48

no, people do way more things wrong than that. tons and tons of different mistakes. i wrote about some of them here: https://rhaas.blogspot.com/2012/03/why-is-my-database-corrupted.html

PostgreSQL

16 Oct 2024 22:46

I'm running out of +1's here :)

PostgreSQL

16 Oct 2024 22:46

I really disagree with this. It sounds like you're saying "I'm going to give that person an intentionally unhelpful answer because I think they're too dumb to not mess it up". Well, how will anyone ever get smarter with that attitude? If you don't want to give a helpful answer, just let it go.

PostgreSQL

16 Oct 2024 22:46

bet you didn't expect that

PostgreSQL

16 Oct 2024 22:45

+1 to that.

I include both pg_dump *and* pg_basebackup in my trainings btw. Imagine you need a PITR of a particular DB/schema. You PITR from your basebackup. And then???

PostgreSQL

16 Oct 2024 22:44

thanks 😊, but currently I'm spending like 8 secs in that query, I want to check if there's something doable there before optimize it changing database structure

PostgreSQL

16 Oct 2024 22:42

No.

Strangely, this seems an obvious answer — but if he really wants a backup — it's a deeply wrong answer.
A person not able to find -n by itself definitely shouldn't do backups by pg_dump. It would probably lead to catastrophic consequences.

PostgreSQL

16 Oct 2024 22:39

Hi there, someone know how can I get a list of elements joined to the first element of another table in a many to many relation ?
like:

with second_table as (
Select id, a_id, name,  row_number() over (partition by B.a_id order by B.date desc) as rn From B
)
 select * from A  join second_table on A.id = second_table.a_id;

PostgreSQL

16 Oct 2024 22:35

And what's also ironic is that the first comment of that answer is somebody complaining that it doesn't work due to a conflict ( https://dba.stackexchange.com/questions/53185/backup-only-one-schema-and-restore-it-on-another-server#comment640604_53190 )

PostgreSQL

16 Oct 2024 22:56

it's honestly shocking to me that we're having this discussion.

PostgreSQL

16 Oct 2024 22:54

Not clueless person @ his home, playing with postgresql (not understanding)

PostgreSQL

16 Oct 2024 22:53

i have used pg_dump -n many times and not once has it made my database unrecoverable.

PostgreSQL

16 Oct 2024 22:51

"how to do [very trivial stuff the don't understand]"
"do [direct simple answer]"

"aight the output looks fine sounds good 👍 (the database will be unrecoverable)"

PostgreSQL

16 Oct 2024 22:51

>and people don't read the documentation, or they don't understand it, or they don't follow it. in so, so many different ways.

Which circles back to my answer. People do not care about knowing what they are doing

PostgreSQL

16 Oct 2024 22:49

i think people messing up something around base backups or restores is probably the most common cause of database corruption that gets escalated to me within EDB

PostgreSQL

16 Oct 2024 22:47

Exactly. And you pg_dump that and restore it into your live system...

PostgreSQL

16 Oct 2024 22:46

You have a PITR of a particular DB/schema

PostgreSQL

16 Oct 2024 22:46

so we're discussing performance?

PostgreSQL

16 Oct 2024 22:45

you DROP everything but that schema

PostgreSQL

16 Oct 2024 22:45

Wasn't it times when -X stream was not the default?

PostgreSQL

16 Oct 2024 22:43

That SQL seems sound at a quick glance. What more are you looking for?

PostgreSQL

16 Oct 2024 22:41

My take is: pg_basebackup is just as bad as pg_dump *if you don't have a proper WAL archive*. RTO/RPO is just as bad (RTO is 'duration of the backup' later for pg_basebackup, obviously).
Anyway. If you're fine with the RPO, I see few reasons not to use pg_dump[all].

PostgreSQL

16 Oct 2024 22:35

I have seen WAY more people mess up backups using pg_basebackup or the low-level backup API than I have seen people mess up pg_dump. Like probably two orders of magnitude.

PostgreSQL

16 Oct 2024 22:35

Nobody should feel obliged to provide free technical support, but that doesn't justify giving deliberate wrong answers.