Intro to PostgreSQL

What is PostgreSQL?

PostgreSQL, also known as postgres, is an oject-relational database management system. Object-relational databases allow you to store objects as data like we can in many programming languages. Postgres is so important in software development today.

Multi-version Concurrency Control

Multi-version Concurrency Control(MVCC) is a feature in postgres that allows for the storage of multiple versions of the same data. In most other database systems, whenever a row of data is updated, accessibility of the updated information is usually limited aftwerwards. With postgres, instead of updating a single version of a piece of data multiple times, a new version of the data is added to the queue and the previous versions remain. This allows anyone looking to view or update the information to do so without being locked out because of the changes to the original version.
Example using diagram

PostgreSQL shell commands

The postgres database is interacted with by using a terminal-based shell command-line. I am going to demonstrate to you a few of the most commands used when building a database in postgres.
CREATING A TABLE

CREATE TABLE opspark (
  phase VARCHAR(255),
  cohort VARCHAR(255),
  year INT
);

The command in the example above is asking postgres to create a table that has 3 fields that consist of a phase, cohort, and a year. The text on the right of each field created is the data type that the empty field will accept as an input. VARCHAR is used for alphabetical characters and INT stands for an interger, or number.
INSERTING INTO A TABLE

INSERT INTO opspark (phase, cohort, year)
VALUES ('Immersion Junior', 'Golf', 2023);

In order to add information into the table we created up above, we can use INSERT INTO in the command-line. This example shows us filling the empty opspark table with data for the phase of the bootcamp we're in, the cohort we're a part of, and the year we joined the program.
RETREIVING DATA FROM A DATABASE

SELECT phase, year FROM opspark; 

In order to retrieve data from the database through command-line, we can use the SELECT keyword. In this example, we're attempting to retreive the phase and year rows of data from our opspark table. Here is what that data would look like from the information we inserted into the table earlier.

phase  | year
--------+------
 Immersion Junior   | 2023
(1 row)

These are just a few of the basic commands that are used to build tables in postgres. There are tons of more commands to look into when exploring postgres. If are you interested in using postgres in the future, I would highly recommend researching some of those commands.

Potential Downsides of Using PostgreSql

Even though we talked about some of the amazing benefits of using postgres and it's cool commands, there's still a few cons. Just like anything in life, postgres has its upsides and it has it's downsides. One of the downsides of using postgres is it's inefficiency in terms of performance. Postgres lacks efficiency in performance because of the amount of resources it takes to be ran. Another potential downside of postgres is that it isolates each client. This means that a new thread has to be started for each client so in return, it consumes much more memory than it should have to.

Conclusion