Any advantages if we use multiple database instead of only one in PostgreSQL?

Posted on

Question :

I would like to get the suggestion for my usecase.

We have a database in our production machine.We are doing continuous writes in that database since we are doing crawling,parsing and it is being inserted into database as a streamer.We are running some crons on the database.That database gets some reads for the web application also.

I got the requirement just to store the html document into one another table for the cross verification.So,the plan was to store the pages into one new table which was created in the same database.Here we need to run one more cron on the new table to find the page number and update into that table.

We got one more idea like to create the table and running cron in new database instead of the old database by considering performance improvement.

Will it be any difference to create a new database and update into it rather than using old database to update the data and running a cron?

we are using amazon ec2 medium instance with Ubuntu operating system also PostgreSQL 8.4.

your thoughts please?

Answer :

If it needs to be cross-verified, put it in the same db. That way you can do your cross-verification in the db, taking advantage of indexes, query optimization, etc. which you can’t do as easily in separate db’s without a fair bit of extra work.

In theory you can spread out your disk/IO loads by putting it in another machine but typically, if this is virtually all write, I don’t think it will be worth it.

If it is a different db on the same machine, you will not get any better write performance and you will likely get worse read performance.

Leave a Reply

Your email address will not be published. Required fields are marked *