I am developing a somewhat big web application, which uses data which is aggregated using a crawler. The crawler is constantly writing to the database and updates many tables at the same time. For the frontend it is not important to have the very latest data available all the time, but the response time is critical for the user experience. The frontend accesses the same database (read-only), but is really slow as long as the crawler is active. I suspect that unfortunate locks on the database cause this, as the server still has many CPU cores idling and iostat also shows that the HDDs aren't fully utilized yet.
I know that I could probably resolve this issue by creating a master/slave setup using another database server, which is replicated periodically, but I wonder if I could achieve the same performance jump in another way:
Does it make sense to replicate/mirror a database (mirror all the tables needed for the application) within the same database-server-instance? If so, what would be the smartest way to do so?
(I am using postgreSQL 8.4.17 if that makes any difference in this case)