PostgreSQL

10.10.21


Since Django is my preferred backend framework, PostgreSQL has followed as my preferred database. Even though Django connects to a SQLite database out of the box, PostgreSQL (or just Postgres) is one of four other officially supported databases. The others being MySQL (owned by Microsoft), MariaDB (which is a fork of MySQL), and Oracle. Since Oracle is notoriously expensive to run, Postgres is probably the most powerful and versatile database officially supported by Django. Well, technically PostgreSQL is an object relational database management system (RDBMS). The word database is used a little ubiquitously; a database can really refer to any collection of data, not even necessarily on a computer and a data base management system is software that allows you to interact with a database.

Let’s circle back to Django’s stock database, SQLite. SQLite is a file based, serverless database. As the name suggests, it is super lightweight. Being lightweight comes with advantages and disadvantages. Advantages include ease of use, especially with Django. There is a small footprint, no configuration, no external dependencies and is actually a great solution for applications that read and write files to disk directly. A few disadvantages include scalability, security, user management and concurrency. This is where Postgres can step in.

A little background – Postgres has been around for 30 years and is open source which means there is a tremendous community of developers supporting it and keeping it modern. It is also flexible and scalable enough for it to be used for free in testing and small app development and to be used in production as the primary database for new startups and tech giants like Apple, Instagram and Reddit. It is also fully ACID (database integrity standards) and SQL compliant, where technically MySQL is not.

While MySQL remains the more popular and more widely used RDBMS, Postgres does outshine in a few more ways. MySQL can be more costly to run and does reserve some features and plugins for proprietary licenses. Postgres supports additional data types too. Postgres and MySQL both support string, numeric, date and time data types but Postgres extends support for JSON, network addresses, geometric shapes, text searches, currency and more.

Developing with Postgres also means options as far as clients go. You can use the command line to query the database using a tool like psql or you can go the graphic user interface (GUI) route and use cross-platform software like Postbird or the more robust and popular PgAdmin (see stock photo below). In addition to those tools Postgres even features a native Mac OS app.



In order to use Postgres with Django (or Flask) you will have to pip install psycopg. “Psycopg is the most popular PostgreSQL adapter for the Python programming language” says their website. That could be because the official Django documentation states that Psycopg 2.5.4 or higher is required. Once the adapter is installed to the project, connecting a Django backend to a live Postgres server is just a matter of editing a few lines in the settings.py file.

I hope that comparing and contrasting PostgreSQL to SQLite and MySQL helped to shine some light on these popular databases.