On Thu, May 21, 2015 at 8:54 AM, Kido Kouassi <jjkido@xxxxxxxxx> 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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
