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. 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 If someone feels this database is not representative of their use case, as mentioned above, they will be able to run the query tests on their own databases and provide feedback. Hope this helps. -- Dan Langille d...@langille.org _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
