current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

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?

share|improve this question
2  
In PostgreSQL there is no native support for cross-database queries. There is an extension called dblink, but it is not very handy. Of course, you can fetch pieces of data from two or more databases in an application. Either way, it adds considerable complexity, which is worth IMHO only when you face performance problems and the usual optimisation approaches do not yield sufficient results. – dezso Dec 5 '12 at 15:38
    
"more write loads into only one DB" and "share the writes by creating another new DB " What are the different effect it will produce in terms of performance? – Nava Dec 5 '12 at 16:31
2  
It is a bit more complex than this. I'd expect that reorganizing your database physically (say using different tablespaces and respective hard disks (or arrays)) would have much greater effect than split it logically into two databases. – dezso Dec 5 '12 at 17:27
    
what, exactly, are these cronjobs doing? additionally, have you measured the actual impact of everything that is going on? – swasheck Dec 5 '12 at 17:29
1  
@NavaneethanRamasamy: Is upgrading to a 9.x version of PostgreSQL possible? – Mike Sherrill 'Cat Recall' Dec 5 '12 at 17:44
up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.