Обсуждение: 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.
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:
> Instead of storing x/y, have you considered referencing a region of pixels?Good idea, but I don't always have all points and regions will not be fully
The bigger the region, the larger your possible savings.
filled
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121p5740079.htmlSent 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
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.