Skip site navigation (1) Skip section navigation (2)

Indexes and Primary Keys on Rapidly Growing Tables

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Indexes and Primary Keys on Rapidly Growing Tables
Date: 2012-02-20 22:06:28
Message-ID: CAAB3BBK8-f3R16LwjDJS3i+Kp8nj6-bOob+L=x5F4DmKk+aisA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
New question regarding this seen_its table: It gets over 100 inserts per
second. Probably many more if you include every time unique_violation occurs.
This flood of data is constant. The commits take too long (upwards of 100
ms, ten times slower than it needs to be!) What I'm wondering is if it
would be better to insert all of these rows into a separate table with no
constraints (call it daily_seen_its) and then batch insert them into a
table with something like: INSERT INTO seen_its SELECT user_id, moment_id,
MIN(created) FROM daily_seen_its GROUP BY user_id, moment_id WHERE created
BETWEEN 'yesterday' AND 'today'; the idea being that a table with no
constraints would be able to accept insertions much faster and then the
primary key could be enforced later. Even better would be if this could
happen hourly instead of daily. But first I just want to know if people
think that this might be a viable solution or if I'm barking up the wrong
tree.

Thanks!
-Alessandro

On Fri, Feb 17, 2012 at 10:34 AM, Alessandro Gagliardi
<alessandro(at)path(dot)com>wrote:

> CREATE TABLE seen_its (
>   user_id character(24) NOT NULL,
>   moment_id character(24) NOT NULL,
>   created timestamp without time zone,
>   inserted timestamp without time zone DEFAULT now(),
>   CONSTRAINT seen_its_pkey PRIMARY KEY (user_id , moment_id )
> ) WITH ( OIDS=FALSE );
>
> CREATE INDEX seen_its_created_idx  ON seen_its  USING btree  (created );
>
> CREATE INDEX seen_its_user_id_idx  ON seen_its  USING btree  (user_id );
>
>

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2012-02-20 23:30:06
Subject: Re: Indexes and Primary Keys on Rapidly Growing Tables
Previous:From: Alessandro GagliardiDate: 2012-02-20 21:14:37
Subject: Re: Why so slow?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group