DEV Community

Cover image for Should I use SQLite, PostgreSQL, or MySQL?
Simon Pfeiffer for Codesphere Inc.

Posted on

Should I use SQLite, PostgreSQL, or MySQL?

Written by Dan Parry.

Database Management Systems (DBMS), in short, are collections of tools used to access and manipulate the data stored in a database. There are lots of different types of DB management systems but the most widely used are Relational Database Management Systems (RDBMSs). 

RDBMSs use the relational model to store data in the form of tables (referred to as relations). Each relation is then further organized into rows (tuples) and columns (attributes). A relation is managed and/or queried using Structured Query Language (SQL).

There are many dialects of SQL, the three most popular of which are SQLite, PostgreSQL, and MySQL. Each has its own limitations and use cases, so let's take a look at when to use each to see which one might be the best fit for your next project.

Alt Text


Let's start with a look at by far the most popular of the three - MySQL. Used by Google, Linkedin, Amazon, Netflix, and Twitter, it is an easily accessible, high-performing system that is most commonly used for web-based applications.

MySQL is built into major content management systems like WordPress, so you can't go far online without finding something that is supported by it.

Written in C and C++ and ACID compliant, MySQL is commonly used for enterprise applications thanks to its scalability, replication, and security features. It's also the go-to system if speed is a requirement for your application because the exclusion of some more advanced features MySQL is lightweight while still delivering high performance.

MySQL's popularity also means it has the best resources to take advantage of. It's part of the Oracle ecosystem so if you're looking for support you'll have no trouble finding tools for management, monitoring, and learning.

Overall MySQL provides a reliable DB solution that's simple to set up and requires minimal developer input. This, along with its great toolset and multi-user feature support, makes it the most accessible option out there.

When to use MySQL:

  • Websites and Web-based applications - MySQL's easy setup, huge support community, and robust security features make it a great choice for most websites and applications.

  • When speed is a requirement - At the expense of some more advanced features you might find in PostgreSQL, MySQL is able to optimize speed.

  • Distributed operations - thanks to its replication support MySQL is the best choice for distributed database setups.

  • For predicted scaling and growth - Thanks again to that wonderful replication support, it is easy to implement horizontal scaling on MySQL, so if your website is expected to take off, MySQL is your best choice.


Alt Text

PostgreSQL is an open-source object RDBMS that is typically used in more complex websites and applications that require a customizable DB solution. PostgreSQL is ACID-compliant and is used by companies such as Apple and Cisco. Like MySQL, PostgreSQL also offers features such as security and replication. 

PostgreSQL is known for supporting a lot of data types and having deep functionality, but this comes at the cost of a more intensive setup than MySQL. It's more popular in teams that already have dedicated DB specialists as they can create and implement custom PostgreSQL functions in a variety of languages like C, C++, and Java.

PostgreSQL is the go-to solution for complex analytical processes. It does, however, take a more specialist toolset to manage and due to its highly customizable nature, it can be harder to find support resources than MySQL. There may not always be a solution available for your specific issue in the forums and documentation but there are paid third-party setup services available, making it a viable solution for companies with specialized DB needs to have their system set up and their team trained.

When to use PostgreSQL:

  • For complex queries and operations - PostgreSQL's ability to support multiple concurrent writers and query plans that can make use of multiple CPUs make it the clear choice for complex tasks.

  • When customizability is needed - An extensive amount of customizability gives you far more control over MySQL when it comes to database setup.

  • Data Warehousing - Due to its ability to read/write at fast speeds, PostgreSQL is perfect for data analysis applications.


Alt Text

SQLite is a server-less and self-contained database that does not require any setup or installation to get started. Also known as an embedded database, SQLite runs as part of the app itself instead of part of a separate server process. 

SQLite is the most compact and easy-to-use entry of the three and is also ACID adherent. While it is a great out the box solution for low to medium traffic websites, its lack of versatility and feature set found in its more complete counterparts give it some major disadvantages. 

SQLite is best implemented in embedded software - anything that only requires a small amount of data to be saved or even websites with the south of 100k visitors a day.

When to use SQLite:

  • Embedded applications - when you want to save a small amount of data, systems like a telephone, fitness tracker, or air conditioning system would be a great time to use SQLite.

  • Low traffic website - SQLite isn't the best choice for huge amounts of data or users, but for small operations, the no-setup and config requirements make SQLite the way to go.

  • Testing - A great lightweight alternative to fast testing without the need to implement an actual database.


Horses for courses

Looking at all three libraries it's clear that while they all have pros and cons, a direct comparison doesn't make sense, they all fill a specific need. MySQL with its great feature set, community support, and easy setup, is the best allrounder for most web-based applications. PostgreSQL is the way to go for high customizability and specific database requirements, and SQLite is the best option for an application with low DB storage needs or a website with low traffic.

It's different horses for different courses, but here are some bonus rounds to help you a clearer picture:

Pricing

All three options are open-source so they are completely free to implement!

Data Queries 

  • MySQL - Is more resource-efficient because it offers a scalable buffer pool to pull cache.

  • PostgreSQL - is more organized because it isolates processes by treating them as a separate OS process.

Temporary tables

  • PostgreSQL - offers a lot more functionality than MySQL. Tables can be divided into local and global to be configured with flexible variables. If you're developing an application with a lot of complex processes, this is the choice for you.

Memory-Optimized tables 

  • MySQL - Memory-optimized tables are basically the native approach of MySQL is the best option and a great way to save resources.

Conclusion

Open source solutions like the ones we've looked at today are the best options for flexibility, cost efficiency, and innovation, but which one you use depends entirely on your needs.

MySQL is the clear option for most applications and PostgreSQL is the choice for more complex and custom projects. SQLite is your best friend for small operations and embedded systems.


At Codesphere, we are happy to announce that we just added support for setting up SQLite databases seamlessly in a project, but more on that later this week!

Happy Coding!

Top comments (25)

Collapse
 
dewofyouryouth_43 profile image
Jacob E. Shore

Generally speaking, just use postgres, it'll be fine. There are many ways to optimize for speed but the limits of MySQL are significant and will likely eventually come to bite you.
SQLite is good for native app local storage and also I think it's really a good way to transfer data in some use cases.

Collapse
 
mtrantalainen profile image
Mikko Rantalainen

I agree that the real options are SQLite and Postgres. If you want SQL database for offline use in a single app, SQLite is the way to go. For everything else you should pick up Postgres.

The whole point of MySQL and MariaDB is backwards compatibility. New projects should not start with either.

Collapse
 
yw662 profile image
yw662

Sqlite might also be an option in some online cases. But yeah postgres is usually the way to go.

Thread Thread
 
grizzlysmit profile image
Francis Grizzly Smit

yep I agree SQLite for little in app stuff, and Postres for all the rest, but if I am forced to use the MySQl/MariaDB route then MariaDB is better, it keeps you out of clutches of Oracle

Collapse
 
buphmin profile image
buphmin

I've used both MySQL and Postgres, and somewhat prefer postgres nowadays. Though I'm not sure what you mean by "limits of MySQL are significant". In what way is it so significantly different? At my last job the I saw MySQL being used housing tens of thousands of tables, some of which having billions of records and over a petabyte of production data being used to analyze marketing metrics. Honestly just curious: what are the limits? I don't have any evidence to say what the limits to MySQL are.

Collapse
 
dewofyouryouth_43 profile image
Jacob E. Shore

Well. I won't give a comprehensive list. But list a few examples that should give you an idea of what I'm talking about. Until MySQL 8.0 there wasn't even support for any window functions. In 8.0 there's minimal support for window functions. There's no support for row level security. Upsert constraints are reduced to INSERT or REPLACE (which is dicey).

Thread Thread
 
buphmin profile image
buphmin

So more of a functional limitation rather than a scalability one then right?

Collapse
 
dsonck92 profile image
Daniel Sonck

Extending on Jacob's points, one that I find inexcusable (and has in fact hit me in the face a few moments ago at work) is the fact that MySQL does not support Transactional DDL and only supports Atomic DDL starting from 8.0. Whereas both PostgreSQL and SQLite support this.

To specify it's significance for those unaware: say your application manages migrations and needs to do several steps in one go (e.g. alter some column name and some data modification), you should be able to start a transaction and run all steps. If something goes wrong (e.g. the application is somehow terminated mid-migration, as happened here), then all migration steps are perfectly reverted. Hence it will be completely ready for a new apply, or, you can go back to the old version of the software and you know the database should be in a state as if there was no update whatsoever.

Yes, this can be alleviated by making a backup before upgrading, which we did, but it's also demonstrated by two systems, one of which (SQLite) I always saw as a simplistic database, that it can be done. I can kill the application modifying the schema of an SQLite database without any ill effects.

Collapse
 
peerreynders profile image
peerreynders

For offline first web apps sqllite-worker is worth consideration as well.

Collapse
 
dewofyouryouth_43 profile image
Jacob E. Shore

Thanks, not familiar but I'll take a look!

Collapse
 
papaponmx profile image
Jaime Rios

I'm thinking of doing an app with RedwoodJS, and use Postgres for everything.

Collapse
 
simoncodephere profile image
Simon Pfeiffer

good to know, thanks for the insight!

Collapse
 
chuniversiteit profile image
Chun Fei Lung

It's part of the Oracle ecosystem so if you're looking for support you'll have no trouble finding tools for management, monitoring, and learning.

This is seen as a good thing? 🙈

Joking aside, I think Oracle has done a reasonable job maintaining MySQL. Given the choice between MySQL and MariaDB, its Oracle-free competitor, I would still choose MariaDB over MySQL though, because it’s better in almost every way.

Collapse
 
simoncodephere profile image
Simon Pfeiffer

Interesting take, will look into MariaDB :)

Collapse
 
tadman profile image
Scott Tadman

It's astonishing how far Postgres has come since the 8.0 era, circa 2005. A lot of the arguments about it being "more complicated" and "lacking tools" are falling away as it gains more and more traction.

If you don't like Postgres enough to switch away from MySQL today, give it a few years and you'll have even more reasons.

Really love what the Postgres team has done over the last decade and a half. It's astonishing the progress they've made.

Collapse
 
peerreynders profile image
peerreynders

One other point about MySQL is that it fell into Oracle's lap with their acquisition of Sun Microsystems in 2009. Given Oracle's regular commercial shenanigans it wouldn't be too surprising if there are directives in place to ensure that MySQL never becomes a serious competitor to their own in-house RDBMS product.

Now granted there is MariaDB as a fallback if the unthinkable happens — however personally for me there is enough there to bypass MySQL entirely and go straight to PostgreSQL.

(Amazon put in a significant effort which finally in October 2019 resulted in their business shedding the last of its dependencies on Oracle — a move that had little to do with "Dogfooding").

Collapse
 
patarapolw profile image
Pacharapol Withayasakpunt • Edited

I think SQLite and PostgreSQL are significantly different, other than embedded vs server.

SQLite - much less feature-rich; much smaller, suitable for embedded; add functions in the driver of language of your choice

PostgreSQL - more features; write functions into SQL, or that can compile for Postgres; comply to 12-factor app

Some time ago, I was also recommended firebird; but I dropped it, because of no full text search (FTS) support.

If I remembered correctly, at least Firebird, Neo4j and CouchDB have this dual nature.

Collapse
 
yawaramin profile image
Yawar Amin

Let's start with a look at by far the most popular of the three - MySQL.

Depends how you define 'popular'. SQLite is by far the most widely-deployed RDBMS, by orders of magnitude: sqlite.org/mostdeployed.html

Collapse
 
danp20 profile image
Dan Parry

Fine, I meant just my favorite :D

Collapse
 
lvbuwei profile image
lvbuwei

Good article. Thanks for sharing. i trust postgres

Collapse
 
ahrasel profile image
AH Rasel Ahamed

Good article. Thanks for sharing

Collapse
 
papaponmx profile image
Jaime Rios

Great article. Thanks for sharing.

Collapse
 
simoncodephere profile image
Simon Pfeiffer

Thanks! Glad to hear that

Collapse
 
hesamrad profile image
Hesam Rad

Informative article. Thanks for putting the time to write it.

Collapse
 
simoncodephere profile image
Simon Pfeiffer

Thanks! Glad you enjoyed it.