Re: neither CPU nor IO bound, but throttled performance

Thank you Magnus. 68% steal. Indeed. You probably hit the target. Yes. That explains the discrepancy. I need to watch and understand that CPU credits issue. regards, -Gunther On 2/21/2019 4:08, Magnus Hagander wrote: On Thu, Feb 21, 2019 at 12:34 AM Gunther > wrote:

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

Wow, yes, partition instead of index, that is interesting. Thanks Corey and Justin. The index isn't required at all if all my pending jobs are in a partition of only pending jobs. In that case the plan can just be a sequential scan. And Jeff James, sorry, I failed to show the LIMIT 1 clause

Re: Best Filesystem for PostgreSQL

On 4/17/2019 18:03, Imre Samu wrote: test:    PostgreSQL v10.3 + Linux 5.0 File-System Benchmarks: Btrfs vs. EXT4 vs. F2FS vs. XFS https://www.phoronix.com/scan.php?page=article=linux-50-filesystems=3 So looks like XFS won. I like XFS for its ease of use especially when growing. Any ideas

Re: Pg10 : Client Configuration for Parallelism ?

By the way On 4/17/2019 7:26, laurent.decha...@orange.com wrote: I can see whether there is parallelism with pg_top or barely top on the server. PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 38584 postgres 20 0 8863828 8.153g 8.151g R 100.0 3.2 1:23.01

Re: Pg10 : Client Configuration for Parallelism ?

On 4/17/2019 4:33, Thomas Kellerer wrote: A CTE would prevent parallelism. You mean like always? His SELECT count(1) FROM BigTable would be parallel if run alone but as WITH Data AS (SELECT count(1) FROM BigTable) SELECT * FROM Data nothing would be parallel any more? How about: SELECT *

Is there a known bug with SKIP LOCKED and "tuple to be locked was already moved to another partition due to concurrent update"?

Hi all, long time ago I devised with your help a task queuing system which uses SELECT ... FOR UPDATE SKIP LOCKED for many parallel workers to find tasks in the queue, and it used a partitioned table where the hot part of the queue is short and so the query for a job is quick and the skip

Conundrum with scaling out of bottleneck with hot standby, PgPool-II, etc.

Hi all, I have a general question on scaling PostgreSQL for unlimited throughput, based on some experience. TL;DR: My question is: given that the work-load on any secondary/standby database server is almost the same as that of the master database server, is there any point to bother with

PosgtgreSQL hot standby reading WAL from muli-attached volume?

Hi, I wonder can we set up a hot standby in such a way that we don't need any log streaming nor shipping, where instead every hot standby just mounts the same disk in read-only mode which the master uses to write his WAL files? Even without a clustered file system, e.g., a UFS on FreeBSD,

Re: Postgres performance comparing GCP and AWS

Hi Maurici, in my experience the key factor about speed in big queries is sequential scan. There is a huge variance in how the system is tuned. In some cases I cannot read more than 10 MB/s, in others I get to expect 20-40 MB/s. But then, when things are tuned well and the parallel workers

Faster more low-level methods of having hot standby / secondary read-only servers?

I was on this DBA.StackExchange  question https://dba.stackexchange.com/questions/316715/in-postgresql-repmgr-master-slave-environment-is-it-necessary-to-have-same-h-w-c/316756, and which reminded me of my experience and disappointment with the hot-standby design. Say you have millions of

When you really want to force a certain join type?

I have a complex query which essentially runs a finite state automaton through a with recursive union, adding the next state based on the previous.  This is run at 100,000 or a million start states at the same time, picking a new record (token), matching it to the FSA (a three-way join:

Re: When you really want to force a certain join type?

On 12/28/2022 10:48 AM, Justin Pryzby wrote: Maybe the new parameter in v15 would help. https://www.postgresql.org/docs/15/runtime-config-query.html#GUC-RECURSIVE-WORKTABLE-FACTOR recursive_worktable_factor (floating point) Sets the planner's estimate of the average size of the working