5
18 Comments

Worries about massive writes to DB?

My idea consists of a platform that keeps track of the financial metrics of thousands of publicly traded companies, so think tens of measurements taken multiple times a day for over a thousand companies.

I have found the sources for all the data that I need, but one thing that worries me is that my cloud DB bill would be through the roof because of all the workers that I have constantly writing large amounts of data to it, not to mention all the reads that would come up as the app deals with user traffic.

there are tons of platforms out there that do this, but I just needed some input as to whether that was normal or if I am missing something that might be helpful.

Thanks!

on August 7, 2020
  1. 6

    Ten measurements taken multiple times a day for a couple thousand companies is basically nothing for modern DB technology. Unless by multiple times a day you mean like every millisecond.

    1. 1

      oh not at all, definitely should've specified that in my original question. Most of the metrics I will be keeping track of will happen at pretty spaced out intervals, with the most frequent being every hour or so. Having typed that, I see how ridiculous it would be for a DBMS not to have support for something like that lol.

      thanks for the help!

  2. 4

    One thing to consider is whether you need historical data or not. If that isn't important, take a look at distributed streaming services like Kafka. They're built to scale to billions of events with no problems.

    1. 2

      since the main focus of the platform is research, I do intend to have historical data for users to look at, although I was thinking of using Kafka for the more frequently updated metrics like stock price, news articles, etc.

      Actually, Robinhood recently open-sourced Faust, their internal event processing framework and I was thinking of using that to handle the streams.

      Thanks for your answer!

  3. 3

    Pre-mature optimization kills startups before they get off the ground. You will discover your bottlenecks and usage as you build it. Iterate accordingly. If you worry about server costs right now, would you be able to sustain for weeks/months waiting to achieve traction?

    Your worry should be to find product-market fit asap. Efficiency comes later on.

  4. 3

    If you are using AWS and you don't need real time data you can publish the data to SQS and then read from the queue in a lambda and write to the db. You can set a concurrency limit of how many lambdas can run in parallel limiting the number of writes happening to the db at any point. SQS and lambda are fairly inexpensive to run and also give away a lot of free usage.

  5. 3

    Because there are a lot of good technical answers here already, I want to add this:

    People often underestimate what a database can take and overestimate the complexity of their problem.

    Instead of worrying about an issue that you don't have yet, go and build it. If you get at a point where the database can't handle your throughput any more, you can still circle back and ask for specific programming approaches to make it work. Also at that point, StackOverflow will be likely a much better resource than IH.

  6. 2

    Somehow the question seems to be pretty vague.

    think tens of measurements taken multiple times a day for over a thousand companies.

    how many times a day? 2, 3 , 100 ?
    how many measurements? 10, 20 50?
    how many thousand? 1000?

    a quick math gives you a lower bound of 20k datapoints/day -- that is not much. Any modern db can handle more than X10 datapoints. Pricing really depends on your cloud provider, I would venture on self-hosting the db and just a managed storage volume (again, really depends on your budget, selfhosting = do your own maintenance!)

    You can find many dbs that specialize on "tick data" that offers a good balance between data compression vs performance.

    Start small, iterate as you go. Focus more on your architecture than technology

  7. 2

    I've worked on systems that do 100K+ requests per second. Unfortunately, the answer to any question at that scale, or your scenario, really depends on the what you're trying to achieve... For your particular scenario, you didn't say what you're trying to do with the data. Do you need that accuracy or are you okay with less frequent updates?

    What's the end goal?

    1. 1

      what I plan to do with the data is be able to present it to the user in a more cleaned up, organized way, like most financial research websites. Since most of the recurring metrics that I need happen at pretty spaced out intervals (financial statements every quarter, etc.), I think I'm okay with less frequent updates.

      The end goal would be something along the lines of yahoo finance or Bloomberg, only with more calculations on the backend and less complexity onthe pages themselves.

      Thanks for the answer!

  8. 2

    What’s on your mind in terms of the database, programming language, software architecture?

    For now, I have 2 things (not sure if you have already think about)

    1. Find a suitable database.

    Each database can have a different data format that affects its operation (read, insert, update, delete)

    In general, insert and read data will be less expensive than the update. And your application seems to use insert and read the most.

    If so, find one that optimizes for reading and insert. Hopefully, it is available

    1. Cache or store query

    Your app seems like a search engine. Try to cache or store query’s result somewhere, so it only gets data and calculates once

    1. 2

      it does somewhat function as a search engine, allowing users to discover companies. my current tech stack is a Django monolith app with a PostgreSQL DB (was looking into timescale but decided to not start out with it), I've worked at companies that have processed billions of records using postgres so, given that and your point about inexpensive reads and writes, I would be better off taking that first step and adjusting course if I encounter any stack-specific issues.

      note taken on caching and storing queries, that's definitely going to be useful very soon.

      I appreciate the help!

      1. 2

        No worries!

        Django is good, and you're comfortable using to. Bitbucket, Dropbox started out using Django too.

        All be best and pls keep us posted :)

  9. 2

    How long do you need to keep that data around? And do you actually need to write to a database? Are you going to have relationships between the data that requires a database?

    It's kind of hard to solution for this without knowing what the data and schemas look like that you'll be storing, but you can look into data compression before storage, other options like S3 + Athena, etc.

    For the reads, you can take some pressure off the database by implementing caching (check out Redis, for example). And if you are literally just fetching data (a la SELECT something FROM some_table), and don't have complex joins or queries, then the queries will be faster. There are of course other query optimizations you can do (like indexing your tables to help make the SELECT's faster if there is lots of data).

    That being said, I wouldn't start out with caching (or worrying about queries too much) until you actually are noticing performance issues. You should avoid premature optimization.

    1. 1

      the platform is definitely going to contain relationships between the data and, since I'd like to allow users to do some thorough research, I am intending for all collected data to be there for as long as possible.

      Thanks for your points about query/response caching, premature optimization is something that I am constantly guilty of on my projects because I get too paranoid about scaling which, in the moment, I fail to realize is a good problem to have.

      I appreciate the answer!

  10. 1

    You should probably do some calculation to figure out what you are really talking about in term of volume, most importantly written top burst per second, secondly total volume stored after a year and only thirdly the read/sec top.
    What you described on top sounds like it might be too low to discuss, but if you consider different paths...
    Try to think of it as flat files for simplicity, take the biggest "table" and do average char count, than how many rows and multiple.
    That's your bytes, than /1024/1024 and we talk MB /1024 and we are in GB
    Write, unless you are pass 20MB/sec, it's tiny, single disk cheap, not interesting, 100MB you might be needing bigger nodes but it's still not too much probably
    on storage you can see AWS EBS per GB price for example, it's not too much...
    The thing with reads is usually this is already usage, meaning you can already get income, so when read grow, your income grows, so you don't care much as it's direct costs... inc. read capacity is way easier than the other parts as there isn't a congested bottleneck usually and there are many layers you can cache info at....

    on finance btw there are plenty of APIs you can probably do a no storage system that just calls other apis to resolve information without even storing anything if you wanted to..

    What might be more interesting is if you want just stale data like yesterday or live up to the minute data (that's expensive to get), your data sources, what processing your going to do etc.
    Finance/stock market data starts sounding simple and when you dig deep there are a lot of small details, like stocks that do splits or reverse splits, renames, dividends, exchange hiccups that backdate fixed and more.

    Also there are nosql datastores for example that sacrifice stuff for gaining speed like less efficient storage and possibility of losing data but give you 10x-100x and high returns on higher bounds...

    Also you can save money with non cloud hosting, getting VMs/HW might be cheaper than real cloud providers by 3x on some contexts...

  11. 1

    This comment was deleted 6 years ago

    1. 1

      I have been looking for ways to get into NoSQL DBs for the sake of learning more about them, most time series DBs are NoSQL as well.

      one relational alternative I was looking into was TimescaleDB that is an extension of postgres that has bucketing implemented, but the lack of documentation on how to hook it up to a django app was kinda concerning.

      thanks for the help!

      1. 2

        If you want to use Mongo to learn it, sure, but I would not recommend using it for anything production grade unless you have a very specific use-case for it. When in doubt, always use a relational DB. If you still need a document store, just use JSONB inside Postgres. Financial metrics for thousands of companies sounds about as relational as you can get, and it's going to be a constant uphill battle trying to shoehorn that into a doc store.