[GOT-50] Increase gateway geographical footprint

[dtbuchholz]

Users in Asia, particularly in China, have noted a slow gateway response time, up to 1.2 seconds round trip time for certain queries. We're still digging into the issue and requesting additional information on the queries used. Regardless, it'd likely be beneficial to consider adding additional gateways that are closer to Asian countries as a way to reduce the read response time.

This will likely require a project that incorporates some tests to know if any other Google Cloud regions will alleviate this UX issue. It would also have a downstream impact on the SDK and CLI, which would need to allow secondary gateways to be set. Note the CLI does not offer this feature but has an open issue for custom base URLs: https://github.com/tablelandnetwork/js-tableland-cli/issues/152

_GOT-50

[dtbuchholz]

For context, here is a query being used in the 1.2 sec scenario:

SELECT year||'.'||month as date,avg(eth) as eth_avg,avg(usd) as usd_avg,min(eth) as eth_min,min(usd) as usd_min FROM netvrk_trade_137_71 group by date order by date desc limit 1;

And a screenshot showing the avg response time at the bottom.

[jsign]

@dtbuchholz, thanks for the details. Testing that query directly in the gateway, it gets resolved fast.

This is just to confirm that ~1.2s duration isn't related to the query itself, but (presumably) geolocation.

[jsign]

@dtbuchholz, an offtopic comment if this wasn't apparent when seeing the result of the query. That table has this schema:

CREATE TABLE netvrk_trade_137_71 (id INTEGER PRIMARY KEY, token_id TEXT, buyer TEXT, seller TEXT, eth REAL DEFAULT 0, usd REAL DEFAULT 0, year TEXT, month TEXT, day TEXT, time TEXT, week TEXT, quarter TEXT, tx_time TEXT, CONSTRAINT uniq_trade UNIQUE (token_id, buyer, seller, tx_time)) STRICT

That means that it has floating point values that were deprecated at the protocol level, so that exact same query in the new testnet validator won't return results since that table is invalid.

cc @carsonfarmer, @brunocalza

[dtbuchholz]

Ahh, thanks for the heads up. At least we know the table owner here. Will let them know.