[Sqlgrey-users] Congratulations & PostgreSQL issue

[Jean-Michel P. - G. <jm...@go...>]

Dear Lionel,

First, I would like to congratulate you for bringing SqlGrey to the
community. I just discovered SqlGrey and it solved my SPAM problems.

Together with SPF validation, DNSBL and other filtering techniques, I
now have nearly zero spam.

I am running Debian and I am a big fan of PostgreSQL. The automatic
population of the database and the SQL code using prepared queries was
a very nice surprise. 

Previously, I tested other grey listing solutions and they don't have
the simplicity and power of SqlGrey. I am surprised to see that some
SqlGrey databases have been running years without incident.

There might be modifications needed to stand "huge" loads under
PostgreSQL. For example, I think of as much as 10.000 queries per
second. This does not happen very often, I agree.

First, I think it should be interesting to :

* Use special the inet type for IPs and subnets
http://www.postgresql.org/docs/current/static/datatype-net-types.html

I believe that using the inet type should be compatible with current
code in a fresh database.

SELECT '216.34.181'::cidr =>  "216.34.181.0/24"
SELECT '216.34.181.6'::inet => "216.34.181.6"

* IP4r type could also be used from managing ranges of servers and
spammers.IP4r reported 6000 queries per second on complex projects:
https://www.siafoo.net/article/53

* Add indexes on domain_awl and from_awl tables. The advantage of CIDR
is that it will be very well indexed.

Indexes allow PostgreSQL planner to make clean search plans, mainly on
indexes and reducing disc access to what should be zero.

Just my 2 cents!
Kind regards,
Jean-Michel