Very large table: Partition it or not?

From: Jack Orenstein <jao(at)geophile(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Very large table: Partition it or not?
Date: 2020-12-16 19:44:25
Message-ID: CAGNxcaveCA0wPux6gko2yX=e_N0Y0AUkbBGAjZOt6g7txgC3aQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Jack Orenstein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martín Marqués 2020-12-16 21:42:35 Re: undefined reference to `pg_snprintf when we upgraded libpq version from 10.3 to 12.3
Previous Message Jeff Janes 2020-12-16 18:18:00 Re: Autovacuum not functioning for large tables but it is working for few other small tables.