In Data Engineer’s Lunch #8: SQL Databases part 2, we continued our discussion of relational concepts, popular SQL databases, and advantages and disadvantages. The live recording of the Data Engineer’s Lunch, which includes a more in-depth discussion, is also embedded below in case you were not able to attend live. If you would like to attend a Data Engineer’s Lunch live, it is hosted every Monday at noon EST. Register here now!
This week’s lunch was a continuation of last week’s talk on SQL databases. This week, we extended the topic to cover Cloud Databases and database tools compatible with SQL databases. The first part can be found here.
File-based SQL Databases
Our first topic this week was to extend the taxonomy of databases we built last week. Within this topic, we discussed using CSV files to store data as a type of filesystem or in-memory database. We also go over which of the file/in-memory databases users might still have reason to use today, as well as their advantages and disadvantages.
- File-based Relational Storage (In Memory Query)
- H2
- SQLite
- CSV/YAML (not recommended)
- Access*
- SQL Server Database Engine/Express
- Sybase SQLAnywhere
We then moved on to open source databases. DB-engines allowed us to analyze the different open source databases and get a relative ranking of relational databases and trends in database usage. We also covered tech stacks including open source databases that may have contributed to their relative popularity. We also briefly talked about the underlying storage engines for relational databases.
- Open Source
- MySQL / MariaDB / GaleraDB / AWS MySQL RDS / AWS AuroraDB for MySQL / TiDB
- Linux Apache HTTPD / MySQL Per / PHP / Python (LAMP)
- phpMyAdmin
- Different Data Storage Engines
- MyISAM
- InnoDB
- InnoDB has row-level locking. MyISAM only has full table-level locking.
- InnoDB has what is called referential integrity which involves supporting foreign keys (RDBMS) and relationship constraints, MyISAM does not (DMBS).
- InnoDB supports transactions, which means you can commit and rollback. MyISAM does not.
- InnoDB is more reliable as it uses transactional logs for auto-recovery. MyISAM does not.
- Stored Procedure
- MySQL / MariaDB / GaleraDB / AWS MySQL RDS / AWS AuroraDB for MySQL / TiDB
- Postgres / Greenplum / CockroachDB / YugaByteDB / Citus
- More Data Types
- Transactions Support
- Mongo compliant database on top of Postgres
- Postgres / Federation
- Stored Procedure
Cloud SQL Databases
We then moved on to discussing could managed databases. We discussed their similarities in terms of the interface to normal relational databases as well as their advantages in terms of stability.
- Cloud Managed Databases
- Aiven
- AWS
- Azure
- Spanner – Postgres
- YugabyteCloud – Postgres
- CockroachDBCloud – Postgres
SQL Database Tools
Finally, we discussed a couple of external tools for working with SQL databases.
- Tool
- https://dbeaver.io/
- https://www.navicat.com/en/
- https://www.dbvis.com/
- https://www.jetbrains.com/datagrip/
Cassandra.Link
Cassandra.Link is a knowledge base that we created for all things Apache Cassandra. Our goal with Cassandra.Link was to not only fill the gap of Planet Cassandra but to bring the Cassandra community together. Feel free to reach out if you wish to collaborate with us on this project in any capacity.
We are a technology company that specializes in building business platforms. If you have any questions about the tools discussed in this post or about any of our services, feel free to send us an email!
