Re: match an IP address - Mailing list pgsql-general

From Marcus Engene
Subject Re: match an IP address
Date
Msg-id 48D8D6A2.7000606@engene.se
Whole thread Raw
In response to Re: match an IP address  ("Phoenix Kiula" <phoenix.kiula@gmail.com>)
List pgsql-general
Phoenix Kiula wrote:
>>  If you don't want to store IPs for registered users, I'd use:
>>
>>  user_id INTEGER,
>>  ip cidr,
>>  CONSTRAINT must_have_userstamp
>>  CHECK ( user_id IS NOT NULL OR ip IS NOT NULL)
>>
>>  ... and yes, I'd use a functional index to look it up, or even a
>>  trigger-maintained cache of the text representation if I had to. Then
>>
>
>
> Ok, this is an idea. And I do agree that multifunction fields are a
> potential pain in the distant future.
>
> My questions:
>
> 1. What extra tax will this constraint levy on an INSERT or UPDATE on
> this table? There are about 100,000 inserts a day, and over three
> times as many UPDATES. The concurrency is pretty high -- I mean
> sometimes 1,000 users at the same time but no more than that. If the
> additional cost of insertion/updating is not too heavy, I suppose this
> could be a nice approach.
>
> 2. Why not have an INET field...why a CIDR? What's the benefit? It
> stores those pesky ".../8" type additional data which one has to mask
> with functions. Would INET work just as well?
>
> 3. Storage wise does this add significantly? How much space does an
> INET field take as opposed to, say, a VARCHAR field?
>
> 4. Most importantly, how would you structure the index for this? I
> would much rather have a fast "=" in my sql's WHERE clause. No "OR"
> etc. Any thoughts?
>
> Thanks
>
>
Use the best of two worlds - consider memcached and use the db only when
you create/update an entry so that you can restore it if memcached
(perhaps as a consequence of a server reboot) gets restarted.
http://www.slideshare.net/vishnu/livejournals-backend-a-history-of-scaling

best regards,
Marcus


pgsql-general by date:

Previous
From: Tino Wildenhain
Date:
Subject: Re: match an IP address
Next
From: Craig Ringer
Date:
Subject: Re: match an IP address