Database as Filesystem

Database as Filesystem

Database as Filesystem: Most network file systems are either a layer over an existing filesystem (NFS, CIFS), or are developed from scratch to have separate, replicated, purpose-designed databases for metadata and object store (GFS, Glusterfs). At the same time, most database engines provide (or can be coerced into providing) replication and all the ACID properties needed for a high-performance filesystem.

Most network file systems are either a layer over an existing filesystem (NFS, CIFS), or are developed from scratch to have separate, replicated, purpose-designed databases for metadata and object store (GFS, Glusterfs). At the same time, most database engines provide (or can be coerced into providing) replication and all the ACID properties needed for a high-performance filesystem.

Idea: Use a database engine (Postgres, MariaDB) on raw partitions with a fast separate nVME log file; build POSIX file system semantics on top. It's pretty obvious that this could work; I'm just starting to implement it so performance and durability can be measured.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about Databases

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

How to write SQL queries in PostgreSQL

The performance benchmarks of Redis and MySQL

Database Caching With Redis and Java

How to write a simple toy database in Python

How to using multiple database in nodejs

MySQL vs PostgreSQL - What to Choose the Right Database for Your Project

MySQL vs PostgreSQL - What to Choose the Right Database for Your Project

Perhaps you're building a brand new project. Or maybe your current database isn't working well. Choosing the right database for your project can be given by MySQL or PostgreSQL?

Perhaps you're building a brand new project. Or maybe your current database isn't working well. Choosing the right database for your project can be given by MySQL or PostgreSQL?

The choice of a database management system is usually an afterthought when starting a new project, especially on the Web. Most frameworks come with some object-relational mapping tool (ORM) which more or less hides the differences between the different platforms and makes them all equally slow. Using the default option (MySQL in most cases) is rarely wrong, but it’s worth considering. Don’t fall into the trap of familiarity and comfort – a good developer must always make informed decisions among the different options, their benefits and drawbacks.

Database Performance

Historically, MySQL has had a reputation as an extremely fast database for read-heavy workloads, sometimes at the cost of concurrency when mixed with write operations.

PostgreSQL, also known as Postgres, advertises itself as “the most advanced open-source relational database in the world”. It was built to be feature-rich, extendable and standards-compliant. In the past, Postgres performance was more balanced - reads were generally slower than MySQL, but it was capable of writing large amounts of data more efficiently, and it handled concurrency better.

The performance differences between MySQL and Postgres have been largely erased in recent versions. MySQL is still very fast at reading data, but only if using the old MyISAM engine. If using InnoDB (which allows transactions, key constraints, and other important features), differences are negligible (if they even exist). These features are absolutely critical to enterprise or consumer-scale applications, so using the old engine is not an option. On the other hand, MySQL has also been optimized to reduce the gap when it comes to heavy data writes.

When choosing between MySQL and PostgreSQL, performance should not be a factor for most run-of-the-mill applications – it will be good enough in either case, even if you consider expected future growth. Both platforms are perfectly capable of replication, and many cloud providers offer managed scalable versions of either database. Therefore, it’s worth it to consider the other advantages of Postgres over MySQL before you start your next project with the default database setting.

Postgres Advantages over MySQL

Postgres is an object-relational database, while MySQL is a purely relational database. This means that Postgres includes features like table inheritance and function overloading, which can be important to certain applications. Postgres also adheres more closely to SQL standards.

Postgres handles concurrency better than MySQL for multiple reasons:

Postgres implements Multiversion Concurrency Control (MVCC) without read locks Postgres supports parallel query plans that can use multiple CPUs/cores Postgres can create indexes in a non-blocking way (through the CREATE INDEX CONCURRENTLY syntax), and it can create partial indexes (for example, if you have a model with soft deletes, you can create an index that ignores records marked as deleted) Postgres is known for protecting data integrity at the transaction level. This makes it less vulnerable to data corruption.

Default Installation and Extensibility of Postgres and MySQL

The default installation of Postgres generally works better than the default of MySQL (but you can tweak MySQL to compensate). MySQL has some outright weird default settings (for example, for character encoding and collation).

Postgres is highly extensible. It supports a number of advanced data types not available in MySQL (geometric/GIS, network address types, JSONB which can be indexed, native UUID, timezone-aware timestamps). If this is not enough, you can also add your own datatypes, operators, and index types.

Postgres is truly open-source and community-driven, while MySQL has had some licensing issues. It was started as a company product (with a free and a paid version) and Oracle’s acquisition of MySQL AB in 2010 has led to some concerns among developers about its future open source status. However, there are several open source forks of the original MySQL (MariaDB, Percona, etc.), so this is not considered a huge risk at the moment.

When to Use MySQL

Despite all of these advantages, there are still some small drawbacks to using Postgres that you should consider.

Postgres is still less popular than MySQL (despite catching up in recent years), so there’s a smaller number of 3rd party tools, or developers/database administrators available.

Postgres forks a new process for each new client connection which allocates a non-trivial amount of memory (about 10 MB).

Postgres is built with extensibility, standards compliance, scalability, and data integrity in mind - sometimes at the expense of speed. Therefore, for simple, read-heavy workflows, Postgres might be a worse choice than MySQL.

These are only some of the factors a developer might want to consider when choosing a database. Additionally, your platform provider might have a preference, for instance Heroku prefers Postgres and offers operational benefits to running it. Your framework may also prefer one over the other by offering better drivers. And as ever, your coworkers may have opinions!

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

SQL Full Course - SQL Tutorial For Beginners - MySQL Essentials - SQL Training

This SQL Full Course video will cover all the topics of SQL starting from scratch. This video is great for beginners who want to learn SQL and for advanced people to brush up their skills.


Thanks for watching

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Learn More

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

MySQL Databases With Python Tutorial

SQL vs NoSQL or MySQL vs MongoDB

MySQL Database Tutorial for Beginners to Advanced Part 1/2

An Introduction to Queries in MySQL

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

Build a Basic CRUD App with PHP and MySQL

Learn Database Administration - PostgreSQL Database Administration (DBA) for Beginners

In this video, we will go over the basics of the PostgreSQL. We will cover topics ranging from installations, to writing basic queries and retrieving data from tables. We will also explore the logic of joining tables to retrieve data and much more.

The course also covers the basics of creating tables, storing data with data types, and working with expressions, operators, and strings.

Topics also includes:

Installing PostgreSQL

Loading sample database

Creating database and tables

Performing CRUD operations

Joining Tables

Using aggregate and analytic functions

Creating views and triggers

What you’ll learn

Install PostgreSQL Server

Load sample database

Create a database

Create a table

Insert data into tables

Update existing records inside a table

Delete Records in a table

Remove duplicate records

Query data from a table

Create a subquery

Get data from multiple tables

Create and manage roles

Create a view

Create tablespace

Backup and restore database

Filter and sort data

Use various operators

Use aggregate and analytic functions

Create triggers

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

An Introduction to Queries in PostgreSQL

Build a Basic App with Spring Boot and JPA using PostgreSQL

Why We Moved From NoSQL MongoDB to PostgreSQL?