Questions after xml->sqlite conversion

John Ralls jralls at ceridwen.us
Thu Feb 21 20:06:59 EST 2013


On Feb 21, 2013, at 9:08 AM, StephenAndMandy Cleary <stephenandmandy at gmail.com> wrote:

> Hello, folks -
> 
> Long time user of GNUCash here. I've been looking forward to the db file
> format for a while, and recently updated my primary finances file to use
> the new sqlite format. I've got ~6 years of transactions in there.
> 
> With the XML -> db conversion, I was expecting a reduced file size and
> faster startup times, neither of which has happened. I no longer have to
> save, but I notice that it doesn't keep open a lock file, either.
> 
> So, my questions:
> 1) Is GNUCash still safe for multiple users? I have my finances on Dropbox
> and occasionally use multiple access (restricting writes to the first user
> is fine, as long as the others can read). The lock file worked for this but
> will sqlite?
> 2) Is the expected file size for sqlite much larger than xml? My xml db was
> 840,531 bytes and I thought it would go down when moving from xml to a
> relational db. The sqlite file is 6,503,424 bytes. I went from <1MB to
> 6.5MB. Is there something wrong or is this normal?
> 

Gnucash still uses SQL backends only for persistence: Everything is loaded into memory at startup, and it's probably slower
than XML because the current data model is a better fit for XML than for an RDB.

The lock is maintained in a table in the database rather than  a separate file: That's the only way to get it to work on MySql
and Postgresql. The lock is very weak protection against two people opening the database at the same time. I'd never claim
that it makes Gnucash "safe for multiple users", regardless of whether you're using the SQL or XML backends. Your use-case
is extremely risky.

As David suggested, part of the difference in file size might be due to compression, which AFAIK isn't available for relational
data base (RDB) storage. It would impose a massive performance penalty on queries, and because the largest compressible unit
 would be a single row in a table, it wouldn't be very effective. The other thing is that as verbose as XML is, it still has less 
overhead thanan RDB. I wouldn't expect uncompressed XML to be significantly larger than the equivalent RDB file.

Regards,
John Ralls




More information about the gnucash-user mailing list