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 all of these tables for partitioning by the same transaction_date column > and it would be daily partitions. We have some questions, > > 1)While creating these tables and related indexes, do we need to be careful of defining any > other storage parameters like tablespaces etc Or its fine to make those table/indexes > aligned to the default tablespace only? and are there any constraints on tablespace size , > as we will have 100's GB of data going to be stored in each of the daily partitions? There are no constraints on the size of a tablespace other than the limits of the underlying file system. Use the default tablespace. > 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. > 3)We were thinking of simple Btree indexes to be created on the columns based on the search > criteria of the queries. but the indexes doc i see in postgres having INCLUDE keywords also > available in them. So I'm struggling to understand a bit, how it's adding value to the read > query performance if those additional columns are not added explicitly to the index but are > part of the INCLUDE clause? Will it give some benefit in regards to the index storage space? > or should we always keep all the columns in the index definition itself other than some > exception scenario? Struggling to understand the real benefit of the INCLUDE clause. The benefits of putting a column into INCLUDE is twofold: 1) The column only has to be stored in the leaf pages, since it is not used for searching. That makes the intermediate index entries smaller, which causes a wider fan-out of the index, which in turn makes the tree shallower and hence faster to search. 2) It is an implicit documentation that the column is not to be used for searching. Yours, Laurenz Albe
