Post reply

Horizontal partitioning as a database design

  • August 8, 2004 at 3:21 am

    #86036

    Introduction

     

    We are going start the design of new database for a web-based application in SQL 2000. We have an existing application and its database in SQL 2000. The current size of this database is 30 GB.

     

    The problem with the old database which we thought is that

     

    1. Some portion of database (a set of tables I would say 40% of the total tables) is too normalized.

     

    1. Data tables are too large (that is in terms of row count) as there is no data archiving policy implemented.

     

    1. Unnecessarily duplication of same information in multiple tables i.e. data redundancy

     

    1. Unable to scale up the database. 

    So all these factors (and some other also) tend us to go for a new database design. Our plan is to design a new database and then port the existing data into new database.

     

    Queries

    Since our application run in four different regions in US so our team has come up with a basic design architecture that is horizontal partitioning based on region (i.e. the portioning key will be the region in question). I have done initial research on horizontal partitioning and has found out both limitations and benefits of this architecture but still it is not clear whether we should go for this architecture or not.

     

    Questions

     

    1. What I think is that the horizontal partitioning is more like an optimization technique not database design architecture for an Web based ERP solution. What you say on this?

     

    1. Can you people suggest any other suitable architecture/technique for our database?

     

    1. Since I am doubtful on using horizontal partitioning architecture, can you guide me about any instance of real world situation where this architecture is useful?

     

    1. What kind of drawbacks are there in terms of administration of a database if we go for horizontal partitioning architecture. for example what will the issues related backup, restoration etc.

     

    1. There is an alternative to horizontal partitioning in which instead of creating partitioned view we will manually fire queries from our web pages to correct instance database and thus avoiding the need of partitioned views. Any idea about this technique? I am not very sure on this technique, but one of my team member suggested this idea so i am just asking for confirmation.

     

    1. Any idea about design practices and patterns one should follow for designing a new database for large online applications.

      

    1. Any good resource on internet and books regarding the problem.

  • August 11, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • September 22, 2004 at 7:57 am

    #523979

    I think that partioning is a good solution, should you be working with large tables.  If the tables are very large, it will allow you to get smaller.

     

    By partioning the data, you can configure a view.  This view in conjunction with the optimizer, will be faster than going at all the data at once, even given proper indexes.

     

    In addition, you should consider properly indexing (finding a good primary key, preferrably an integer) and also non-clustered indexes on fields that have 'distributed' values.

     

    All of these will perform well.  Take current queries and turn on io statistics and have a look.  Partition the view and try the io statistics again.  Based on queries, these can be different.

     

    Hope this helps.

     

  • September 23, 2004 at 12:41 am

    #524110

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply