Re: Creating table and indexes for new application

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?

>
>