[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

[PATCH] Use the `WITHOUT ROWID` SQLite optimization for rep-cache.db

From: Evgeny Kotkov <evgeny.kotkov_at_visualsvn.com>
Date: Thu, 30 Nov 2017 19:44:44 +0300

Hi all,

The recent SQLite versions (starting from 3.8.2, released in December 2013)
feature a `WITHOUT ROWID` optimization [1] that can be enabled when creating
a table. In short, it works well for tables that have non-integer primary
keys, such as

    name TEXT PRIMARY KEY

by not maintaining the hidden rowid values and an another B-Tree to match
between a primary key value and its rowid. This reduces the on-disk size
and makes the lookups faster (a key → rowid → data lookup is replaced with
a key → data lookup).

Currently, the rep-cache.db schema uses a non-integer primary key:

    hash TEXT NOT NULL PRIMARY KEY

and can benefit from this optimization. A quick experiment showed a
reduction of the on-disk size of the database by ~1.75x. The lookups
should also be faster, both due to the reduced database size and due to
the lesser amount of internal bsearches. This should improve the times
of new commits and `svnadmin load`, especially for large repositories
that also have large rep-cache.db files.

I think that it would be nice to have this optimization in rep-cache.db,
and that we can start using it in a compatible way:

  - All existing rep-cache.db statements are compatible with it.

  - Since SQLite versions prior to 3.8.2 don't support it, we would
    only create the new tables with this optimization in fsfs format 8,
    and simultaneously bump the minimal required SQLite version from
    3.7.12 (May 2012) to 3.8.2 (December 2013). This would ensure that
    all binaries supporting format 8 can work with the tables with this
    optimization.

Would there be any objections to a change like this (see the attached patch)?

[1] https://sqlite.org/withoutrowid.html

Thanks,
Evgeny Kotkov

Received on 2017-11-30 17:45:11 CET

This is an archived mail posted to the Subversion Dev mailing list.