Select and Update limits

[cdedreuille]

Hi, 👋 I'm wondering if there is some select or update limits. In my case, I have a table with 22k rows. I'm sending all this data to Algolia at once but it doesn't always work. In another context, using supabase-js I'm querying around 1k rows from a table and it seems that it's too much.

So I'm wondering if there are limits in place and any documentation on it?

[steve-chavez]

Edit: Nowadays the UI has the MAX-ROWS config, which solves this use case.

Hey @cdedreuille,

Right now you can place the limit per query - see limit.

But we're working on exposing a global max-rows that will apply the limits for all endpoints(tables/views/functions). This will come out soon and you'll be able to configure it through the Supabase UI.

[steve-chavez]

I can't update 1000 rows at once

@cdedreuille What error did you get in this case? Also, do you have many indexes on that table?

[cdedreuille]

I made a test trying to query 1038 items from a list of ids.

const list = checked.map((c) => `id.eq.${c}`);

const { data: products, error } = await supabase
  .from('products')
  .select(`id, ${supabaseId}`)
  .or(list.join(','));

Just looking at the request, not sure this is the right approach. I can't really see the error as it says 'Failed to load response data'.

Perhaps there's a better way to query from a list of ids.

[steve-chavez]

@cdedreuille The in operator would be better in that case. Check: https://supabase.io/docs/client/in

Perhaps the error is because the url is too long, in could shorten that.

[cdedreuille]

Thanks. It looks indeed way cleaner. I tried and I have the same issue. I'll try perhaps to separate this into multiple queries.

[steve-chavez]

There's another option. If the in value is too big for a GET request(which from does), you could turn it into a POST with a function:

-- run on SQL editor
create or replace function products_in("bigList" int[]) returns setof products as $$
  select * from products where id = ANY ("bigList");
$$ language sql;

Then you can call it through supabase RPC(uses POST):

const { data: products, error } = await supabase
  .rpc('products_in', { bigList : [1,2,..] })
  .select(`id, name, ..`);

RPC is pretty similar to from, it supports filters, select, order, etc: https://supabase.io/docs/client/rpc#with-filters

Maybe try that and see if it gives an error.