How to store clickmap points?

On Jan 8, 2013, at 2:12 AM, aasat wrote:

Hi,

I want to store clickmap points (X, Y and hits value) for website

I currently have table like this

CREATE TABLE clickmap (
page_id integer,
date date,
x smallint,
y smallint,
hits integer
)

But this generated about 1M rows per day.

Can Postgres have better method to store this data? I also have the
possibility to update hits value for point

Instead of storing x/y, have you considered referencing a region of pixels? The bigger the region, the larger your possible savings.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

aasat wrote

Hi,

I want to store clickmap points (X, Y and hits value) for website

I currently have table like this

CREATE TABLE clickmap (
page_id integer,
date date,
x smallint,
y smallint,
hits integer
)

But this generated about 1M rows per day.

Can Postgres have better method to store this data? I also have the
possibility to update hits value for point

Generally questions like this require that some knowledge of how the
resultant data is going to be used in order to make appropriate design
trade-offs. I would also question why you wouldn't just store the actual
timestamp value as opposed to just storing the date.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121p5739449.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2013-01-08, aasat <satriani@veranet.pl> wrote:

Hi,

I want to store clickmap points (X, Y and hits value) for website

I currently have table like this

CREATE TABLE clickmap (
page_id integer,
date date,
x smallint,
y smallint,
hits integer
)

But this generated about 1M rows per day.

Can Postgres have better method to store this data? I also have the
possibility to update hits value for point

convert it into a heatmap at the end of each day.

--
⚂⚃ 100% natural

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

convert it into a heatmap at the end of each day.

How to do it with Postgresql?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121p5740076.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Instead of storing x/y, have you considered referencing a region of pixels?

The bigger the region, the larger your possible savings.

Good idea, but I don't always have all points and regions will not be fully
filled

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121p5740079.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Whatever you did to get 1 million points a day on your site.. I want in..
the name of your marketer please!

I agree with condensing this into a heatmap or a set of RRDs.. one for X..
one for Y.. one for x * (y * max_height)). You can easily query RRDs later
on.. even multiple RRD files at once.

Also.. don't be afraid of flat files. They compress well.

- Shane

On Mon, Jan 14, 2013 at 5:29 AM, aasat <satriani@veranet.pl> wrote:

I finally store points in structure with arrays, and pack it once at day.

create type t_point as (
x smallint,
y smallint,
hits integer
);

CREATE TABLE clickmap (
page_id integer,
date date,
points t_point[]
);

This method save 6x more space than previous

Thanks for all!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121p5740725.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general