Maximum length of an SQL Query

Question

SELECT f.* 
FROM feeds f, 
     user_feeds uf 
WHERE (f.id=uf.feed_id and uf.user_id in (1,2,5,6,23,45)) 
ORDER BY created_at DESC

This is a query used to construct a user's feeds. The issue I have with this query is that the "uf.user_id in ()" increases as the number of users the user follows increases.

What is the allowed max length of an SQL query? Is there a better way to implement the above query?

Note: I am using ActiveRecord and Postgres.

Answer 1

The maximum length of a query that PostgreSQL can process is 2147483648 characters (signed 4-byte integer; see src/include/lib/stringinfo.h).

Answer 2

To avoid the query size, you could replace the IN (1, 2) with IN (select followed_id from following where follower_id = ?) or whatever the appropriate query would be to find the ids of the followed users from the follower's id.

Answer 3

While there is no (real) limit on the length of the query string, there is a limit on the number of IN (x,y,z...) clauses: 10000, configurable in the postgres.ini-file:

See: http://grokbase.com/t/postgresql/pgsql-general/061mc7pxg6/what-is-the-maximum-length-of-an-in-a-b-c-d-list-in-postgresql :

"In 7.4 and earlier it depends on the max_expr_depth setting." ... "In 8.0 and later max_expr_depth is gone and the limit depends on max_stack_depth."