Re: Should I use table partitions

On Tue, Oct 9, 2018 at 10:19 AM Campbell, Lance <lance(at)illinois(dot)edu> wrote:

> PostgreSQL 10.x
>
>
>
> Question:
>
> Should I use table partitions if my table meets the below parameters? The
> size of the table should remain relatively consistent in size and number of
> rows. I would not expect it to vary more than 15% at any given time.
>
>
>
> Info:
>
> Table rows: 83,051,164
>
> Table size only: 25.6 G
>
> Total size of table including indexes: 38.6 G
>
> Table definition:
>
>
>
> CREATE TABLE xyz.abc
>
> (
>
> fk_def_id integer NOT NULL,
>
> id integer NOT NULL DEFAULT
> nextval(('xyz.abc_id_seq'::text)::regclass),
>
> email_address text,
>
> created_timestamp timestamp with time zone DEFAULT now(),
>
> updated_timestamp timestamp with time zone DEFAULT now(),
>
> provider text,
>
> server_id integer DEFAULT 0,
>
> read_count integer DEFAULT 0,
>
> fk_group_id integer DEFAULT 0,
>
> mail_log_timestamp timestamp with time zone DEFAULT now(),
>
> mail_log_process_code integer DEFAULT '-1'::integer,
>
> mail_log_message text DEFAULT,
>
> mail_log_mail_id text DEFAULT '',
>
> mail_log_rule text DEFAULT '',
>
> is_listserv boolean DEFAULT false,
>
> is_bad_email_address boolean DEFAULT false,
>
> bad_email_address_message text DEFAULT '',
>
> finder text DEFAULT '',
>
> constituent_id text DEFAULT '',
>
> CONSTRAINT abc_pkey PRIMARY KEY (id)
>
> );
>
>
>
> Thanks,
>
>
>
> Lance Campbell
>
>
>

The big, main reason for partitioning in PostgreSQL is if you have data
retention that has to be managed. It makes having to deal with bloat a lot
easier since you don't have to do bulk deletes that may never be filled
with new data again. If you have no data retention being done now, I
wouldn't worry about it until you start getting closer to 1 billion rows or
more. That's when vacuuming run times may start to be a concern and
partitioning can help reduce those since the individual objects are smaller
to manage. Also, if there's a lot of static data, and you're on 9.6+, you
can greatly improve vacuum times by running a VACUUM FREEZE on the table.
That added a feature to allow vacuum to skip pages that are fully frozen.

Query read performance is really a secondary concern for partitioning in
PG, especially if you're not managing data retention. Btree lookups are FAR
more efficient than constraint exclusion (partition pruning in PG11+) will
ever be. And if you're only doing more narrow data lookups, you'll actually
see a performance decrease in queries since removing partitions in the
planner adds overhead. That overhead isn't as big of a concern if there's
data retention to manage, though, since bloat will be a bigger impact than
the planner overhead. If you're grabbing larger blocks of data at one time,
that's when partitioning may start helping with reads more since it has to
pull fewer pages from disk.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com