4 ms·

A lot of this advice is not very good, or at best is misleading. 1) Turning "enable_seqscan" off is almost never a good idea. If Postgres isn't using an index

by birken 15y ago

A lot of this advice is not very good, or at best is misleading. 1) Turning "enable_seqscan" off is almost never a good idea. If Postgres isn't using an index that you think it should, you should figure out the underlying reason why. This would be akin to killing a fly with a sledgehammer. 2) The main thing that is going to impact whether a given index is in memory is how often that index is used, not it's size. The advice about reducing the size of indexes is true (in that a smaller index uses less memory, which is always good), but if you have 10 indexes and you only actively use 1 of them, then those other 9 indexes aren't going be taking up much memory. The biggest issue is they are going to slow down your inserts. 3) Manually vacuuming isn't really necessary for newer versions of Postgres (>8.3 I believe, where autovaccum is enabled by default). There are times when you do need to VACCUM, but probably not that often in standard usage. Here is some simple practice advice for postgres users: 1) If you are using an older version of Postgres, upgrade to Postres 9. It has a lot of huge improvements over previous versions. 2) Purchase & read Greg Smith's PostgreSQL 9.0 High Performance (as mentioned previously by another poster). This book is phenomenal.

frankwiles 15y ago
Agreed! Most of this advice isn't that useful and in all honesty can be bad advice for many sites. Really "fast websites" use external caching to not hit the database at all.
- epi0Bauqu 15y ago
  The post wasn't about anything but the DB layer. I cache everything, but still when you hit the DB layer, you want it to return instantly if at all possible. I said specifically at the very top this wasn't a tuning or scaling guide.
epi0Bauqu 15y ago
1) I said that was "crazier" and pointed to the thread saying it was generally a bad idea. That is, I wasn't really endorsing it, just offering it as a last ditch option. 2) The idea (sorry if wasn't clear) but to make sure the indexes you routinely use (get hit by the site many times a second) are all in memory. In the past for me that has generally meant roughly all the indexes (in aggregate) fit in memory. 3) I have autovacuum on and find my daily vacuuming actually does a lot. Sure, I can probably tune it to be more effective, but it is easier just do put a daily vacuum in cron (and also a safeguard in case anything goes way out of wack). On your points, I bought the book :)

by birken 15y ago

frankwiles 15y ago

Agreed! Most of this advice isn't that useful and in all honesty can be bad advice for many sites. Really "fast websites" use external caching to not hit the database at all.

epi0Bauqu 15y ago
The post wasn't about anything but the DB layer. I cache everything, but still when you hit the DB layer, you want it to return instantly if at all possible. I said specifically at the very top this wasn't a tuning or scaling guide.

epi0Bauqu 15y ago

1) I said that was "crazier" and pointed to the thread saying it was generally a bad idea. That is, I wasn't really endorsing it, just offering it as a last ditch option. 2) The idea (sorry if wasn't clear) but to make sure the indexes you routinely use (get hit by the site many times a second) are all in memory. In the past for me that has generally meant roughly all the indexes (in aggregate) fit in memory. 3) I have autovacuum on and find my daily vacuuming actually does a lot. Sure, I can probably tune it to be more effective, but it is easier just do put a daily vacuum in cron (and also a safeguard in case anything goes way out of wack). On your points, I bought the book :)