Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Billion rows database design

    We have a database with Table 'X' can go over a billion rows. However, not all rows are used frequently and hence we plan to make a small subset of rows that are accessed frequently into Table 'Y' and use it. As and when requires, rows from Table 'X' will be moved to table 'Y'. Table 'Y' could grow up to 10 million rows.

    I need some help in deciding how to organize table 'X' & 'Y' for high performance design. Will separating both tables in two different databases or say even two entirely different servers improve performance?

    Thanks for any inputs.

  2. #2
    Join Date
    Feb 2012
    Location
    Netherlands
    Posts
    2
    Quote Originally Posted by tanra View Post
    We have a database with Table 'X' can go over a billion rows. However, not all rows are used frequently and hence we plan to make a small subset of rows that are accessed frequently into Table 'Y' and use it. As and when requires, rows from Table 'X' will be moved to table 'Y'. Table 'Y' could grow up to 10 million rows.

    I need some help in deciding how to organize table 'X' & 'Y' for high performance design. Will separating both tables in two different databases or say even two entirely different servers improve performance?

    Thanks for any inputs.
    We have a database containing a lot of content. Most of the content is referd by the primary key. The last – say 1000 – records are used more that 100k times more than the other records. We also used a secondary database for fast access. The primary database contain all records. The last 1000 records are synchronised with a secondary database. On every change in the primary database the secondary database is updated. Only the top 1000 most requested records are kept in that secondary database.

    When there is a query first we look if its a select “where primary key = x” construct, if so, the secondary database is used, otherwise the primary database. If the select on the secondary gives no result the primary database is used. The number of records that is kept in the secondary database is used to balance between the database loads. For our data structure such kind of construction works fine, but if this kind of solutions work for you completely depend on the query construction and knowledge witch data is request more often.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What database product do you plan to use? Many of today's products have vendor specific funcitonality that provides exactly that features that you've described. Unfortunately, each vendor's solution is different than the the others.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •