Decision: Which partitioning strategy?

Partitioning splits a large table into multiple physical pieces under a single logical name, making it easy to drop old data and letting queries look only at the relevant pieces (partition pruning). On small tables, however, it only adds complexity. This tree first questions whether partitioning is really needed, then guides you among Range, List, Hash, and Composite based on access and distribution patterns.

Is the table really large enough to need partitioning?

Partitioning is not performance magic; it is a scale and maintenance (lifecycle) tool. If the table is not large enough or queries already perform well with indexes, partitioning only brings plan complexity and management overhead. Is your table reaching tens of millions+ rows/hundreds of GB, or is it still small/medium?

• Yes, it is very large and/or old data needs to be deleted/archived in bulk
• No, small/medium table, or I am not sure

How do you access the data and how should it be split?

The partition key should be the column most queries filter on; only then does pruning kick in. What is the dominant access pattern? By date/sequential ranges (last 30 days), by specific category/region values, or is there no clear filter and you want to distribute the load evenly?

• Sequential ranges such as date/sequence number (last X days, by month)
• Discrete value sets such as category/region/tenant
• No clear range/category; the goal is to distribute load evenly
• Both date and high volume; need to split on two dimensions

Recommendation: Do not partition (yet)

Partitioning brings no benefit on small/medium tables; well-chosen indexes and normal maintenance are enough. Premature partitioning complicates query plans, misses pruning with the wrong key, and adds operational overhead.

Recommendation: Range partitioning

Range partitioning splits rows by sequential ranges of a column (e.g. date ranges: one partition per month/quarter). It is the most common and most powerful strategy for time series and continuously growing tables.

Recommendation: List partitioning

List partitioning groups rows by discrete, known values of a column (e.g. region = 'TR','EU','US' or tenant ID). When natural, finite categories exist, it makes access and management clear.

Recommendation: Hash partitioning (even distribution)

Hash partitioning distributes rows relatively evenly across a predefined number of partitions based on the hash of the key column. It is used to avoid hotspots and balance load when there is no natural range/category.

Recommendation: Composite (multi-level) partitioning

Composite partitioning combines two strategies: it splits at the top level first, then divides each partition into subpartitions (e.g. Range-by-Hash or Range-by-List). It is used when both time-based lifecycle and even distribution/category separation are needed at the same time.