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@path.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 );