Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The data rules worth $40k a day

tinybird.co

Post reply on HN
Print view View original

61–70 of 72 posts

Re: The data rules worth $40k a day

#61

Post by ericd »

xhrpost wrote: post #43
ericd wrote: post #8

Earlier quoted context omitted.

Seriously? A $10k dedicated server would have a payback period measured in minutes.

I don't think it's always that easy. I work on some large databases (1TB+) where even queries utilizing indexed columns still take prohibitively long to run. Sure we could look into partitioning (and we likely might) but then more work is progressively falling on engineering to keep the system running for the standards of analysts but not necessarily the product. BigQuery in these cases has been very useful. Everythi…

Sounds like you need a better index :-)

If you need fulltext search, I've enjoyed using Sphinx and Lucene in the past. Is the column you want to do fulltext on 1 TB? 1 TB of RAM in 128 gig DIMMs is <$10k these days, so might as well get at least that much if you're running anything like the bills these guys are talking about.

Re: The data rules worth $40k a day

#62

Post by RileyJames »

didgetmaster wrote: post #56
paulmd wrote: post #37

Earlier quoted context omitted.

Yes. Fundamentally there is almost no data that is not relational in some aspect. Any given document, you can virtually instantly start picking out "ok that could be a foreign key...", and if one does not exist, it certainly will before too many sprints go by. And most usages of NoSQL are essentially equivalent to a row within a table, especially given many NoSQL solutions have bugs or performance issues with deeply…

Storing Json data into a highly structured RDBMS table can be problematic if any document contains arrays or nested documents. I built a new general-purpose data management system that uses key-value stores that I invented to attach meta-data tags to objects. These key-value stores can also be used to create relational tables. Because each table is basically a columnar store, I can map multiple values to each row key…

How different is this from using pg’s jsonb field type? Which is also queryable.

What are the advantages/disadvantages? Or what am I misunderstanding?

Re: The data rules worth $40k a day

#63

Post by cultofmetatron »

leeoniya wrote: post #14
kiernanmcgowan wrote: post #7

Materialized Views are damn near magic for solving issues involving slow queries on for tools that don't need real time results (eg daily reporting). They essentially act as a cache of a query at a given point in time that you're able to refresh whenever you want. https://www.postgresql.org/docs/current/rules-materializedvi...

> It’s only significant weakness now is in Materialized views, with their lack of incremental refresh. > That work towards incrementally updated views is happening and progressing. For now, it's a separate extension, though: https://github.com/sraoss/pg_ivm . https://news.ycombinator.com/item?id=32098603

I just sent this to our devops guy with the headline "INVESTIGATE FEASABILITY, I WANT THIS ASAP"

Re: The data rules worth $40k a day

#64

Post by didgetmaster »

RileyJames wrote: post #62
didgetmaster wrote: post #56

Earlier quoted context omitted.

Storing Json data into a highly structured RDBMS table can be problematic if any document contains arrays or nested documents. I built a new general-purpose data management system that uses key-value stores that I invented to attach meta-data tags to objects. These key-value stores can also be used to create relational tables. Because each table is basically a columnar store, I can map multiple values to each row key…

How different is this from using pg’s jsonb field type? Which is also queryable. What are the advantages/disadvantages? Or what am I misunderstanding?

To be honest, I haven't played around with the jsonb feature of pg enough to know which is better. I do know that the queries of data in my system average about 10x faster than regular pg tables for the same data set. Also my tables do not need a separate indexing step in order to achieve maximum speed for any query. Do you have a data set in pg you created using jsonb? If you want to try my system, the beta is available for free download at: https://didgets.com/download

Re: The data rules worth $40k a day

#65

Post by paulmd »

didgetmaster wrote: post #56
paulmd wrote: post #37

Earlier quoted context omitted.

Yes. Fundamentally there is almost no data that is not relational in some aspect. Any given document, you can virtually instantly start picking out "ok that could be a foreign key...", and if one does not exist, it certainly will before too many sprints go by. And most usages of NoSQL are essentially equivalent to a row within a table, especially given many NoSQL solutions have bugs or performance issues with deeply…

Storing Json data into a highly structured RDBMS table can be problematic if any document contains arrays or nested documents. I built a new general-purpose data management system that uses key-value stores that I invented to attach meta-data tags to objects. These key-value stores can also be used to create relational tables. Because each table is basically a columnar store, I can map multiple values to each row key…

what I'm saying is, use the relational DB for OLTP, but export in a JSON document format to NoSQL in whatever document shapes are efficient for various services. And that can be multiple different shapes generated from the same set of relational "ground truth", if various services need different "views" to run efficiently.

The idea is you always have a relational "source of truth" and optimize that for OLTP, but also get the scalability benefits of documents/microservices/etc by having data already pre-coalesced/pre-digested into your correct format(s), so you're not doing complex analytical/window/aggregation queries on the RDBMS for every request. You run the analytical queries once, convert the result to json, and store that in the NoSQL.

Of course you still potentially have some "sync time" between the OLTP and the final commit to all the various nosql collections... unless you hold OLTP locks until everything is synced, which would be excessive. But this goes back to CAP and there's no magic wand for that - you can either put everything inside the RDBMS and take the performance hit, or you can have external nosql read replicas and accept the inconsistency due to the sync time, or you can hold locks until both systems are consistent at the cost of "availability" (updatability).

Re: The data rules worth $40k a day

#66

Post by WesleyJohnson »

faxmeyourcode wrote: post #54
WesleyJohnson wrote: post #41

Earlier quoted context omitted.

One of the many reasons I wish we hadn't chosen MySQL. Our internal dashboard has loads of "reports" that are basically tabular displays of queried data with optional filtering and sorting on every column, plus pagination. It's near impossible to make any of them performant due to not being able to optimize for a specific use case; because of the myriad of combinations of filters and sorts different users might apply…

You should schedule a "create or replace table" statement every N hours/days/etc. It will be effectively just the same as a scheduled materialized view refresh.

I've been reading up on this as a potential solution; I may have to finally take it seriously and try it out.

Re: The data rules worth $40k a day

#67

Post by johnthescott »

WesleyJohnson wrote: post #41
johnthescott wrote: post #17

Earlier quoted context omitted.

relational databases do best with facts. a proper materialized view is just another fact. in my experience materialized views are critical for most large databases.

One of the many reasons I wish we hadn't chosen MySQL. Our internal dashboard has loads of "reports" that are basically tabular displays of queried data with optional filtering and sorting on every column, plus pagination. It's near impossible to make any of them performant due to not being able to optimize for a specific use case; because of the myriad of combinations of filters and sorts different users might apply…

is caching the correct analogy?

the stars you see died eons ago. special relativity is your friend. we witness "immutability" casually.

nothing in the relational model that i am aware of says that a materialized view cannot be a simple immutable table of "facts". same input same output. unambiguous.

simply use the dynamic queries to summarize the "facts" in, typically, a small mat view tables and your gui will never lie.

https://www.youtube.com/watch?v=-6BsiVyC1kM

Re: The data rules worth $40k a day

#68

Post by [deleted] »

WesleyJohnson wrote: post #66
faxmeyourcode wrote: post #54

Earlier quoted context omitted.

You should schedule a "create or replace table" statement every N hours/days/etc. It will be effectively just the same as a scheduled materialized view refresh.

I've been reading up on this as a potential solution; I may have to finally take it seriously and try it out.

[deleted]

Re: The data rules worth $40k a day

#69

Post by johnthescott »

faxmeyourcode wrote: post #54
WesleyJohnson wrote: post #41

Earlier quoted context omitted.

One of the many reasons I wish we hadn't chosen MySQL. Our internal dashboard has loads of "reports" that are basically tabular displays of queried data with optional filtering and sorting on every column, plus pagination. It's near impossible to make any of them performant due to not being able to optimize for a specific use case; because of the myriad of combinations of filters and sorts different users might apply…

You should schedule a "create or replace table" statement every N hours/days/etc. It will be effectively just the same as a scheduled materialized view refresh.

seriously, does google "query" all the documents that you might be interested in? no, i argue google makes mat view(s) of your interests by querying the response database and specifically NOT the actual documents.

remember, google only needs to be "kinda" CORRECT, unlike a relational db. try 'plaining that to management.

Post reply on HN