PostgreSQL

22 May 2025 23:25

Theoretically it should be possible but why bother with seperate EC2 set up when you can buy rds proxy?

PostgreSQL

22 May 2025 22:22

This feels very much like the X -> Y problem.

How are tou actually trying to search?

Maybe something like good old Soundex or DMetaphone will work better and faster.

PostgreSQL

22 May 2025 22:04

If you do vacuum verbose on any table in primary, it should show you oldest xmin. From that you could see which session is the culprit.

PostgreSQL

22 May 2025 19:26

Transfer your data to DuckDB and let it handle the heavy lifting. already tested your query on 334 MB text file, 2 million rows — output was instant. Everything runs in RAM.

Ingest your data using read_csv_auto or connect directly to Postgres. transfer the data back to Postgres for further processing.

PostgreSQL

22 May 2025 16:20

hello, sometimes I observe that some physical replication slots dont update xmin and this cause to bloat in master, vacuum could not remove some dead rows, there is no cascading replications or long runing queries on replicas, is it bug or something , what causes that?

PostgreSQL

22 May 2025 12:57

Can he do similarity(a,b) though? 😏

PostgreSQL

22 May 2025 12:40

OP is doing %wildcard% lookups. I'm pretty sure that's not a good usecase for etcd and the likes...

PostgreSQL

22 May 2025 12:10

Years ago, I read a very interesting blog post comparing (I think) plain PF, PGStrom, plain C (or Perl?) code and handwritten CUDA code. And even the Perl (?) code ran circles around PGStrom, as it was tailored to the task.
I don't find that article anymore, maybe someone remembers it? May have been a Tweet thread or so as well...

PostgreSQL

22 May 2025 12:05

"bigm+" doesn't sound very efficient, RAM-wise, though... how large is your friend's index? 🤓

PostgreSQL

22 May 2025 12:03

😥 I feel so sad since I found the PostgreSQL performance less than Google Chrome 😭😭😭.

PostgreSQL

22 May 2025 11:53

A KeyValue database, actually, Chrome IndexedDB 🥹

PostgreSQL

22 May 2025 11:07

Depending on the amount of data involved, that's excellent or abysmal. Remember that you're performing a search that would require a SeqScan otherwise...

PostgreSQL

21 May 2025 09:24

The documentation itself is actually pretty good:
https://www.postgresql.org/docs/current/index.html
If you prefer books, there are many, I'd suggest any of these:
- PostgreSQL 16 Administration Cookbook
- PostgreSQL: Up and Running, 3rd Edition
- Learn PostgreSQL - Second Edition: Use, manage and build secure and scalable databases with PostgreSQL 16
- The Art of PostgreSQL
Two additional resources:
https://neon.tech/postgresql/tutorial
https://www.tutorialspoint.com/postgresql/index.htm

PostgreSQL

20 May 2025 15:49

Tapping the hive mind:

My client experiences random HAProxy check timeouts (up to 2 seconds) when polling Patroni. Quite a PITA when HAProxy takes the backend down and kills all connections... :/

Sadly, since those events appear randomly, chances to look at the system when they happen are close to zero. What we can see in hindsight is that atop reports high numbers of "scan", "compact" and also "memsome" & "memall" events. And those "kcompactd[01]" kernel threads use quite some CPU time as well. But at 10 min granularity, that's just hints...

However, I'm quite certain that the root cause is (transparent) huge page stuff (which gives me terribel RHEL 6 vibes tbth.).

Is anyone else experiencing stalls on Ubuntu 24.04 Postgres servers with contemporary amounts of RAM (512-768GB)? Any hints how I can get rid of them? Kernel is "6.8.0-53-generic #55-Ubuntu SMP PREEMPT_DYNAMIC", GUC "huge_pages" is "try".

PostgreSQL

19 May 2025 15:58

How do you usually stop the streaming replication? In case needed...

PostgreSQL

22 May 2025 22:54

Can we use pgpool or pgbouncer for Aurora postgresql

PostgreSQL

22 May 2025 22:06

Article on topic https://pganalyze.com/docs/checks/vacuum/xmin_horizon

PostgreSQL

22 May 2025 19:38

there is nothing under the pg_prepared_xacts

PostgreSQL

22 May 2025 16:56

Third option is prepare transaction uncommitted exist, could you check it?

PostgreSQL

22 May 2025 12:59

I try to select sum(length(the_field_I_indexed)), more than 200 MB text

PostgreSQL

22 May 2025 12:47

It's wildcard lookup or key-value search? Mention of KV db distracted me.

PostgreSQL

22 May 2025 12:11

Why don't you use etcd, consul etc for KV pair? KV db isn't usually a real database, it sacrifices many other things full featured database offers. So naturally different performance is expected.

PostgreSQL

22 May 2025 12:06

He still not tell me. But he's making a note program. I don't think there're so many texts like me...

PostgreSQL

22 May 2025 12:04

Now mind you, anything handwritten to a specific task will always outperform a generic solution like GIN. Can't asses "bigm+", I'm not a CS person 🥴
Maybe trigrams aren't the fastest way either in your case?

PostgreSQL

22 May 2025 11:58

Ok. Need more coffee, apparently 😅

PostgreSQL

22 May 2025 11:18

Are you have any suggestion?🥺 I found my friends hand-written bigm+ reverse index in kv db has a better performance than me. But I don't make sure how many data he has.

PostgreSQL

22 May 2025 09:53

Hello, friends. What's the typical performance of Gin index? In common web keyword search case. I create index like that:

CREATE INDEX xxx_index USING gin (name gin_trgm_ops)

" Output: name, ecosystem, repository, total_versions, last_update_date"
" -> Bitmap Heap Scan on public.component_name_agg (cost=71.43..996.23 rows=843 width=42) (actual time=159.196..159.509 rows=10 loops=1)"
" Output: name, ecosystem, repository, total_versions, last_update_date"
" Recheck Cond: (component_name_agg.name ~~ '%com.a%'::text)"
" Rows Removed by Index Recheck: 199"
" Heap Blocks: exact=145"
" -> Bitmap Index Scan on component_name_agg_ecosystem_name_gin_index (cost=0.00..71.22 rows=843 width=0) (actual time=146.764..146.765 rows=229191 loops=1)"
" Index Cond: (component_name_agg.name ~~ '%com.a%'::text)"
"Planning Time: 0.100 ms"
"Execution Time: 159.540 ms"

PostgreSQL

21 May 2025 08:04

Kindly Suggest/share me some postgres 14 DBA admin book I am new to postgres database administration.i have 5+ years of experience in Oracle DBA administration.

PostgreSQL

19 May 2025 16:33

stop instance, and delete the standby.signal and start the instance

PostgreSQL

19 May 2025 15:41

Hi everyone.im an postgresql developer.. I want to learn to snow flake with real time scenarios . please help me