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