5 ms·

> Because of this dedicated data warehouses…use column-oriented storage and don't have indexes. Well, that’s not really correct is it. ClickHouse for one defin

> Because of this dedicated data warehouses…use column-oriented storage and don't have indexes. Well, that’s not really correct is it. ClickHouse for one definitely has them as Snowflake the last time I used it. This is a lot of work to go through to avoid using the right tool for the job. Just use something like ClickHouse or even DuckDB and reap the benefits of better performance with less caveats.

simonw 5y ago
I had the opposite reaction: running analytics against the database you are already using feels like a lot less work to me than adopting an additional tool and solving the problem of synchronizing your data to it.
- mumblemumble 5y ago
  If the database stays small and the load stays low, sure. But, as things grow, you tend to run into problems with analytical loads adversely impacting, and even knocking over, the production system by locking resources and causing timeouts. Especially if you're allowing analysts to run ad-hoc analytical queries. Long story short, yes, resiliency is expensive, but it's not always more expensive than not having resiliency.
  - Footkerchief 5y ago
    Read replicas can take the brunt and have widespread availability in Postgres cloud providers.
  - marcosdumay 5y ago
    I do order the solution in "you should choose the simplest one that doesn't disrupt your environment" as: Use a single database for transational and analytical workloads. Replicate your transational database as is for analytical workloads. Remodel your data and replicate to the exact same technology stack. Remodel your data and replicate into specialized tools for analytics. I have never seen anybody that actually needs the last one. But the largest environments I've looked are government databases with a few thousands of people working on (there are bigger envs out there).
- FridgeSeal 5y ago
  The blog post itself makes a point of running this on a dedicated instance, so you’re already shipping data somewhere, and it’s not that unreasonable to assume that pretty quickly you’re going to need/want to start performing transforms on the data to get it into a more appropriate schema for analytic queries. Synchronising the data can be done in stages as well. Daily loads are pretty straightforward and usually small/straightforward enough to get going quickly and maintain. As your needs increase you up the frequency or start employing more sophisticated methods.
kwillets 5y ago
Snowflake tries to auto-sort its containers to fit the query pattern; most of these platforms use at least one data ordering. Vertica allows multiple projections on a table with different orderings and distribution keys. On top of that BRIN (block range) indexes are usually used to capture the value of sorting by pruning I/O. I don't see these mentioned here -- they seem like a good open-source version of this idea.
- zX41ZdbW 5y ago
  ClickHouse has support for block range indexes as well. If you want to check open-source implementations - this is probably the best place to do it.
tomnipotent 5y ago
It's correct. Snowflake does not have indexes, and ClickHouse indexes are what they call "data skipping indexes". BigQuery, Redshift, Netezza, and Vertica also do not have support for indexes.
- FridgeSeal 5y ago
  > ClickHouse indexes are what they call "data skipping indexes". That’s still an index though isn’t it? Might work slightly differently, but the purpose is still the same.
  - tomnipotent 5y ago
    Not in the same sense we consider them in traditional databases, used to find specific rows (needle in haystack). ClickHouse indexes are used to eliminate data pages to reduce IO for range queries, not to find specific rows by value.
    - tremon 5y ago
      Row-store indexes (indices?) are used for both. The first few levels of a b-tree index can be used for discarding/narrowing ranges, the leaf levels are for pinpointing a specific row. What I think you meant to say was that Snowflake does not offer user-manageable indexes, not that they don't use indexes at all.
- zX41ZdbW 5y ago
  ClickHouse has both variants: primary and seconday indexes. Primary key is range index - to quickly locate records. Secondary indexes are data skipping indexes - to quickly skip blocks of data.
hodgesrm 5y ago
In addition to other example cited here, Druid has bitmap indexes on dimension columns. So, it's hard to make a hard and fast generalization. All databases use indexes to a greater or lesser extent. To my mind the big differences for data warehouses are the following: (a) Table scans are relatively cheap thanks to columnar structure and compression. It's much more important to tune compression than indexes. If you can reduce stored data size by 10^3, you don't need an index. That's exactly the opposite of row stores like MySQL. (b) Data warehouses don't use indexes to maintain referential integrity, because it's not something they really care about in the first place. My DW experience is with ClickHouse, but I think it illustrates a lot of the principles.

simonw 5y ago

I had the opposite reaction: running analytics against the database you are already using feels like a lot less work to me than adopting an additional tool and solving the problem of synchronizing your data to it.

mumblemumble 5y ago
If the database stays small and the load stays low, sure. But, as things grow, you tend to run into problems with analytical loads adversely impacting, and even knocking over, the production system by locking resources and causing timeouts. Especially if you're allowing analysts to run ad-hoc analytical queries. Long story short, yes, resiliency is expensive, but it's not always more expensive than not having resiliency.
- Footkerchief 5y ago
  Read replicas can take the brunt and have widespread availability in Postgres cloud providers.
- marcosdumay 5y ago
  I do order the solution in "you should choose the simplest one that doesn't disrupt your environment" as: Use a single database for transational and analytical workloads. Replicate your transational database as is for analytical workloads. Remodel your data and replicate to the exact same technology stack. Remodel your data and replicate into specialized tools for analytics. I have never seen anybody that actually needs the last one. But the largest environments I've looked are government databases with a few thousands of people working on (there are bigger envs out there).
FridgeSeal 5y ago
The blog post itself makes a point of running this on a dedicated instance, so you’re already shipping data somewhere, and it’s not that unreasonable to assume that pretty quickly you’re going to need/want to start performing transforms on the data to get it into a more appropriate schema for analytic queries. Synchronising the data can be done in stages as well. Daily loads are pretty straightforward and usually small/straightforward enough to get going quickly and maintain. As your needs increase you up the frequency or start employing more sophisticated methods.

kwillets 5y ago

Snowflake tries to auto-sort its containers to fit the query pattern; most of these platforms use at least one data ordering. Vertica allows multiple projections on a table with different orderings and distribution keys. On top of that BRIN (block range) indexes are usually used to capture the value of sorting by pruning I/O. I don't see these mentioned here -- they seem like a good open-source version of this idea.

zX41ZdbW 5y ago
ClickHouse has support for block range indexes as well. If you want to check open-source implementations - this is probably the best place to do it.

tomnipotent 5y ago

It's correct. Snowflake does not have indexes, and ClickHouse indexes are what they call "data skipping indexes". BigQuery, Redshift, Netezza, and Vertica also do not have support for indexes.

FridgeSeal 5y ago
> ClickHouse indexes are what they call "data skipping indexes". That’s still an index though isn’t it? Might work slightly differently, but the purpose is still the same.
- tomnipotent 5y ago
  Not in the same sense we consider them in traditional databases, used to find specific rows (needle in haystack). ClickHouse indexes are used to eliminate data pages to reduce IO for range queries, not to find specific rows by value.
  - tremon 5y ago
    Row-store indexes (indices?) are used for both. The first few levels of a b-tree index can be used for discarding/narrowing ranges, the leaf levels are for pinpointing a specific row. What I think you meant to say was that Snowflake does not offer user-manageable indexes, not that they don't use indexes at all.
zX41ZdbW 5y ago
ClickHouse has both variants: primary and seconday indexes. Primary key is range index - to quickly locate records. Secondary indexes are data skipping indexes - to quickly skip blocks of data.

hodgesrm 5y ago

In addition to other example cited here, Druid has bitmap indexes on dimension columns. So, it's hard to make a hard and fast generalization. All databases use indexes to a greater or lesser extent. To my mind the big differences for data warehouses are the following: (a) Table scans are relatively cheap thanks to columnar structure and compression. It's much more important to tune compression than indexes. If you can reduce stored data size by 10^3, you don't need an index. That's exactly the opposite of row stores like MySQL. (b) Data warehouses don't use indexes to maintain referential integrity, because it's not something they really care about in the first place. My DW experience is with ClickHouse, but I think it illustrates a lot of the principles.