POSTGRESQL VS ORACLE 泰坦之战 (翻译)

一篇 2021年1月份国外的一篇文字关于评论 PG VS ORACLE的文字.看完觉，从技术的角度看，有点LOWER

As the saying goes, change is the only constant in life. Interestingly, this also applies to the tech and software world. Oracle Database has been a globally recognized and appreciated solution for decades, but there is a rising star called PostgreSQL, which is gaining the confidence of developers from all around the world.

The following article will put these two rivals under the microscope to try and determine which is more capable of handling the ever-growing list of challenges in the IT space.

说来有趣, 变化是生活中的唯一的真理, 同样这也适用于技术和软件的行业,ORACLE作为数据库业界的解决方案并且赢得了业界的共识已经数十年了. 可事情就如此了,POSTGRESQL 是这个业界的后起之秀,获得了众多开发业者的认同. 下面这篇文字将从微观的方式来试着阐述双方在面对IT行业的整体不断增长的挑战.

Oracle in a Nutshell

Oracle is one of the largest vendors of Relational Database Management Systems (RDBMS) in the market today. It’s commonly known as Oracle Database or Oracle DB. Oracle Database is being used by many companies in the IT industry for transaction processing, business analytics, and also business intelligence app purposes.

First commercialized in 1979, it was built around a relational database where data could be accessed by users via an application or Structured Query Language (SQL).

In an attempt to accommodate various company sizes and needs, Oracle Database is currently available as Enterprise Edition, Standard Edition, Express Edition and Oracle Lite. Oracle Database runs on all major platforms such as Windows, UNIX, Linux, and MacOS. Its biggest traditional rival has been the popular Microsoft SQL Server.

硬可的"ORACLE"

时至今日,ORACLE 依然是数据库业界最大的软件提供商, ORACLE 依然在不少的公司处理事务化的任务,以及商业分析,和商业智能方面的支持工作. 从它 1979年首次商业化后,建立一个以关系型为基础,通过SQL进行访问的数据库系统. 适应不同的业务需求,ORACLE 本身提供了多个版本，包含企业版,标准版,以及免费的EXPRESS 版本和 ORACLE LITE, 并且ORACLE 运行在多种操作系统, 如 UNIX ,LINUX , MACOS 同时SQL SERVER 也是ORACLE 曾经最大的商业对手。

What is PostgreSQL (Postgres)?

PostgreSQL, commonly known as Postgres, is an open-source and object-relational database management system. It was written in C language and developed by a team of volunteering developers. PostgreSQL didn’t support SQL until 1994 and initially required QUEL to query data from it, making it less than ideal. But things have evolved since then.

那么今天的另一个"POSTGRESQL" 到底是什么 ?

POSTGRESQL , 或者叫POSTGRES, 他是一个开源的和面向对象的数据库DBMS系统, 通过一些开源的C语言的程序员打造了这样的一套数据库系统.POSTGRESQL 本身最早是不支持SQL的,直到1994年后,支持了SQL ,而之前的想法是通过QUEL来进行数据的获取,当然结果不是太让人满意.

Did You Know?

The PostgreSQL license is a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.

这里提示,POSTGRESQL 的LICENSE 是具有自由开源的LICENSE的软件,你可以对他进行修改和分发.

PostgreSQL went open-source route in 1996, after which full SQL support was added. It now supports all features of RDBMS with an addition of other features such as views, stored procedures, indexes, and triggers in addition to the primary key, foreign key and atomicity features.

PG 在1996年在开源世界开始有了自己的"声音",当然这是在他完全支持SQL 语言以后的事情. 并且支持所有的RDBMS的特性,如视图,存储过程,索引和触发器等, 除此以外,主键和外键和原子性.

This system’s functionality can be extended by users by modifying existing features, adding new features and distributed freely. It runs on major platforms such as UNIX, MacOS, Windows, and Linux etc., while supporting programming interfaces for languages such as C/C , Java, Python, Perl, and open database connectivity.

PostgreSQL manages concurrency through Multi-Version Concurrency Control (MVCC), which gives each transaction a " database snapshot", allowing changes to be made without affecting other transactions. This eliminates the need for read locks, while ensuring the database maintains ACID principles.

这套系统支持用户自己扩展和修改已经存在的功能,或添加新的功能,二次开发后发布免费, 他也运行在主流的 UNIX ,MACOS ,WINDOWS, LINUX 等系统中, 支持面向 C C JAVA PYTHON PERL 的开源数据库, PG 通过MVCC 完成数据一致性支持, 让每一个事物都有一个 snapshot, 允许数据变动时并不影响其他读操作, 通过读锁来支持数据库的ACID的特性.

POSTGRESQL 和 ORACLE 正面"刚"

Both methodologies are extremely popular today, but it’s safe to say that PostgreSQL is the one trending upwards to its inherited characteristics, that are more suited to today’s dynamic development requirements. Below is a comparison along 5 crucial parameters that cannot be ignored before choosing a solution and making the move.

两种数据库都是当今流行的数据库,但是稳妥的讲, POSTGRESQL 是一个趋向于继承特性的数据库, 更适合当前的开发方式和需求. 我们通过下面的不可忽略的5种特性来对我们现在的解决方案的选择来进行探讨

Value for Money - PostgreSQL Oracle is a commercial solution that has pretty steep pricing options, with additional payments required for extra features. PostgreSQL easily clinches this comparison since acquisition, installation and support is completely free of charge. This factor alone can prove to be crucial when it comes to small to medium sized organizations.

1 到底值不值

ORACLE 是一个商业的解决方案, 截止目前ORACLE的价格还是令人"难以平静",并且诸多的附加的功能都需要 "钱" 来解决. POSTGRESQL 在支持和安装使用上的免费的特性, 这样的特性对于中小企业来说,是至关重要的.

2 Support – PostgreSQL Another win for the open-source solution. PostgreSQL has an extremely active community where patches, tweaks, updates and more can be found easily. Even answers to questions that arise during installation or upgrades are to be found easily with minimal delays. This is not the case with Oracle, where support costs money.

Large organizations that choose to implement PostgreSQL can also opt for paid support professionals, whose services tend to be cheaper than their Oracle counterparts.

2 关于POSTGRESQL 的支持

开源的方案,最值得被认可的另一个原因就是免费的支持, PG有一个非常活跃的开源社团, 其中包含补丁, 升级,都可以被容易的获得, 包含一些问题的解决都可以在社区快速找到方案解决,而ORACLE 这方面你一定是要花钱解决的.

大型的组织选择POSTGRESQL数据库系统后，也可以选择专业的支持团队但付出的费用远远低于ORACLE对应的支持方式的费用。

3 Functionality – Oracle

Oracle Database pulls one back thanks to decades of experience and high levels of development expertise. It not only provides more transactions per second than PostgreSQL, but also arguably provides higher levels of security. However, it should be noted that many of Oracle’s security features come at an added cost.

3 ORACLE 的功能性

从ORACLE 多年的业界积累的经验,他的确目前可以提供比POSTGRESQL 更多的每秒事务的特性(这点我其实到时觉得,可以测一测), 同时还提供更多的安全特性的选择,然而还的绕回到这些特性,是要付出昂贵的费用的问题上.

Oracle Database is secure and ensures that user data is not tampered with through prompt updates. Its better experience with various industries also gives it the upper hand.

ORACLE 数据库在安全和用户方式数据的篡改,相关的经验是业界认可和公认的 PostgreSQL is no slouch in the functionality front. It offers three levels of transaction isolation: Read Committed, Repeatable Read and Serializable. Its immune to dirty reads. Requesting a Read Uncommitted transaction isolation level provides read committed instead. PostgreSQL supports full serializability via serializable snapshot isolation (SSI).

POSTGRESQL 本身在功能上也是可以和ORACLE正面刚,PG提供的隔离级别,中包含 RC RR SERIALIZABLE 都是齐全的,对于脏读是天生免疫的, 并且PG是支持完全的序列化,以及SNAPSHOT LEVLE的隔离级别, SSI

4 Scalability – Draw While both solutions are quite capable in this category, PostgreSQL possesses the advantage due to its open-source characteristics. Not only is it much lighter than Oracle, you also don’t have to fork out more cash to expand your infrastructure. PostgreSQL is completely capable of accommodating any volume of data.

4 扩展性妥妥的

虽然两种数据库的扩展性都是比较强的, 但POSTGRESQL 本身的扩展与轻量级相对来说是更有力的,对于ORACLE 来说PG 是一个更轻的, 对于使用的成本来说, POSTGRESQL 是完全能HOLD住与ORACLE 同等数据存储.

5 Compatibility – PostgreSQL Oracle has a robust language in PL/SQL, however PostgreSQL allows you to write language handlers in multiple languages (Python, R, etc.) directly in the database.

ORACLE 有一个强有力的PL/SQL的语言系统,PG除了有自己的语言系统同时也支持多种语言（python ,R 等语言的支持）. PostgreSQL also clearly has the edge when it comes to compatibility with operating systems, which is extremely crucial in today’s diverse and complex development environments. FreeBSD, HP-UX, Linux, NetBSD, OpenBSD, OS X, Solaris, Unix and Windows are all compatible with PostgreSQL , which is a big advantage.

Postgresql 在系统的兼容性方面是支持是非常强大的, 这在复杂的开发环境中是非常重要的, 如PG支持 freeBSD, HP-UX, linux, netbsd , openbsd, OS X , Solaris , unix , WINDOW 这是一个大卖点 .

Did You Know?

PostgreSQL currently supports 12 authentication systems, including LDAP, Kerberos, GSSAPI, RADIUS, password-based or even "trust-based" authentication.

It also must be noted that PostgreSQL tends to feel much friendlier for developers, so that they don’t mind installing and reinstalling development versions on their laptops. In contrast, more and more developers are looking to avoid installing Oracle as much as possible, given its complexity and the overhead involved.

Postgresql 当前支持12种验证的方式包含 LDAP, Kerberos, GSSAPI, RADIUS, password-based 以及基于信任方式的认证模式。

虽然POSTGRESQL是免费的，可对于开发者来说他更友好，你不会担心必须安装开发版本在你的笔记本上，换句话来说，更多的开发者基于ORACLE的复杂性和开销，而避免安装ORACLE在自己的开发工作平台上。