Earlier quoted context omitted.
Came here to post this, fantastic paper and probably the most comprehensive thing on the internet available about this Something else I've taken away from research about columnar and vectorized databases: there doesn't seem a good reason why they aren't fit for OLTP workloads Analytics from SaaS/line-of-business apps shows about a 90/10 read/write ratio for CRUD apps. Pavlo et al. have a solid paper on an HTAP databa…
For scan heavy apps columnstores will be much much faster. For simple CRUD apps that just want to read/write a few rows at a time (with high concurrency) they have a number of inefficiencies vs a rowstore. - Columnstore often don't support indexing at all (or have weak support for it). This means a scan is needed to find any row (with min/max or segment elimination to avoid opening up files with no matching rows at a…
Let me ask this: if you have something like a GraphQL API, which often does sparse column selection from multiple different tables, would that also be a good fit for columnar database?
In most GraphQL queries, you're grabbing a portion of the fields from one or more tables, IE something like:
query JoesCompletedTodos {
users(where: { name: { _eq: "Joe" } }) {
id
name
todos(where: { completed: { _eq: true } }) {
text
}
}
}
SELECT users.id, users.name, json_arrayagg(todos) FROM users
INNER JOIN todos ON todos.user_id = users.id
WHERE users.name = 'Joe' AND todos.completed = true
GROUP BY users.id, users.name
Curious how well a columnar DB would do with something like this?
Also, on this point:
> "Most columnstores don't use compression schemes that are incremental. To grab a single row the columnstore likely decompresses many adjacent rows (could be millions of rows - depends on the particular columnstore)."