Understanding Postgres Performance

I would be more cautious about indexes than this article is.<p>The trade-off with indexes is that they make reads faster and writes slower. But if you're running a high performance application, it is not that hard to have read-only slaves to offload read queries to. (I won't say that it is trivial, because there are issues, but it is doable. I've done it.) However fixing poor write performance is much more complicated.<p>Therefore create all of the indexes that you need. But no more. And any indexes that are not carrying their weight should be dropped. Furthermore if you have intensive queries that you have to do (eg for reports), consider offloading those to another database.<p>Of course if you don't have significant data/volume/etc, then don't worry about this. Any semi-sane approach will work for you. (That applies to most of you.)

Slightly OT: I've been using MySQL for quite some time now and whenever I start a new project thinking that I'll use Postgres this time I find myself lazily opting for something that I know and going back to MySQL. Is there a Postgres guide that is concise, easy to follow and illustrates its advantages over other relational databases. The Postgres guide[1] is too sparse currently and the beginner Apress book[2] mentioned is 644 pages long.<p>1- <a href="http://www.postgresguide.com/" rel="nofollow">http://www.postgresguide.com/</a><p>2 - <a href="http://www.amazon.com/gp/product/1590594789" rel="nofollow">http://www.amazon.com/gp/product/1590594789</a>

I really liked the NewRelic graph and I wanted to know if I can get it running on my setup: Scala, sbt, liftweb, Jetty.<p>It worked! Just downloaded the ZIP, unpacked and started: sbt ~container:start -J"-javaagent:/path/myapp/newrelic/newrelic.jar"<p>Now I have a nice graph which matches up with the numbers I see in my logs. As I suspected there's some work that needs to be done on the DB access side.

A better query to see how indexes are used to avoid division by zero errors.<p><pre><code> SELECT relname, CASE WHEN seq_scan = 0 THEN 0 ELSE 100 * idx_scan / (seq_scan + idx_scan) END AS percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;</code></pre>

I think the ratio expressions are slightly off. Using the queries from the article can result in negative ratios, which shouldn't be possible if we're measuring the percent of accesses that hit the buffer. Each access is either a cache hit or a read, not both. So instead of subtracting in the numerator, they should be added in the denominator:<p><pre><code> SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;</code></pre>

Off topic: grok is now my new favorite word.