PostgreSQL

🐘what is Postgres?

• if you've been looking at job postings recently, you may have seen a lot of them are now looking for Postgres experience that's because a lot of tech companies are moving to Postgres or starting out a new stack. why are the likes of some tech giants like TripAdvisor, Reddit, and Instagram all on Postgres. Postgres is kind of like Python, you can do anything, it has a lot of reasons like open-source, a very hard working community that has given it a huge range of flexibility.

• history: The PostgreSQL project started in 1986 under the direction of Professor Michael Stonebreaker at the University of California, Berkeley. The project was originally named POSTGRES, about the older Ingres database, also developed at Berkeley. POSTGRES aimed to add the fewest features needed to completely support multiple data types. In 1996, the project was renamed to PostgreSQL to illustrate its support for the SQL querying language (although PostgreSQL is still commonly abbreviated as Postgres). A dedicated and diverse community of contributors – the PostgreSQL Global Development Group – continues to make regular major and minor releases of the free and open-source database project.

🤓Why Postgres?

• because of supporting OOP in Postgres, the Multidimensions array is available.

    CREATE TABLE holiday_picnic 
        holiday varchar(50) -- single value
        sandwich text[], -- array
        side text[] [], -- multi-dimensional array
        dessert text ARRAY, -- array
        beverage text ARRAY[4] -- array of 4 items
    );
  
    INSERT INTO holiday_picnic VALUES
    ('Labor Day',
    '{"roast beef","veggie","turkey"}',
    '{
        {"potato salad","green salad","macaroni salad"},
        {"chips","crackers"}
    }',
    '{"fruit cocktail","berry pie","ice cream"}',
    '{"soda","juice","beer","water"}'
    );
  

• PostgreSQL source code is available under an open-source license, granting you the freedom to use, modify, and implement it as you see fit, at no charge. PostgreSQL carries no licensing cost, which eliminates the risk of over-deployment. PostgreSQL's dedicated community of contributors and enthusiasts regularly find bugs and fixes, contributing to the overall security of the database system.

  

• PostgreSQL is an advanced, open-source relational database that supports both SQL (relational) and JSON (non-relational) querying. it would be efficient for situations in that we don't have a specified data structure.

• you can create your new data type 😺

  A feature that MySQL and MariaDB lack because they are not object-oriented.

     -- create a new composite type called "wine"
    CREATE TYPE wine AS (
        wine_vineyard varchar(50),
        wine_type varchar(50),
        wine_year int
    );
  
    CREATE TABLE pairings (
        menu_entree varchar(50),
        wine_pairing wine
    );
    -- insert data into the table using the ROW expression
    INSERT INTO pairings VALUES
    ('Lobster Tail',ROW('Stag''s Leap','Chardonnay', 2012)),
    ('Elk Medallions',ROW('Rombauer','Cabernet Sauvignon',2012));
    /*
    query from the table using the table column name
    (use parentheses followed by a period
    then the name of the field from the composite type)
    */
    SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type
    FROM pairings
    WHERE menu_entree = 'Elk Medallions';
  

• nowadays most applications use sensitive geographical data. it has been supported for years by Postgres. Although MySQL and MariaDB supporting this recently, but working with them is still not as easy as Postgres.

CREATE TABLE trails (
    trail_name varchar(250),
    trail_path path
);

• PostgreSQL possesses robust feature sets including Multi-Version Concurrency Control (MVCC), point-in-time recovery, granular access controls, tablespaces, asynchronous replication, nested transactions, online/hot backups, a refined query planner/optimizer, and write-ahead logging(WAL).

🐧Postgres vs MySQL

🐥postgres vs MongoDB

💼Work

Postgres Requirements (v15)

• GNU make

• You need a C compiler(recent versions of GCC are recommended)

• [more](https://www.postgresql.org/docs/12/install-requirements.html)

• more detail

Complie & Installation

first, select a path to install Postgres, then clone from GitHub and change the branch to 12

export PGPATH=$HOME/pg/12
git clone https://github.com/postgres/postgres.git
cd postgres
git checkout REL_12_STABLE

now, compile and installation

./configure --prefix=$PGPATH --enable-cassert --enable-debug --enable-depend
make -j 8
make install

# install some extensions
make -C contrib/pageinspect/ install
make -C contrib/pg_buffercache/ install
make -C contrib/pg_visibility/ install
make -C contrib/pgstattuple/ install
make -C contrib/pg_freespacemap/ install
make -C contrib/pgrowlocks/ install