On Mar 9, 2013, at 11:54 AM, Paul Jungwirth wrote:
Hello,
I'm running a specialized search engine that indexes a few tens of millions of web pages, keeping everything in Postgres, and one problem I'm starting to see is poor cache hit rates. My database has two or three tables just for the text of the scraped pages, with one row every time a page is scraped and a `text` column for the HTML. These tables are almost-but-not-quite write-only. They are only read by one job, which uses them to create other objects in the system. I'd like the rest of the database to be in-memory all the time, but I don't really care if I have to read these tables from disk. To keep my problem tables from dragging down performance on the rest of the system, I'm thinking of splitting them out. I suppose I could save the HTML outside of Postgres entirely, but I'm wondering if a nicer solution would be to keep these tables in a separate "cluster" (i.e. /var/lib/postgresql/9.1/something_else -- I hope this is the right terminology). Then I could tune that cluster differently from the main cluster, or even put it on a different machine. And I could still use dblink to query both clusters conveniently (I think; this isn't really that important). Does this seem like a worthwhile approach? Is there a better way to deal with a few out-sized tables?
Well, what problem exactly are you trying to solve? Having large tables itself isn't a problem, but it often tends to imply other things that might be problematic:
- large vacuum or analyze times. (Probably fixable in your case with per-table autovacuum thresholds.)
- large disk usage. (Probably fixable in your case with tablespaces.)
- slow selects or updates. (Probably fixable in your case with partitioning.)
- blowing out your buffer cache with useless dirty pages - this is where you might want to look into separate servers.