0

I have to rate limit PHP REST API endpoint. Each request is already made with unique client/customer id. I have to be able to add following rules:

  1. rate limit per 5min (last 5 min at any time)
  2. rate limit per hour (last 60 min at any time)
  3. rate limit per week (starting to count at 1st day of week)
  4. rate limit per month (starting to count at 1st day of each month)

Also I have to log each request type and some meta-data. Of course it should work as fast as possible to minimize extra overhead on API endpoint.

In Postgres it would look like this:

INSERT INTO requests (created_at, client_id)
SELECT now(), client_id
FROM clients c
WHERE
  c.id = 123 -- client id is passed inside request
  AND (
     SELECT COUNT(*) 
     FROM requests r
     WHERE r.client_id = c.id
     AND r.created_at > now() - '5 minutes'::interval
  ) > c.limit_per_5min
  AND (
     SELECT COUNT(*) 
     FROM requests r
     WHERE r.client_id = c.id
     AND r.created_at > now() - '1 hour'::interval
  ) > c.limit_per_1hour
  -- and other limits...

But I am not sure if it's a good idea to use Postgres here. I thought also about MongoDB or Redis but I am also not sure if they can handle so many rules (and how?).

Please advice what technology should I use here?

Also I will have to query for example how many requests of some type, were served to customer with id xxx.

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
user606521
  • 14,486
  • 30
  • 113
  • 204
  • 1
    You need this to be fast. Meaning, the solution must unquestionably be in-memory based. Given that you are using PHP where storing data in-process is difficult/impossible, I'd choose redis (spiced with some Lua scripts, perhaps) – Sergio Tulentsev Aug 17 '15 at 11:17
  • @SergioTulentsev - nearly every RDBMS out there is able to store working data set in memory. That makes your comment false. One should use the database or storage layer that they're *comfortable* with (initially). Using Redis or mongoloid doesn't have to make anything faster - it can be quite the contrary. Everyone seem to forget the learning curve these days.. TL;DR: nothing wrong with sticking to Postgres, if you are really comfortable with it. When you are sure that there's a different storage that's really quicker or scales better and when you learn it - consider switching. – N.B. Aug 17 '15 at 11:26
  • @N.B.: throttling is a kind of problem for which you really really really don't want to execute an SQL query. Especially one with 5 sub-selects. Assuming, of course, that performance matters. – Sergio Tulentsev Aug 17 '15 at 11:29
  • @SergioTulentsev - and how will Redis help you perform throttling exactly? My point is that one can write down throttling data to data store of their choice. Sticking Redis in doesn't necessarily make it quicker. Now, I'd argue that I wouldn't perform throttling this way at all, but that's for another discussion. – N.B. Aug 17 '15 at 11:39
  • @N.B.: yes, I omitted the whole design of the system that I have in my head. Too long for a comment. :) But it is generally accepted that redis is exceptionally good at counting stuff, wouldn't you agree? – Sergio Tulentsev Aug 17 '15 at 11:55
  • 1
    Here, rate limiting is even an example in Redis' documentation: http://redis.io/commands/INCR – Sergio Tulentsev Aug 17 '15 at 11:57
  • @SergioTulentsev - I agree that Redis is good at counting stuff. So is Postgres. Or MySQL. Or SQLite. Or SQL Server. Point being: why use a tool that you are not familiar with, if the one you are familiar with is good enough? – N.B. Aug 17 '15 at 12:51
  • It's not a problem for me to use REDIS. I found also this article (for python though) http://www.binpress.com/tutorial/introduction-to-rate-limiting-with-redis/155/. I also think that RDBMS wont be a good choice here, that's why I've created this question. The only thing I can't figure out is how to handle rate limit with "starting from the first day of the week/month" option. – user606521 Aug 17 '15 at 12:56
  • 1
    @user606521: I would store it in separate counters. That is, upon performing a successful api request, following counters in redis would be incremented: `throttling:monthly:$user_id:$month_of_year`, `throttling:weekly:$user_id:$week_of_year`. – Sergio Tulentsev Aug 17 '15 at 13:10

0 Answers0