4 ms·

The key is that, what kind of ACID properties you need in your business. RDBMS puts a lot of effort to support a wide range of ACID properties. However which a

The key is that, what kind of ACID properties you need in your business. RDBMS puts a lot of effort to support a wide range of ACID properties. However which also make it very difficult to be used as cluster. So if your bussiness data is too large to be hold by a single instance of database, you will need a database cluster. And maybe someday, you want to get better performance, then you change your schema to remove some data constraints. Like removing FK, use application logic instead of storage procedure, etc. That's the reason why we need NoSql database. If OLTP is not the key part of your business, and it is expected to store a large amount of data in future. Then it would be better to carefully design your data schema so you can use NoSql database instead of a traditional RDBMS.

TylerE 4y ago
I don’t find the schema argument compelling at all. Postgres JSON columns are highly performant and queryable.
- ammo1662 4y ago
  Not the JSON schema of one object, but the "data schema" of the whole project. E.g. use nested document instead of FK to store 1 to 1 relationship. So you can take advantages of single document atomicity. Use array to store 1 to many relationship instead of an additional mapping table.
hardwaresofton 4y ago
> The key is that, what kind of ACID properties you need in your business. > RDBMS puts a lot of effort to support a wide range of ACID properties. However which also make it very difficult to be used as cluster. Note that while common, ACID is actually not a requirement for RDBMSes -- there are NoSQL datastores that provide acid guarantees, like FoundationDB[0]. > So if your bussiness data is too large to be hold by a single instance of database, you will need a database cluster. And maybe someday, you want to get better performance, then you change your schema to remove some data constraints. Like removing FK, use application logic instead of storage procedure, etc. I agree with the point, but I want to note that the vast majority of apps do not have data too large to be held by a single instance, especially with the eye-watering density of hardware these days. Some proof for my essentially wild conjecture: - Reddit started with just postgres[1] - LetsEncrypt (the reason most of the internet will have TLS in the future if not already) supports over 235MM sites with just one MySQL box[2] Now it's not that you can't mis-use RDBMS (Postgres), or that it's always the right tool for the job, but I just want to note that it's very possible to get very far with application size without scaling out horizontally. Scaling out horizontally & compromising your data model should be the last options you pursue, IMO. Also, that said, Citus for postgres is now fully open source[3], TimescaleDB has been open source and only got open-er[4] (they have a clustering mechanism) so this diminishes the use case for NoSQL going forward. This means that the specific use case for NoSQL is eroding somewhat. > If OLTP is not the key part of your business, and it is expected to store a large amount of data in future. Then it would be better to carefully design your data schema so you can use NoSql database instead of a traditional RDBMS. I'd argue that you should default to OLTP and only deviate when you need the extra power, but we'd probably agree to disagree there :) [EDIT] Highscalability.com has a great writeup on this from 2010 (!) that as I skim through still looks mostly relevant: http://highscalability.com/blog/2010/12/6/what-the-heck-are-you-actually-using-nosql-for.html http://highscalability.com/blog/2010/12/6/what-the-heck-are-... [0]: https://www.foundationdb.org/ https://www.foundationdb.org/ [1]: http://highscalability.com/blog/2013/8/26/reddit-lessons-learned-from-mistakes-made-scaling-to-1-billi.html http://highscalability.com/blog/2013/8/26/reddit-lessons-lea... [2]: https://letsencrypt.org/2021/01/21/next-gen-database-servers.html#openzfs-and-nvme https://letsencrypt.org/2021/01/21/next-gen-database-servers... [3]: https://www.citusdata.com/blog/2022/06/17/citus-11-goes-fully-open-source/ https://www.citusdata.com/blog/2022/06/17/citus-11-goes-full... [4]: https://www.timescale.com https://www.timescale.com
- ammo1662 4y ago
  > Note that while common, ACID is actually not a requirement for RDBMSes -- there are NoSQL datastores that provide acid guarantees, like FoundationDB[0]. Yes, most of NoSql provides some kind of ACID guarantees, however not as good as RDBMS. MongoDB also provides single document atomicity. But in RDBMS you can configure the ACID properties. > but I want to note that the vast majority of apps do not have data too large to be held by a single instance I agree with that. For me, the order would be: 1. Do not use database 2. Use SQLite 3. Use single instance RDBMS 4. Use single instance NoSql 5. Use single instance RDBMS + NoSql cluster 6. Use RDBMS cluster > I'd argue that you should default to OLTP and only deviate when you need the extra power, but we'd probably agree to disagree there :) Personally I would prefer NoSql when the business model fits some specific patterns. But I agree with you that. The article of Highscalability.com is very useful, and also some other articles. I did not know the site before, thanks for sharing.
  - hardwaresofton 4y ago
    > I agree with that. For me, the order would be: 1. Do not use database 2. Use SQLite 3. Use single instance RDBMS 4. Use single instance NoSql 5. Use single instance RDBMS + NoSql cluster 6. Use RDBMS cluster Yeah it’s so funny, HN seems to rediscover way of thinking every so often! I think it’s why SQLite projects are so popular (and of course how well built and popular SQLite is)
  - gqewogpdqa 4y ago
    >>>MongoDB also provides single document atomicity. And multi-document, in sharded clusters. 2018: https://www.mongodb.com/blog/post/mongodb-multi-document-acid-transactions-general-availability https://www.mongodb.com/blog/post/mongodb-multi-document-aci... >>>But in RDBMS you can configure the ACID properties. https://www.mongodb.com/docs/manual/reference/write-concern/ https://www.mongodb.com/docs/manual/reference/write-concern/

TylerE 4y ago

I don’t find the schema argument compelling at all. Postgres JSON columns are highly performant and queryable.

ammo1662 4y ago
Not the JSON schema of one object, but the "data schema" of the whole project. E.g. use nested document instead of FK to store 1 to 1 relationship. So you can take advantages of single document atomicity. Use array to store 1 to many relationship instead of an additional mapping table.

hardwaresofton 4y ago

> The key is that, what kind of ACID properties you need in your business. > RDBMS puts a lot of effort to support a wide range of ACID properties. However which also make it very difficult to be used as cluster. Note that while common, ACID is actually not a requirement for RDBMSes -- there are NoSQL datastores that provide acid guarantees, like FoundationDB[0]. > So if your bussiness data is too large to be hold by a single instance of database, you will need a database cluster. And maybe someday, you want to get better performance, then you change your schema to remove some data constraints. Like removing FK, use application logic instead of storage procedure, etc. I agree with the point, but I want to note that the vast majority of apps do not have data too large to be held by a single instance, especially with the eye-watering density of hardware these days. Some proof for my essentially wild conjecture: - Reddit started with just postgres[1] - LetsEncrypt (the reason most of the internet will have TLS in the future if not already) supports over 235MM sites with just one MySQL box[2] Now it's not that you can't mis-use RDBMS (Postgres), or that it's always the right tool for the job, but I just want to note that it's very possible to get very far with application size without scaling out horizontally. Scaling out horizontally & compromising your data model should be the last options you pursue, IMO. Also, that said, Citus for postgres is now fully open source[3], TimescaleDB has been open source and only got open-er[4] (they have a clustering mechanism) so this diminishes the use case for NoSQL going forward. This means that the specific use case for NoSQL is eroding somewhat. > If OLTP is not the key part of your business, and it is expected to store a large amount of data in future. Then it would be better to carefully design your data schema so you can use NoSql database instead of a traditional RDBMS. I'd argue that you should default to OLTP and only deviate when you need the extra power, but we'd probably agree to disagree there :) [EDIT] Highscalability.com has a great writeup on this from 2010 (!) that as I skim through still looks mostly relevant: http://highscalability.com/blog/2010/12/6/what-the-heck-are-you-actually-using-nosql-for.html http://highscalability.com/blog/2010/12/6/what-the-heck-are-... [0]: https://www.foundationdb.org/ https://www.foundationdb.org/ [1]: http://highscalability.com/blog/2013/8/26/reddit-lessons-learned-from-mistakes-made-scaling-to-1-billi.html http://highscalability.com/blog/2013/8/26/reddit-lessons-lea... [2]: https://letsencrypt.org/2021/01/21/next-gen-database-servers.html#openzfs-and-nvme https://letsencrypt.org/2021/01/21/next-gen-database-servers... [3]: https://www.citusdata.com/blog/2022/06/17/citus-11-goes-fully-open-source/ https://www.citusdata.com/blog/2022/06/17/citus-11-goes-full... [4]: https://www.timescale.com https://www.timescale.com

ammo1662 4y ago
> Note that while common, ACID is actually not a requirement for RDBMSes -- there are NoSQL datastores that provide acid guarantees, like FoundationDB[0]. Yes, most of NoSql provides some kind of ACID guarantees, however not as good as RDBMS. MongoDB also provides single document atomicity. But in RDBMS you can configure the ACID properties. > but I want to note that the vast majority of apps do not have data too large to be held by a single instance I agree with that. For me, the order would be: 1. Do not use database 2. Use SQLite 3. Use single instance RDBMS 4. Use single instance NoSql 5. Use single instance RDBMS + NoSql cluster 6. Use RDBMS cluster > I'd argue that you should default to OLTP and only deviate when you need the extra power, but we'd probably agree to disagree there :) Personally I would prefer NoSql when the business model fits some specific patterns. But I agree with you that. The article of Highscalability.com is very useful, and also some other articles. I did not know the site before, thanks for sharing.
- hardwaresofton 4y ago
  > I agree with that. For me, the order would be: 1. Do not use database 2. Use SQLite 3. Use single instance RDBMS 4. Use single instance NoSql 5. Use single instance RDBMS + NoSql cluster 6. Use RDBMS cluster Yeah it’s so funny, HN seems to rediscover way of thinking every so often! I think it’s why SQLite projects are so popular (and of course how well built and popular SQLite is)
- gqewogpdqa 4y ago
  >>>MongoDB also provides single document atomicity. And multi-document, in sharded clusters. 2018: https://www.mongodb.com/blog/post/mongodb-multi-document-acid-transactions-general-availability https://www.mongodb.com/blog/post/mongodb-multi-document-aci... >>>But in RDBMS you can configure the ACID properties. https://www.mongodb.com/docs/manual/reference/write-concern/ https://www.mongodb.com/docs/manual/reference/write-concern/