From: | "Campbell, Lance" <lance(at)illinois(dot)edu> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Should I use table partitions |
Date: | 2018-10-09 14:38:07 |
Message-ID: | 92E6547E-B4A7-419B-B3CC-85907D5E1C4A@illinois.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)illinois(dot)edu>
Date: Tuesday, October 9, 2018 at 9:20 AM
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Campbell, Lance | 2018-10-09 14:43:37 | Re: Should I use table partitions |
Previous Message | Keith Fiske | 2018-10-09 14:34:37 | Re: Should I use table partitions |