Re: Should I use table partitions

["Campbell, Lance" <lance@xxxxxxxxxxxx>]

I forgot to include a few additional pieces of information:

1. I am using AWS RDS PostgreSQL solution for hosting.
2. I have a primary key (index) on the id field.
3. I have an index on the (fk_def_id, email_address) field.
4. I only access rows based on the fields (id), (fk_def_id) or (fk_def_id & email_address) . 
5. All SELECT statements should use one of the indexes I listed in #3 or #4.

 

Thanks for your feedback,

 

Lance

 

 

From: Lance Campbell <lance@xxxxxxxxxxxx>
Date: Tuesday, October 9, 2018 at 9:20 AM
To: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx>
Subject: Should I use table partitions

 

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