SQL Server offers a pagination syntax like so:

SELECT col1, col2, ...
 FROM ...
 WHERE ... 
 ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

pagination is generally less useful when you have the data in your peer already anyway

(take 10 result) 🙂

oh i see then, it's like sorting

but what people usually do when they have queries that return too much data? it would stay in memory for a moment because you're paginating after the query execution

that's generally difficult to avoid. That would have to happen on a database server too

you're right. the first thing i can think of would be to perform a query that retrieves the ids, then i'd paginate the ids and use them on a second query, that would retrieve the expected result using something like min max

the only optimization I could think of is if you wanted to paginate without sorting, i.e. in whatever order the peer walks the index

maybe just retrieving the IDs could work, yeah. Unless they're just stored in the same chunks as most of the data anyway

my mental model of the organization of data in the cunks is lacking, so not sure if doing ids first would help...

i'm not sure, either. because if the pagination depends on some sort of ordering this resolution model wouldn't be that great, in the first query i'd have to retrieve the ids and the other attributes required for the sorting before the pagination

the attribuets you sort on are probably indexed I suppose? Then I would assume that the data are mostly in the same chunks in avet anyway

or, wait. Does each index have its own set of chunks? Then at least you'd only have the indexed attributes in avet. I think?

heh

avet only contains the actual data that is indexed, I suppose? And if you need additional facts, you'd need to look it up in eavt or elsewhere

"you" being the query engine

oh, i didn't know there was indexes in Datomic, just checked the docs and there's a page on that, I guess I'll have to study xD

you typically want to tag attributes you query for as indexed

oh, i will keep that in mind

doing so would affect the query results so that ordering would be done over the tagged attribute?

reading the docs

sort of kind of

the indices started making sense to me after I started to play with the "datoms" API, that gives you direct access to the indices

i.e. what the query engine ends up doing

each index is a nested sorted set of data, nested differently. Or something like that. So "eavt" lets you input an e (entity id ) and get all attributes for that entity. Then you add an a and you get all values for that attribute. Then you walk those to find the attribute closest to the T of the db you're accessing

and avet is just a different pattern, and only indexed values are stored there. So you add an attribute and you get all values for that attribute. And so on

thanks for the help @augustl, that shed me a light

it seems that the datoms API might be of some one, but I will have to experiment with it first