Skip to main content

​
Get the SQLite file

A SQLite database file can use any extension - .db, .sqlite, .sqlite3, etc. The extension doesn’t affect functionality; all contain the same SQLite format. To ensure no recent changes are lost, you can either: Pull the associated Write-Ahead Log (WAL) file (with the same name as the database plus the suffix -wal). Or alternatively, run the command PRAGMA wal_checkpoint(TRUNCATE); on the database before pulling it, which will merge the WAL file’s changes into the main database file.
  • Android
  • iOS
  • Web
Ensure your emulator is running, then replace com.package-name with your application’s package name and your-db-name.sqlite with your database file name.
This method may not work on Windows. Alternatively, you can copy the database to /sdcard/ then use adb pull, though this may encounter permission issues on some devices.
adb exec-out run-as com.package-name cat databases/your-db-name.sqlite > "your/local/path/your-db-name.sqlite"
adb exec-out run-as com.package-name cat databases/your-db-name.sqlite-wal > "your/local/path/your-db-name.sqlite-wal"
Common database locations:Note: If the database is in a different location, first find it with:
adb shell run-as com.package-name find /data/data/com.package-name -name "your-db-name.sqlite"

​
Inspecting the SQLite file

​
1. Open your SQLite file

Use the sqlite3 command-line tool or a GUI tool like DB Browser for SQLite to open your database file: 
sqlite3 your-db-name.sqlite

​
2. Merge the WAL file

Temporary changes are stored in a separate Write-Ahead Log (WAL) .wal file. To measure the database size accurately, merge these changes into the main database: 
PRAGMA wal_checkpoint(TRUNCATE);

​
3. Get storage statistics

Query the built-in dbstat virtual table to see how much space each table uses on disk: 
SELECT name, pgsize AS storage_size, payload AS data_size 
FROM dbstat 
WHERE aggregate = true;
This returns:
  • name: Table name
  • storage_size: Total storage used on disk (in bytes, including SQLite overhead)
  • payload: Actual data size (in bytes)
The dbstat table is automatically available in SQLite and provides low-level information about physical storage. Values represent on-disk usage including SQLite’s internal structures (page headers, B-trees, indexes, free space), which is why they’re larger than your logical data size.

​
Understanding the size breakdown

PowerSync databases contain more data than just your application tables to support the sync functionality:
  1. Application data: Your synced data in ps_data__<table> tables
  2. Operation log (ps_oplog): A complete copy of all synced data required for offline conflict resolution and sync
  3. Indexes: For efficient queries and lookups
  4. PowerSync metadata: System tables and views for managing sync state (see Client Architecture)
  5. SQLite overhead: Page structure, alignment, fragmentation, and internal bookkeeping
The difference between storage_size and payload in the dbstat results shows SQLite’s storage overhead. The ps_oplog table will typically be one of the largest tables since it maintains a full copy of your synced data. To see just the JSON data size in ps_oplog (excluding SQLite overhead), run: 
SELECT sum(length(data)) / 1024.0 / 1024.0 AS size_mb FROM ps_oplog;
This measures only the raw JSON payloads, which will be smaller than the on-disk storage reported by dbstat.

​
Reducing SQLite file size

Both methods of reducing the size of the SQLite file can be executed within the client using powerSync.execute().
Consider these optimizations if your app’s database is growing larger than expected or you’re working with high data volumes in production.

​
VACUUM Command

The VACUUM command reclaims unused space in the database: 
VACUUM;
The VACUUM command has important constraints:
  • Disk space: Requires enough free disk space to create a temporary copy of the entire database
  • Database locking: Locks the database during execution, which may affect app responsiveness
  • Ensure sufficient space is available and run during low-activity periods

​
Increase page size

Increasing the page size from the default 4KB (4096 bytes) to 16KB (16384 bytes) can reduce storage overhead significantly.
IndexedDB Compatibility Issue: Changing the page size is not supported when using IndexedDB on web platforms and could corrupt the database. Only use this optimization for native SQLite implementations.
Additional caveats:
  • May increase overhead for many small writes.
  • Best suited for apps with larger data records
The page size must be set before any tables are created and before running VACUUM. It should be one of the first PRAGMA statements after opening a new database: 
PRAGMA page_size = 16384;
If you’re changing the page size on an existing database, you must run VACUUM immediately after setting it to apply the change. For optimal results, set the page size when first creating the database.