4 ms·

Since Wix is using MySQL as a key-store ... I wonder why they didn't look at using Postgres HStore [1]. HStore is a key value store built directly in the RDBMS

Since Wix is using MySQL as a key-store ... I wonder why they didn't look at using Postgres HStore [1]. HStore is a key value store built directly in the RDBMS of Postgres. [1] http://www.postgresql.org/docs/9.6/static/hstore.html http://www.postgresql.org/docs/9.6/static/hstore.html

luka-birsa 10y ago
We've drank the NoSQL coolaid mostly as we prefered a schemaless approach to our database and couchdb looked like a cool thing to use. Tested, deployed in production abd after a while we figured out that most of the promises about performance, stability, etc we're mostly bull. HStore was released, we've migrated to PG and we can't be happier. Zero issues so far.
- BinaryIdiot 10y ago
  > Tested, deployed in production abd after a while we figured out that most of the promises about performance, stability, etc we're mostly bull. Well, to be fair you would typically expect a database that's the equivalent of a remote hash table to be pretty much as fast as you can get. Now I don't have any experience with couch but most of the other key value stores I've used they scream with performance. But if you're doing anything beyond basic manipulations then it's going to require a lot of tuning depending on the solution you went with. But RDMS can be very similar. Both are useful tools when used correctly and there is a huge amount of overlap in terms of capability.
  - tracker1 10y ago
    It really depends on your use case... if you can rely on heavy caching, then working against a transactional sql database can work for the bulk of your application, while also "publishing" to a distributed k/v store... Most applications are probably fine regardless of the underlying data store, and are better served by what works best for the model of the data, which can often be SQL. I'm more inclined to react for mongo or rethinkdb first, as I tend to think more in terms of more complex records/objects in most of the applications I've worked on.
- suneilp 10y ago
  Couchdb isn't exactly the best thing to judge NoSQL by today especially by it's old performance issues, lack of automatic compaction, indexing on demand instead of proactive background indexing, etc.
  - 3pt14159 10y ago
    At what point do I get to tout out the No True Scotsman paradox? Every NoSQL solution whether as its own DB or built into another one (like Postgres) I've worked with I've either hated (Couch, Mongo, Cassandra, Neo4j), or relegated to _very_ specific use-cases (Riak, Redis, Postgres). I agree with the Wix team. I've used Mongo in a very high volume site and it was a constant source of headaches. We even had an engineer we called "MongoDB" because he was constantly having to deal with scaling it. It didn't even handle more than 10% of the application data, it was strictly for timelines; the rest of it was in trusty old MySQL. If we'd just done blobs as JSON in MySQL in the first place we would have been completely fine. Even though I prefer Postgres, knowing nothing else, I would prefer 100% SQL in MySQL than a Postgres backed application that splits the storage between tables and KV store.
- gshulegaard 10y ago
  Just curious, but why use HStore instead of JSONB?
  - calpaterson 10y ago
    Presumably because HStore predates JSONB
    - gshulegaard 10y ago
      While this is the conventional wisdom, I just wanted to make sure there wasn't some obscure benefit of HStore over JSONB that I wasn't aware of yet.
bpicolo 10y ago
Hstore has a lot of issues in my experience. Very hard to query through most ORMs, expensive indexes, strings only, not nested data, lots of unoptimized parsers out there relative to json. Use jsonb if you want postgres KV storage. Hstore is more k->k->v, which is the same but different, and also leads inexperienced developers to model entire relationships in a single column
agentgt 10y ago
IRC If you are using JDBC (which is pretty much the only choice on Java) HStore is a pain to use since its query operators conflict with JDBC's parameter syntax ("?").
atomic77 10y ago
Presumably they preferred to stay within the mysql world. I didn't read the article in detail, but I couldn't help but wonder why not use innodb more directly [1]? Imagine how much CPU is wasted using a full blown RDBMS SQL engine on top of InnoDB just to do key-value read/writes. I have some experience with this having worked on an experimental storage engine for mysql that we connected to a transactional in-memory k-v store. The performance penalty for simple k-v workload through mysql was quite substantial, though our storage engine code was probably not sufficiently optimized. It would be interesting to explore this for innodb though [1] https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.htm...
yoava 10y ago
At the time we started, MySQL was more mature. Today, having a lot of MySQL installations at Wix we have no need to try HStore as well. Having said that, it we would have stared today we may have considered it, among other options.

luka-birsa 10y ago

We've drank the NoSQL coolaid mostly as we prefered a schemaless approach to our database and couchdb looked like a cool thing to use. Tested, deployed in production abd after a while we figured out that most of the promises about performance, stability, etc we're mostly bull. HStore was released, we've migrated to PG and we can't be happier. Zero issues so far.

BinaryIdiot 10y ago
> Tested, deployed in production abd after a while we figured out that most of the promises about performance, stability, etc we're mostly bull. Well, to be fair you would typically expect a database that's the equivalent of a remote hash table to be pretty much as fast as you can get. Now I don't have any experience with couch but most of the other key value stores I've used they scream with performance. But if you're doing anything beyond basic manipulations then it's going to require a lot of tuning depending on the solution you went with. But RDMS can be very similar. Both are useful tools when used correctly and there is a huge amount of overlap in terms of capability.
- tracker1 10y ago
  It really depends on your use case... if you can rely on heavy caching, then working against a transactional sql database can work for the bulk of your application, while also "publishing" to a distributed k/v store... Most applications are probably fine regardless of the underlying data store, and are better served by what works best for the model of the data, which can often be SQL. I'm more inclined to react for mongo or rethinkdb first, as I tend to think more in terms of more complex records/objects in most of the applications I've worked on.
suneilp 10y ago
Couchdb isn't exactly the best thing to judge NoSQL by today especially by it's old performance issues, lack of automatic compaction, indexing on demand instead of proactive background indexing, etc.
- 3pt14159 10y ago
  At what point do I get to tout out the No True Scotsman paradox? Every NoSQL solution whether as its own DB or built into another one (like Postgres) I've worked with I've either hated (Couch, Mongo, Cassandra, Neo4j), or relegated to _very_ specific use-cases (Riak, Redis, Postgres). I agree with the Wix team. I've used Mongo in a very high volume site and it was a constant source of headaches. We even had an engineer we called "MongoDB" because he was constantly having to deal with scaling it. It didn't even handle more than 10% of the application data, it was strictly for timelines; the rest of it was in trusty old MySQL. If we'd just done blobs as JSON in MySQL in the first place we would have been completely fine. Even though I prefer Postgres, knowing nothing else, I would prefer 100% SQL in MySQL than a Postgres backed application that splits the storage between tables and KV store.
gshulegaard 10y ago
Just curious, but why use HStore instead of JSONB?
- calpaterson 10y ago
  Presumably because HStore predates JSONB
  - gshulegaard 10y ago
    While this is the conventional wisdom, I just wanted to make sure there wasn't some obscure benefit of HStore over JSONB that I wasn't aware of yet.

bpicolo 10y ago

Hstore has a lot of issues in my experience. Very hard to query through most ORMs, expensive indexes, strings only, not nested data, lots of unoptimized parsers out there relative to json. Use jsonb if you want postgres KV storage. Hstore is more k->k->v, which is the same but different, and also leads inexperienced developers to model entire relationships in a single column

agentgt 10y ago

IRC If you are using JDBC (which is pretty much the only choice on Java) HStore is a pain to use since its query operators conflict with JDBC's parameter syntax ("?").

atomic77 10y ago

Presumably they preferred to stay within the mysql world. I didn't read the article in detail, but I couldn't help but wonder why not use innodb more directly [1]? Imagine how much CPU is wasted using a full blown RDBMS SQL engine on top of InnoDB just to do key-value read/writes. I have some experience with this having worked on an experimental storage engine for mysql that we connected to a transactional in-memory k-v store. The performance penalty for simple k-v workload through mysql was quite substantial, though our storage engine code was probably not sufficiently optimized. It would be interesting to explore this for innodb though [1] https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.htm...

yoava 10y ago

At the time we started, MySQL was more mature. Today, having a lot of MySQL installations at Wix we have no need to try HStore as well. Having said that, it we would have stared today we may have considered it, among other options.