7 ms·

Use a relational database instead of NoSQL for your IoT app

17 points by ishikawa 8y ago

riku_iki 8y ago
and what would be the story if suddenly data stops fitting single node database?..
- F_J_H 8y ago
  Deal with it at the time? Key is to get your project up and running quickly. Worrying about problems that might happen down the road, the solution to which may hamper your speedy launch, is the heart of premature optimization. In many cases, a single node database will take you a loonnng way.
  - riku_iki 8y ago
    If your project is IOT, data size is in well foreseen problem. Pushing that problem to the later means you will need to rewrite more logic to new backend.
- taffer 8y ago
  You could probably combine Timescale with Citus, but I don't know if anyone has tried it yet.
- yellowapple 8y ago
  The common solution is to, you know, not fill your database. Old data should be archived whenever possible. If you really need to keep around more data than can fit in a single server, the PostgreSQL wiki has some documentation to that effect [1], with PostgreSQL-XL [2] and Citus [3] being specific examples of systems which can shard a Postgres database across multiple nodes. A more DIY alternative to the above is to leverage PostgreSQL's support for foreign data wrappers [4] to include results from other servers. The cool thing about this approach is that the other servers don't even have to be PostgreSQL servers; hell, they don't even have to be SQL, nor do they even have to be servers. The great thing about most/all of these solutions is that you don't have to worry about them upfront; you can roll them out when you're closer to actually hitting practical/theoretical limits. No need to prematurely optimize :) [1]: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an... [2]: https://www.postgres-xl.org/overview/ https://www.postgres-xl.org/overview/ [3]: https://www.citusdata.com/product/community#scale-out https://www.citusdata.com/product/community#scale-out [4]: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
  - F_J_H 8y ago
    Good point re: PostgresSQL foreign data wrappers (aka "linked tables"). I've used this with data in an Oracle database, and it worked well.
  - riku_iki 8y ago
    All those solutions have much more complexity than adapting NoSQL/NewSQL solution from the beginning, which supposed to provide much easier scale out story. Also FDW is probably limited in terms of transaction support and push down predicates.
    - yellowapple 8y ago
      Right, but even with the added complexity (not that much for something like Postgres-XL), you still end up with all the benefits of a robust and performant relational database. Meanwhile, for the FDW-based DIY approach, it should be at least theoretically (if not practically) possible to support both transactions and predicate pushdowns, especially if the foreign database is another Postgres database (or SQL at all, for that matter). More to the point, though: the idea is that you shouldn't have to prematurely optimize. The vast majority of websites don't need a full-blown distributed database. In the rare event that your site grows to that point, it ain't really all that difficult to switch to Postgres-XL or CockroachDB or something that gets you the best of both worlds.
jkoberg 8y ago
USE OUR PRODUCT!
jzoch 8y ago
I disagree pretty strongly. They talk about having a semi structured schema for sql and specifically call out IoT but those perfectly describe nosqls bread and butter. Dynamodb for example is best used when you model your data up front and define your usage patterns and handles the scale needed when you have thousands of IoT devices. SQL is not scalable enough. If I want to run complex queries then I'll run an event based sync to Athena or another data warehouse and run them there but still be free to scale my oltp use case

17 points by ishikawa 8y ago

riku_iki 8y ago

and what would be the story if suddenly data stops fitting single node database?..

F_J_H 8y ago
Deal with it at the time? Key is to get your project up and running quickly. Worrying about problems that might happen down the road, the solution to which may hamper your speedy launch, is the heart of premature optimization. In many cases, a single node database will take you a loonnng way.
- riku_iki 8y ago
  If your project is IOT, data size is in well foreseen problem. Pushing that problem to the later means you will need to rewrite more logic to new backend.
taffer 8y ago
You could probably combine Timescale with Citus, but I don't know if anyone has tried it yet.
yellowapple 8y ago
The common solution is to, you know, not fill your database. Old data should be archived whenever possible. If you really need to keep around more data than can fit in a single server, the PostgreSQL wiki has some documentation to that effect [1], with PostgreSQL-XL [2] and Citus [3] being specific examples of systems which can shard a Postgres database across multiple nodes. A more DIY alternative to the above is to leverage PostgreSQL's support for foreign data wrappers [4] to include results from other servers. The cool thing about this approach is that the other servers don't even have to be PostgreSQL servers; hell, they don't even have to be SQL, nor do they even have to be servers. The great thing about most/all of these solutions is that you don't have to worry about them upfront; you can roll them out when you're closer to actually hitting practical/theoretical limits. No need to prematurely optimize :) [1]: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an... [2]: https://www.postgres-xl.org/overview/ https://www.postgres-xl.org/overview/ [3]: https://www.citusdata.com/product/community#scale-out https://www.citusdata.com/product/community#scale-out [4]: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
- F_J_H 8y ago
  Good point re: PostgresSQL foreign data wrappers (aka "linked tables"). I've used this with data in an Oracle database, and it worked well.
- riku_iki 8y ago
  All those solutions have much more complexity than adapting NoSQL/NewSQL solution from the beginning, which supposed to provide much easier scale out story. Also FDW is probably limited in terms of transaction support and push down predicates.
  - yellowapple 8y ago
    Right, but even with the added complexity (not that much for something like Postgres-XL), you still end up with all the benefits of a robust and performant relational database. Meanwhile, for the FDW-based DIY approach, it should be at least theoretically (if not practically) possible to support both transactions and predicate pushdowns, especially if the foreign database is another Postgres database (or SQL at all, for that matter). More to the point, though: the idea is that you shouldn't have to prematurely optimize. The vast majority of websites don't need a full-blown distributed database. In the rare event that your site grows to that point, it ain't really all that difficult to switch to Postgres-XL or CockroachDB or something that gets you the best of both worlds.

jkoberg 8y ago

USE OUR PRODUCT!

jzoch 8y ago

I disagree pretty strongly. They talk about having a semi structured schema for sql and specifically call out IoT but those perfectly describe nosqls bread and butter. Dynamodb for example is best used when you model your data up front and define your usage patterns and handles the scale needed when you have thousands of IoT devices. SQL is not scalable enough. If I want to run complex queries then I'll run an event based sync to Athena or another data warehouse and run them there but still be free to scale my oltp use case