Rule of thumb when to partition tables

I've seen many entries about partitioning tables, but there is not a lot开发者_如何学Python of information on when you should make a partition.

Is there a rule of thumb when you should partition tables in SQL Server.

Thanks

My benchmarks indicate that it depends on the query load.

• If the queries you perform ALWAYS contain a filter on the partition field the performance benefit is virtually instant (like 1000 records in the table is already beneficial)
• If the queries do NOT always contain a filter on the partition field you really have to benchmark with a good sample of the query load before making the decision.

You also have to account for the partition system you use. if you use "static" partitions there is not much harm in creating them immediately. When you use a "sliding window" system you need to take into account the overhead of creating and merging partitions. (which can take a long time on big tables)

@Filip's post is a great topical guide. When your doing your ontologies and estimating how your application will be used, that is, how your users will interact with the application and how that translates to database access, you should have a good idea of the kind of queries that will be performed and how fast certain tables will grow. If your that confident, then you should immediately partition the tables to defer from any maintenance hapzards.

But if your trying to decide on whether to partition populated tables, or you like to perform partitioning lazily like me, here's a nice little nugget from the PostgreSQL docs:

The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server. [src]