DEV Community

Cover image for My Cursor state.vscdb was 8.9 GB. You can work out how much VACUUM will save before you run it
Rapls
Rapls

Posted on

My Cursor state.vscdb was 8.9 GB. You can work out how much VACUUM will save before you run it

#cursor #sqlite #macos #productivity

My Mac was running low on disk space, so I went through the big folders one at a time. One of them was Cursor's globalStorage, at 19 GB:

~/Library/Application Support/Cursor/User/globalStorage
  8.9G  state.vscdb
  8.9G  state.vscdb.backup
Enter fullscreen mode Exit fullscreen mode

state.vscdb is a SQLite database. It holds Cursor's settings and state, including the agent chat history. The usual advice is to quit Cursor and run sqlite3 state.vscdb "VACUUM;".

Before you do that, you can work out how much it will shrink. Multiply page_size by freelist_count and you have the space VACUUM will give back. I built a 574 MiB test database, deleted two thirds of the rows, and predicted 191.4 MiB. After VACUUM it was 191.5 MiB.

I'm not the first to write about this file. The Cursor forum has several threads about state.vscdb reaching tens of gigabytes, and the quit-then-VACUUM fix is posted there. The SQLite docs also say VACUUM needs temporary disk space. What I'm adding is a set of measurements on a WAL-mode database shaped like Cursor's: the prediction, how much free disk VACUUM used while running, and when it refuses to run.

Deleting rows doesn't shrink the file

When SQLite deletes data, the file stays the same size. The freed pages go on a "freelist" and get reused by later writes. VACUUM rebuilds the file without them.

You can see the count without changing anything:

cd ~/Library/Application\ Support/Cursor/User/globalStorage
sqlite3 state.vscdb "PRAGMA page_size; PRAGMA page_count; PRAGMA freelist_count;"
Enter fullscreen mode Exit fullscreen mode

This only reads the file, so you can run it while Cursor is open.

My test database had the same two tables as Cursor's (ItemTable and cursorDiskKV) and used WAL mode, like the real file (mine had -wal and -shm files next to it). I inserted 3,000 rows of 200 KB each, then deleted 2,000 of them:

after insert:        574.3 MiB
after deleting 2/3:  574.3 MiB   <- no change

page_size       4096
page_count      147030
freelist_count  98017
Enter fullscreen mode Exit fullscreen mode

4096 × 98017 is about 383 MiB. Take that from 574.3 MiB and you get 191.4 MiB.

after VACUUM:        191.5 MiB
Enter fullscreen mode Exit fullscreen mode

I also ran VACUUM on a copy where I hadn't deleted anything. freelist_count was 0 and the file stayed at 574.3 MiB. If your freelist is small, VACUUM won't help much; the file really is that full of data.

VACUUM needs about twice the final size in free space

VACUUM builds a new copy of the database and then writes it back. While that happens, it uses disk.

I watched df while the 574.3 MiB database was being vacuumed. At the peak, disk usage was 384 MiB higher than before I started. That's about twice the final size (191.5 MiB).

The WAL file accounts for half of it: -wal grew to 192.6 MiB. The other half is most likely the temporary copy. SQLite deletes its temp file from the directory as soon as it opens it, so du never saw it. Only df did.

For Cursor, this matters if the file won't shrink much. If 8.9 GB only drops to 8 GB, you'd need around 16 GB free while it runs. If your disk is nearly full, clear something else first.

Quit Cursor first

I also tried running VACUUM while another connection had the file open:

Other connection VACUUM
open, idle succeeded
inside a read transaction succeeded
inside a write transaction Error: stepping, database is locked (5)

In WAL mode, a reader didn't block VACUUM. A writer did. Cursor writes to this database while you use it, so quit it fully (Cmd+Q) before you run VACUUM.

The .backup file

A reply on the Cursor forum describes state.vscdb.backup as a backup Cursor makes automatically, and says it should shrink once the main file does. I didn't find anything about when it's created.

In my case, VACUUM brought state.vscdb down to [TO FILL IN: size after VACUUM]. I started Cursor, checked that it worked normally, and then deleted the .backup file.

cd ~/Library/Application\ Support/Cursor/User/globalStorage
sqlite3 state.vscdb "VACUUM;"
du -sh state.vscdb
# start Cursor, check it works, then:
rm state.vscdb.backup
Enter fullscreen mode Exit fullscreen mode

If VACUUM doesn't shrink it, the same forum thread describes deleting chat-history keys from cursorDiskKV and then running VACUUM again. That removes old chats from the UI, so I haven't tried it.

Checklist

  • Work out the gain first: page_size × freelist_count
  • A small freelist_count means VACUUM won't free much
  • Have about twice the final size free while it runs
  • Quit Cursor; a writer makes VACUUM fail with database is locked
  • Delete state.vscdb.backup only after Cursor starts fine

How big is your globalStorage right now?

Reproduce it 

# mk.py: a WAL-mode database shaped like Cursor's
import sqlite3, os, sys
c = sqlite3.connect(sys.argv[1])
c.execute('pragma journal_mode=wal')
c.execute('create table ItemTable(key text unique on conflict replace, value blob)')
c.execute('create table cursorDiskKV(key text unique on conflict replace, value blob)')
b = os.urandom(200_000)
for i in range(3000):
    c.execute('insert into cursorDiskKV values(?,?)', (f'bubbleId:{i}', b))
c.commit()
c.execute('pragma wal_checkpoint(truncate)')
c.close()
Enter fullscreen mode Exit fullscreen mode 
python3 mk.py s.db
sqlite3 s.db "delete from cursorDiskKV where cast(substr(key,10) as int) % 3 != 0;
              pragma wal_checkpoint(truncate);"
sqlite3 s.db "pragma page_size; pragma page_count; pragma freelist_count;"
sqlite3 s.db "VACUUM;"
ls -l s.db
Enter fullscreen mode Exit fullscreen mode

I ran these with SQLite 3.45.1 on Linux. The real Cursor file on a Mac may not give the same numbers.

Disclaimer: The tests were run on a test machine I set up for this post. English isn't my first language, so I use an AI assistant to help draft and edit the writing.

Top comments (0)