Sqlite VACUUM - Is it safe to run? #5581

New Issue

GiteaMirror · 2025-11-02T06:29:38-06:00

GiteaMirror commented

2025-11-02 06:29:38 -06:00

Originally created by @salesgroup on GitHub (Jun 19, 2020).

We are using Gitea with sqlite3.
Since we have several delete operations (repos, users, webhooks, etc) we would like to run a cron job every day, to clean the database file.
Sqlite vacuum has some potencial problems with tables without "INTEGER PRIMARY KEY"

I think these tables are: gpg_key_import, external_login_user, oauth2_session, email_hash
details:
CREATE TABLEgpg_key_import (key_idTEXT PRIMARY KEY NOT NULL,contentTEXT NOT NULL);
CREATE TABLEexternal_login_user (external_idTEXT NOT NULL,user_idINTEGER NOT NULL,login_source_idINTEGER NOT NULL,raw_dataTEXT NULL,providerTEXT NULL,emailTEXT NULL,nameTEXT NULL,first_nameTEXT NULL,last_nameTEXT NULL,nick_nameTEXT NULL,descriptionTEXT NULL,avatar_urlTEXT NULL,locationTEXT NULL,access_tokenTEXT NULL,access_token_secretTEXT NULL,refresh_tokenTEXT NULL,expires_atDATETIME NULL, PRIMARY KEY (external_id,login_source_id));
CREATE TABLEoauth2_session (idTEXT PRIMARY KEY NOT NULL,dataTEXT NULL,created_unixINTEGER NULL,updated_unixINTEGER NULL,expires_unixINTEGER NULL);
CREATE TABLEemail_hash (hashTEXT PRIMARY KEY NOT NULL,emailTEXT NOT NULL);

Is it safe to run sqlite vacuum every day?

Originally created by @salesgroup on GitHub (Jun 19, 2020). We are using Gitea with sqlite3. Since we have several delete operations (repos, users, webhooks, etc) we would like to run a cron job every day, to clean the database file. Sqlite vacuum has some potencial problems with tables without "INTEGER PRIMARY KEY" I think these tables are: gpg_key_import, external_login_user, oauth2_session, email_hash details: ` CREATE TABLE `gpg_key_import` (`key_id` TEXT PRIMARY KEY NOT NULL, `content` TEXT NOT NULL); ` ` CREATE TABLE `external_login_user` (`external_id` TEXT NOT NULL, `user_id` INTEGER NOT NULL, `login_source_id` INTEGER NOT NULL, `raw_data` TEXT NULL, `provider` TEXT NULL, `email` TEXT NULL, `name` TEXT NULL, `first_name` TEXT NULL, `last_name` TEXT NULL, `nick_name` TEXT NULL, `description` TEXT NULL, `avatar_url` TEXT NULL, `location` TEXT NULL, `access_token` TEXT NULL, `access_token_secret` TEXT NULL, `refresh_token` TEXT NULL, `expires_at` DATETIME NULL, PRIMARY KEY ( `external_id`,`login_source_id` )); ` ` CREATE TABLE `oauth2_session` (`id` TEXT PRIMARY KEY NOT NULL, `data` TEXT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL, `expires_unix` INTEGER NULL); ` ` CREATE TABLE `email_hash` (`hash` TEXT PRIMARY KEY NOT NULL, `email` TEXT NOT NULL); ` Is it safe to run sqlite vacuum every day?

GiteaMirror closed this issue

2025-11-02 06:29:38 -06:00

GiteaMirror commented

2025-11-02 06:29:39 -06:00

@stale[bot] commented on GitHub (Aug 24, 2020):

This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.

@stale[bot] commented on GitHub (Aug 24, 2020): This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.

GiteaMirror commented

2025-11-02 06:29:39 -06:00

@salesgroup commented on GitHub (Aug 24, 2020):

any updates on this?

@salesgroup commented on GitHub (Aug 24, 2020): any updates on this?

GiteaMirror commented

2025-11-02 06:29:39 -06:00

@zeripath commented on GitHub (Aug 29, 2020):

In my quick searches I can't see an obvious problem with VACUUM. How would you be running VACUUM?

@zeripath commented on GitHub (Aug 29, 2020): In my quick searches I can't see an obvious problem with VACUUM. How would you be running VACUUM?

GiteaMirror commented

2025-11-02 06:29:39 -06:00