General peformance advice for batch reading?

Suppose there is a table X and we want to do the following queries

SELECT * FROM X;

or
SELECT * FROM X where some_id in (id1, id2, … idn);

where there can be up to 10,000 IDs.

The rows need not be consistent with each other, they should just be at least as up-to-date as the start of the querying. Nothing else is happening in the transaction. There could be plenty of writes to the table.

Is the best option to

  • Issue this as a single query
  • Issue this with pagination, each one in a different transaction (something with LIMIT/OFFSET in the first case maybe, and listing say 100 IDs at a time in the second case). If so, what sort of size is best?
  • Issue individual queries per ID (though this would only work in the second scenario)
  • Something else

10,000 rows isn’t too terrible, so for now a single query would probably be okay. However if the number of rows is likely to keep growing, I’d avoid using a single query due to the memory pressure it’ll put on loading the result set into both the database node that you’re communicating to, as well as into your application.

The traditional way to do this in most RDBMS’s would be to use a cursor to iteratively batch results and traverse all the rows. Cockroach does not support cursors however, so manual pagination (your second proposal) is likely your best bet.

One note on manual paging; if the table is being modified during your selects (you mention that there could be plenty of writes), there is the possibility of missing or duplicating some rows in your final result set if using OFFSET, as the number of rows above that offset number may increase or decrease between queries. Depending on what you’re doing with the data, this may or may not be an issue, but it’s something to keep in mind.

One way to avoid this would be to start by running the query SELECT now(); to get the current system time, and then issue your paginated queries as Time Travel queries using that timestamp:

> SELECT now();
+---------------------------------+
|              now()              |
+---------------------------------+
| 2017-09-27 05:53:20.04984+00:00 |
+---------------------------------+

> SELECT * FROM X AS OF SYSTEM TIME '2017-09-27 05:53:20.04984+00:00' LIMIT 200 OFFSET 0;
...

> SELECT * FROM X AS OF SYSTEM TIME '2017-09-27 05:53:20.04984+00:00' LIMIT 200 OFFSET 200;
...

This will ensure that all of your paginated queries are iterating over the same snapshot of the database at that time, so offsets shouldn’t be shuffling around under your feet and every row that was present at that time should be seen once and exactly once.

Thanks. I’m not too familiar with the Cockroach consistency model/implementation yet, are there concerns about this potentially being aborted or aborting other transactions? If so, does pagination reduce that in any way (maybe it needs to do somewhat less work per transaction or something?)? Most of the writes would be to existing rows though there could indeed be new rows added.

In general, the Cockroach consistency model has the possibility of having a transaction be aborted and needing to be retried by the client. For most languages, you can find sample code/libraries for how to implement this retry logic in the build and app section of the docs.

With that said, for this particular operation, you should not see your transactions being aborted. That’s because of a few reasons.

  1. They are individual statements, which falls into the category of transactions that should be automatically retried by Cockroach.
  2. If implemented using Time Travel queries as described above, the reads are happening on “historical” data, which should have no risk of having a read/write conflict.

Depending on how your write operations are implemented and how much data contention there is, you’re much more likely to require client-side intervention when writing the data than when doing the bulk read as described here :slight_smile:

Question on historical queries, does it matter if you use now() or
something older than now ()? My impression was that there was a max clock
skew and if now () is within that clock skew it could still be
aborted/retried. And something about long running transactions as well
could force a retry?

Also so even with non historical queries, this wouldn’t abort other
transactions?

For time travel queries you can use any timestamp within the configured garbage collection window (by default 24 hours). With that said, the clock skew shouldn’t be an issue for this type of query, because any clock skew should already be elapsed by the time the first SELECT now(); query responds, and the returned value should be considered strictly in the past when used in later queries. So the value returned should be fine to use in the subsequent pagination.

WRT impacting other transactions if not using time travel queries, I believe that the reads would be aborted (and then automatically retried) rather than any writes that they are conflicting with (However I’d like a Cockroach dev to confirm that).

If there is so much contention that the reads are frequently retrying, you could reduce the transaction isolation level to SNAPSHOT, which should avoid retries during read/write conflicts.

If you’re really concerned about the now() being too recent, you can also subtract some small amount of time from it when selecting it using INTERVAL:

> SELECT (now() - INTERVAL '10s');
+----------------------------------+
|         (now() - '10s')          |
+----------------------------------+
| 2017-09-27 16:37:30.744345+00:00 |
+----------------------------------+

In CockroachDB 1.0, the server holds the entire results of your query in memory at once, so you need to use a LIMIT when scanning the whole table to avoid running out of memory. OFFSET is very bad for performance (in nearly all databases), so when doing a full table scan via repeated LIMIT queries, you should instead use ORDER BY to sort the results by the primary key, then use a WHERE clause in the next query to select results that come after the ones you’ve already seen:

SELECT * FROM my_table ORDER BY id LIMIT 1000000;
...
...
123456789, asdf,... (last record)
SELECT * FROM my_table WHERE id > 123456789

In CockroachDB 1.1, the server can serve streaming results to the client, so as long as your client can handle this you can simply do SELECT * FROM my_table.

For queries like SELECT * FROM X WHERE some_id IN (...), it’s best to do this in a single query, at least up to fairly large lists of IDs. I think 10k ids is probably fine, but I wouldn’t go higher than that. If you do split up a query like this, it’s best to sort the IDs first, so the first query has the 10k smallest IDs, etc.

In both cases, you can get a consistent view of the data across multiple queries by picking a timestamp and using the same time in an AS OF SYSTEM TIME clause for each query. Reads can conflict with concurrent writes; if you don’t need the latest data, choosing a timestamp a short time in the past can help. It’s best to pick a timestamp that is older than your longest-running write transaction (if you can figure that out)

For read-only transactions, changing the transaction isolation to SNAPSHOT doesn’t do anything. SNAPSHOT isolation helps read/write transactions that encounter concurrent reads.