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. It only takes a minute to sign up.
Sign up to join this community
Recently I have been looking to optimize my database and its size is 7GB. I have an option to partition big table. But partitioning would just divide the big table into small ones while remaining on the same machine so same resources would be available.
Do you think partition table on same machine would bring any positive affect on performance?
My another question may have relevant information too When do hash indexes become reasonable?
7GB database size itself doesn't tell anything until unless its user case is known. If your this question relates to your another question https://stackoverflow.com/questions/7403167/index-on-url-or-hashing-considering-ram then even when it will grow to 150+ million records while on sitting on same machine partitioning is not going to boost your performance if you partition URL table because as you are looking for marking index on hashes then it would just complicate your application logic.
But if you put your database on multiple machines as you seem to incline from your another question Single Drive vs Multiple Drives, Then you can think of positive performance affect if you implement it right. Though I think it should be called sharding not partitioning actually.
If I am wrong someone can correct me.
The table is only 7 Gigs. Table partitioning probably won't do much for you. Table partitioning is typically for really large tables. Think 100 Gigs or more. Now if the table will be getting that large then it might help, but if that isn't very likely I would just leave it as is. You probably just need to add any missing indexes.
