How Partitioning Impacts Index Performance

Partitioning in databases is a method of dividing large tables and indexes into smaller, more manageable segments, improving query performance and maintenance efficiency. By organising data into partitions, the database engine can skip irrelevant sections during queries, reducing disk I/O and speeding up operations. However, partitioning isn’t a universal fix for performance issues and requires careful planning to align with query patterns and workload needs.

Key points to know:

• Partition Types: Horizontal partitioning splits data by rows (e.g., by date or category), while vertical partitioning separates by columns.
• Indexing Approaches: Aligned (local) indexes integrate with partitions for easier maintenance, while non-aligned (global) indexes span multiple partitions but are harder to manage.
• Partition Pruning: Skips unnecessary partitions during queries, especially when the partitioning column is included in filters.
• Challenges: Over-partitioning can increase complexity, and global indexes require more resources for updates and rebuilds.
• Cloud Cost Savings: Partitioning reduces the amount of data scanned in cloud environments, lowering query costs.

Partitioning is most effective for large tables exceeding the server’s physical memory, but it demands proper setup to balance performance gains with maintenance overhead.

Database Partitioning: Core Concepts and Types

Database partitioning involves breaking a large table into smaller, more manageable pieces called partitions. Each partition holds a subset of the data but collectively, they function as a single logical table. This method can significantly improve query performance by limiting scans to only the relevant partitions. It also simplifies tasks like archiving older records and optimises storage by placing frequently accessed data on faster drives while storing less active data on cost-effective media.

A general guideline is to consider partitioning only when a table grows larger than the physical memory of the database server. Below that size, managing partitions can introduce unnecessary complexity. As noted in PostgreSQL documentation:

partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit into memory.

With this foundation in mind, let’s delve into the two main partitioning methods: horizontal and vertical.

Horizontal vs Vertical Partitioning

Horizontal partitioning splits a table by rows. Each partition contains the same set of columns but holds different rows of data. This can be done in a few ways:

• Range partitioning: Divides rows based on a range of values, such as dates or IDs. This is ideal for time-series data, where older entries can be archived efficiently.
• List partitioning: Groups rows based on categorical values, like regions or country codes.
• Hash partitioning: Distributes rows evenly by applying a hash function, which helps prevent I/O bottlenecks.

For instance, a table partitioned by range might store January data in one partition, February data in another, and so on. Similarly, list partitioning could separate data for different countries, while hash partitioning ensures an even spread of rows across partitions.

Vertical partitioning, on the other hand, divides a table by columns. Frequently accessed columns are separated from larger, less commonly used ones, such as BLOBs or lengthy text fields. This approach reduces I/O when queries only need a subset of columns. However, horizontal partitioning is generally more prevalent since performance issues often arise from managing large numbers of rows.

Partition Pruning and Efficiency

Partition pruning is an optimisation technique where the query optimiser examines the WHERE clause to skip partitions that don’t contain the requested data. For example, if a query filters on order_date >= '2025-01-01' in a table partitioned by month, the database engine can ignore all partitions before January 2025, cutting down on disk I/O. PostgreSQL users should ensure the enable_partition_pruning setting is active to take advantage of this feature.

When using hash partitioning, it’s a good idea to create a number of partitions that is a power of two (e.g., 2, 4, 8, 16). This avoids uneven data distribution, where the largest partition could end up twice as large as the smallest. Additionally, partition pruning works best when the partition key is included in query filters, so selecting keys that align with common search patterns is crucial.

How Partitioning Changes Index Structure

When a table is partitioned, its index structure undergoes a transformation to align with the partitioned data. This means indexes are divided into multiple physical units, which can either operate independently or in sync with the table itself. Two main approaches emerge from this: aligned indexes (also called local indexes) and non-aligned indexes (or global indexes).

Choosing between these approaches impacts everything from memory requirements during index creation to the efficiency of maintenance tasks. For instance, non-aligned indexes demand sorting across all partitions simultaneously – requiring at least 40 pages per partition. In contrast, aligned indexes handle sorting one partition at a time. Let’s break down these two options further.

Aligned vs Non-Aligned Indexes

Aligned indexes are tightly integrated with the partitioning scheme of the table. Each index partition corresponds directly to a table partition. This setup simplifies operations: if a table partition is rebuilt or switched, only the matching index partition needs adjustment. This makes aligned indexes particularly useful in scenarios like “sliding window” operations, where older data is archived, and new partitions are regularly added.

Non-aligned indexes, on the other hand, are not tied to the table’s partitioning scheme. A single index partition in a global index can span rows from multiple table partitions. However, this flexibility comes at a price. Structural changes to the table, such as SPLIT, MOVE, or DROP operations, can trigger extensive updates or even rebuilds of the entire global index. Despite these challenges, non-aligned indexes are often necessary for enforcing uniqueness on columns that aren’t part of the partitioning scheme.

For unique partitioned indexes, the partitioning column is usually included in the index key, enabling the database to verify uniqueness within a single partition. In SQL Server, partitioning a clustered index requires the clustering key to include the partitioning column. For non-unique, non-clustered indexes, the system often adds the partitioning column as an “included” column by default to maintain alignment.

Now, let’s compare local partition-level indexes with global indexing strategies.

Partition-Level vs Global Indexes

The choice between partition-level (local) and global indexes depends heavily on workload requirements. Local prefixed indexes, where the index is partitioned on the leading index key column, are well-suited for OLTP (Online Transaction Processing) systems because they reduce the need for partition-wide probes. Meanwhile, local non-prefixed indexes are better for DSS (Decision Support Systems), as they enable parallel range queries across partitions.

Memory usage varies significantly between these approaches. For example, a non-aligned index with 100 partitions and a degree of parallelism (DOP) of 4 requires enough memory to sort 16,000 pages simultaneously (40 pages × 100 partitions × 4 processors). To avoid memory exhaustion during such operations, it’s advisable to either increase the server’s “index create memory” configuration or lower the MAXDOP setting. Systems with numerous partitions should also have at least 16 GB of RAM to prevent failures during DML and DDL operations.

Global indexes can be advantageous for specific lookups that don’t involve the partitioning column. However, they lack the independence of local indexes. For instance, in point-in-time recovery scenarios, local indexes allow you to restore only the relevant table and index partitions. In contrast, global indexes require a complete rebuild since entries for the restored partition are spread across all index partitions.

Query Execution and Performance Improvements

Partition Elimination in Query Execution

Partition elimination, often called partition pruning, allows the optimiser to bypass irrelevant partitions during a query, cutting down on both I/O and resource usage. This works particularly well when the query’s WHERE clause includes the partitioning column. For instance, if a sales table is partitioned by date and the query specifies a particular month, the database engine will focus only on that month’s partition instead of scanning the entire table.

Partitioning also streamlines data access by replacing the higher levels of traditional indexes. Frequently accessed data stays in physical memory, reducing the need for slower disk operations. When a query targets a large section of a single partition, a sequential scan of that partition can often outperform using an index, which might result in scattered random-access reads.

Partition pruning can be static or dynamic. Static pruning happens at compile time, identifying relevant partitions before execution. Dynamic pruning occurs during execution, using values like bind variables to determine which partitions to access. Additionally, local indexes – aligned with specific table partitions – enable the optimiser to prune both table and index partitions simultaneously, maintaining strong performance even as the database scales.

That said, partitioning comes with its own set of challenges and compromises.

Limitations and Trade-Offs

Partitioning doesn’t always lead to better performance. For example, single-row queries filtering on columns that aren’t part of the partitioning key may perform worse. In such cases, the database engine might have to search across all partitions to find the record. Imagine a table partitioned by date: a query filtering by customer ID would require scanning multiple partitions.

Similarly, functions like TOP, MAX, or MIN on non-partitioning columns might force the engine to evaluate every partition to produce results. Unique constraints add another layer of complexity. These constraints must include the partitioning key, and if uniqueness is required on other columns, a global index becomes necessary, which increases maintenance workload.

Choosing the right index type involves compromises. Local prefixed indexes, where the partitioning key is the leading column, are highly effective for partition pruning and work well in both transactional and analytical scenarios. On the other hand, local non-prefixed indexes might require scanning each index partition for point lookups but are ideal for parallel range scans. Global indexes are better suited for single-row lookups on non-partitioning columns in transactional workloads but come with higher maintenance overhead, as changes in one partition affect the entire index.

As a general guideline, partitioning tends to offer noticeable performance gains when the table size exceeds the physical memory of the database server. For smaller tables, the overhead of managing partitions might outweigh the benefits.

Index Maintenance and Operational Overhead

Rebuild Complexity and Resource Requirements

Partitioning has a direct impact on how index rebuilds are managed. With local partitioned indexes, only the modified partition needs to be rebuilt, which significantly reduces the time and effort required for the process.

However, global indexes present a much greater challenge. Any structural change to the table – such as splitting, moving, or dropping a partition – usually invalidates the entire global index. Unless you use clauses like UPDATE INDEXES during these operations, the global index will need a full rebuild. While the UPDATE INDEXES clause allows the index to stay available, it can increase the duration of the partition maintenance task. Generally, this approach is efficient when the affected partition represents less than 5% of the total table size.

Memory requirements also vary significantly depending on the type of index. Rebuilding nonaligned partitioned indexes can be particularly resource-intensive, as it involves building sort tables for multiple partitions simultaneously. This can place a heavy load on memory. For example, with 100 partitions and a degree of parallelism (DOP) of 4, a nonaligned index might require sorting 16,000 pages at the same time. In contrast, aligned indexes handle sort tables one partition at a time, which significantly reduces memory usage.

Schema Modifications and Maintenance Effort

The challenges of rebuilding indexes are closely tied to schema modifications. For local indexes, updates occur automatically when partitions are modified. This ensures that the indexes remain aligned with the table partitions without requiring manual adjustments. For example, if an old partition containing historical data is dropped, only the corresponding partition’s index needs to be addressed. The rest of the table and its indexes remain unaffected and fully operational.

When it comes to regular maintenance, reorganising indexes is often a better choice than rebuilding. Reorganisation focuses on defragmenting the leaf level of the index, and it avoids long-term object-level locks, making it a less disruptive and resource-heavy online operation. On the other hand, rebuilding an index can demand additional storage space to create a temporary copy of the index, along with the potential for locking.

To optimise maintenance, it’s better to monitor fragmentation using tools like sys.dm_db_index_physical_stats rather than sticking to fixed rebuild schedules. Additionally, rebuilding a rowstore index updates its statistics by scanning all rows, an operation equivalent to a full table scan (FULLSCAN), which further adds to the resource costs.

Optimising Partitioned Indexes for Better Performance

Composite Partitioning Techniques

Composite partitioning combines two methods – such as Range-Hash or Range-List – to create a hierarchy of partitions. This setup allows for multi-level partition pruning, where the optimiser can eliminate both entire partitions and specific sub-partitions based on multiple query conditions. For instance, a data warehouse might use Range-Hash partitioning to organise historical sales data by date ranges, then further split each range using hash partitioning to distribute index leaf block contention across multiple physical segments.

Hash sub-partitioning is particularly useful in high-concurrency OLTP environments. When index insertions occur at the “right edge” of a B-tree index – common with columns that increment monotonically – this can create a hot spot, leading to performance issues. Hash partitioning mitigates this by spreading the contention across a set number of partitions, which improves throughput. For the best distribution, use a power-of-two number (e.g., 8, 16, or 32) when implementing hash partitioning.

Local indexes on composite partitions reflect the table’s sub-partitioning, ensuring maintenance remains proportional to the sub-partition size. You can also apply index compression on a per-partition basis – for example, applying high compression to historical data while leaving active data uncompressed.

These partitioning strategies can be further enhanced when combined with modern storage formats.

Integration with Columnar Formats

Pairing partitioning with columnar formats (like Parquet or ORC) can significantly improve performance. For data warehousing and Decision Support Systems, local nonprefixed indexes are often the better choice because they enable parallel scanning of multiple index partitions during range queries. This capability aligns well with columnar formats, which are already optimised for analytical workloads.

Partitions can also be assigned to different storage tiers depending on usage patterns. Older, less frequently accessed partitions can be moved to slower, more cost-effective storage, while active partitions remain on faster, high-performance storage. Zone maps add another layer of optimisation by enabling the database to skip irrelevant data blocks based on metadata, complementing the efficiency of columnar formats.

When table compression is introduced, any existing bitmap indexes must either be dropped or marked as UNUSABLE before adding the compressed partition. Once the partition is added, the indexes should be rebuilt to ensure they remain compatible.

Cost and Scalability in Cloud Environments

Cloud data warehouses like Amazon Athena and Google BigQuery charge based on the amount of data processed. This makes partitioning a key tool for controlling costs through effective partition pruning. As Google BigQuery’s documentation explains:

The pruned partitions are not included when calculating the bytes scanned by the query. In general, partition pruning helps reduce query cost.

In simpler terms, fewer bytes scanned equals lower costs. Amazon Athena echoes this, stating:

Reducing the amount of data that must be scanned improves query performance and lowers cost.

Take this example: if you’re analysing a year’s worth of sales data but only need January’s figures, partitioning by month ensures the remaining eleven months are neither scanned nor billed.

Partitioning does more than save money – it also tackles scalability issues in cloud environments. For instance, Amazon S3 supports up to 5,500 requests per second to a single index partition. Exceeding this limit can trigger throttling errors, such as “SlowDown”, which can halt queries. Additionally, S3 list operations return only 1,000 objects per call. Large partitions require multiple calls, slowing down performance. By spreading data across multiple partitions, you distribute the load, avoid bottlenecks, and maintain efficiency. These scalability benefits make partitioning a cornerstone of optimising cloud data warehouse performance.

Partitioning in Cloud Data Warehouses

To maximise cost savings, include the partitioning column as a constant in the WHERE clause. For instance, use a condition like WHERE order_date = '2024-01-01', keeping the partition column separate in the predicate (e.g., WHERE partition_col > TIMESTAMP_SUB(...)) to ensure proper partition pruning.

Be cautious about over-partitioning high-cardinality columns. For example, partitioning by hour when most queries target entire days creates thousands of small partitions. These increase metadata overhead and slow down query planning. BigQuery suggests partitions should ideally hold at least 10 GB of data to avoid excessive metadata costs. For tables with tens of thousands of partitions, Amazon Athena’s partition projection can help. This feature calculates partition locations in memory instead of relying on a metadata catalogue like AWS Glue, which speeds up query planning.

Lastly, enable “Require partition filter” settings on critical tables. This prevents accidental full-table scans, which could lead to unexpectedly high costs.

Conclusion

Partitioning transforms index performance by enabling pruning, which skips over irrelevant data and reduces I/O. This approach also simplifies maintenance tasks – operations like dropping or truncating partitions happen almost instantly, avoiding the need for bulk deletes and vacuuming. By splitting large indexes into smaller, manageable parts and keeping frequently accessed sections in RAM, partitioning enhances memory efficiency and cuts down on disk swaps.

That said, there are trade-offs to consider. Choosing between local and global indexes involves balancing ease of maintenance for data warehousing against performance needs in transactional systems. Over-partitioning can lead to increased memory usage and slower optimisation.

To make the most of partitioning, align your indexing strategy with your query patterns. For transactional systems, global or locally prefixed indexes reduce partition probes, while for analytical workloads, local non-prefixed indexes allow parallel scans across partitions, improving range query performance. Always ensure your query filters include the partitioning column to enable effective pruning.

Partitioning requires careful planning to suit your workload, query behaviour, and maintenance requirements. When executed properly, it delivers substantial performance improvements and cost efficiencies, particularly in cloud environments where query costs depend on the amount of data scanned. For tailored strategies to optimise performance and manage costs in cloud-based data solutions, Tucanoo Solutions Ltd provides expert support.

FAQs

What’s the difference between horizontal and vertical partitioning, and how do I choose the right one for my database?

When deciding between horizontal and vertical partitioning, the choice hinges on your database’s structure, how the data is accessed, and your performance objectives.

Horizontal partitioning, often called sharding, involves splitting a table into smaller tables that share the same columns but contain different rows. This method works well for handling massive datasets, like time-series or historical data, as it reduces the amount of data scanned during queries. Additionally, it enables parallel query processing, which can significantly improve performance.

Vertical partitioning, on the other hand, breaks a table into smaller tables based on columns. It separates frequently accessed columns from those that are used less often. This approach reduces input/output operations and boosts cache efficiency, making it ideal for workloads where queries focus on specific subsets of columns.

To summarise, go for horizontal partitioning when dealing with large datasets that benefit from data pruning and parallel processing. Choose vertical partitioning if your queries often target specific columns and you want to optimise performance.

What are the key differences between aligned and non-aligned indexes, and how do they impact performance?

The differences between aligned and non-aligned indexes boil down to how they handle performance, flexibility, and upkeep.

Aligned indexes follow the same partitioning structure as their underlying tables. This setup makes them great for boosting query performance while keeping maintenance straightforward. Since the table and index partitions stay in sync, managing partitions becomes less of a headache.

Non-aligned indexes, however, don’t stick to the same partitioning scheme as their tables. This allows for greater flexibility, which can be especially handy when fine-tuning for specific query patterns, like parallel scans in data warehousing. But, this added flexibility means maintenance can get trickier.

To put it simply, aligned indexes are a solid choice for transactional systems (OLTP), where speed and ease of maintenance matter most. Non-aligned indexes, meanwhile, shine in analytical systems (DSS), where adaptability and query performance take centre stage.

How does partitioning help reduce costs in cloud-based data systems?

Partitioning plays a key role in cutting costs in cloud-based data systems by streamlining query execution. By breaking data into smaller, more manageable chunks, databases can leverage partition pruning. This technique allows the system to bypass irrelevant partitions during query processing, reducing the need for computational power. The result? Faster queries and lower operational costs.

It also improves storage management by enabling you to handle older or less frequently accessed data separately. When paired with tiered storage solutions in the cloud, this approach can lead to further cost savings.