> > > 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.
