Re: Read performance on Large Table

On Thu, May 21, 2015 at 8:54 AM, Kido Kouassi <jjkido(at)gmail(dot)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.