# SQLite vs. PostgreSQL
In this chapter we'll talk about some of the differences between SQLite and PostgreSQL regarding the features we've already learned about.
Remember though that as you learn more about either one, that the other may not behave exactly the same way. Don't assume that because a feature works in a certain way in SQLite, it'll work in the same way in PostgreSQL--no matter what the feature is!
As well as the differences mentioned below (which are the main ones for us), this link contains basic descriptions of other differences: https://tableplus.com/blog/2018/08/sqlite-vs-postgresql-which-database-to-use-and-why.html (opens new window)
# Database size and setup
SQLite is extremely easy to set up and use. In addition, it is very small in size.
However, PostgreSQL is quite large in size and can require a lengthy setup. In addition, for newer users PostgreSQL can be quite tricky to set up well.
# Speed
SQLite is very fast at reading from the database, but it is slow at writing (or rather, only one transaction can write to it at a time). This can make SQLite great at read-heavy applications, but not great at write-heavy applications or applications where many users should be able to write at the same time.
For example, Twitter. In Twitter, millions of users are constantly writing Tweets. These are written to a database for permanent storage.
If Twitter was using SQLite, only one user could save a Tweet to a database at a time. Although the act of saving to database is quite quick, due to the sheer volume of users there would be problems!
By contrast, using PostgreSQL would let them have separate users writing at the same time.
# Primary and Foreign Keys
By default in PostgreSQL when you create two tables, one with a primary key and the other with a foreign key referencing it, you will not be able to delete a row from the primary table if there is a row referencing it in the other table[1].
However, this is possible in SQLite by default.
This is something important to remember as you'll find that you won't be able to delete things. For example, if you have a users table with an id column (Primary Key), and an accounts table with a user_id column (Foreign Key), you won't be able to delete a User if they have an Account.
You would have to delete the Account first, and then delete the User.
Alternatively, you could tell PostgreSQL to delete using a CASCADE--i.e. to apply the deletion to all related tables. In that case, if you delete the User, PostgreSQL would delete the Account for you.
Note that if you have a complicated database structure with many tables and many relationships, this can be dangerous because it may delete things you didn't foresee.
To delete using a cascade in PostgreSQL, you'll need to set it up when you create the secondary (referencing) table:
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
user_id INTEGER,
account_number TEXT,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)
What we've added is ON DELETE CASCADE. That means that when a deletion happens on users(id) that matches accounts(user_id), it should also be applied to this row.
Alternatively you can do ON DELETE RESTRICT, which would prevent the account from being deleted.
The final option is the default, ON DELETE NO ACTION, which raises an error.
# Next steps
That's about it for this chapter! We've looked at some differences between SQLite and PostgreSQL.
In the next few chapters we'll be introducing PostgreSQL, how you can use it, and how you can talk to it from your Python code.