8 ms·

On my personal projects, my default database choice is Postgres unless I have a glaring, obvious reason to choose something NoSQL (in which case I like CouchDB

by jcadam 10y ago

On my personal projects, my default database choice is Postgres unless I have a glaring, obvious reason to choose something NoSQL (in which case I like CouchDB if the situation calls for a document store, don't know much about the others). At work, it doesn't matter if we have a perfect use case for a particular NoSQL solution (I'm working on something now that I think would be a great fit for a Graph database like Neo4J). We use Oracle, because it's expensive, we're already paying for it, so damn it, we're going to use it.

DiabloD3 10y ago
I agree. However, re: document store, I've been bouncing Postgres around in my head for that: all required columns (ie, columns that always exist, or will be queried on) are normal SQL columns, everything else is stashed in one or more hstore columns.
- StavrosK 10y ago
  > everything else is stashed in one or more hstore columns. This has the same problem as most NoSQL solutions: You don't get the benefit of a schema, and schemas are damn useful things.
  - elmigranto 10y ago
    You can define arbitrary constraints of any level of complexity with Postgres for any column, be it plain int, hstore, jsonb or anything else. Most of that will also be efficiently indexable out of the box.
  - DiabloD3 10y ago
    Not everything needs a schema. The alternative I've also been bouncing around in my head is multiple tables: objects (rows in the "primary" table) have traits (rows in "secondary" tables with identical primary keys) that break out columns according to what they do. Having 9000 columns in a single table is a bit unwieldy.
    - virtuabhi 10y ago
      I guess you are talking about database normalization. And it is part of schema design. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization
    - coldtea 10y ago
      >Not everything needs a schema. Everything does. Data without schema are just noise. The real question is whether you'll enforce it at the DB level (and suffer from inflexibility) or enforce it at the code level (that, obviously, should know what field is what, and which fields are available, e.g. enforcing a schema at runtime) and get potentially corrupted data, management overhead, etc.
      - danso 10y ago
        > Everything does. Data without schema are just noise. Love that concise adage. Will probably adopt it when explaining databases to novices.
      - deleted 10y ago
        [deleted]
      - DiabloD3 10y ago
        To clarify, I meant exactly that: enforced at the DB level, or enforced in the code base.
      - int_19h 10y ago
        There are times when you don't care about the schema, though. E.g. when the data is mostly just pass-through, and you need to get it from here to there. Sometimes you need just a tiny bit of schema to know where "there" is (when that's encoded in the data), but you don't really care about the rest at all.
    - st553 10y ago
      >Not everything needs a schema. Any good examples?
- cwmma 10y ago
  you can totally do this, you can even go one farther and put the value into a JSON column which can allow you to do queries on the value
SmellTheGlove 10y ago
That's us with Teradata, but in fairness, it does everything we want from an operational standpoint really well. A new project might present a use case where we might not want TD as the first choice, but TD ends up doing it well enough anyway. And it's expensive, we're already paying for it, so damn it, we're going to use it.
chatmasta 10y ago
One could argue that Neo4j is much closer to a relational database than a NoSQL object store, since a relational database is effectively a graph. You could model graph data like Neo4J in a relational database by simply keeping a table of nodes, edges, and a join table for both. The advantage of Neo4J is that its query language is explicitly optimized for the graph use case, whereas querying a relational database as a graph engine would necessitate fairly complex SQL.
- KurtMueller 10y ago
  When would I use Neo4j over a relational sql database like PG/MySQL?
  - chatmasta 10y ago
    Ultimately it's up to you, of course. But the most obvious use case would be when you're building something explicitly modeled onto a graph, like a social network.
  - johnymontana 10y ago
    If you are writing lots of joins in SQL, that might be a good use case for a graph database like Neo4j. Neo4j treats relationships as first class citizens, so really anytime you care about relationships in your data you might have a good graph use case. One way to think about relationships in graph databases is as materialized joins. Relationships are modeled and stored explicitly so there is no join at query time, only a constant time graph traversal. There are differences in flexibility of data model, ease of expressing traversals in the query language, etc but I think traversal vs JOIN is a good way to compare relational vs graph.
  - brightball 10y ago
    My understanding is if you had widespread, conditional joins. I haven't ever implemented though, so somebody feel free to correct me if I'm wrong here: Example: News Feed - JOIN with my friends - JOIN recent posts - JOIN topics / keywords that I tend to care about - Filter by people I interact with more - Filter out shared posts from sources I ignore - Sort by some combination of recency, likes, likes from my friends The idea, it seems, is to be optimized for wide joins.
overcast 10y ago
RethinkDB is my default at this point. Relational documents provide 99.9% of the features I need. When I need actual graph data, I play around with ArangoDB.
- makkesk8 10y ago
  RethinkDB is also my default at this point, their scaling features are great. But they have several major unresolved issues, check their issue tracker on github for reference.
  - kinkdr 10y ago
    Anything serious that can corrupt or eat data?
    - makkesk8 10y ago
      No not really, It's really solid. But stuff like correlating writes with feeds are not possible atm.
      - ironchef 10y ago
        One thing we've done in the past is included the feed name / source in the document (just an fyi...not sure if applicable to your use case or not).
- brightball 10y ago
  As a fairly staunch advocate of PostgreSQL for the 99% use case, I'd be really interested to hear what makes RethinkDB stand out?
  - tracker1 10y ago
    The admin UX... setting up a redundant sharded cluster with auto-failover in RethinkDB is in the box, straight forward and easy. With PostgreSQL you're stuck choosing between at least a half dozen less than optimal solutions. That would be the single biggest difference... Neither have particularly great DBAAS offerings, outside of RDS Postgres, though if you're on Amazon, you can use Compose.io's rethinkdb option there too. RethinkDB has sharding + redundancy similar to ElasticSearch or Cassandra, while having some other features that put it more in line with an SQL rdbms, while having an administrative UI that's second to none.. and I mean none.
Mister_Snuggles 10y ago
I do the same. If I'm doing something where I'm storing JSON (e.g., from an external service), but only care about certain bits of it, I'll store the bits I care about in columns, then put the whole JSON object into a jsonb column. The key advantage that I see is that when I decide that a new piece of the JSON needs to be cared about, I can do something like this: (modify data types as required) ALTER TABLE blah ADD COLUMN newthing text; CREATE INDEX ON blah.newthing; -- if needed UPDATE blah SET newthing = jsoncolumn->>'new_thing';
- takeda 10y ago
  Just a nitpick, but I believe it would be more efficient to create index as the last step here.
  - brightball 10y ago
    Not necessarily. If the table is already fairly large you'd have to use Postgres CREATE INDEX CONCURRENTLY to avoid locking the table while it sorted out the index. The only downside is that you have very little control of the process. If you already have the data available in a JSONB column you can create the column, create the index and then set your app code to look to the JSON data if the column is null during transition while a background job goes through and populates rows either individually or in smaller batches to avoid disrupting user experience.
  - Mister_Snuggles 10y ago
    This was just an example of what you can do. I'd do this sort of thing while developing an application, but if it were in Production I'd take a much closer look at how the steps in my change would impact the running application.
- NickNameNick 10y ago
  you should be able to index on an expression without adding the column. eg create index on blah((jsoncolumn->>'new_thing')); although I haven't tried it myself yet, I've been meaning to play with that feature.
deleted 10y ago
[deleted]
marknadal 10y ago
If you are interested in graph databases (full disclosure: I am the author) you should check out https://github.com/amark/gun https://github.com/amark/gun . - It is Open Source under MIT license, very liberal, at no cost. - It has property graph features like Neo4j. - It does realtime updates, like Firebase. - You can store JSON and Relational Documents, like RethinkDB. - Offline first (or mobile first), like CouchDB. - Upcoming release has been benchmarked at 25M+ reads/sec and 6K+ writes/sec. - The community is really friendly and accepting.

by jcadam 10y ago

DiabloD3 10y ago

I agree. However, re: document store, I've been bouncing Postgres around in my head for that: all required columns (ie, columns that always exist, or will be queried on) are normal SQL columns, everything else is stashed in one or more hstore columns.

StavrosK 10y ago
> everything else is stashed in one or more hstore columns. This has the same problem as most NoSQL solutions: You don't get the benefit of a schema, and schemas are damn useful things.
- elmigranto 10y ago
  You can define arbitrary constraints of any level of complexity with Postgres for any column, be it plain int, hstore, jsonb or anything else. Most of that will also be efficiently indexable out of the box.
- DiabloD3 10y ago
  Not everything needs a schema. The alternative I've also been bouncing around in my head is multiple tables: objects (rows in the "primary" table) have traits (rows in "secondary" tables with identical primary keys) that break out columns according to what they do. Having 9000 columns in a single table is a bit unwieldy.
  - virtuabhi 10y ago
    I guess you are talking about database normalization. And it is part of schema design. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization
  - coldtea 10y ago
    >Not everything needs a schema. Everything does. Data without schema are just noise. The real question is whether you'll enforce it at the DB level (and suffer from inflexibility) or enforce it at the code level (that, obviously, should know what field is what, and which fields are available, e.g. enforcing a schema at runtime) and get potentially corrupted data, management overhead, etc.
    - danso 10y ago
      > Everything does. Data without schema are just noise. Love that concise adage. Will probably adopt it when explaining databases to novices.
    - deleted 10y ago
      [deleted]
    - DiabloD3 10y ago
      To clarify, I meant exactly that: enforced at the DB level, or enforced in the code base.
    - int_19h 10y ago
      There are times when you don't care about the schema, though. E.g. when the data is mostly just pass-through, and you need to get it from here to there. Sometimes you need just a tiny bit of schema to know where "there" is (when that's encoded in the data), but you don't really care about the rest at all.
  - st553 10y ago
    >Not everything needs a schema. Any good examples?
cwmma 10y ago
you can totally do this, you can even go one farther and put the value into a JSON column which can allow you to do queries on the value

SmellTheGlove 10y ago

That's us with Teradata, but in fairness, it does everything we want from an operational standpoint really well. A new project might present a use case where we might not want TD as the first choice, but TD ends up doing it well enough anyway. And it's expensive, we're already paying for it, so damn it, we're going to use it.

chatmasta 10y ago

One could argue that Neo4j is much closer to a relational database than a NoSQL object store, since a relational database is effectively a graph. You could model graph data like Neo4J in a relational database by simply keeping a table of nodes, edges, and a join table for both. The advantage of Neo4J is that its query language is explicitly optimized for the graph use case, whereas querying a relational database as a graph engine would necessitate fairly complex SQL.

KurtMueller 10y ago
When would I use Neo4j over a relational sql database like PG/MySQL?
- chatmasta 10y ago
  Ultimately it's up to you, of course. But the most obvious use case would be when you're building something explicitly modeled onto a graph, like a social network.
- johnymontana 10y ago
  If you are writing lots of joins in SQL, that might be a good use case for a graph database like Neo4j. Neo4j treats relationships as first class citizens, so really anytime you care about relationships in your data you might have a good graph use case. One way to think about relationships in graph databases is as materialized joins. Relationships are modeled and stored explicitly so there is no join at query time, only a constant time graph traversal. There are differences in flexibility of data model, ease of expressing traversals in the query language, etc but I think traversal vs JOIN is a good way to compare relational vs graph.
- brightball 10y ago
  My understanding is if you had widespread, conditional joins. I haven't ever implemented though, so somebody feel free to correct me if I'm wrong here: Example: News Feed - JOIN with my friends - JOIN recent posts - JOIN topics / keywords that I tend to care about - Filter by people I interact with more - Filter out shared posts from sources I ignore - Sort by some combination of recency, likes, likes from my friends The idea, it seems, is to be optimized for wide joins.

overcast 10y ago

RethinkDB is my default at this point. Relational documents provide 99.9% of the features I need. When I need actual graph data, I play around with ArangoDB.

makkesk8 10y ago
RethinkDB is also my default at this point, their scaling features are great. But they have several major unresolved issues, check their issue tracker on github for reference.
- kinkdr 10y ago
  Anything serious that can corrupt or eat data?
  - makkesk8 10y ago
    No not really, It's really solid. But stuff like correlating writes with feeds are not possible atm.
    - ironchef 10y ago
      One thing we've done in the past is included the feed name / source in the document (just an fyi...not sure if applicable to your use case or not).
brightball 10y ago
As a fairly staunch advocate of PostgreSQL for the 99% use case, I'd be really interested to hear what makes RethinkDB stand out?
- tracker1 10y ago
  The admin UX... setting up a redundant sharded cluster with auto-failover in RethinkDB is in the box, straight forward and easy. With PostgreSQL you're stuck choosing between at least a half dozen less than optimal solutions. That would be the single biggest difference... Neither have particularly great DBAAS offerings, outside of RDS Postgres, though if you're on Amazon, you can use Compose.io's rethinkdb option there too. RethinkDB has sharding + redundancy similar to ElasticSearch or Cassandra, while having some other features that put it more in line with an SQL rdbms, while having an administrative UI that's second to none.. and I mean none.

Mister_Snuggles 10y ago

I do the same. If I'm doing something where I'm storing JSON (e.g., from an external service), but only care about certain bits of it, I'll store the bits I care about in columns, then put the whole JSON object into a jsonb column. The key advantage that I see is that when I decide that a new piece of the JSON needs to be cared about, I can do something like this: (modify data types as required) ALTER TABLE blah ADD COLUMN newthing text; CREATE INDEX ON blah.newthing; -- if needed UPDATE blah SET newthing = jsoncolumn->>'new_thing';

takeda 10y ago
Just a nitpick, but I believe it would be more efficient to create index as the last step here.
- brightball 10y ago
  Not necessarily. If the table is already fairly large you'd have to use Postgres CREATE INDEX CONCURRENTLY to avoid locking the table while it sorted out the index. The only downside is that you have very little control of the process. If you already have the data available in a JSONB column you can create the column, create the index and then set your app code to look to the JSON data if the column is null during transition while a background job goes through and populates rows either individually or in smaller batches to avoid disrupting user experience.
- Mister_Snuggles 10y ago
  This was just an example of what you can do. I'd do this sort of thing while developing an application, but if it were in Production I'd take a much closer look at how the steps in my change would impact the running application.
NickNameNick 10y ago
you should be able to index on an expression without adding the column. eg create index on blah((jsoncolumn->>'new_thing')); although I haven't tried it myself yet, I've been meaning to play with that feature.

deleted 10y ago

[deleted]

marknadal 10y ago

If you are interested in graph databases (full disclosure: I am the author) you should check out https://github.com/amark/gun https://github.com/amark/gun . - It is Open Source under MIT license, very liberal, at no cost. - It has property graph features like Neo4j. - It does realtime updates, like Firebase. - You can store JSON and Relational Documents, like RethinkDB. - Offline first (or mobile first), like CouchDB. - Upcoming release has been benchmarked at 25M+ reads/sec and 6K+ writes/sec. - The community is really friendly and accepting.