Re: Creating table and indexes for new application

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.

>
>>