6 ms·
Understanding Postgres Performance
- krat0sprakhar 14y agoSlightly 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. 1- http://www.postgresguide.com/ http://www.postgresguide.com/ 2 - http://www.amazon.com/gp/product/1590594789 http://www.amazon.com/gp/product/1590594789
- craigkerstiens 14y agoWhat content would you like to see within Postgresguide?
- jcampbell1 14y agoProbably a good idea to add a bit about serial, and sequences. This tends to confuse mysql people like myself. I for instance don't know what happens when I copy or dump and restore a postgres table that relies on a sequence. Mysql auto-magically takes care of it by saving the next auto increment id in table dump or copy.
- joevandyk 14y agoIf you use pg_dump to dump/restore the table, it will reset the sequence to the correct value. Look at the dump file, there will be a setval of the sequence.
- jeltz 14y agoAgreed, especially since sequences is a more powerful tool with a wider use than auto increment. For example you can use the same sequence for multiple tables, or use them without being connected to any table.
- mattyb 14y agoThe recently released 'PostgreSQL: Up and Running' may be right up your alley, at only 168 pages: http://shop.oreilly.com/product/0636920025061.do http://shop.oreilly.com/product/0636920025061.do
- atsaloli 14y agohttp://www.wikivs.com/wiki/MySQL_vs_PostgreSQL http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL MySQL vs PostgreSQL - an objective comparison
- rpq1480 14y agoOff topic: grok is now my new favorite word.
- djabatt 14y agoGrok was a favorite word in the .com collapse days with marketing types. Just saying.
- liotier 14y agoGrok : favorite word since 1961 - and quite well documented... http://en.wikipedia.org/wiki/Grok http://en.wikipedia.org/wiki/Grok
- djabatt 14y agoGrok was a favorite word in the .com collapse days with marketing types. Just saying.
- Someone 14y agoNew? It's over 50 years old: http://en.wikipedia.org/wiki/Grok http://en.wikipedia.org/wiki/Grok
- adamtj 14y ago"new favorite word" not "favorite new word". Also, see http://xkcd.com/1053/ http://xkcd.com/1053/
- omni 14y agoHe said "my new favorite word," not "my favorite new word." You could have linked to your potentially-of-interest article without taking a jab at the original commenter. edit: This is what I get for leaving pages open and coming back to them later.
- btilly 14y agoI would be more cautious about indexes than this article is. 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. 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. 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.)
- andrewcooke 14y agoam i wrong in thinking that a missing index can change an O(logn) query to an O(n), while an additional index is going to add a constant cost to writes? because if that's correct then it makes sense to err on the side of too many indices. of course, it is better to be perfect. but if you have an error, which side would you prefer to be on? and you don't need "significant" volumes to hurt with a full table scan (ie it will hurt most of you). [what surprised me was the 99% cache. that is very dependent on your application, but i guess is common for web apps?]
- craigkerstiens 14y agoThe 99% cache recommendation is absolutely for web applications. Most web applications will want queries to return in less than 10 ms and are only retrieving a small amount of data. With data warehousing and reporting applications this definitely changes.
- chanks 14y agoOne thing people don't mention that often with Postgres, but is worth some attention, is the Heap-Only Tuples (HOT) optimization that was added in 8.3. During an UPDATE, Postgres will try to fit the new version of the row on the same page as the old one - if it's able to do this, it means the indices don't have to be updated at all. So if you're expecting a table to have a heavy UPDATE load it may be a good idea to lower its fillfactor from the default of 100 (for example, ALTER TABLE foo SET (fillfactor = 95);). This means that once a page is 95% full no new rows will be inserted into it, and the remaining 5% will be available for updated rows. HOT will also do "mini-vacuums" on individual pages as needed to free up space. Of course, it's up to you to decide whether this tradeoff is worth increasing the effective size of your table by ~5%. But even tables with fillfactors at the default of 100 will get some benefit from HOT. More info: https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT https://github.com/postgres/postgres/blob/master/src/backend... Edit to add: One caveat is that HOT won't work if you UPDATE a column that is indexed - then, of course, Postgres will have to touch the index.
- CCs 14y agoI really liked the NewRelic graph and I wanted to know if I can get it running on my setup: Scala, sbt, liftweb, Jetty. It worked! Just downloaded the ZIP, unpacked and started: sbt ~container:start -J"-javaagent:/path/myapp/newrelic/newrelic.jar" 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.
- madelfio 14y agoI 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: 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;
- Axsuul 14y agoA better query to see how indexes are used to avoid division by zero errors. 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;