Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vXivW-003ALq-19 for pgsql-admin@arkaria.postgresql.org; Mon, 22 Dec 2025 16:38:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vXivV-00FVS9-0G for pgsql-admin@arkaria.postgresql.org; Mon, 22 Dec 2025 16:38:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vXivU-00FVS1-1v for pgsql-admin@lists.postgresql.org; Mon, 22 Dec 2025 16:38:29 +0000 Received: from mail-lj1-x236.google.com ([2a00:1450:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vXivS-0029J9-34 for pgsql-admin@lists.postgresql.org; Mon, 22 Dec 2025 16:38:29 +0000 Received: by mail-lj1-x236.google.com with SMTP id 38308e7fff4ca-37fd6e91990so31082801fa.3 for ; Mon, 22 Dec 2025 08:38:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1766421505; x=1767026305; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=DMT40gXRPRqCPiNp87DXdjmCDcOZ0GnVDOyC0eN6ijg=; b=XTQb5VUmsNyD4w70x16+lhxhZQIrqt8TZsvXswMtcNP5Yb62xW4+DIVBk3mSMr0uD7 xtwtiNWUbT2Nbtiecaq8GTI+aP81IW1s5tZZf8IS4OwMiJA4NpMaK07Fvq4ZPJy8KEy5 YyotY+yGPlKG2S7rCATDMahpIKg23kFayW8XkCS+jWRWPW9ZI9/lGy4Z0RO8GpTDdvea aJzdO7gcgn+jSTFPDa51tiQbBzx3RuHLtKF4/fn/jou36rFw06KwSo3/yNVorEMIrNy+ tPIvEfhCSMy0wwwPqLYgekOh1thzA4X9xakpOEwMD2DFY80PtRnT4DPqIYpt1ZTqdUrO th2g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1766421505; x=1767026305; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-gg:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=DMT40gXRPRqCPiNp87DXdjmCDcOZ0GnVDOyC0eN6ijg=; b=I4VPl6Au/3zuLrM7lNSJxceG9NVGnIkwGlsoo4Aq/lhycl63GtB647RR0QKBU996sS x6FknRdfYQ83B4/ZA7wnyprrl4xZ39mS/qB2BKQKCoAGA9Ziw8NvmJvfaDVCjrI63vRb UGzymg1+NGEWAPltsERUVlqVLvY0gE4YjH4A0G/aeY0OUxlxvdAbWdOjS1+D8cIZzdrC +6jaq2A3/wWP5pEPwSx33rHjguoPhmGn/Fm2NMsCI/WVKKMmgl8iGBFrs0EyfjbFeXxJ 4osmQkVmxQrrK3acxQp95RjuupxzfnjysS7o/tWQyvcj72JYwMuR17kGG4T/ZPdEyxat sGTQ== X-Gm-Message-State: AOJu0Yx7zgEA512WjzYRe/yHUqJhWBhNO/CJShWU4YqkkcEkuhvmqM3b NOzxHDJI3y5ZqEDPX4ZOEYw+Al7Gux7xZzlfJUM2aCVq7PZoSKHLzN82yD2EIA== X-Gm-Gg: AY/fxX7RE9jIpzuGdnqsQXLCm8c4+hneUS6XW51TulJafcMRO8f1yS61OCF8P5Mumed 42P4uLi7WTjZmM7q8cqjN1eG8c729aganWHmJ/cKYp+a2pPO+c+5BAQ2XUkKkQsc4Oymk2f96gN L5gHrJYcwNk4lZdMOAlolV1183YMxOI53fTdK7nrqiDCPSzgH84sFBWogrw95MIfZ5VRsmrwGli /UhxSjALQ5SRp1w1Pzu3buzPpODkrMuzvZw0F0vuwAtwqp+loEOeebejw9YE+hEO2aj+PhW8y4L 1oaAR8ZjxFZTnVftNQmoDl+UdUj69Pog3xHk2YQU7qiLDeG40Ots/wUC3fCHtafSpnU1T0UtOaO fPJBz2B3zFkHLhkeb+5CZrr0ZmmdYtgD7BhaqGPZqx7Q+KU9vcUQkbWs+wnzv7VAbi2HcYXQn3T KYLUn6B2d+1pxRbDudJUn99vkLPT29LiqXz3PgQ++/vb0x3Q== X-Google-Smtp-Source: AGHT+IFhMiuBO/ylNN86K0GtFwwYaiRzwYLH4Pi9ClyRRK20LQ/X4w+dGjTEDEeh/KADf5LbJNyZug== X-Received: by 2002:a2e:be0f:0:b0:37f:c796:7867 with SMTP id 38308e7fff4ca-38121605a65mr36033661fa.13.1766421504296; Mon, 22 Dec 2025 08:38:24 -0800 (PST) Received: from smtpclient.apple ([2001:2043:1e5c:3f00:c872:345b:bc7f:aebc]) by smtp.gmail.com with ESMTPSA id 38308e7fff4ca-3812262ccfasm27118261fa.26.2025.12.22.08.38.23 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 22 Dec 2025 08:38:23 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-9E4870C3-42C8-4BB8-9532-EE48E258CC61 Content-Transfer-Encoding: 7bit From: =?utf-8?Q?P=C3=A4r_Mattsson?= Mime-Version: 1.0 (1.0) Subject: Re: Performance issue! Date: Mon, 22 Dec 2025 17:38:13 +0100 Message-Id: References: <3547F473-3C9B-7642-BDFB-1AEF2A3DADEB@hxcore.ol> Cc: Pgsql-admin In-Reply-To: <3547F473-3C9B-7642-BDFB-1AEF2A3DADEB@hxcore.ol> To: ceo@goodfellas.agency X-Mailer: iPhone Mail (22G100) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-9E4870C3-42C8-4BB8-9532-EE48E258CC61 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi! It is Postgres version 16.
Thanks a= lot for the answer!
Regards P=C3=A4r
Mobile: +46 70 606 96 45

22 dec. 2025 kl. 17:26 skrev CEO Goodfellas H= oldings LLC <ceo@goodfellas.agency>:

=EF=BB=BF
Hi everyone, I=E2=80=99m g= onna answer the gentleman. Maybe I can share some wealth . 
T= he disclaimer is I have a strong AI background and I=E2=80=99m running a per= sonal multi boat with API connections to five different providers and then I= run the responses through vertex to ensure reliability. But we all know tha= t it=E2=80=99s always a capture so happy holidays and hope this helps!
=

 Yep =E2=80=94 here are a few Postgres perfor= mance script =E2=80=9Cbundles=E2=80=9D I=E2=80=99ve used in real life that p= roduce clean, executive-friendly output (and are safe to run in production i= f you keep the sampling windows sane). They=E2=80=99re grouped by what stake= holders usually ask: =E2=80=9CIs the DB slow?=E2=80=9D =E2=80=9CWhat=E2=80=99= s slow?=E2=80=9D =E2=80=9CWho=E2=80=99s causing it?=E2=80=9D =E2=80=9CIs it I= O/CPU/locks?=E2=80=9D =E2=80=9CIs it indexes?=E2=80=9D



=


1) =E2=80=9CWhat=E2=80=99s= happening right now?=E2=80=9D (top waits, locks, long queries)




1A. Active sessions + wait reasons (quick triage)<= /b>


SELECT

  now() AS ts,

  pid,

= =   usename,

&nb= sp; application_name,

  client_addr,

  state,

<= span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&= nbsp; wait_event_type,

  wait_event,

  date_trunc('second', now() - query_start) AS runtime,

<= p style=3D"line-height: normal; margin: 0px;">  left(regexp_replace(que= ry, '\s+', ' ', 'g'), 140) AS query_140

FROM pg_stat_activity

WHERE pid <> pg_backend_pid()

  AND state <> 'idle'

ORDER BY (now() - que= ry_start) DESC

LIMI= T 30;


1B. Blocking / blocked chain= (this one wins political battles)


WITH blocked AS (

  SELECT

    a.pid AS bloc= ked_pid,

  &nb= sp; a.usename AS blocked_user,

    a.application_name AS blocked_app,

    a.client_addr AS blo= cked_client,

  &= nbsp; now() - a.query_start AS blocked_runtime,

    left(regexp_replace(a.query, '\s+= ', ' ', 'g'), 120) AS blocked_query,

    unnest(pg_blocking_pids(a.pid)) AS blocking_= pid

  FROM pg_= stat_activity a

&nb= sp; WHERE a.state <> 'idle'

    AND cardinality(pg_blocking_pids(a.pid)) > 0<= /span>

)

SELECT

  b.blocked_pid,

  b.blocked_user,

  b.blocked_app,

  b.blocked_client,

  date_trunc('second', b.bloc= ked_runtime) AS blocked_runtime,

  b.blocked_query,

  b.blocking_pid,

  a.usename AS blocking_user,

  a.application_name AS blocking_ap= p,

  date_trun= c('second', now() - a.query_start) AS blocking_runtime,

  left(regexp_replace(a.query, '\s+= ', ' ', 'g'), 120) AS blocking_query

FROM blocked b

JOIN pg_stat_activity a ON a.pid =3D b.blocking_pid

<= p style=3D"line-height: normal; margin: 0px;">ORDER BY b.blocked_runtime DES= C;


1C. Heavy relations currently l= ocked (what table is =E2=80=9Con fire=E2=80=9D)


SELECT

  c.relname AS r= elation,

  l.m= ode,

  l.grant= ed,

  count(*)= AS lock_count

FROM= pg_locks l

JOIN pg= _class c ON c.oid =3D l.relation

WHERE c.relkind IN ('r','p','i','m')  -- table/partition/i= ndex/materialized view

GROUP BY 1,2,3

= = ORDER BY lock_count DESC, relation

LIMIT 50;





2) =E2=80=9CWhat are the slowest queries?=E2=80=9D (requi= res pg_stat_statements)



If you can enable one extension for performanc= e work, make it this one.

You=E2=80=99ll need shared_preload_libra= ries =3D 'pg_stat_statements' and CREATE EXTENSION pg_stat_statements;



<= p style=3D"line-height: normal; margin: 0px 0px 14px;">2A.= Top total time (who costs you the most)

<= br>

SELECT

  calls,

  round(total_exec_time::num= eric, 1) AS total_ms,

  round(mean_exec_time::numeric, 3) AS mean_ms,

  rows,

  round(100.0 * shared_blks_hit / NULLIF(= shared_blks_hit + shared_blks_read,0), 2) AS cache_hit_pct,

  left(regexp_replace(query, '= \s+', ' ', 'g'), 160) AS query_160

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 20;


=

2B. Top mean time (classic =E2=80=9Cone query ruins the day=E2=80=9D= list)


SELECT

  calls,

  round(mean_exec_time::numeric, 3) AS mean_ms,

  round(stddev_exec_time::nume= ric, 3) AS stddev_ms,

  round(max_exec_time::numeric, 1) AS max_ms,

  rows,

  left(regexp_replace(query, '\s+', ' ', '= g'), 160) AS query_160

FROM pg_stat_statements

WHERE calls >=3D 10

ORDER BY mean_exec_time DESC

LIMIT 20;


2= C. IO-heavy queries (read storms)


SELECT

  calls,

  (shared_blks_read + local_blks_re= ad) AS blks_read,

<= span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&= nbsp; (shared_blks_hit  + local_blks_hit)  AS blks_hit,

=

  round( (blk_read_time += blk_write_time)::numeric, 1) AS io_ms,

  round(total_exec_time::numeric, 1) AS total_ms,<= /span>

  left(regexp_= replace(query, '\s+', ' ', 'g'), 160) AS query_160

FROM pg_stat_statements

ORDER BY (shared_blks_read + local_blks_= read) DESC

LIMIT 20= ;



<= /p>



3) =E2= =80=9CAre we index/scan healthy?=E2=80=9D (high-signal, low-noise)



<= p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><= span style=3D"font-family: "Times New Roman"; font-size: 12px; col= or: rgb(0, 0, 0);">

3A. Tables with heavy sequential scans


SELECT

  relname,

&n= bsp; seq_scan,

&nbs= p; seq_tup_read,

&n= bsp; idx_scan,

&nbs= p; n_live_tup,

&nbs= p; round(100.0 * idx_scan / NULLIF(seq_scan + idx_scan,0), 2) AS idx_ratio_p= ct

FROM pg_stat_use= r_tables

WHERE (seq= _scan + idx_scan) > 0

ORDER BY seq_tup_read DESC

LIMIT 25;


3B. Index hit= ratio (quick =E2=80=9Ccache health=E2=80=9D indicator)


<= span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">S= ELECT

  datname= ,

  round(100.= 0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,<= /p>

  blks_read,

  blks_hit

<= p style=3D"line-height: normal; margin: 0px;">FROM pg_stat_database

ORDER BY cache_hit_pct ASC N= ULLS LAST;



=



4) =E2=80=9CAutovacuum / bloat / dead tuples=E2=80=9D (the silent killer= )




4A. Dead tuples & vacuum stats (= which tables need love)


SELECT

  relname,

  n_live_tup,

  n_dead_tup,

  round(100.0 * n_dead_tup / NULLIF(n_live_t= up + n_dead_tup,0), 2) AS dead_pct,

  last_vacuum,

  last_autovacuum,

  last_analyze,

  last_autoanalyze

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 25;


4B. Autovacuum activity =E2=80=9Cright now=E2=80=9D


SELECT

  pid,

  d= atname,

  usen= ame,

  state,<= /span>

  wait_event_t= ype,

  wait_ev= ent,

  date_tr= unc('second', now() - query_start) AS runtime,

  left(regexp_replace(query, '\s+', ' ', 'g'= ), 140) AS query_140

WHERE query ILIKE 'autovacuum:%'

ORDER BY (now() - query_start) DESC;



=


5) =E2=80=9COne-liner repo= rt=E2=80=9D you can paste into Slack (executive snapshot)


  now= () AS ts,

  (S= ELECT count(*) FROM pg_stat_activity WHERE state <> 'idle') AS active_= sessions,

  (S= ELECT count(*) FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND s= tate <> 'idle') AS waiting_sessions,

  (SELECT count(*) FROM pg_stat_activity WHERE c= ardinality(pg_blocking_pids(pid)) > 0) AS blocked_sessions,

  (SELECT round(100.0 * blks= _hit / NULLIF(blks_hit + blks_read,0), 2)

   FROM pg_stat_database WHERE datname =3D c= urrent_database()) AS cache_hit_pct;





How to turn these into a =E2=80=9Cnice output=E2= =80=9D script (repeatable + pretty)



If you want one script that prints= sections with headers, timestamps, and consistent columns, the cleanest rou= te is:


  • ps= ql + \echo headers + \x auto (fast, minimal dependencies)
  • Or a small Bash/Python wrappe= r that runs each query, renders tables, and optionally exports CSV/JSON.



=

= Here=E2=80=99s a simple pattern for psql:

\timing on

\x auto

\pset pager off


\echo '=3D=3D=3D ACTIVE SESSIONS (top 30) =3D=3D=3D'

-- paste query 1A


\echo '=3D=3D=3D BLOCKING CHAINS =3D=3D= =3D'

-- paste query= 1B


\echo '=3D=3D= =3D TOP QUERIES (TOTAL TIME) =3D=3D=3D'

-- paste query 2A

Run it:

psql "postgresql://user:pass@host:5432/d= bname" -f perf_report.sql





What I=E2=80=99d enable day-1 on any new project (minimal= , high ROI)



  1. pg_stat_statements (= query-level truth)
  2. auto_explain (optional: catches slow queries at runtime, but use car= efully)
  3. log= _min_duration_statement (or sampling) for forensic timeline correlation
    4. =



<= p style=3D"line-height: normal; margin: 0px 0px 12px;">T= hese are standard Postgres instrumentation options discussed in official doc= s for pg_stat_statements and statistics views (pg_stat_activity, pg_locks, p= g_stat_database, etc.).




If you tell me (= a) Postgres version, (b) managed service vs self-hosted, and (c) whether you= can enable pg_stat_statements, I=E2=80=99ll bundle this into a single =E2=80= =9CPerf Pack=E2=80=9D file with:


  • perf_report.sql (human-readable)
  • perf_report.csv export mode
    • <= li style=3D"font-family: "Times New Roman"; font-size: 19px; color= : rgb(0, 0, 0); line-height: normal; margin: 0px 0px 12px;">optional =E2=80=9C= baseline vs now=E2=80=9D delta mode (best for stakeholder questions like =E2= =80=9Cdid it get worse after deploy?=E2=80=9D)

Lazaro Sanchez CEO at Cyberellum Technologies & Lab= oratory

ceo@cyberellum.t= echnology 





From: P=C3=A4r Mattsson <par.x.mattsso= n@gmail.com>
Sent: Monday, December 22, 2025 10:35 AM
<= b>To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Su= bject: Performance issue!
 
Hi!
Anyone who have performance scripts with nice output to share.
<= br>
I am in a new project where we got lots of questions about performance on th= e database!


Mvh P=C3=A4r
= --Apple-Mail-9E4870C3-42C8-4BB8-9532-EE48E258CC61--