6 ms·

I like Arango but these days it's very hard for a database to compete with Postgres which is incredibly powerful. Every time I've bet against Postgres and used

I like Arango but these days it's very hard for a database to compete with Postgres which is incredibly powerful. Every time I've bet against Postgres and used some other data storage mechanism, I always come back to Postgres.

manigandham 8y ago
The main thing to compete on is distributed scale-out and high-availability which PostgreSQL does not have a good answer for.
- lykr0n 8y ago
  CitusDB for sharding & replication. Patroni & Stolon for automated replication. Not native solutions, but are an answer.
  - contrahax 8y ago
    CitusDB has a ton of limitations on your schema/table structure right now. Over time it has gotten better, so hopefully in the future it will be a true drop-in scale out solution. Specifically some types of relationships + indexes are not supported.
    - lykr0n 8y ago
      Oh yeah totally; it's a slow march forward. MySQL Galera took some time before it was ready for serious use, but the fact that it's semi-usable is awesome.
    - doh 8y ago
      True, but as you said, the compatibility is quickly increasing. They actually did a great job and there is fairly little that is not compatible, usually on very advanced side (which is exactly what we would love to have), but the team is especially good at what they are doing and believe they will get there sooner than later.
  - manigandham 8y ago
    Citus only does sharding, not replication. Either way, this is a fundamental design limitation with traditional single-master relational systems.
    - bsg75 8y ago
      http://docs.citusdata.com/en/v8.1/cloud/availability.html http://docs.citusdata.com/en/v8.1/cloud/availability.html
      - manigandham 8y ago
        Yea, that's just normal Postgres replication, nothing to do with Citus.
    - tpetry 8y ago
      Citus is doing sharding and replication: Citus will shard your data and place every shard on e.g. 3 servers. So one server dying is no problem because the data is replicated 2 times.
      - jbergens 8y ago
        Do they have ACID transactions around writing to all 3 servers? Doesn't that cause bad performance? You might also end up with a LOT of servers unless you starting sharing servers for different shards (but still with different sets of 3 servers for every shard).
      - manigandham 8y ago
        Ah I see that now. However that's not by default and it still only applies to "distributed" tables, not all the data in the database. Combined with a single-master, it's nowhere near the replica functionality provided by natively distributed storage systems.
- 75dvtwin 8y ago
  what about PostgresXL. it is a release v behind Postgres. But it has ACID and cluster. I do not have personal experience with it, but would be interested to hear what folks say. Overall, I agree -- PG with JsonB is extraordinary powerful system, covering document oriented and typical row-oriented use cases. PGs ecosystem with external foreign interfaces, and in memory streaming solutions (like PipelineDB) - must be an envy of any other db ecosystem.
- zbentley 8y ago
  That's absolutely true. However, most systems that think they need scale-out are either worrying about a problem they don't have yet, suffering performance problems due to poor database design, or have not fully explored logical (dynamic or hash-based) sharding as a solution to their problems. A huge proportion of modern software businesses can run on single-writer RDBMS instances, properly engineered, at a fraction of the operational and implementation cost of a scale-out solution. That applies to hosted and self-managed solutions equally, in my experience.
  - zorga 8y ago
    The desire is generally for high availability now and ability to scale out in the future; while a single write db is generally more than enough performance wise, it doesn't give you high availability which is the main desire.
  - manigandham 8y ago
    Sure, but scale isn't everything. Operations are far more important, especially when you're a small team. Modern distributed databases scale better but also have better replication, high-availability with automatic failover and no downtime, easier upgrades, easier backups, and generally less maintenance. Removing the single-point of failure with efficient distribution while being able to run easily on docker/kubernetes makes a big difference over a single monolithic database server.
- _Codemonkeyism 8y ago
  Valid point, for 99% of startups scaling on one machine (todays SSD based multi core, GB memory hardware is very powerful) with read slaves and failover (Patroni) works just fine. Many I meet as a consultant though want to scale out when they have 100 customers.
thdxr 8y ago
Postgres' inability to handle multiple concurrent queries on a single connection makes it a pain when using it for multi-tenant services. With all the SAAS companies out there I'm surprised this issue doesn't get brought up more often
- andrewstuart 8y ago
  Why not just open another connection?
  - chatmasta 8y ago
    Each open postgres connection requires about 10mb of memory overhead IIRC
    - grumpydba 8y ago
      That's why you need a connection pool. That's true for all database systems.
      - cbsmith 8y ago
        Not all databases systems require a connection pool, though it is often a good idea.
        grumpydba 8y ago
        I disagree. I've faced incidents on oracle/sql server/postgres/sybase instances when several hundreds of connections where spawned. You definitely need to think about pooling fro the beginning of the design of your application.
        cbsmith 8y ago
        Yes, all of those databases allocate query memory for queries at connection time, and consequently don't scale efficiently for large number of idle clients. That's not all databases. Some don't do that. Some don't even do connections period. Connection pools are a necessary work around for a specific design decision with the system software. They can be helpful other problems as well, but they aren't necessarily a requirement.
pddpro 8y ago
Any resources you'd like to recommend to a beginner that comprehensively covers all aspects of Postgres?
- brylie 8y ago
  PostgreSQL Tutorial covers many fundamentals: http://www.postgresqltutorial.com/ http://www.postgresqltutorial.com/
porker 8y ago
Postgres is powerful, I want a row in a table to have a relationship to one or more rows in _any other table_ in the database, and Postgres can't help with that. That to me is a compelling reason to use something other than Postgres.
- ralusek 8y ago
  There are a few solutions to what you're proposing which might help. One solution is to have a generic reference property paired with another indicating which table to reference reference_id: '1234', reference_table: 'posts' reference_id: '4321', reference_table: 'comments' You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql https://stackoverflow.com/questions/3074535/when-to-use-inhe... Another form of modeling the data in such a manner is called Exclusive Arc, which is that you just simply put all of the possible keys you might reference on the table, and add a foreign key on all of them. Then, when you need to make a reference, you just leave all but the one in use on that row as null values. If a "like" can go on a "post" or a "comment" or an "image", you would just have all 3 foreign key columns/constraints on the "like" table. And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database.
  - porker 8y ago
    > One solution is to have a generic reference property paired with another indicating which table to reference > You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Thanks, I had not thought JOINs possible on this - how would a query look? I'm guessing it works only for one type of reference_table. > Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inhe.. https://stackoverflow.com/questions/3074535/when-to-use-inhe.... Been there, tried it, banged my head on most of these (particularly the FK limitation): https://www.postgresql.org/docs/11/ddl-inherit.html https://www.postgresql.org/docs/11/ddl-inherit.html :) If it was fixed by Postgres it'd be awesome. I've lost the wiki page which was around on the topic, but it was unchanged for ~10 years, so really should've been marked 'wontfix'. > Another form of modeling the data in such a manner is called Exclusive Arc, > And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database. That's what I'm doing, with the 'node' table using JSON to store the node-specific data. I feel sooooooo dirty but it works. Yet to figure out schema evolution of the JSON in a controlled manner :)
_Codemonkeyism 8y ago
My two wishes for Postgres (as a fanboy): 1. Unify JSON and SQL syntax, instead of user->>'email' make it user.email 2. Support sub tables, so I can decide if I want a sub table (schema) or JSONB (schema-less)
- TheJeebus 8y ago
  Problem is that JSON property names can contain '.', so it would be ambiguous.
neunhoef 8y ago
Disclaimer: I am one of the core developers of ArangoDB and I work for the company ArangoDB. Yes, ArangoDB is young (6 years) in comparison to PostgreSQL (30 years). Yes, PostgreSQL is a phantastic database with an amazing open source community and is not going away any time soon. Yes, PostgreSQL is a good choice for a project in which you need a relational single server database. However, ArangoDB actually has a different value proposition, so in a sense, it is not a direct competitor. ArangoDB is native multi-model, which means it is a document store (JSON), a graph database and a key/value store, all in one engine and with a uniform query language which supports all three data models and allows to mix them, even in a single query. Furthermore, ArangoDB is designed as a fault-tolerant distributed and scalable system. In addition it is extensible with user defined JavaScript code running in a sandbox in the database server. Finally, we do our best to make this distributed system devops friendly with good tooling and k8s integration. Last but not least, ArangoDB is backed by a company which offers professional support. Therefore any well informed decision for a project needs to look at the value propositions and capabilities and not only at the age and experience, which is of course a big argument, since people are - rightfully so - conservative with their databases.
- djupblue 8y ago
  >is designed as a fault-tolerant distributed and scalable system What is the consistency model and have you validated that it actually works as designed (for example with Jepsen)? I didn't find anything detailed on your website.

manigandham 8y ago

The main thing to compete on is distributed scale-out and high-availability which PostgreSQL does not have a good answer for.

lykr0n 8y ago
CitusDB for sharding & replication. Patroni & Stolon for automated replication. Not native solutions, but are an answer.
- contrahax 8y ago
  CitusDB has a ton of limitations on your schema/table structure right now. Over time it has gotten better, so hopefully in the future it will be a true drop-in scale out solution. Specifically some types of relationships + indexes are not supported.
  - lykr0n 8y ago
    Oh yeah totally; it's a slow march forward. MySQL Galera took some time before it was ready for serious use, but the fact that it's semi-usable is awesome.
  - doh 8y ago
    True, but as you said, the compatibility is quickly increasing. They actually did a great job and there is fairly little that is not compatible, usually on very advanced side (which is exactly what we would love to have), but the team is especially good at what they are doing and believe they will get there sooner than later.
- manigandham 8y ago
  Citus only does sharding, not replication. Either way, this is a fundamental design limitation with traditional single-master relational systems.
  - bsg75 8y ago
    http://docs.citusdata.com/en/v8.1/cloud/availability.html http://docs.citusdata.com/en/v8.1/cloud/availability.html
    - manigandham 8y ago
      Yea, that's just normal Postgres replication, nothing to do with Citus.
  - tpetry 8y ago
    Citus is doing sharding and replication: Citus will shard your data and place every shard on e.g. 3 servers. So one server dying is no problem because the data is replicated 2 times.
    - jbergens 8y ago
      Do they have ACID transactions around writing to all 3 servers? Doesn't that cause bad performance? You might also end up with a LOT of servers unless you starting sharing servers for different shards (but still with different sets of 3 servers for every shard).
    - manigandham 8y ago
      Ah I see that now. However that's not by default and it still only applies to "distributed" tables, not all the data in the database. Combined with a single-master, it's nowhere near the replica functionality provided by natively distributed storage systems.
75dvtwin 8y ago
what about PostgresXL. it is a release v behind Postgres. But it has ACID and cluster. I do not have personal experience with it, but would be interested to hear what folks say. Overall, I agree -- PG with JsonB is extraordinary powerful system, covering document oriented and typical row-oriented use cases. PGs ecosystem with external foreign interfaces, and in memory streaming solutions (like PipelineDB) - must be an envy of any other db ecosystem.
zbentley 8y ago
That's absolutely true. However, most systems that think they need scale-out are either worrying about a problem they don't have yet, suffering performance problems due to poor database design, or have not fully explored logical (dynamic or hash-based) sharding as a solution to their problems. A huge proportion of modern software businesses can run on single-writer RDBMS instances, properly engineered, at a fraction of the operational and implementation cost of a scale-out solution. That applies to hosted and self-managed solutions equally, in my experience.
- zorga 8y ago
  The desire is generally for high availability now and ability to scale out in the future; while a single write db is generally more than enough performance wise, it doesn't give you high availability which is the main desire.
- manigandham 8y ago
  Sure, but scale isn't everything. Operations are far more important, especially when you're a small team. Modern distributed databases scale better but also have better replication, high-availability with automatic failover and no downtime, easier upgrades, easier backups, and generally less maintenance. Removing the single-point of failure with efficient distribution while being able to run easily on docker/kubernetes makes a big difference over a single monolithic database server.
_Codemonkeyism 8y ago
Valid point, for 99% of startups scaling on one machine (todays SSD based multi core, GB memory hardware is very powerful) with read slaves and failover (Patroni) works just fine. Many I meet as a consultant though want to scale out when they have 100 customers.

thdxr 8y ago

Postgres' inability to handle multiple concurrent queries on a single connection makes it a pain when using it for multi-tenant services. With all the SAAS companies out there I'm surprised this issue doesn't get brought up more often

andrewstuart 8y ago
Why not just open another connection?
- chatmasta 8y ago
  Each open postgres connection requires about 10mb of memory overhead IIRC
  - grumpydba 8y ago
    That's why you need a connection pool. That's true for all database systems.
    - cbsmith 8y ago
      Not all databases systems require a connection pool, though it is often a good idea.
      - grumpydba 8y ago
        I disagree. I've faced incidents on oracle/sql server/postgres/sybase instances when several hundreds of connections where spawned. You definitely need to think about pooling fro the beginning of the design of your application.
        cbsmith 8y ago
        Yes, all of those databases allocate query memory for queries at connection time, and consequently don't scale efficiently for large number of idle clients. That's not all databases. Some don't do that. Some don't even do connections period. Connection pools are a necessary work around for a specific design decision with the system software. They can be helpful other problems as well, but they aren't necessarily a requirement.

pddpro 8y ago

Any resources you'd like to recommend to a beginner that comprehensively covers all aspects of Postgres?

brylie 8y ago
PostgreSQL Tutorial covers many fundamentals: http://www.postgresqltutorial.com/ http://www.postgresqltutorial.com/

porker 8y ago

Postgres is powerful, I want a row in a table to have a relationship to one or more rows in _any other table_ in the database, and Postgres can't help with that. That to me is a compelling reason to use something other than Postgres.

ralusek 8y ago
There are a few solutions to what you're proposing which might help. One solution is to have a generic reference property paired with another indicating which table to reference reference_id: '1234', reference_table: 'posts' reference_id: '4321', reference_table: 'comments' You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql https://stackoverflow.com/questions/3074535/when-to-use-inhe... Another form of modeling the data in such a manner is called Exclusive Arc, which is that you just simply put all of the possible keys you might reference on the table, and add a foreign key on all of them. Then, when you need to make a reference, you just leave all but the one in use on that row as null values. If a "like" can go on a "post" or a "comment" or an "image", you would just have all 3 foreign key columns/constraints on the "like" table. And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database.
- porker 8y ago
  > One solution is to have a generic reference property paired with another indicating which table to reference > You can still benefit from indexes for joins in the same way that you would with actual foreign key constraints, but the downside with this is that you can't actually apply a foreign key constraint. Thanks, I had not thought JOINs possible on this - how would a query look? I'm guessing it works only for one type of reference_table. > Another solution you can look into, which I don't have much experience with myself, is table inheritance: https://stackoverflow.com/questions/3074535/when-to-use-inhe.. https://stackoverflow.com/questions/3074535/when-to-use-inhe.... Been there, tried it, banged my head on most of these (particularly the FK limitation): https://www.postgresql.org/docs/11/ddl-inherit.html https://www.postgresql.org/docs/11/ddl-inherit.html :) If it was fixed by Postgres it'd be awesome. I've lost the wiki page which was around on the topic, but it was unchanged for ~10 years, so really should've been marked 'wontfix'. > Another form of modeling the data in such a manner is called Exclusive Arc, > And lastly, and probably best of all, is to simply use 1-2 tables for your entire data model for vertices/edges, and just treat Postgres as a graph database. That's what I'm doing, with the 'node' table using JSON to store the node-specific data. I feel sooooooo dirty but it works. Yet to figure out schema evolution of the JSON in a controlled manner :)

_Codemonkeyism 8y ago

My two wishes for Postgres (as a fanboy): 1. Unify JSON and SQL syntax, instead of user->>'email' make it user.email 2. Support sub tables, so I can decide if I want a sub table (schema) or JSONB (schema-less)

TheJeebus 8y ago
Problem is that JSON property names can contain '.', so it would be ambiguous.

neunhoef 8y ago

Disclaimer: I am one of the core developers of ArangoDB and I work for the company ArangoDB. Yes, ArangoDB is young (6 years) in comparison to PostgreSQL (30 years). Yes, PostgreSQL is a phantastic database with an amazing open source community and is not going away any time soon. Yes, PostgreSQL is a good choice for a project in which you need a relational single server database. However, ArangoDB actually has a different value proposition, so in a sense, it is not a direct competitor. ArangoDB is native multi-model, which means it is a document store (JSON), a graph database and a key/value store, all in one engine and with a uniform query language which supports all three data models and allows to mix them, even in a single query. Furthermore, ArangoDB is designed as a fault-tolerant distributed and scalable system. In addition it is extensible with user defined JavaScript code running in a sandbox in the database server. Finally, we do our best to make this distributed system devops friendly with good tooling and k8s integration. Last but not least, ArangoDB is backed by a company which offers professional support. Therefore any well informed decision for a project needs to look at the value propositions and capabilities and not only at the age and experience, which is of course a big argument, since people are - rightfully so - conservative with their databases.

djupblue 8y ago
>is designed as a fault-tolerant distributed and scalable system What is the consistency model and have you validated that it actually works as designed (for example with Jepsen)? I didn't find anything detailed on your website.