Thread: How to store clickmap points?

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









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

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
possiblesavings. 

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.

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

> 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.

> 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.

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.