<ol>
<li><p>No, SQLite doesn't automatically reuse freed space after deleting rows. Instead, it marks the space as free but doesn't immediately reclaim it. This can lead to fragmentation over time, where the database file becomes larger than necessary. The <code>VACUUM</code> command is used to reclaim this unused space. It rebuilds the entire database, reorganizing the data and removing the free space. This can significantly reduce the database file size and improve performance. That being said sqlite3 implements an auto-vacuum, that will reclaim space, just the timing is not user-controlled.</p>
</li>
<li><p>Vacuuming is safe, as it compacts the data as is, and doesn't perform any user-visible changes (the data is reorganized, but it doesn't change as such). Yes.</p>
</li>
<li><p>None that I am aware of. Potentially you could recover some deleted rows I guess, however CLN is careful to never delete important information, until we're absolutely certain it won't be needed anymore. For longer term archival you can use a backup plugin which replicates changes to a plugin.</p>
</li>
</ol>

CC BY-SA 4.0
2024-12-22T08:28:11.970Z
Many thanks for your response. *sqlite3 implements an auto-vacuum* I would like to know how frequently might one expect `auto-vacuum` to be executed on a CLN node that is run 24/7.
John Smith
CC BY-SA 4.0
2024-12-26T13:12:17.263Z
Good question, as far as I understand the sqlite3 code it tracks how dirty and fragmented the blocks are and will trigger an auto-vacuum when the effective data dips below a percentage. So the auto-vacuum interval depends on the DB use, and therefore on both the nodes activity as well as the operator behavior. As such there is no answer that works for everybody.
cdecker
CC BY-SA 4.0
2024-12-20T18:07:03.607Z
2024-12-20T18:07:03.607Z
cdecker
<p>I ran</p>
<pre><code>lightning-cli autoclean-once failedforwards 600
lightning-cli autoclean-once succeededforwards 600
lightning-cli autoclean-once failedpays 600
lightning-cli autoclean-once succeededpays 600
lightning-cli autoclean-once expiredinvoices 600
lightning-cli autoclean-once paidinvoices 600
</code></pre>
<p>on my Core Lignitning node to remove hundreds of items that are older than 10 minutes. Despite than the size of <code>lightningd.sqlite3</code> remained the same. From <a href="https://www.sqlite.org/lang_vacuum.html" rel="nofollow noreferrer">what I gathered</a> this is how sqlite3 works:</p>
<blockquote>
<p>When content is deleted from an SQLite database, the content is not usually erased but rather the space used to hold the content is marked as being available for reuse.</p>
</blockquote>
<p><strong>1.</strong> Does <code>autoclean-once</code> without vacuuming slow down the future growth of the database (due to reuse)?</p>
<p><strong>2.</strong> Would it be safe to use <a href="https://www.sqlite.org/lang_vacuum.html" rel="nofollow noreferrer">vacuum</a> after shutting the node down?</p>
<p><strong>3.</strong> What is the forensic aspect of vacuuming.</p>

<core-lightning><database><storage>
Does `autoclean-once` without vacuuming lightningd.sqlite3 slow down the future growth of size lightningd.sqlite3?
John Smith
# Does `autoclean-once` without vacuuming lightningd.sqlite3 slow down the future growth of size lightningd.sqlite3?

(Asked by: username_0 on 2024-12-20)

I ran

```
lightning-cli autoclean-once failedforwards 600
lightning-cli autoclean-once succeededforwards 600
lightning-cli autoclean-once failedpays 600
lightning-cli autoclean-once succeededpays 600
lightning-cli autoclean-once expiredinvoices 600
lightning-cli autoclean-once paidinvoices 600

```

on my Core Lignitning node to remove hundreds of items that are older than 10 minutes. Despite than the size of `lightningd.sqlite3` remained the same. From [what I gathered](https://www.sqlite.org/lang_vacuum.html) this is how sqlite3 works:

> When content is deleted from an SQLite database, the content is not usually erased but rather the space used to hold the content is marked as being available for reuse.

**1.** Does `autoclean-once` without vacuuming slow down the future growth of the database (due to reuse)?

**2.** Would it be safe to use [vacuum](https://www.sqlite.org/lang_vacuum.html) after shutting the node down?

**3.** What is the forensic aspect of vacuuming.

**Tags:** core-lightning, database, storage


---

## 1 answers

### Answer by username_1 on 2024-12-20. Score: 2 | Accepted answer

1. No, SQLite doesn't automatically reuse freed space after deleting rows. Instead, it marks the space as free but doesn't immediately reclaim it. This can lead to fragmentation over time, where the database file becomes larger than necessary. The `VACUUM` command is used to reclaim this unused space. It rebuilds the entire database, reorganizing the data and removing the free space. This can significantly reduce the database file size and improve performance. That being said sqlite3 implements an auto-vacuum, that will reclaim space, just the timing is not user-controlled.
2. Vacuuming is safe, as it compacts the data as is, and doesn't perform any user-visible changes (the data is reorganized, but it doesn't change as such). Yes.
3. None that I am aware of. Potentially you could recover some deleted rows I guess, however CLN is careful to never delete important information, until we're absolutely certain it won't be needed anymore. For longer term archival you can use a backup plugin which replicates changes to a plugin.

> **Comments:**
> **username_0**: Many thanks for your response. *sqlite3 implements an auto-vacuum* I would like to know how frequently might one expect `auto-vacuum` to be executed on a CLN node that is run 24/7.  
> **username_1**: Good question, as far as I understand the sqlite3 code it tracks how dirty and fragmented the blocks are and will trigger an auto-vacuum when the effective data dips below a percentage. So the auto-vacuum interval depends on the DB use, and therefore on both the nodes activity as well as the operator behavior. As such there is no answer that works for everybody.  

---

