[GRASS-dev] [GRASS GIS] #3697: sqlite db: automatically enable vacuum

[GRASS GIS]

#3697: sqlite db: automatically enable vacuum
--------------------------+---------------------------
  Reporter:  mlennert     |      Owner:  grass-dev@…
      Type:  enhancement  |     Status:  new
  Priority:  normal       |  Milestone:  7.8.0
 Component:  Database     |    Version:  unspecified
Resolution:               |   Keywords:  sqlite vacuum
       CPU:  Unspecified  |   Platform:  Unspecified
--------------------------+---------------------------

Comment (by mmetz):

 Replying to [ticket:3697 mlennert]:
 > I just noticed that a mapset sqlite db file was huge, despite the fact
 that there was only one small vector file left in it. Running 'vacuum;' in
 the sqlite database reduced the announced file size from 47G to 78K !
 >
 > There is a pragma auto_vacuum which allows to enable an auto-vacuum
 mode, which is not as efficient as calling the vacuum command, as it does
 not defragment the database, but I would think that it would at least keep
 file size to expected levels.
 >
 > I would, therefore, plead for enabling this pragma by default at
 database creation and setting it to 1 (full). I think this would be the
 behavior expected by most users.

 This auto_vacuum would occur at every transaction commit. Maybe it is more
 efficient to VACUUM the sqlite db now and then, e.g. when the database is
 closed or when the GRASS session is terminated.

 VACUUM when the database is closed would still be quite often, e.g. with
 every SELECT statement or also just describing the table.

 I would prefer to VACUUM the sqlite db when the GRASS session is
 terminating, i.e. in lib/init/grass.py. This would not slow down standard
 db operations and leaves the db in a clean state.

-- 
Ticket URL: <https://trac.osgeo.org/grass/ticket/3697#comment:1>
GRASS GIS <https://grass.osgeo.org>