SQL - Need some advices for building a large db

Question

currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:

• Size: HDD has > 4tb to store filegroup if using partition tables.

• Performance: Using partition tables. In diagram, we will not create relationship among tables. I heard that it will help us query data faster.

But, in reality, I don't know it is good solution or not, please advice me with some concerns:

1. if I have 100 milion records in each tables, and using parttion table, each quarter is 1 table/part. So, when I need to report in 1 year, how is its performance to query/filter data?

2. With 100 milion records, it is posible to query data faster with no relationship?

3. How about insert data with checking exist data first? it will take a long time to check duplicate before insert?

4. How about replicate data by using partition table?

I know that, some ways, this is the job of DBA, but I readly want to understand them, please advise me.

I'm very appriciate for you helps.

Thanks.

Answer 1

I think you're having a hard time with this because I'm not sure you're looking at it from the right perspective. Database performance is a big topic and generally depends on an enumerable number of factors. But table partitioning is only addresses parallelism across cores vs. parallelism across threads. Generally speaking, the bottle-neck is not going to be there.

Let me give you some advice. Don't worry about partitioning - leave it in whatever state it's currently in - but do focus on the following.

1. What's more important, reads or writes? If it's reads then ensure the queries you are performing against the database are optimized by building covering indexes where you can - reading the data page is expensive. If it's writes then be careful how many indexes you build because of the shear size of your data, so only build what you have to because rebuilding the index is expensive if there are a lot of transactions.
2. Key constraints. Do not go down the road of not building proper foreign key constraints. When you build a foreign key it will build an index, you do not want to build in the referential integrity yourself, there is no performance bottle-neck with foreign keys. If you are using a database engine where foreign keys are a bottle-neck you should probably consider a different engine.
3. Last but not least, tune your database to your usage. A substantial amount of usage statistics are needed to understand exactly how to tune your database and because of the size of your database it will need tuned regularly.

I'm sorry if this feels nebulous, but you have a huge task before you and database optimization is a regular process on a database of that size. So, in short, the aforementioned items will start you down the right path but there is no answer to your whoas.