7 ms·

As someone who generally finds "couple million rows? Sqlite is fine" works as a philosophy, what generally should I know here? I get that there are huge timese

As someone who generally finds "couple million rows? Sqlite is fine" works as a philosophy, what generally should I know here? I get that there are huge timeseries based data flows going round the world (transactions, metrics, weather, self driving telemetry) and sticking those into something is useful. I understood the world has settled on "use hdfs to store the data, use apache spark to run over those files and reduce your query data set till it is small enough to fit into pandas and then process" This looks like it is trying to cut out the first two step?

gopalv 7y ago
> what generally should I know here? The "real useful" part was a click further in for me. https://github.com/BTrDB/smartgridstore/blob/master/tools/c37ingress/pmu.go#L204 https://github.com/BTrDB/smartgridstore/blob/master/tools/c3... Ingress endpoints which is usually the more annoying component, rather than the exact storage format. > As someone who generally finds "couple million rows? Sqlite is fine There's still unsolved annoyances with analog timeseries measurements, which aren't easily solved in SQL with a columns + btree index. So combining an airplane altitude sensor at 0.1Hz with a 10Hz fuel sensor, starts giving you trouble in SQL because the question someone might ask is "when the plane was at 31,000 feet, at 0.85 mach, what was the fuel rate", while the plane might have gone from 30,999 to 31,001 feet without ever recording the intermediate point. Indirectly speaking, you can do range-scans for each measurement if you consider time as something that is different from a generic column value in a generic SQL engine (to join rows by temporal proximity). The ASF proposal from IoTDB[1] might make interesting reading on the topic (the frequencies part) or the Boeing talk from XLDB 2018 [2] [1] - https://wiki.apache.org/incubator/IoTDBProposal https://wiki.apache.org/incubator/IoTDBProposal [2] - https://conf.slac.stanford.edu/xldb2018/sites/xldb2018.conf.slac.stanford.edu/files/Tues_15.50%20Ian%20Willson%20XLDB%202018%20Boeing%20IOT.pdf https://conf.slac.stanford.edu/xldb2018/sites/xldb2018.conf....
- SahAssar 7y ago
  > So combining an airplane altitude sensor at 0.1Hz with a 10Hz fuel sensor, starts giving you trouble in SQL because the question someone might ask is "when the plane was at 31,000 feet, at 0.85 mach, what was the fuel rate", while the plane might have gone from 30,999 to 31,001 feet without ever recording the intermediate point. I thought that was part of the problem that postgres range types solves? https://www.postgresql.org/docs/current/rangetypes.html https://www.postgresql.org/docs/current/rangetypes.html
  - dboreham 7y ago
    Type is one thing. Efficient querying (which implies some appropriate index structure) is another.
    - asah 7y ago
      Weak example to ask precisely when the plane is at X feet, not an inch higher/lower - this is more typically a range query, and the Postgres range datatype is quite good at this, notably GiST indexes for overlap queries: https://www.postgresql.org/docs/9.3/functions-range.html#RANGE-OPERATORS-TABLE https://www.postgresql.org/docs/9.3/functions-range.html#RAN... https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-INDEXING https://www.postgresql.org/docs/current/rangetypes.html#RANG...
    - vog 7y ago
      PostgreSQL allows for all of that: User-defined types with user-defined index structures and whatnot. PostgreSQL is great and flexible, yet transaction safe and fast. You should really try it. It is one of the highest-quality FLOSS projects I have ever seen, not just in term of code quality, but also in terms of project and community management.
  - scottlocklin 7y ago
    It is hilarious seeing this problem over and over and over again when it was solved in the APL language family ... some time in the 1960s.
    - i_don_t_know 7y ago
      Could you explain how this is solved in APL?
      - scottlocklin 7y ago
        I did a startup solving this problem (yet again) in an APL; we sucked at enterprise sales, but our data structures went rather faster than BTrDB. The most famous example of solving this problem being Kx systems. Less famous, but basically using the same ideas (these guys all worked with Art and the Iversons): http://marketgridsystems.com/ http://marketgridsystems.com/ And no, I won't explain it in detail. I consider the spark team basically cretins, and it would ruin several of my pals businesses while transferring wealth to hedge funds, exchanges and advertising groups making solutions to this problem available for free. I'll continue to snigger up my sleeve at the fact that decades of open source development still haven't noticed things which were obvious in the 1960s. Edit add: a great example of this kind of thing, if not this exact thing: some poor guy trying to solve a 25tb problem using spark, parquet files ... finally solving it with ... Awk. Old tools written when 1mb was a lot of memory; sed, awk, tr, cut: vastly better than new tools. https://news.ycombinator.com/item?id=20293579 https://news.ycombinator.com/item?id=20293579

gopalv 7y ago

> what generally should I know here? The "real useful" part was a click further in for me. https://github.com/BTrDB/smartgridstore/blob/master/tools/c37ingress/pmu.go#L204 https://github.com/BTrDB/smartgridstore/blob/master/tools/c3... Ingress endpoints which is usually the more annoying component, rather than the exact storage format. > As someone who generally finds "couple million rows? Sqlite is fine There's still unsolved annoyances with analog timeseries measurements, which aren't easily solved in SQL with a columns + btree index. So combining an airplane altitude sensor at 0.1Hz with a 10Hz fuel sensor, starts giving you trouble in SQL because the question someone might ask is "when the plane was at 31,000 feet, at 0.85 mach, what was the fuel rate", while the plane might have gone from 30,999 to 31,001 feet without ever recording the intermediate point. Indirectly speaking, you can do range-scans for each measurement if you consider time as something that is different from a generic column value in a generic SQL engine (to join rows by temporal proximity). The ASF proposal from IoTDB[1] might make interesting reading on the topic (the frequencies part) or the Boeing talk from XLDB 2018 [2] [1] - https://wiki.apache.org/incubator/IoTDBProposal https://wiki.apache.org/incubator/IoTDBProposal [2] - https://conf.slac.stanford.edu/xldb2018/sites/xldb2018.conf.slac.stanford.edu/files/Tues_15.50%20Ian%20Willson%20XLDB%202018%20Boeing%20IOT.pdf https://conf.slac.stanford.edu/xldb2018/sites/xldb2018.conf....

SahAssar 7y ago
> So combining an airplane altitude sensor at 0.1Hz with a 10Hz fuel sensor, starts giving you trouble in SQL because the question someone might ask is "when the plane was at 31,000 feet, at 0.85 mach, what was the fuel rate", while the plane might have gone from 30,999 to 31,001 feet without ever recording the intermediate point. I thought that was part of the problem that postgres range types solves? https://www.postgresql.org/docs/current/rangetypes.html https://www.postgresql.org/docs/current/rangetypes.html
- dboreham 7y ago
  Type is one thing. Efficient querying (which implies some appropriate index structure) is another.
  - asah 7y ago
    Weak example to ask precisely when the plane is at X feet, not an inch higher/lower - this is more typically a range query, and the Postgres range datatype is quite good at this, notably GiST indexes for overlap queries: https://www.postgresql.org/docs/9.3/functions-range.html#RANGE-OPERATORS-TABLE https://www.postgresql.org/docs/9.3/functions-range.html#RAN... https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-INDEXING https://www.postgresql.org/docs/current/rangetypes.html#RANG...
  - vog 7y ago
    PostgreSQL allows for all of that: User-defined types with user-defined index structures and whatnot. PostgreSQL is great and flexible, yet transaction safe and fast. You should really try it. It is one of the highest-quality FLOSS projects I have ever seen, not just in term of code quality, but also in terms of project and community management.
- scottlocklin 7y ago
  It is hilarious seeing this problem over and over and over again when it was solved in the APL language family ... some time in the 1960s.
  - i_don_t_know 7y ago
    Could you explain how this is solved in APL?
    - scottlocklin 7y ago
      I did a startup solving this problem (yet again) in an APL; we sucked at enterprise sales, but our data structures went rather faster than BTrDB. The most famous example of solving this problem being Kx systems. Less famous, but basically using the same ideas (these guys all worked with Art and the Iversons): http://marketgridsystems.com/ http://marketgridsystems.com/ And no, I won't explain it in detail. I consider the spark team basically cretins, and it would ruin several of my pals businesses while transferring wealth to hedge funds, exchanges and advertising groups making solutions to this problem available for free. I'll continue to snigger up my sleeve at the fact that decades of open source development still haven't noticed things which were obvious in the 1960s. Edit add: a great example of this kind of thing, if not this exact thing: some poor guy trying to solve a 25tb problem using spark, parquet files ... finally solving it with ... Awk. Old tools written when 1mb was a lot of memory; sed, awk, tr, cut: vastly better than new tools. https://news.ycombinator.com/item?id=20293579 https://news.ycombinator.com/item?id=20293579