Re: Bit count - Mailing list pgsql-novice

On Thu, 2013-08-22 at 19:26 +0300, pablo platt wrote:

> I'll use a bit varying field with unlimited length.
>
> To record unique visitors per day, I'll flip the bit corresponding to
> a user's id.
>
> Each event type will have a separate record.
> It is possible to get useful information using union(&) and
> intersection(|) of several fields.
>
> A field for 1 M users will required 1M bits = 125KB.

Even though you're modifying only a single bit at a time, every update
will consume significant overhead. It will also be fairly hard to query
and require procedural functions like you have.

One thing I would consider is using a "raw events" table for the
incoming data, and then periodically summarize it into another table and
delete the raw data after you summarize it.

Something like:

   create table event_raw(ts timestamptz, user_id integer);
   create table event_summary(day date, unique_users bigint);

And after a day has passed do:

   insert into event_summary(day, unique_users)
      select day, count(*)
      from
         (select distinct ts::date as day, user_id
          from event_raw) r
      where day < current_date
      group by day;
   delete from event_raw where ts::date < current_date;

That will make it easier to query. Remember that if you need to include
the current data in a report, you need to do a UNION (and probably make
a view so that it's easier).

If you want to be a little more efficient, you can use a common table
expression to do the delete and insert in one step:

   with d as (
      delete from event_raw where ts::date < current_date
         returning ts, user_id
   )
   insert into event_summary(day, unique_users)
      select day, count(*)
      from
         (select distinct ts::date as day, user_id from d) r
      where day < current_date
      group by day;

Also, if you need to do hourly summaries instead of daily, then use
date_trunc() rather than just casting to date.

I hope this helps,
        Jeff Davis