On Tue, Oct 3, 2023, at 1:31 PM, Martin Simmons wrote:
>>>>>> On Tue, 03 Oct 2023 09:35:27 -0400, Dan Langille said:
>>
>> On Mon, Oct 2, 2023, at 11:17 AM, Martin Simmons wrote:
>> >>>>>> On Sat, 30 Sep 2023 09:54:51 -0400, Dan Langille said:
>> >>
>> >> Hello,
>> >>
>> >> The Bacula PostgreSQL schema is missing several foreign keys (FK).
>> >> Foreign keys are not a new database concept; they've been around for
>> >> decades. They are reliable and robust.
>> >>
>> >> Wednesday, I started a dbcheck on a Bacula database. Granted, that
>> >> database is 19 years old and this is the first time I've run dbcheck (as
>> >> far as I know). That dbcheck is still going. FYI, the dump to disk is
>> >> about 140GB; lots of cruft removal.
>> >>
>> >> When PostgreSQL was first added to Bacula, there was resistance to FK,
>> >> and I did not pursue the issue. Thus, it persists to this day. I hope to
>> >> change that.
>> >>
>> >> I would like to take that development work back up (pun intended), and
>> >> start adding foreign keys back into Bacula, at least for PostgresQL. That
>> >> might remove the need for dbcheck (again, at least for Bacula on
>> >> PostgreSQL).
>> >
>> > What is the performance cost of foreign keys?
>>
>> I'm replying so it does not appear as if I am ignoring you. Short answer: I
>> don't know. Yet. That is the purpose of my project.
>
> OK, fair enough.
>
>>
>> I can't answer that in a way which would sound satisfying. I have not
>> started the work. I have only my personal experience - My backups seem fast
>> enough to me.
>>
>> It is easy enough to test. There are several ways to optimize foreign keys
>> usage.
>>
>> >> For example, one index I have been using this index for years. I find it
>> >> referenced[1] in the the 5.x documentation, but it is not part of the
>> >> catalog creation.
>> >>
>> >> "file_jobid_idx" btree (jobid)
>> >>
>> >> This index vastly improves the construction of the files, often going
>> >> from hours to seconds. I don't recall when that index was added here, but
>> >> building trees has never been an issue here.
>> >
>> > It was removed in this change:
>> >
>> > commit 740704c9c66d0b049a7cd548ac1204ef1aaf7356
>> > Author: Eric Bollengier <e...@baculasystems.com>
>> > Date: Mon May 11 17:11:40 2020 +0200
>> >
>> > BEE Backport bacula/src/cats/make_postgresql_tables.in
>> >
>> > Does PostgreSQL use file_jpfid_idx for the query if you don't have
>> > file_jobid_idx?
>>
>> Testing will show that. I am not at that stage yet. I will be examining the
>> queries used and running them through the PostgreSQL 'EXPLAIN ANALYZE'
>> process. I'll post results at https://explain.depesz.com so progress can be
>> seen and compared. Others will be able to run the same non-destructive
>> commands on their own databases for comparison.
>>
>> I just check my database and it has these row counts:
>>
>> filename: 22,232,549
>> file: 1,208,708,804
>> path: 8,340,411
>> job: 97,139
>> jobmedia: 331,379
>> media: 12,848
>
> Is this an old version? The filename table shouldn't exist now. The new
> catalog format could make a big difference to the queries (and foreign key
> performance).
Yes, it's Bacula 9... for shame...
--
Dan Langille
d...@langille.org
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
