search

CartoDB / CartoDB-SQL-API

CartoDB SQL API
BSD 3-Clause "New" or "Revised" License
62 stars 63 forks source link

limit max export filesize #213

Open javisantana opened 9 years ago

javisantana commented 9 years ago

a 4xx should be raised make configurable per user

rochoa commented 9 years ago

Is there an easy way to estimate export size from a given SQL query?

Another one: this is a problem for formats we are currently streaming. If we can estimate the size before doing the real query it's not a problem.

javisantana commented 9 years ago

we could trust in the planer and the stats tables cc @pramsey

pramsey commented 9 years ago

Sure can, as long as the stats are gathered. For example, my table of 18000 watersheds…

explain select * from sheds;

                          QUERY PLAN                           

 Seq Scan on sheds  (cost=0.00..8001.81 rows=18481 width=8961) (1 row)

Oh, so 18481 rows at 8961 bytes each?

select 18481 * 8961;

 ?column?  

 165608241

And final answer?

select sum(st_memsize(geom)) from sheds;

    sum    

 162988032 (1 row)

Not bad! Stats are our friend, the database already knows the answers...

rochoa commented 9 years ago

:heart: lovely!

pramsey commented 9 years ago

One place this would fail would be if you’re dramatically changing the size of the on-disk object  before sending it back to the client, so if you’ve got geom wrapped in ST_Simplify() then the estimate is not going to notice that. However, the estimate will “correctly” (within the bounds of stats knowledge) for things like filters (spatial and non-spatial) and joins.