Oracle vs My Sql vs MS SQL vs Postgre

There’s always been some contention over which SQL database tool is superior. Every administrator has their preferences, and every programmer has their own unique way of dealing with code. For that reason, if you ask five different sys- -admins which platform they prefer, you’re like to receive five different answers.

Let’s see if we can’t help you come to your own answer.

Today, we’re going to look at some of the major database management systems currently available on the web in regards to their pros and cons. Once we’ve worked that part out, we’ll offer up a few recommendations as to which projects each one is best-suited for. Let’s dive right in.

Oracle

• Closed-source; free version has very limited feature set
• Temporary tables persist across sessions, and must be removed by the user
• Support for four different character/string types: CHAR, VARCHAR2, NCHAR, NVARCHAR2
• Offers both table and row locking
• Extensive and flexible storage customization with commands like tablespace, synonym, and packages
• Extensive backup mechanisms
• Designed to manage tables and databases on a large-scale basis

MySQL

• Open-source
• Compatible with a wide range of engines and interfaces; one of the most mature databases on the market
• Lightweight
• One of the most popular database tools; easy to find support online
• Temporary tables are only visible within the current active session, and are removed automatically afterwards.
• Lacks ACID compliance
• Can partition tables via LIST, HASH, RANGE, and SET
• Support for two different character/string types: CHAR and VARCHAR
• Offers only table locking
• Lacks options for table views
• Limited storage customization
• Admin tools are incredibly powerful
• Two backup mechanisms: mysqlhotcopy and mysqldump
• Experiences significant performance degradation at high scale.
• Provides little in the way of performance optimization
• Issues with reliability
• Limited security compared to some other database systems.
• Designed for transactional workloads, and as such is ill-suited for analytical workloads

MS SQL Server

• Closed-source, aimed at corporate/enterprise environments.
• Offers full support for common table expressions
• Requires a deeper understanding of databases/database configuration than other tools
• Can fine-tune security features, such as who can run each stored procedure, who can access data, etc.
• Utilizes an engine that’s slightly slower and resource-heavy, but fully ACID compliant.
• Extremely comprehensive reporting system/storage customization
• High degree of control over transactions and procedures
• Community is not terribly active, not as much support online as MySQL.
• Offers more than simple database functionality through a suite of tools: an ETL tool, analytical DBMS, relational DBMS, and reports server.
• Schema changes do not lock tables.
• Relatively high resource footprint

DB2

• Closed-source. Enterprise version only available for a price
• Schema-based table management
• Does not support XML
• Can only partition tables via sharding
• No in-memory capabilities
• Designed for relational integrity
• More robust table/data management than MySQL
• Materialized table views
• Lacks native character/string support
• Multiple options for disaster recovery, availability, and scalability

PostgreSQL

• Open-source
• Adheres well to current SQL standards, and easier to learn as a result
• Large footprint makes it ill-suited for read-heavy operations
• Advanced business/location analytics features
• Rich variety of data and character types
• Fully ACID-compliant
• Designed for reliability and data-integrity; developer-focused
• Full-text search, support for powerful server-side procedural languages
• Full support for advanced SQL features such as table expressions and window functions
• Can efficiently join large numbers of tables
• Replication is poorly-implemented
• Not well-suited for low-concurrency projects

So, Which Should You Use?

As you can no doubt tell, each of the database tools we’ve profiled here is vastly different in terms of functionality and design focus. The question of which is the right choice for you therefore depends on the type of project you plan to manage.

You should use Oracle if…

• You require flexibility in terms of transaction control
• You plan to host a large database
• You require a high degree of scalability
• You want your database to be platform-independent

You should use MySQL if…

• You aren’t going to scale to any large degree
• You’re planning to create a read-only web app or a website
• You require a high degree of replication
• Your project only requires simple queries, and has a low concurrency rate

You should use MS SQL Server if…

• You’re working in a .NET development environment.
• Your database serves a large corporate/enterprise environment
• You’re processing workloads rather than developing applications
• You require fine-tuned control over your database.

You should use DB2 if…

• Your company already maintains an in-house DB2 Installation
• You wish to federate data from multiple sources
• You need to be able to access data at high speed
• Performance optimization is extremely important to your project

You should use PostgreSQL if…

• You plan to make use of complex custom procedures
• You’re going to be working Java
• Your database will be large and complex, with high concurrency and multiple query types
• You will be carrying out many write operations, and read speed is not a factor
• Your project is developer-focused