>>>>> 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?
> 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?
__Martin
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
