When to consider partitioning
- When a table grows so big that searching it becomes impractical even with the help of indexes (which will invariably become too big as well).
- When data management is such that the target data is often the most recently added and/or older data is constantly being purged/archived, or even not being searched anymore (at least not as often).
- If you are loading data from different sources and maintaining it as a data warehousing for reporting and analytics.
- For a less expensive archiving or purging of massive data that avoids exclusive locks on the entire table.