[GNC] pros/cons of storage formats

Thu May 17 15:54:36 EDT 2018

I have used xml, postgresql, and sqlite (which must default to sqlite3
on Ubuntu) with gnucash.  sqlite3, as mentioned already, is a good
compromise between a database backend and simplicity of set up.
PostgreSQL is, probably, technically the best of the database backend
options for being robust, able to replay record of transactions, and
speed.  But it is highly unlikely that you (and most users) will ever
notice a difference in the speed of any of the sql backends.

I now use sqlite3.  It's usually the easiest of the sql backends to
set up, and it does fine with large gnucash files (57.6 Megs as of
today).  But I had no problem with xml format, and if I remember, it
saved log files after each session, which might come in handy.  My
impression (but not verified) is that the sqlite3 file loads faster
than the xml at startup, but I expect that with a few years of data.

sqlite3 is a great little "container" for data that you can later
import to other apps and for sql queries.  If you already are a dba
for a PostgreSQL installation, then you have more options, and you
know that Postgres rocks for speed and optimisation.  But I am also a
big fan of "easy and lazy", so I doubt you could go wrong with any of
these formats.  But if xml still generates log files, be aware you
might have to do some clean up over time if the sheer number of log
files bothers you.  I used to archive them to a zfs server after a
year or so and delete them on the machine running gnucash.  Also be
aware that depending on whatever jurisdiction you are in, the
"compliance" people may become interested, and they may want to see
log files if you have them.  I don't know how far back sqlite3 can
"replay" transactions, but I never kept the log files from .xml for
more than a year or so.  Postgres, if I remember correctly, can keep
transaction records going back to the beginning of the database
record.

So perhaps stick with sqlite3 or xml, and make backups.  I backup to a
remote machine every day I make a change to the gnucash file.  Daily
backups have saved me more time and aggravation than anything else.

Gordon

On Thu, May 17, 2018 at 12:16 PM, Adrien Monteleone
<adrien.monteleone at lusfiber.net> wrote:
> When playing with it I noticed zero performance hit on High Sierra. But I also have no performance lag with XML. Mileage may vary.
>
> Regards,
> Adrien
>
>> On May 17, 2018, at 9:07 AM, John Ralls <jralls at ceridwen.us> wrote:
>>
>> Should be since the SQL (including SQLite3) backend saves each change immediately so there is no bulk saving. The initial “save as” operation might be, but that’s a one-time hit.
>>
>> Regards,
>> John Ralls
>>
>>
>>> On May 17, 2018, at 6:35 AM, Sébastien de Menten <sdementen at gmail.com> wrote:
>>>
>>> Is the SQLite storage format immune to the issue regarding "very long time
>>> to save the XML format" some users are experiencing?
>>>
>>> On Thu, May 17, 2018, 10:46 David T. via gnucash-user <
>>> gnucash-user at gnucash.org> wrote:
>>>
>>>> And, as I noted in January, the sql format does not get compressed, so the
>>>> files are much larger.
>>>>
>>>>
>>>>
>>>> On Thu, May 17, 2018 at 10:37, Adrien Monteleone<
>>>> adrien.monteleone at lusfiber.net> wrote:   Keith,
>>>>
>>>> The SQL backends are having a few issues at the moment. It would be a good
>>>> idea to search the list here and also to look over the bug reports on
>>>> Bugzilla.
>>>>
>>>> I originally used MySQL when I was running an Ubuntu box as my daily
>>>> machine, but changed to XML when I moved to MacOS, and then switched to
>>>> sqlite3 about two years ago, but had to change back to XML since the 3.x
>>>> series release due to several bugs related to the business features. (if
>>>> you don’t use those, you might not have any problems)
>>>>
>>>> If all you want is the ability to run outside queries for custom reporting
>>>> or data integration, then sqlite3 is probably your safest bet. MySQL and
>>>> Postgres are much more involved to setup and maintain. Essentially, if
>>>> you’re not a database admin, you probably don’t need those two or will not
>>>> find any advantage to the maintenance learning curve and overhead, stick
>>>> with sqlite3 or XML.
>>>>
>>>> As for searching the list archives, use the following syntax before your
>>>> search query terms:
>>>>
>>>> site:lists.gnucash.org
>>>>
>>>> However, I see that DuckDuckGo does not return any results with this
>>>> method.
>>>>
>>>> Google does, as well as StartPage. (the latter I find to be more privacy
>>>> conscious than DDG and usually provides me more relevant results.) I
>>>> haven’t tested any other search engines with that syntax.
>>>>
>>>> Regards,
>>>> Adrien
>>>>
>>>>
>>>>> On May 16, 2018, at 10:32 PM, Keith Keller <
>>>> kkeller at wombat.san-francisco.ca.us> wrote:
>>>>>
>>>>> On Thu, May 17, 2018 at 03:21:13AM +0000, David T. wrote:
>>>>>> I'm not sure how you looked,  but this topic had been discussed for
>>>> many years on the list. A recent one was
>>>> https://lists.gnucash.org/pipermail/gnucash-user/2018-January/074315.html.
>>>> Another was
>>>> https://lists.gnucash.org/pipermail/gnucash-user/2013-September/050410.html
>>>> .
>>>>>
>>>>> Thanks David!  That January 2018 thread was helpful.  I'm not sure why
>>>>> DDG didn't turn it up.  :(
>>>>>
>>>>> My impression is that the format really doesn't make an appreciable
>>>>> difference to performance, and that a SQL format might be more flexible
>>>>> for reporting but otherwise the formats are basically interchangeable.
>>>>> Does that sound like a reasonable summary?
>>>>>
>>>>> I may simply stick with XML for now since it's easy and lazy.
>>>>>
>>>>> --keith
>>>>>
>>>>>
>>>>> --
>>>>> kkeller at wombat.san-francisco.ca.us
>>>>>
>>>>> _______________________________________________
>>>>> gnucash-user mailing list
>>>>> gnucash-user at gnucash.org
>>>>> To update your subscription preferences or to unsubscribe:
>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>> If you are using Nabble or Gmane, please see
>>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>>>> -----
>>>>> Please remember to CC this list on all your replies.
>>>>> You can do this by using Reply-To-List or Reply-All.
>>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> gnucash-user mailing list
>>>> gnucash-user at gnucash.org
>>>> To update your subscription preferences or to unsubscribe:
>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>> If you are using Nabble or Gmane, please see
>>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>>> -----
>>>> Please remember to CC this list on all your replies.
>>>> You can do this by using Reply-To-List or Reply-All.
>>>> _______________________________________________
>>>> gnucash-user mailing list
>>>> gnucash-user at gnucash.org
>>>> To update your subscription preferences or to unsubscribe:
>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>> If you are using Nabble or Gmane, please see
>>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>>> -----
>>>> Please remember to CC this list on all your replies.
>>>> You can do this by using Reply-To-List or Reply-All.
>>> _______________________________________________
>>> gnucash-user mailing list
>>> gnucash-user at gnucash.org
>>> To update your subscription preferences or to unsubscribe:
>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>> -----
>>> Please remember to CC this list on all your replies.
>>> You can do this by using Reply-To-List or Reply-All.
>>
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> To update your subscription preferences or to unsubscribe:
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.