PostgreSQL

22 Apr 2025 23:15

Could you share the query or a solid explain plan.
You mentioned calling a function. I've seen PG do some crazy stuff when functions (or what it sees as lateral joins) are needed.
A trick I have used a few times is :
WITH qry AS MATERIALIZED ( Select FUNCTION(),id from security_table)
SELECT ... and join in the materialized query. the upside is it only runs the selects ONCE from whatever table. Otherwise, I've seen it call it for EVERY ROW in the FILTER as it does a SEQ Scan.

But sharing the query would make it a lot easier to comment.
Also, sometimes I will break normalization and store something twice, or use a functional index that pre-parses what I am looking for.

PostgreSQL

22 Apr 2025 14:28

Has anyone seen that pgbackrest started filling the root file system while the backup destination is AWS S3 due to any issue ?

PostgreSQL

22 Apr 2025 10:56

Your startup log on the former primary _should_ tell you if it did restore any WAL segments before the missing one or if it fails straight away.
A more common problem would be a split brain situation.
My recommendation would be to make a (delta) backup and (delta) restore that. If you lost some WAL, you want a proper backup anyway...

PostgreSQL

22 Apr 2025 10:30

Rsync to only transfer back the changed data

PostgreSQL

22 Apr 2025 10:22

Hi all, I have 2 postgresql node patroni cluster, something went wrong before 4 hrs on primary and failover has happened. Now the previous primary is in stopped state, and after monitoring for few times as automatic pg_rewind was running status changes to starting but not coming up because when I see the postgresql logs it says some wal_file file is missing, now I have one option to reinit the cluster but will take lot of time as db size is 3TB , any other approach you suggest????

PostgreSQL

22 Apr 2025 07:30

Yes. Not informative.

PostgreSQL

22 Apr 2025 07:29

Why, you came to this conclusion ourselves - to use long names

PostgreSQL

22 Apr 2025 07:26

You right. So you need using long names for PK fields in tables. Else it will be strange

CREATE TABLE bar
(
    bar_id bigserial not null primary key,
    id bigint    not null references foo (id)
);

PostgreSQL

22 Apr 2025 07:20

Oh, correct. But in practice we are rarely join two table with their PK. We usually use the FK and PK, right?

PostgreSQL

22 Apr 2025 07:15

I means name the primary key with the table name prefix. Such as:

CREATE TABLE foo(
    foo_id bigserial not null primary key
)

PostgreSQL

22 Apr 2025 07:05

cannot, the USING can only used when the field in the two tables has the same name. ON t1.name = t2.name

PostgreSQL

22 Apr 2025 06:51

I prefer the shorter one in the past, but recently, I'm more prefer to use the USING keyword in JOIN clause. In that cases, the longer one is easier to use... I feel difficult to make the decision...

PostgreSQL

21 Apr 2025 22:48

I was just surprised by how much of a difference DNS can make. Switching to a dedicated DNS provider cut 120ms off of our page load time.

PostgreSQL

21 Apr 2025 21:13

Cashed DNS resolver, seems likely then right¿

PostgreSQL

21 Apr 2025 17:39

You mean that it took a bit to propagate the certs, and cash the lookup both in my own local DNS resolver and VPS DNS resolver?

PostgreSQL

22 Apr 2025 21:52

du -cms * | sort -n is your friend... Some logs, I guess?

PostgreSQL

22 Apr 2025 11:26

Thankyou, let me check the delta backup and restore

PostgreSQL

22 Apr 2025 10:32

I have backup running via pgbackrest and destination is s3 so wals I can take from there but how many exactly needed as in 4 hrs there would have been multiple files

PostgreSQL

22 Apr 2025 10:29

So you don't have a WAL archive?

PostgreSQL

22 Apr 2025 09:49

That's a bit like "do you prefer red or blue". Both ways work well enough, as long as you don't mix them [in your team].
I personally prefer to name FKs like fk_customerid or even fk_customer. And customer.id to me feels more natural than customer.customer_id 🤷

PostgreSQL

22 Apr 2025 07:29

Because as you said, it's more strange and a bit ugly.

PostgreSQL

22 Apr 2025 07:26

🥲🥲🥲 I feel difficult to make the decision...

PostgreSQL

22 Apr 2025 07:22

Such as we have two table

CREATE TABLE foo
(
    foo_id bigserial not null primary key
);
CREATE TABLE bar
(
    bar_id bigserial not null primary key,
    foo_id bigint    not null references foo (foo_id)
);

SELECT * FROM foo INNER JOIN bar b USING (foo_id);

PostgreSQL

22 Apr 2025 07:19

Well, but if you will create another table with longer name of PK column

CREATE TABLE bar(
    bar_id bigserial not null primary key
)

cannot, the USING can only used when the field in the two tables has the same name. ON t1.name = t2.name

PostgreSQL

22 Apr 2025 07:08

But how name of primary key depends of name of columns?

PostgreSQL

22 Apr 2025 07:04

Are there two tables (t1, t2) with columns with different names (t1.t1_id, t2.t2_id) can be joined by USING?

PostgreSQL

22 Apr 2025 06:50

Hello friends, I want to know what do you think of the name of the primary key. id vs tablename_id ? Which one do you think better?

PostgreSQL

21 Apr 2025 21:28

Not impossible.

Also DNS autoconfiguration problems.

Also hostname written to hosts or other database lately.

PostgreSQL

21 Apr 2025 21:12

Certs don't propagate via DNS (usually).

PostgreSQL

21 Apr 2025 17:37

In fact, most probably that was s dns issue.