<p>I'm setting up a web application with a FreeBSD PostgreSQL back-end. I'm looking for some database performance optimization tool/technique.</p>

[{'answer_id': 37083, 'author': 'Jason Baker', 'author_id': 2147, 'author_profile': 'https://Stackoverflow.com/users/2147', 'pm_score': 4, 'selected': True, 'text': '<p><a href="http://pgfouine.projects.postgresql.org/" rel="noreferrer">pgfouine</a> works fairly well for me.  And it looks like there\'s a <a href="http://portsmon.freebsd.org/portoverview.py?category=databases&amp;portname=pgfouine" rel="noreferrer">FreeBSD port</a> for it.</p>\n'}, {'answer_id': 37086, 'author': 'Paul Tomblin', 'author_id': 3333, 'author_profile': 'https://Stackoverflow.com/users/3333', 'pm_score': 1, 'selected': False, 'text': '<p>Well, the first thing to do is try all your queries from psql using "explain" and see if there are sequential scans that can be converted to index scans by adding indexes or rewriting the query.</p>\n\n<p>Other than that, I\'m as interested in the answers to this question as you are.</p>\n'}, {'answer_id': 65180, 'author': 'brianestes', 'author_id': 8993, 'author_profile': 'https://Stackoverflow.com/users/8993', 'pm_score': 2, 'selected': False, 'text': "<p>I've used pgtop a little. It is quite crude, but at least I can see which query is running for each process ID.</p>\n\n<p>I tried pgfouine, but if I remember, it's an offline tool.</p>\n\n<p>I also tail the psql.log file and set the logging criteria down to a level where I can see the problem queries.</p>\n\n<pre><code>#log_min_duration_statement = -1        # -1 is disabled, 0 logs all statements\n                                        # and their durations, &gt; 0 logs only\n                                        # statements running at least this time.\n</code></pre>\n\n<p>I also use EMS Postgres Manager to do general admin work. It doesn't do anything for you, but it does make most tasks easier and makes reviewing and setting up your schema more simple. I find that when using a GUI, it is much easier for me to spot inconsistencies (like a missing index, field criteria, etc.). It's only one of two programs I'm willing to use VMWare on my Mac to use.</p>\n"}, {'answer_id': 113340, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Check out Lightning Admin, it has a GUI for capturing log statements, not perfect but works great for most needs.  <a href="http://www.amsoftwaredesign.com" rel="nofollow noreferrer">http://www.amsoftwaredesign.com</a></p>\n'}, {'answer_id': 3123986, 'author': 'John P', 'author_id': 94629, 'author_profile': 'https://Stackoverflow.com/users/94629', 'pm_score': 2, 'selected': False, 'text': '<p>Munin is quite simple yet effective to get trends of how the database is evolving and performing over time. In the standard kit of Munin you can among other thing monitor the size of the database, number of locks, number of connections, sequential scans, size of transaction log and long running queries. </p>\n\n<p>Easy to setup and to get started with and if needed you can write your own plugin quite easily.</p>\n\n<p>Check out the latest postgresql plugins that are shipped with Munin here:</p>\n\n<p><a href="http://munin-monitoring.org/browser/branches/1.4-stable/plugins/node.d/" rel="nofollow noreferrer">http://munin-monitoring.org/browser/branches/1.4-stable/plugins/node.d/</a></p>\n'}, {'answer_id': 11556903, 'author': 'dhorton', 'author_id': 1537283, 'author_profile': 'https://Stackoverflow.com/users/1537283', 'pm_score': -1, 'selected': False, 'text': '<p>DBTuna <a href="http://www.dbtuna.com/postgresql_monitor.php" rel="nofollow">http://www.dbtuna.com/postgresql_monitor.php</a> has recently started supporting PostgreSQL monitoring. We use it extensively for MySQL monitoring, so if it provides the same for Postgres then it should be a good fit for you too.</p>\n'}, {'answer_id': 20422573, 'author': 'Michael Renner', 'author_id': 183622, 'author_profile': 'https://Stackoverflow.com/users/183622', 'pm_score': 3, 'selected': False, 'text': '<p>Database optimization is usually a combination of two things</p>\n\n<ol>\n<li>Reduce the number of queries to the database</li>\n<li>Reduce the amount of data that needs to be looked at to answer queries</li>\n</ol>\n\n<p>Reducing the amount of queries is usually done by caching non-volatile/less important data (e.g. "Which users are online" or "What are the latest posts by this user?") inside the application (if possible) or in an external - more efficient - datastore (memcached, redis, etc.). If you\'ve got information which is very write-heavy (e.g. hit-counters) and doesn\'t need <a href="http://en.wikipedia.org/wiki/ACID" rel="noreferrer">ACID</a>-semantics you can also think about moving it out of the Postgres database to more efficient data stores.</p>\n\n<p>Optimizing the query runtime is more tricky - this can amount to creating <a href="http://www.postgresql.org/docs/current/static/indexes-partial.html" rel="noreferrer">special indexes</a> (or <a href="http://www.postgresql.org/docs/current/static/indexes.html" rel="noreferrer">indexes in the first place</a>), changing (possibly denormalizing) the data model or changing the fundamental approach the application takes when it comes to working with the database. See for example the <a href="https://wiki.postgresql.org/images/3/35/Pagination_Done_the_PostgreSQL_Way.pdf" rel="noreferrer">Pagination done the Postgres way</a> talk by <a href="http://use-the-index-luke.com/" rel="noreferrer">Markus Winand</a> on how to rethink the concept of pagination to make it more database efficient </p>\n\n<h2>Measuring queries the slow way</h2>\n\n<p>But to understand which queries should be looked at first you need to know how often they are executed and how long they run on average. </p>\n\n<p>One approach to this is logging all (or "slow") queries including their runtime and then parsing the query log. A good tool for this is <a href="http://pgfouine.projects.pgfoundry.org/" rel="noreferrer"><code>pgfouine</code></a> which has already been mentioned earlier in this discussion, it has since been replaced by <a href="http://dalibo.github.io/pgbadger/" rel="noreferrer"><code>pgbadger</code></a> which is written in a more friendly language, is much faster and more actively maintained.</p>\n\n<p>Both <code>pgfouine</code> and <code>pgbadger</code> suffer from the fact that they need query-logging enabled, which can cause a noticeable performance hit on the database or bring you into disk space troubles on top of the fact that parsing the log with the tool can take quite some time and won\'t give you up-to-date insights on what is going in the database.</p>\n\n<h2>Speeding it up with extensions</h2>\n\n<p>To address these shortcomings there are now two extensions which track query performance directly in the database - <a href="http://www.postgresql.org/docs/current/static/pgstatstatements.html" rel="noreferrer"><code>pg_stat_statements</code></a> (which is only helpful in version 9.2 or newer) and <a href="https://github.com/2ndQuadrant/pg_stat_plans" rel="noreferrer"><code>pg_stat_plans</code></a>. Both extensions offer the same basic functionality - tracking how often a given "normalized query" (Query string minus all expression literals) has been run and how long it took in total. Due to the fact that this is done while the query is actually run this is done in a very efficient manner, the measurable overhead was less than 5% in synthetic benchmarks.</p>\n\n<h2>Making sense of the data</h2>\n\n<p>The list of queries itself is very "dry" from an information perspective. There\'s been work on a third extension trying to address this fact and offer nicer representation of the data called <a href="http://pgstatsinfo.projects.pgfoundry.org/" rel="noreferrer"><code>pg_statsinfo</code></a> (along with <code>pg_stats_reporter</code>), but it\'s a bit of an undertaking to get it up and running.</p>\n\n<p>To offer a more convenient solution to this problem I started working on a commercial project which is focussed around <code>pg_stat_statements</code> and <code>pg_stat_plans</code> and augments the information collected by lots of other data pulled out of the database. It\'s called <code>pganalyze</code> and you can find it at <a href="https://pganalyze.com/" rel="noreferrer">https://pganalyze.com/</a>.</p>\n\n<p>To offer a concise overview of interesting tools and projects in the Postgres Monitoring area i also started compiling a list at the <a href="http://wiki.postgresql.org/wiki/Monitoring" rel="noreferrer">Postgres Wiki</a> which is updated regularly.</p>\n'}]
['https://Stackoverflow.com/questions/37056', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2313/']