PostgreSQL 与 Oracle

Postgresql vs Oracle

提问by Mayank

Well, the subject suggests the question:

好吧，这个主题提出了一个问题：

1. Oracle has a good support, so has enterpriseDB
2. Huge amount of data can be easily managed by Oracle, Specs of PostgreSql suggest the same
3. Feature wise I'm not sure, but reading on internet about both the DBs result in a tie

1. Oracle 有很好的支持，enterpriseDB 也有
2. Oracle 可以轻松管理海量数据，PostgreSql 的规范建议相同
3. 功能方面我不确定，但在互联网上阅读有关这两个数据库的信息会导致平局

I wonder now, why oracle is more popular.

我现在想知道，为什么 oracle 更受欢迎。

Which DB should be considered (leave the license/money thing) for a website which should be scaleable and may have large number of active users (lets say 10,000 users online).

对于一个应该可扩展并且可能拥有大量活跃用户（假设有 10,000 名在线用户）的网站，应该考虑哪个数据库（留下许可证/金钱的事情）。

I'm not looking for anything like Cassandra, mongo DB etc...

我不是在寻找像 Cassandra、mongo DB 等的任何东西......

采纳答案by lzap

Ask yourself these questions:

问问自己这些问题：

Whois going to supportyour website (and database)? There are enterprise db companies out there which can provide world wide support, documentation, consultants etc. It is not just Oracle, but it is very likely that it's easier to find Oracle or MSSQL specialist in every country.

谁是要支持你的网站（和数据库）？有一些企业数据库公司可以提供全球范围的支持、文档、顾问等。不仅仅是 Oracle，而且很可能在每个国家/地区都可以更轻松地找到 Oracle 或 MSSQL 专家。

Now, if you are willing to have more challenge to find PostgreSQL specialists or you are willing to train your team for PostgreSQL, go for it! Today (2016) PostgreSQL is the most advanced open-sourceRDBM and is on-par for most deployments with commercial competition.

现在，如果您愿意接受更多挑战来寻找 PostgreSQL 专家，或者您愿意为 PostgreSQL 培训您的团队，那就去吧！今天（2016 年）PostgreSQL 是最先进的开源RDBM，对于大多数具有商业竞争的部署来说是不相上下的。

Are there required featuresthat only one database system offers? You cannot use advanced features of both database platforms, so checkwhat it is that your application really needs. For example, if you require specific replication, backups and other scenarios, you should read documentation for both platforms to make an educated choice. They both offer similar features, but its better to be prepared.

是否有只有一种数据库系统提供的必需功能？您不能同时使用这两种数据库平台的高级功能，因此请检查您的应用程序真正需要的是什么。例如，如果您需要特定的复制、备份和其他方案，您应该阅读这两个平台的文档以做出明智的选择。它们都提供相似的功能，但最好做好准备。

Big companies usually stick with one database platform. When they buy an Oracle license once (and employ DBAs and other specialists), they continue using that vendor. That's the usual scenario, but I assume you're free from such baggage.

大公司通常坚持使用一个数据库平台。当他们购买一次 Oracle 许可证（并雇用 DBA 和其他专家）时，他们会继续使用该供应商。这是通常的情况，但我假设您没有这种包袱。

Also in the modern IT era, another question is to consider. Do I really need RDBMS? Every insert/update/delete costs very much. When you define foreign keys and other constraints it costs even more. It's like 1 to 10 operations when comparing MongoDB vs Oracle. On the other hand, RDBMS gives you more safety and data integrity. If you don't need it (i.e. lost comment is not an issue for you) you could go for MongoDB and others. Beware that usually you want RDBM in 9 out of 10 cases tho!

同样在现代 IT 时代，另一个问题是需要考虑的。我真的需要 RDBMS 吗？每次插入/更新/删除的成本都非常高。当您定义外键和其他约束时，它的成本甚至更高。比较 MongoDB 与 Oracle 时，这就像 1 到 10 次操作。另一方面，RDBMS 为您提供更高的安全性和数据完整性。如果您不需要它（即丢失评论对您来说不是问题），您可以选择 MongoDB 和其他。请注意，通常情况下，10 个案例中有 9 个都需要 RDBM！

回答by a_horse_with_no_name

My opinion is that PostgreSQL is very close to Oracle, especially with the upcoming 9.1 which offers an alternative to Oracle's DataGuard.

我的观点是 PostgreSQL 非常接近 Oracle，尤其是即将推出的 9.1，它提供了 Oracle DataGuard 的替代方案。

On the SQL Level there are really head-to-head, not much difference (with Postgres having actually more "little helpers" and features that make life a lot easier).

在 SQL 级别上确实是正面交锋，没有太大区别（Postgres 实际上有更多的“小助手”和使生活更轻松的功能）。

Things where Oracle offers still more features:

Oracle 提供更多功能的地方：

• Materialized views(available since Postgres 9.3, but Postgres still has no incremental refresh or query rewrite)
• Flashback queries and flashback archives (they are reallycool)
• Index only scans(available since Postgres 9.2, but not as efficient as Oracle's implementation in my experience)
• transaction control in stored procedures (there will be support for that in the upcoming Postgres 11, but not as complete as in Oracle)
• Index organized tables (aka "clustered indexes")
• ASH and AWR reports (Postgres 9.6 added the infrastructure to have something like that in the future)
• parallel queries
  Postgres 9.6 added some basic ability to run queries in parallel, Postgres 10 improved that even more and Postgres 11 will even be able to use it for things like create table as
• parallel DML
• Changing tables that are used in views (e.g. drop a column)

• 物化视图（自 Postgres 9.3 起可用，但 Postgres 仍然没有增量刷新或查询重写）
• 闪回查询和闪回档案（它们真的很酷）
• 仅索引扫描（自 Postgres 9.2 起可用，但根据我的经验不如 Oracle 的实现有效）
• 存储过程中的事务控制（将在即将发布的 Postgres 11 中提供支持，但不像 Oracle 中那样完整）
• 索引组织表（又名“聚集索引”）
• ASH 和 AWR 报告（Postgres 9.6 添加了基础设施以在未来拥有类似的东西）
• 并行查询
  Postgres 9.6 添加了一些并行运行查询的基本能力，Postgres 10 改进了更多，Postgres 11 甚至可以将它用于诸如create table as
• 并行 DML
• 更改视图中使用的表（例如删除列）

As much as I like PostgreSQL one thing that can be really annoying is configuring (auto)vacuum to cope with high write traffic.

尽管我很喜欢 PostgreSQL，但真正令人讨厌的一件事是配置（自动）vacuum 以应对高写入流量。

回答by Denis de Bernardy

Oracle is more popular than PostgreSQL for the same reason that Microsoft is more popular than Linux in larger companies: no head of IT with a budget has ever lost his job for choosing them over an open source alternative.

Oracle 比 PostgreSQL 更受欢迎，原因与 Microsoft 在大公司中比 Linux 更受欢迎的原因相同：没有预算的 IT 主管因为选择它们而不是开源替代品而失去工作。