sqlite db file size increases after dumping & re-creating the db

Question

I run these commands on an sqlite database

sqlite3 data.db .dump > data.txt
cat data.txt | sqlite3 newdata.db

The file size of newdata.db is 10% larger than data.db (for a 100MB database with 400k entries). Why is that so?

data.db was created with sequential inserts & no deletes. Nothing complicated. I had set the following 2 PRAGMA while creating the tables but the dump file doesn't show them (probably they persist across connection only).

PRAGMA page_size = 4096;
PRAGMA cache_size = 72500;

Sample contents of data.txt.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE urltable (url TEXT NOT NULL, PRIMARY KEY url);
INSERT INTO "urltable" VALUES('http://www.example.com/page1.html');
...
COMMIT;

user · Accepted Answer · 2011-07-16 13:53:42Z

2

I'll leave this here for reference... adding these back to data.txt before creating the new db did the trick.

PRAGMA page_size = 4096;
PRAGMA cache_size = 72500;

answered Jul 16, 2011 at 13:53

user

18.7k20 gold badges105 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlite db file size increases after dumping & re-creating the db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related