Databases: SQLite vs PostgreSQL
Databases have been used by computers for decades; since 1960 to be exact — when the first one was developed by Charles W. Bachman while working for General Electric. Before database management systems existed, everything was being stored on paper! A database management system (now simply referred to as a database) is essentially a large collection of data that is stored and organized for rapid search and retrieval by a computer. Though they may seem boring, databases keep the world running. Two of the most popular databases used today are SQLite (“sequel-lite”) and PostgreSQL (“post-gres-sequel”). Let’s take a closer look at the applications, similarities and differences between the two.
SQLite was created in the year 2000 by D. Richard Hip. He developed it while he was contracted by the US Navy to design software for a damage-control system aboard guided-missile launchers(!!). The goal was to allow the program to operate without having to install a database management system, but rather use a standalone, embedded database. Thus the ultra lightweight SQLite was designed to solve the problem. According to D. Richard Hip’s on his twitter account “The original use-case for SQLite was in a damage-control system. SQLite was built to save lives, not blow things up.”
SQLite is one of the most widely used, open-source, relational databases in the world. It is simple to use, even for most beginners. Ruby on Rails and many web browsers use SQLite as their default database. SQLite is serverless — this is to say that the database runs on the same disk as the application using it — basically, it runs within the application. This means there is no setup or installation that has to be done to get the database up and running; SQLite it is often referred to as requiring “zero-configuration”. The small footprint of its library (just under 600kb) makes it an easy choice for compact, lightweight apps where space is a valuable commodity. It is coded in C and uses SQL as its query language, though not all SQL statements are compatible. SQLite is weakly typed ("flexibly typed” is the preferred term according to the SQLite website). This means a string of “1234” can be inserted into an integer column and SQLite will convert between formats to the best of its ability. However, if a non-numeric string is entered into an integer column, SQLite will not throw an error but will instead store the data as a string. This is viewed by SQLite as a feature, not a bug.
PostgreSQL (originally named POSTGRES) has been around a good bit longer than SQLite with the first iteration of it being developed in 1985 by a team at the University of California, Berkeley. Their goal was to create a database that used the fewest features needed to support the most common data types. The database understood relationships and could retrieve information in related tables using rules. In 1996 the project was officially renamed to PostgreSQL to better reflect its support for SQL since 1994. It is a free, open-sourced, object relational database. Like SQLite, it is coded in C and uses SQL as its query language to operate. PostgreSQL is server based, which means it communicates by sending and receiving data in a client-server model using TCP/IP. The client and the server can be on different hosts but connected to each other by using the same network. This makes it a great choice for handling multiple users. However, some configuration and setup is required before use. PostgreSQL is best suited when a complex, customizable and reliable database is needed.
Some other differences in the two databases are as follows: While SQLite does support multiple users, only one user can write to the database at a time, as the database briefly locks when it is written to. PostgreSQL allows multiple users to access the database at the same time and they can perform operations simultaneously. It also features a robust user management system that allows different clearances and permissions to be given to users. SQLite does not have a user management system. When it comes to simple operations, SQLite can perform much faster than PostgreSQL due to its simple lightweight design. However, SQLite can only perform one write operation at a time as mentioned, whereas PostgreSQL can handle multiple simultaneous operations. So when operations get complex, SQLite operates much slower than PostgreSQL. SQLite only supports 5 common data types, while PostgreSQL supports nearly all of them — see the extensive list here: https://www.tutorialspoint.com/postgresql/postgresql_data_types.htm
Both databases are ACID compliant (atomicity, consistency, isolation, durability). Basically, this means that data consistency is guaranteed despite errors such as crashes, power failures, etc. ACID ensures that the entire data “transaction” (a sequence of operations that make up one logical unit of work) that takes place all at once or else it doesn’t happen at all. Think of it like a bank transfer or getting money out of an ATM — either the transaction is 100% completed or it doesn’t happen at all. All transactions are isolated and not visible to any other transactions until they have been fully executed. The ACID acronym was coined in 1983 by Andreas Reuter and Theo Härder; however, the practice had been in use by IBM since 1973.
Ultimately, you should let your application and not your personal preference dictate which database is suited for your needs. If you are making a small, single user app go with SQLite. If you are making something that is going to require large volumes of data, good security, network access, and multiple users, you should use a server based database, such as PostgreSQL. Both databases have their own strengths and weaknesses which help to serve unique needs that each application will have.
DISCLAIMER: I’m in a software coding bootcamp and have only been working with databases for about 3 weeks. I wrote this in an attempt to better understand the different abilities and uses of databases.
Resources for this article listed below:
https://www.tecmint.com/what-is-postgresql-how-does-postgresql-work/
https://www.tutorialspoint.com/postgresql/postgresql_overview.htm
