Re: Creating table and indexes for new application

On Sun, 25 Feb, 2024, 1:05 am yudhi s, <learnerdatabase99(at)gmail(dot)com> wrote:

>
> On Fri, Feb 23, 2024 at 5:26 PM sud <suds1434(at)gmail(dot)com> wrote:
>
>>
>>
>> On Fri, 23 Feb, 2024, 1:28 pm yudhi s, <learnerdatabase99(at)gmail(dot)com>
>> wrote:
>>
>>>
>>>
>>> On Fri, 23 Feb, 2024, 1:20 pm sud, <suds1434(at)gmail(dot)com> wrote:
>>>
>>>>
>>>>
>>>> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at>
>>>> wrote:
>>>>
>>>>> On Fri, 2024-02-23 at 02:05 +0530, yudhi s
>>>>>
>>>>> > 2)Should we be creating composite indexes on each foreign key for
>>>>> table2 and table3, because
>>>>> > any update or delete on parent is going to take lock on all child
>>>>> tables?
>>>>>
>>>>> Every foreign key needs its own index. A composite index is only
>>>>> appropriate if the foreign
>>>>> key spans multiple columns.
>>>>>
>>>>>
>>>>
>>>> From the DDL which OP posted it's using composite foreign key thus a
>>>> composite index would be needed.
>>>> However, if someone doesn't delete or update the parent table PK , is
>>>> it still advisable to have all the FK indexed? Like in general I think
>>>> transaction id should not get updated in a normal scenario unless some
>>>> special case.
>>>>
>>>>
>>>>
>>> Thank you. I can double check if we have confirmed use case of deleting
>>> the parent table or updating PK in the parent table. But anyway it can
>>> happen for data fix for sure in some scenario.
>>>
>>> But yes, we are certainly going to drop/purge partition from all the
>>> parent and child table after specific days. So isn't that need the FK to be
>>> indexed or else it will scan whole parent table partition?
>>>
>>
>>
>> I am not sure if drop partition of parent table, will have a lock or will
>> do a full scan on the child table while doing the partition maintenance or
>> dropping the partitions, in absence of foreign key index. Others may
>> comment here.
>>
>>>
>>>>
> Can you please help me understand, If it's true that all the statements
> like Delete, Update and Drop partition of parent table will take lock on
> the child table and Full scan the child table , and thus foreign key index
> on all the child table is necessary irrespective of the performance
> overhead it has on all the INSERT queries into the child tables?
>

Not sure of the lock but I think you should be able to see a full scan on
child table while doing delete or update of parent table PK. Explain
Analyze should show that I believe. Not sure if explain analyze will work
for drop partition too.

>