Manually trigger major compaction in a Spanner database

This document explains how to manually trigger a major compaction in a Spanner database.

Several storage-related features in Spanner, such as tier storage or columnar engine, rely on a database-wide major compaction before they are fully enabled. By default, major compactions occur automatically across all tables over seven day periods. This means you might wait up to seven days for a new feature to be fully available. To make new features available immediately, you can manually trigger a major compaction.

The compaction process is a long-running operation (LRO) .

Pricing

Triggering a major compaction temporarily increases compute capacity on the Spanner instance. This might result in increased costs.

Performance

Major compactions run as background operations. However, if your instance has consistently heavy CPU usage, the compaction workload might interfere with other critical operations. In such cases, you can temporarily scale up the instance to ensure stable performance during compaction.

Manually trigger a major compaction

Google Cloud console

Open the Google Cloud console and select your instance.

Go to Spanner instance
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking New SQL editor tabor New tab.
Invoke the following command to initiate compaction:
```
  CALL 
  
 compact_all 
 (); 
 
```
This operation returns a long-running operation (LRO) ID that you can use to find the operation in the Operationslist.
To monitor the progress of the compaction operation, in the navigation menu, click Operations.

C++

To trigger compactions programmatically using the C++ client library:

  void 
  
 Compact 
 ( 
 google 
 :: 
 cloud 
 :: 
 spanner 
 :: 
 Client 
  
 client 
 ) 
  
 { 
  
 namespace 
  
 spanner 
  
 = 
  
 :: 
 google 
 :: 
 cloud 
 :: 
 spanner 
 ; 
  
 spanner 
 :: 
 SqlStatement 
  
 select 
 ( 
 "CALL compact_all()" 
 ); 
  
 auto 
  
 rows 
  
 = 
  
 client 
 . 
 ExecuteQuery 
 ( 
 statement 
 ); 
  
 using 
  
 RowType 
  
 = 
  
 std 
 :: 
 tuple<std 
 :: 
 string 
> ; 
  
 auto 
  
 rows 
  
 = 
  
 client 
 . 
 ExecuteQuery 
 ( 
 std 
 :: 
 move 
 ( 
 select 
 )); 
  
 for 
  
 ( 
 auto 
&  
 row 
  
 : 
  
 spanner 
 :: 
 StreamOf<RowType> 
 ( 
 rows 
 )) 
  
 { 
  
 if 
  
 ( 
 ! 
 row 
 ) 
  
 throw 
  
 std 
 :: 
 move 
 ( 
 row 
 ). 
 status 
 (); 
  
 std 
 :: 
 cout 
 << 
 "Long-running operation ID: " 
 << 
 std 
 :: 
 get<0> 
 ( 
 * 
 row 
 ) 
 << 
 " 
 \n 
 " 
 ; 
  
 } 
 }

You can check the progress of a long-running database operation . You can also cancel the ongoing major compaction request using the LRO ID. For more information, see Cancel a long-running database operation .