Re: Read performance on Large Table - Mailing list pgsql-admin

From Scott Marlowe
Subject Re: Read performance on Large Table
Date
Msg-id CAOR=d=3Ft9x+r0N30a_ASgLtSDFNA_m-H7_-sdFuOqD3FRyjPg@mail.gmail.com
Whole thread Raw
In response to Read performance on Large Table  (Kido Kouassi <jjkido@gmail.com>)
Responses Re: Read performance on Large Table
List pgsql-admin
On Thu, May 21, 2015 at 8:54 AM, Kido Kouassi <jjkido@gmail.com> wrote:
> Hello Admin,
>
> I am looking to build a database to log activities on my system
>
> Currently I have the following  table:
>
> CREATE TABLE table
> (
>   attr1 bigint,
>   attr2 text,
>   attr3 timestamp without time zone,
>   attr4 integer,
>   attr5 character varying(50),
>   attr6 character varying(50),
>   attr7 character varying(50),
>   attr8 character varying(50),
>   attr9 character varying(50),
>   attr10 character varying(50)
> )
>
> Over 3 months the table has grown to 600+ Millions Rows,
>
> We mainly do inserts, we almost never do updates or delete.
>
> However the table is not very useful as it stands  because any select  we
> run it takes a very long time ( in hours) to complete.
>
>
> Question1: Is Slowness to be expected with such big table?
> Question2: is there anything I am not doing that I should do to get a better
> perfomance?
>
> I am thinking about 2 solution but I wanted to Ask the more experienced
> people before implementing them:
>
> 1: Break the table into multiple small table for each week.
> 2: Create an index on the Timestamp column sin it is the one we use the most
> in queries.

These are both good ideas. You can use partitioning to do Step1 and
still have all your data in "one place" so to speak. Here's the
standard page on partitioning:

http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html

I've done a lot of partitioning of big data sets in postgresql and if
there's some common field, like data, that makes sense to partition
on, it can be a huge win.


pgsql-admin by date:

Previous
From: Ravi Krishna
Date:
Subject: Re: Read performance on Large Table
Next
From: Scott Ribe
Date:
Subject: Re: Read performance on Large Table