Re: Creating table and indexes for new application

On Sun, 25 Feb, 2024, 1:05 am yudhi s, wrote: > > On Fri, Feb 23, 2024 at 5:26 PM sud wrote: > >> >> >> On Fri, 23 Feb, 2024, 1:28 pm yudhi s, >> wrote: >> >>> >>> >>> On Fri, 23 Feb, 2024, 1:20 pm sud, wrote: >>> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, wrote: >>

Re: Creating table and indexes for new application

On Fri, Feb 23, 2024 at 5:26 PM sud wrote: > > > On Fri, 23 Feb, 2024, 1:28 pm yudhi s, > wrote: > >> >> >> On Fri, 23 Feb, 2024, 1:20 pm sud, wrote: >> >>> >>> >>> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, >>> wrote: >>> On Fri, 2024-02-23 at 02:05 +0530, yudhi s > 2)Should

Re: Creating table and indexes for new application

On Fri, 23 Feb, 2024, 1:28 pm yudhi s, wrote: > > > On Fri, 23 Feb, 2024, 1:20 pm sud, wrote: > >> >> >> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, >> wrote: >> >>> On Fri, 2024-02-23 at 02:05 +0530, yudhi s >>> >>> > 2)Should we be creating composite indexes on each foreign key for >>> table

Re: Creating table and indexes for new application

On Fri, 23 Feb, 2024, 1:20 pm sud, wrote: > > > On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, > 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 g

Re: Creating table and indexes for new application

On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, 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

Re: Creating table and indexes for new application

On Fri, 2024-02-23 at 02:05 +0530, yudhi s wrote: > postgres version 15+ database. And it would be ~400million transactions/rows > per day in the > main transaction table and almost double in the multiple child tables and > some child tables > will hold lesser records too. > > We are considering

Re: Creating table and indexes for new application

My 2 cents... Foreign key indexes are required for avoiding locking when deleting and updating the pk in parent. But insert only table may not have any issue. And we used to do this in other relational databases like oracle but not sure how different is this in PG. However considering your high tra

Creating table and indexes for new application

Hello Friends, We are newly creating tables for a system which is going to consume transaction data from customers and store in postgres version 15+ database. And it would be ~400million transactions/rows per day in the main transaction table and almost double in the multiple child tables and some