Developers
August 31, 2020

Multi-User Databases Compared—Part One

Not all multi-user databases are created equal. Here’s a look at the top contenders.

Databases are one of the most important building blocks of the modern tech industry. Everything from the most basic customer address book to complex web applications all run on databases.

While virtually every major application uses databases to some degree or another, many of these are embedded single-user databases. These are ideal for desktop and mobile applications, where a single user will be accessing them at any given time.

For networked apps, web applications, and cloud computing platforms, a much more robust solution is needed. This is where multi-user databases come in.

Multi-User Databases

Multi-user databases are the ideal solution for web, cloud, and enterprise applications. To serve many people simultaneously these databases are designed to handle reading and writing in a way that doesn’t lock up the entire database.

Most databases can handle multiple read operations without any issue. Issues arise when there are multiple attempts to write data to the same database. To prevent data corruption, most databases must be locked to prevent two simultaneous write operations from happening to the same record.

The difference between a single and multi-user database is the degree of concurrency they provide. For example, single-user databases like SQLite must lock the entire database when a write operation occurs. A true multi-user database has more granular concurrency, providing the ability to lock a single table, or even a single record, rather than the entire database. As a result, a multi-user database provides the ability for multiple people to not only read but also perform write operations simultaneously.

There are a number of databases that are well-known for providing this kind of functionality and are ideally suited to multi-user environments.

MySQL

One of the biggest names in the database world is MySQL. Originally MySQL was the creation of David Axmark and Michael "Monty" Widenius. The two began work on the database in 1994 and went on to establish the company MySQL AB, along with Allan Larsson in 1995, the same year MySQL was officially released.

Originally, MySQL was based on the same APIs that mSQL used. mSQL was a database that offered far more reasonable pricing than competitors of the day. One of the goals with MySQL was to improve the performance over mSQL. In time, however, MySQL almost completely supplanted mSQL in most uses and went on to become far bigger. In 2008, MySQL was purchased by Sun Microsystems, eventually being acquired by Oracle when it purchased Sun.

MySQL is one of the most popular client-server databases. In fact, it is so popular that it is an integral part of the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) web services stack. YouTube, PayPal, LinkedIn, Facebook, Twitter, eBay, Verizon, Netflix, Walmart, Bank of America, Tesla, GitHub, and countless others rely on the database on a daily basis. MySQL has a number of advantages that account for its popularity.

First and foremost is the cost. Thanks to a free open-source version, organizations can get up and running at no cost. Even the paid enterprise edition is substantially cheaper than competing products.

Compared to some other databases, MySQL is also incredibly easy to use. Much of this is thanks to its widespread use in the developer world, with countless tools designed to help with deployment. This third-party ecosystem is also a major benefit when it comes to getting support. Because the database is so widely used, there are a plethora of resources available.

MySQL has a well-deserved reputation for security, a major factor in its widespread adoption by countless large companies.

Few databases offer the scalability of MySQL. From small embedded applications to enterprise-level operations, MySQL has the ability to scale to virtually any need.

PostgreSQL

PostgreSQL is another popular option in the realm of multi-user databases. Released in 1996, PostgreSQL has earned a reputation as one of the most advanced open-source databases in existence, rivaling the abilities and complexity of commercial, enterprise databases.

PostgreSQL takes a slightly different approach to MySQL. While both databases are extremely capable, many consider PostgreSQL to be the better option out-of-the-box, especially for newer developers. MySQL can be configured to offer much of the same advantages as PostgreSQL, but it does take configuration. In contrast, PostgreSQL is ready to go almost immediately, with little to no configuration.

PostgreSQL also handles concurrency differently than MySQL. In addition to offering parallelism, where queries can be split across CPU threads, PostgreSQL features multi-version concurrency control (MVCC). MVCC solves the problems related to locking a database for writes by, instead, using a type of version control. When someone is reading data from the database, they are seeing the data from that moment in time. Meanwhile, another user can be writing data to that record, but the database will create a newer version, while the first user continues to see the older one. Once the transaction is complete or committed, then the data is updated for all users. Because the read and write are occurring on two different versions of the record, there is no need to lock anything.

In times past, there were differences in performance between MySQL and PostgreSQL, with the latter being slower due to the additional features. In recent years, however, the performance gap has narrowed significantly. This has been as a result of MySQL adding some of the data protection features PostgreSQL has always had, as well as PostgreSQL’s implementation speeding up.

PostgreSQL also has the advantage of being an object-relational database, as opposed to simply a relational database. Again, this can be easier for newer programmers to pick up, as it provides a layer of abstraction when compared with MySQL or other relational databases.

Conclusion

In Part One we’ve looked at what makes a multi-user database, as well as two of the most popular implementations. In Part Two, we’ll look at additional examples, as well as what database you should choose.

TagsMySQLPostgreSQLDatabases
Matt Milano
Technical Writer
Matt is a tech journalist and writer with a background in web and software development.

Related Articles

Back
DevelopersAugust 31, 2020
Multi-User Databases Compared—Part One
Not all multi-user databases are created equal. Here’s a look at the top contenders.

Databases are one of the most important building blocks of the modern tech industry. Everything from the most basic customer address book to complex web applications all run on databases.

While virtually every major application uses databases to some degree or another, many of these are embedded single-user databases. These are ideal for desktop and mobile applications, where a single user will be accessing them at any given time.

For networked apps, web applications, and cloud computing platforms, a much more robust solution is needed. This is where multi-user databases come in.

Multi-User Databases

Multi-user databases are the ideal solution for web, cloud, and enterprise applications. To serve many people simultaneously these databases are designed to handle reading and writing in a way that doesn’t lock up the entire database.

Most databases can handle multiple read operations without any issue. Issues arise when there are multiple attempts to write data to the same database. To prevent data corruption, most databases must be locked to prevent two simultaneous write operations from happening to the same record.

The difference between a single and multi-user database is the degree of concurrency they provide. For example, single-user databases like SQLite must lock the entire database when a write operation occurs. A true multi-user database has more granular concurrency, providing the ability to lock a single table, or even a single record, rather than the entire database. As a result, a multi-user database provides the ability for multiple people to not only read but also perform write operations simultaneously.

There are a number of databases that are well-known for providing this kind of functionality and are ideally suited to multi-user environments.

MySQL

One of the biggest names in the database world is MySQL. Originally MySQL was the creation of David Axmark and Michael "Monty" Widenius. The two began work on the database in 1994 and went on to establish the company MySQL AB, along with Allan Larsson in 1995, the same year MySQL was officially released.

Originally, MySQL was based on the same APIs that mSQL used. mSQL was a database that offered far more reasonable pricing than competitors of the day. One of the goals with MySQL was to improve the performance over mSQL. In time, however, MySQL almost completely supplanted mSQL in most uses and went on to become far bigger. In 2008, MySQL was purchased by Sun Microsystems, eventually being acquired by Oracle when it purchased Sun.

MySQL is one of the most popular client-server databases. In fact, it is so popular that it is an integral part of the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) web services stack. YouTube, PayPal, LinkedIn, Facebook, Twitter, eBay, Verizon, Netflix, Walmart, Bank of America, Tesla, GitHub, and countless others rely on the database on a daily basis. MySQL has a number of advantages that account for its popularity.

First and foremost is the cost. Thanks to a free open-source version, organizations can get up and running at no cost. Even the paid enterprise edition is substantially cheaper than competing products.

Compared to some other databases, MySQL is also incredibly easy to use. Much of this is thanks to its widespread use in the developer world, with countless tools designed to help with deployment. This third-party ecosystem is also a major benefit when it comes to getting support. Because the database is so widely used, there are a plethora of resources available.

MySQL has a well-deserved reputation for security, a major factor in its widespread adoption by countless large companies.

Few databases offer the scalability of MySQL. From small embedded applications to enterprise-level operations, MySQL has the ability to scale to virtually any need.

PostgreSQL

PostgreSQL is another popular option in the realm of multi-user databases. Released in 1996, PostgreSQL has earned a reputation as one of the most advanced open-source databases in existence, rivaling the abilities and complexity of commercial, enterprise databases.

PostgreSQL takes a slightly different approach to MySQL. While both databases are extremely capable, many consider PostgreSQL to be the better option out-of-the-box, especially for newer developers. MySQL can be configured to offer much of the same advantages as PostgreSQL, but it does take configuration. In contrast, PostgreSQL is ready to go almost immediately, with little to no configuration.

PostgreSQL also handles concurrency differently than MySQL. In addition to offering parallelism, where queries can be split across CPU threads, PostgreSQL features multi-version concurrency control (MVCC). MVCC solves the problems related to locking a database for writes by, instead, using a type of version control. When someone is reading data from the database, they are seeing the data from that moment in time. Meanwhile, another user can be writing data to that record, but the database will create a newer version, while the first user continues to see the older one. Once the transaction is complete or committed, then the data is updated for all users. Because the read and write are occurring on two different versions of the record, there is no need to lock anything.

In times past, there were differences in performance between MySQL and PostgreSQL, with the latter being slower due to the additional features. In recent years, however, the performance gap has narrowed significantly. This has been as a result of MySQL adding some of the data protection features PostgreSQL has always had, as well as PostgreSQL’s implementation speeding up.

PostgreSQL also has the advantage of being an object-relational database, as opposed to simply a relational database. Again, this can be easier for newer programmers to pick up, as it provides a layer of abstraction when compared with MySQL or other relational databases.

Conclusion

In Part One we’ve looked at what makes a multi-user database, as well as two of the most popular implementations. In Part Two, we’ll look at additional examples, as well as what database you should choose.

MySQL
PostgreSQL
Databases
About the author
Matt Milano -Technical Writer
Matt is a tech journalist and writer with a background in web and software development.

Related Articles