Compacting Buckets

​

Compacting

​

PowerSync Cloud

• Dashboard: Select your project and instance, go to the Settings view, and click the Compact button in the “Compact operation history” section.
• CLI: Run powersync compact against a linked Cloud instance. The CLI polls until the operation completes, with a default timeout of 30 minutes. Pass --timeout=<minutes> to override, or --timeout=0 to wait indefinitely.

​

Self-Hosted PowerSync

​

Background

​

Bucket Operations

(1, PUT, row1, <data>)
(2, PUT, row2, <data>)
(3, PUT, row1, <data>)
(4, REMOVE, row2)

​

Compacting Step 1 - MOVE Operations

(1, MOVE)
(2, MOVE)
(3, PUT, row1, <data>)
(4, REMOVE, row2)

​

Compacting Step 2 - CLEAR Operations

(2, CLEAR)
(3, PUT, row1, <data>)
(4, REMOVE, row2)

​

Defragmenting

1. If a row has never been updated since its initial creation, its original PUT operation remains at the start of the bucket
2. All operations that come after this oldest PUT cannot be fully compacted
3. This is particularly problematic when you have:
   • A small number of rarely-changed rows in the same bucket as frequently-updated rows
   • The rarely-changed rows’ original PUT operations “block” compacting of the entire bucket
   • The frequently-updated rows continue to accumulate operations that can’t be fully compacted

-- Insert a single row that rarely changes
INSERT INTO lists(name) VALUES('a');
-- Insert 50k rows that change frequently
INSERT INTO lists (name) SELECT 'b' FROM generate_series(1, 50000);
-- Delete those 50k rows, but keep 'a'
DELETE FROM lists WHERE name = 'b';

(1, PUT, row_1, <data>)  -- This original PUT blocks further compacting
(2, MOVE)
(3, MOVE)
...
(50001, MOVE)
(50002, REMOVE, row2)
(50003, REMOVE, row3)
...
(100001, REMOVE, row50000)

1. The original PUT operation for row ‘a’ remains at the start
2. All subsequent operations can’t be fully compacted
3. We end up with over 100k operations for what should be a simple bucket

-- Touch all rows to create new PUT operations
UPDATE lists SET name = name;
-- OR touch specific rows at the start of the bucket
UPDATE lists SET name = name WHERE name = 'a';

(100001, CLEAR)
(100002, PUT, row_1, <data>)

​

When to Defragment

1. High Operations-to-Rows Ratio: If you notice that the number of operations significantly exceeds the number of rows in a bucket. You can inspect this using the Sync Diagnostics Client.
2. Frequent Updates: Tables that are frequently updated (e.g., status fields, counters, or audit logs)
3. Large Data Churn: Tables where you frequently insert and delete many rows

​

Defragmenting Strategies

1. Update source rows, then compact. Use this when you can identify the rows whose old PUT operations block compacting. You can target one table or subset of rows, but each update creates a new operation that clients must download. For recurring maintenance, schedule the updates with a tool such as pg_cron:
   -- Daily defragmentation for high-churn tables
   UPDATE audit_logs SET last_updated = now() 
   WHERE last_updated < now() - interval '1 day';
   
   -- Weekly defragmentation for other tables
   UPDATE users SET last_updated = now() 
   WHERE last_updated < now() - interval '1 week';
   
2. Force a full reprocess. In the PowerSync Dashboard, select your project and instance, open Settings, and use Defragment in the “Compact operation history” section. This action forces a new replication stream even when the deployment could otherwise use incremental reprocessing. It rebuilds all prepared sync data and makes clients download all subscribed data again. Use it for one-time cleanup when you intend to rebuild the entire Sync Config.

​

Defragmenting Trade-offs

1. Frequency: More frequent defragmentation means fewer operations per sync but more frequent re-syncs
2. Scope: Defragmenting all rows at once is more efficient but causes a larger sync cycle
3. Monitoring: Use the Sync Diagnostics Client to track operations-to-rows ratio

​

Sync Streams Deployments

​

Technical Details