> > > I have a table in an analytics database (Postgres 12.3), that gathers data continuously. It is at 5B rows, with an average row size of 250 bytes. The table has five indexes, on bigint and varchar columns, all with keys of one or two columns. > > There are currently frequent updates and deletions, but the net change in the number of rows is continuously positive. We are rearchitecting the application to avoid the updates and deletes. I.e., the table will soon be append-only, (so vacuuming will be needed only to avoid transaction id wraparound). > > I know that the maximum table size is 32TB, which allows for 128B rows. Based on this calculation, and the expected growth rate (2B/year currently), we should be good for quite a while. > > What are the pros and cons of partitioning the table? Without partitioning, are we liable to run into trouble as this table keeps growing? I do realize that some query times will grow with table size, and that partitioning, combined with parallel query execution can address that problem. I'm more wondering about problems in maintaining tables and indexes once we have 10B, 20B, ... rows. > > Does the table has a primary key/unique constraint which can not be part of the partitioning key. Then you have a serious issue in converting this table into a partitioned one. Unlike Oracle or Db2, PG still does not have a concept of global index on a partitioned table.
