How to profile PostgreSQL Database?

Question

I want to profile (keep an Eye on) all the activities that goes on in a Database which is in PostgreSQL.

Is there any such utility which will help me do this?

rebra · Accepted Answer · 2008-12-13 12:05:29Z

37

"Keep an eye on" and "profile" are two quite different tasks in my view.

For profiling (not a live view on what's going on right now, but to see which queries take most time etc), check out pgFouine:

http://pgfouine.projects.postgresql.org/

This will let you see which queries are resource intensive, and take appropriate action: Add missing indexes, rewrite queries using other techiques etc.

edited Dec 13, 2008 at 12:05

answered Dec 13, 2008 at 11:52

rebra

1,0882 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EAmez Over a year ago

Link is dead. Read @l-g answer: pgbadger as substitute.

bortzmeyer · Accepted Answer · 2008-12-13 22:13:36Z

21

For "keeping an eye", I use pgtop, a program which deliberately mimics Unix 'top' command.

answered Dec 13, 2008 at 22:13

bortzmeyer

35.8k13 gold badges72 silver badges94 bronze badges

3 Comments

Gunnlaugur Briem Over a year ago

To save someone a minute or two: I found this only under the package name ptop in Debian, though the command itself is called pg_top.

Asnad Atta Over a year ago

link is not available anymore?

some Over a year ago

@GunnlaugurBriem Since version 4 it has been renamed to pgtop.

lofidevops · Accepted Answer · 2022-02-10 22:35:31Z

13

PgBadger is an actively maintained profiling tool that provides very detailed reports and graphs (see examples). It can process large volumes of PostgreSQL logs to answer questions like:

The most frequent waiting queries.
Queries that waited the most.
The slowest queries.

(PgBadger was explicitly built to "replace and outperform" pgFouine, which has not been maintained since about 2010.)

edited Feb 10, 2022 at 22:35

lofidevops

17.4k17 gold badges93 silver badges128 bronze badges

answered Jun 12, 2014 at 14:35

L. G.

9,7817 gold badges60 silver badges81 bronze badges

Comments

mehmet · Accepted Answer · 2017-06-03 02:38:21Z

6

Well, if you're looking at what's going on, regarding selects, updates, deletes, and so on, there are a few views in the pg_catalog schema, I mainly use pg_stat_user_tables and pg_stat_user_indexes but there are many more, all within pg_stat*.

There also is the pg_stat_activity view which tells you what's running on your server right now.

I've hacked together four munin plugins that uses the user_tables and user_indexes, they're available there

edited Jun 3, 2017 at 2:38

mehmet

8,2746 gold badges47 silver badges51 bronze badges

answered Dec 13, 2008 at 11:11

mat

13.4k5 gold badges44 silver badges45 bronze badges

3 Comments

Antony Over a year ago

mat, do you still have the plugins available somewhere? The link points to 404 not found

mat Over a year ago

Hum, unless I'm mistaken, munin comes with a dozen plugins for postgresql that do exactly what mine did. If I'm mistaken, tell me, I'll try to find out what happened with the ones I crafted.

EAmez Over a year ago

Munin project here

Patryk Kordylewski · Accepted Answer · 2008-12-13 12:14:03Z

2

Have a look at Nagios-Plugin script or check_postgres.pl

edited Dec 13, 2008 at 12:14

answered Dec 13, 2008 at 11:41

Patryk Kordylewski

1,2691 gold badge7 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to profile PostgreSQL Database?

5 Answers 5

1 Comment

3 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related