Alternative Databases

Owned by Kevin Lau
Nov 05, 2022
3 min read

Database Alternatives Research

MySQL:

  • Open source 

  • Key features: Scalability, Security, and Replication

Connection Details:

  • MySQL is a relational database, has a client-server architecture and can be used in a networked environment. 

  • Users can write SQL requests to the MySQL server, which the application responds with the appropriate information

Pros:

  • 24/7 Uptime

  • Security, as passwords are encrypted 

  • Multi-user support (good for distributed systems)

  • Extremely scalable and can hold up to 8TB of data (50 million rows of data)

Disadvantages:

  • Not as time efficient with larger databases 

  • Poor bulk insert and full-text search operations

  • Prone to data corruption

  • Less developing and debugging tools vs other databases 

Python Support Libraries:

  • Need to install a MySQL connector driver (mySQLdb), which is then imported into Python 

PostgreSQL:

  • Open source relational database management system

  • Key Features: Scientific Analysis, Versatility

Connection Details:

  • Traditional Client-Server Model - central “post master” controls database connections & files for communication

  • Supports SQL/JSON data

Pros:

  • Compatibility with analysis tools such as Matlab, R, and POSTGis (geodata analysis extension)

  • Easier to distribute stored data across servers and works with popular web apps like Django or Node.js

  • Fault tolerant 

Disadvantages:

  • Room for improvement when it comes to speed (slower than MySQL)

  • More open source apps are compatible with MySQL vs PostgreSQL

  • More complex to install and get started with 

Python Support Libraries:

  • Requires a connector driver to be installed (psycopg2), which is then imported to Python

MongoDB

  • Open source database storage 

  • Key Features: Performance, simplicity, flexibility

Connection Details:

  • Open-source document oriented database

  • NoSQL, BSON data

  • Stores data objects in collections and documents rather than traditional table and rows

  • Key-value pairs in data

Pros:

  • High speed & performance - utilizes RAM and cache (better on more powerful system)

  • Flexible - it is a NoSQL database system so it has both structured and unstructured data. Can store any data type and is not restricted by a schema.  

  • Simplicity - easy and fast installation & setup

  • Scalability - More scalable since it uses horizontal scalability vs vertical with SQL databases

Cons:

  • Doesn’t support joins - pulling and combining different documents of data collections is messy and requires many queries

  • High memory usage

  • Could lead to duplicate data - easy to end up with duplicates without a easy way to remove them

Python Support Libraries:

  • Requires connection driver (PyMongo), which is then imported to Python

Redis

  • Non-relational open source data storage

  • Key Feature: Speed

 

Connection Details:

  • Open source, in-memory data structure storage system

  • Supports complex data structures: lists, sets, hashes

  • Mapped key-value caching system

  • No strict schemas

Pros:

  • Speed - fastest caching

  • Easy setup, easy to use

  • Flexible data structures - supports nearly all

  • Zero downtime or performance hit while scaling

Cons:

  • Requires a decent amount of RAM for large datasets since it is in-memory

  • Queries - not as efficient because it is a key-value database

  • Security - only basic security options and no encryption

Python Support Libraries:

  • Must install and run a Redis server on the PC

  • Import Redis library into Python

References

Databases - Full Stack Python

Python Databases 101: Which to Choose? | Built InUnderstanding the key MongoDB pros and cons - ThinkAutomation

PostgreSQL: features of the open source database - IONOS

The Pros and Cons of MongoDB - Virtual-DBA Remote DBA Services & Support - Certified Database ExpertsHow MongoDB works ? - GeeksforGeeks