4 ms·

I have experience with Oracle, Postgres, MS SQL Server and DB2. Postgres is in the same range of relational enterise SQL database engines. Postgres offers even

by frik 9y ago

I have experience with Oracle, Postgres, MS SQL Server and DB2. Postgres is in the same range of relational enterise SQL database engines. Postgres offers even Oracle PL/SQL compatible syntax, so you can think of Postgres as the Linux of relational databases. (Linux is a clone of Unix). If you need advanced SQL syntax, XML support, complex triggers, inlined procedural code, GIS, etc look no further than this and choose Postgres. If you just want to hit your DB with thousends of connections from your web application frameworks and public APIs or think of easy clustering, it might be a good idea to add a caching layer in-front like memcached/redis or read on... (as the forking model doesn't scale that good) And then there is a unique database-software with a common SQL dialect that supports dozends of database engines. It's called MySQL and it supports plugin-engines like InnoDB (default, true web scale, very fast), ISAM (old 1980s style features, very fast), etc. MySQL can handle many concurrent connections and InnoDB is really good, that's why it's a very good fit for web apps, using basic SQL features and used by Google, Twitter, Facebook, etc as a main production database. And there are NoSQL databases that have different kinds of features like MongoDB/RethinkDB, Lucene (Solr/ElasticSearch), Hadoop (HBase, etc), Cassandra, etc. - they have often just basic SQL-like query language or non at all, but a custom API to interact with the datastore. Those domain specific solutions are often very fast for certain use cases. Some have limited index support, limited join support, transactions, etc. so the it really depends. E.g. for JSON datastore and full text search, those are ideal solutions. We would need a database guide that highlights the common open source database engines. And provide a transition guide and compatibility matrix compared to legacy binary blob database engines - the real competitors to open source. People in the open source communities are often fishing in other communities and try to convert them - instead look no further than your corporate colleagues and enterprise fellows and try to convert them away from their rusty databases.

jeltz 9y ago
You seem mistaken about what the real costs of the forking model. To solve the cost of opening new connections you just need to use a connection pooler. The need for caching is not really relevant here. PostgreSQL can easily handle hundreds of thousands of read queries. The real costs are: 1. Need for a third party connection pooler (built-in in many client libraries like JDBC, ActiveRecord or Sequel). Some poolers like pgboucner lack support for prepared statements. 2. Memory usage can become an issue, especially if you have tens of thousands of stored procedures since stored procedures are compiled and cached per connection. Also working memory for sorting is per connection which means PostgeSQL will use more memory than MySQL on some workloads. 3. Having to recompile all stored procedures on first use after reconnecting to the database can be an issue.

by frik 9y ago

jeltz 9y ago

You seem mistaken about what the real costs of the forking model. To solve the cost of opening new connections you just need to use a connection pooler. The need for caching is not really relevant here. PostgreSQL can easily handle hundreds of thousands of read queries. The real costs are: 1. Need for a third party connection pooler (built-in in many client libraries like JDBC, ActiveRecord or Sequel). Some poolers like pgboucner lack support for prepared statements. 2. Memory usage can become an issue, especially if you have tens of thousands of stored procedures since stored procedures are compiled and cached per connection. Also working memory for sorting is per connection which means PostgeSQL will use more memory than MySQL on some workloads. 3. Having to recompile all stored procedures on first use after reconnecting to the database can be an issue.