Great question. Each new table is given a __row_id column which ScratchDB populates and that is the default primary key.
Then, as data is added, I look at the cardinality of each column and set an ORDER BY appropriately. The goal (implementation in progress, not launched yet) is for ScratchDB to do this automatically - we should be able to automatically look at both the data in your table and queries and analytically come up with an appropriate sorting key.
This sounds like a very interesting problem. How costly is it to modify order by after data has been loaded into the table? It sounds expensive because it should reorder the data after changing order by.
It is not a cheap operation, I basically create a new table and then "INSERT INTO SELECT..." Then again, it isn't something you typically need to do a lot.
