How to avoid locks during DELETE in sqlite?

Question

The DELETE query is simple:

DELETE FROM pages WHERE status = 0

It takes around 15 minutes to complete (for removing ~20K rows). It's a ~500 MB database that maps a local files system, and contains around 3 million records.

The structure:

• pages - just a few records
• files - around 230K records, contains a foreign key constraint with ON DELETE CASCADE that references a column from pages
• meta - around 3 million records, contains foreign key constraints with ON DELETE CASCADE that reference columns from files and pages
• search - a FTS4 table, almost exact duplicate of meta. The integrity of this table is maintained with triggers.

CREATE TABLE pages(
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  slug     TEXT,                           
  name     TEXT NOT NULL,
  type     INTEGER NOT NULL DEFAULT 1,     
  data     TEXT,
  parent   INTEGER,                        
  status   INTEGER DEFAULT 1,              
  comments INTEGER DEFAULT 1,              
  priority INTEGER DEFAULT 0,              
  UNIQUE(slug),
  FOREIGN KEY(parent) REFERENCES pages(id) ON DELETE CASCADE   
);

CREATE INDEX "pageParent" ON "pages"("parent");


CREATE TABLE files(
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  gallery   INTEGER NOT NULL,                      
  type      INTEGER NOT NULL DEFAULT 1,            
  sPath     TEXT,                                  
  rPath     TEXT,                                  
  parent    INTEGER,  
  hero      INTEGER,
  hidden    INTEGER DEFAULT 0,                     
  createdAt DATETIME,                              
  mTime     TEXT,                                  
  UNIQUE(sPath),
  FOREIGN KEY(gallery) REFERENCES pages(id) ON DELETE CASCADE,
  FOREIGN KEY(parent)  REFERENCES files(id) ON DELETE CASCADE,
  FOREIGN KEY(hero)    REFERENCES files(id) ON DELETE SET NULL
);

CREATE INDEX "fileGallery" ON "files"("gallery");
CREATE INDEX "fileType"    ON "files"("type");
CREATE INDEX "fileParent"  ON "files"("parent");
CREATE INDEX "fileRPathNS" ON "files"("rPath" COLLATE NATSORT);


CREATE TABLE thumbs(          
  hash    TEXT,  
  image   INTEGER,
  width   INTEGER,
  height  INTEGER,            
  FOREIGN KEY(image) REFERENCES files(id) ON DELETE CASCADE,
  PRIMARY KEY(hash, image) ON CONFLICT REPLACE
);

CREATE INDEX "thumbImage" ON "thumbs"("image");


CREATE TABLE meta(
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  file    INTEGER NOT NULL,
  key     TEXT NOT NULL,
  value   TEXT,         
  extra   TEXT,         
  gallery INTEGER,
  FOREIGN KEY(gallery) REFERENCES pages(id) ON DELETE CASCADE,  
  FOREIGN KEY(file) REFERENCES files(id) ON DELETE CASCADE
);

CREATE INDEX "metaFileId" ON "meta"("file"); 
CREATE INDEX "metaKey"    ON "meta"("key"); 
CREATE INDEX "metaExtra"  ON "meta"("extra"); 


CREATE VIRTUAL TABLE search USING fts4(file, key, value, gallery);

CREATE TRIGGER metaBeforeUpd BEFORE UPDATE ON meta BEGIN
  DELETE FROM search WHERE docid = OLD.rowid;
END;

CREATE TRIGGER metaBeforeDel BEFORE DELETE ON meta BEGIN
  DELETE FROM search WHERE docid = OLD.rowid;
END;

CREATE TRIGGER metaAfterUpd AFTER UPDATE ON meta BEGIN
  INSERT INTO search(docid, file, key, value, gallery) VALUES(NEW.rowid, NEW.file, NEW.key, NEW.value, NEW.gallery);
END;

CREATE TRIGGER metaAfterIns AFTER INSERT ON meta BEGIN
  INSERT INTO search(docid, file, key, value, gallery) VALUES(NEW.rowid, NEW.file, NEW.key, NEW.value, NEW.gallery);
END;

The problem is that not only it's slow, but it also locks these tables so I cannot do any changes to them during this time.

I tried every suggestion from this question and answers but without significant improvements. Setting journaling mode to MEMORY and turning off sync made it run a little faster, but it's too risky.

To avoid long write locks, I tried deleting records step by step, 40 at time with a 0.5s delay between. But this slows down the entire process even by 10x

Is there any other way I can improve the speed and/or avoid locks?

---

PS: What baffles me is that INSERTs are much faster. It takes 2 minutes to insert the amount of records I was deleting, and that time includes some heavy file processing (Exif reading from lots of images). Why is removing records slower than inserting?

Answer 1

Deleting from pages is slow because there is no index on the gallery column in the meta table. Whenever a pages record is actually deleted, the database has to search for any meta records that match the ON DELETE CASCADE constraint; this results in a full table scan for every deleted record.

(The INSERTs are faster because no such check must be made.)

SQLite is not designed for concurrency; it is never possible to have multiple writers at the same time. However, to allow multiple readers at the same time as a writer, consider enabling write-ahead logging.