PostgreSQL - Tuples Demo

CREATE TABLE ItaiTable ( ItaiID INT4, Name VARCHAR(32) );

SELECT * FROM pgstattuple('itaitable’);

INSERT INTO ItaiTable (ItaiID, Name) VALUES (1, 'Itai’);

INSERT INTO ItaiTable (ItaiID, Name) VALUES (2, 'Yossi’);

SELECT relname, n_live_tup, n_dead_tup,

n_tup_ins, n_tup_upd, n_tup_del, vacuum_count

FROM pg_stat_user_tables WHERE relname = 'itaitable’;

UPDATE ItaiTable SET Name = 'Anat' WHERE ItaiID = 1;

UPDATE ItaiTable SET Name = 'Vitaly' WHERE ItaiID = 1;

DELETE FROM ItaiTable WHERE ItaiID = 1;

SELECT * FROM ItaiTable;

VACUUM ItaiTable;

SELECT * FROM heap_page_items(get_raw_page('itaitable', 0));

VACUUM FULL ItaiTable;

    -- FULL = allocate the unused space back to the operating system

SELECT * FROM heap_page_items(get_raw_page('itaitable', 0));

PostgreSQL Tuples and MVCC

Tuple

• Actually tuple is a row in the table.
• Row is what will be back in a SELECT query.
• Tuple is how it managed.

MVCC - Multiversion Concurrency Control

• MVCC enables operations to occur concurrently by utilizing snapshots of the database.
• In MVCC, When you update or delete any row, Internally It creates the new row and mark old row as unused.
• The tradeoff is that it creates dead rows / dead tuples.
• MVCC is a little bit similar to READ_COMMITTED_SNAPSHOT in SQL Server.

Vacuum

• Vacuuming is needed to get rid of old (dead) tuples which are created when you change/delete rows.
• In PostgreSQL, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.
• It doesn’t reduce the size of the files.
• except for the case when several pages at the end of the file are completely free.
• Space is freed up inside the data pages, which can later be used to insert new tuples.
• Vacuuming runs:
• Manually with the VACUUM command
• Autovacuum background process
• VACUUM update statistics when the configuration parameter “track_counts” is set to “on”.
  
• Running it too often will create unnecessary load on the system. But running vacuum too rare, with a large volume of changes, the files may grow significantly in size.
• VACUUM update statistics when the configuration parameter “track_counts” is set to “on”.
• This parameter is under “Runtime Statistics” section in postgresql.conf configuration file.
• To check what is the current value run: SELECT name, setting FROM pg_settings WHERE name='track_counts';

https://edu.postgrespro.com/2dintro/08_admin_maintenance.pdf

PostgreSQL Architecture

 The physical structure of PostgreSQL consists of

• Processes.
• Shared memory.
• Data files.

PostgreSQL Processes

1. Postmaster (Daemon) Process
   
   1. The first process started when you start PostgreSQL.
   2. At startup it starts all other processes; performs recovery, initialize shared memory, and run background processes.
   3. It creates a backend process when there is a connection request from the client process.
   4. Postmaster process is the parent process of all processes.
2. Background Processes
   

   1. List of Background processes:

      logger	Write the error message to the log file.
      checkpointer	When a checkpoint occurs, the dirty buffer is written to the file.
      writer	Periodically writes the dirty buffer to a file.
      wal writer	Write the WAL buffer to the WAL file.
      Autovacuum launcher	Fork autovacuum worker when autovacuum is enabled.It is the responsibility of the autovacuum daemon to carry vacuum operations on bloated tables on demand
      archiver	When in Archive.log mode, copy the WAL file to the specified directory.
      stats collector	DBMS usage statistics such as session execution information ( pg_stat_activity ) and table usage statistical information ( pg_stat_all_tables ) are colle

3. Backend Process
   
   1. Performs the query request of the user process and then transmits the result.
4. Client Process
   1. Refers to the background process that is assigned for every backend user connection.
   2. Usually the postmaster process will fork a child process that is dedicated to serve a user connection.

Shared Memory

Shared Memory refers to the memory reserved for

• Database caching
• Transaction log caching.

The important elements in shared memory are

• Shared Buffer
  
  • The purpose of Shared Buffer is to minimize DISK IO.
• WAL buffers
  
  • A buffer that temporarily stores changes to the database (to the WAL files).
• Temp buffers
  • Stores temporary tables

Data storage

The data is cached both in the operating system level and in the PostgreSQL level:

• PostgreSQL buffer cache in shared memory.
• OS data cache is the Write-Ahead Log (WAL).

In the case of a failure the contents of the RAM disappear and some data may be lost, which is unacceptable as it violates the durability property.

Therefore, during its operation PostgreSQL constantly writes the so-called Write-Ahead Log (WAL) to the disk.

This allows to re-perform lost operations and restore data in a consistent state.

Transaction logging - WAL

WAL = Write-Ahead Log.

Each transaction is written to the WAL File before it written to the data files on the disk (as described above).

WAL files stored in \data\pg_wal.

A single information unit within a WAL file is called a log record.

“Segment” is sometimes used as synonym for WAL file.

SQL Server LDF files ~ Oracle REDO files ~ PostgreSQL WAL files

PostgreSQL main terms

A tuple is a synonym for a row.

A relation is a synonym for a table.

A filenode is an id which represent a reference to a table or an index.

PostgreSQL database Cluster

• It is not a collection of servers,
• It is a collection of databases managed by a single server

PostgreSQL Architecture useful links

https://severalnines.com/database-blog/understanding-postgresql-architecture

http://bajis-postgres.blogspot.com/2013/10/postgresql-architecture.html

https://edu.postgrespro.com/2dintro/02_arch_general.pdf

https://edu.postgrespro.com/2dintro/04_arch_wal.pdf

PostgreSQL Data Types

“Standard” types

• numeric, floating-point
• string
• Boolean
• date/time
• UUID
• Universally Unique Identifies.
• 16 bytes of storage.
• Example: d5f28c97-b962-43be-9cf8-ca1632182e8e
• XML
• XML type is just a text data type.
• The advantage is that it checks that the XML is well-formed.
• json, jsonB
• Text Search Type:
• Two data types which are designed to support full-text search.
• Money

Special Data types

• Network Address
• Network information like IP address.
• Using Network Address Types has following advantages
• Storage Space Saving
• Input error checking
• Functions like searching data by subnet
• Geometric
• Represent two-dimensional spatial objects.
• They help perform operations like rotations, scaling, translation, etc.
• Enumerated
• A set of values. 
• While inserting, it checks that the value is from the declared set.
• The ordering of the values in an enum type is the order in which the values were listed when the type was created.
• Example:
• ('sad', 'ok', 'happy’);
• In this example: ‘ok’ > ‘sad’ and < ‘happy’.
• Range
• Data in ranges.
• Can be a range of numeric and dates.
• Pseudo-Types
• special-purpose entries.
• Any, An array, Any element, Any enum, Nonarray, Cstring, Internal, Language_handler, Record, Trigger.

Custom data types - Composite Types

PostgreSQL user has the ability to create his own data types based on existing ones (composite types, ranges, arrays, enumerations).

CREATE TYPE inventory_item AS (name text, id integer)

SELECT item.name FROM on_hand WHERE item.price > 9.99;

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

https://www.postgresql.org/docs/10/rowtypes.html

More about PostgreSQL data types: https://www.guru99.com/postgresql-data-types.html

PostgreSQL Databases

System databases

• Two Template Databases:
• Template0
• Template1
• postgres:
• The default database created using the template1 database.
• If you do not specify a database at connection time, you will be connected to the postgres database.
• DVD Rental sample database.
• Downolad: https://www.postgresqltutorial.com/postgresql-sample-database/

Template Databases

Database creation actually works by copying an existing DB.

The template DBs are used for user database creation.

• Two ‘default’ Template Databases: Template0, Template1.
• Template1 is the default template.
• The 2 templates contains the same data.
• Template0 is more empty/"virgin" DB.
• New encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does.
• More templates can be created.

Tablespace

Tablespaces define locations in the file system where the files representing database objects can be stored.

Tablespace creation must be done as a database superuser.

PostgreSQL comes with two default tablespaces:

• pg_default: stores all user data (the default tablespace).
• pg_global: stores all global data.

One tablespace can be used by multiple databases.

The physical location of tablespaces: <PostgreSQLFolder>\12\data .

Tables, indexes, and entire databases can be assigned to particular tablespaces: 

    CREATE TABLE foo(i int) TABLESPACE space1;

Database Creation

Database is created by cloning one of the templates.

Database is created in one of the tablespaces.

CREATE DATABASE name

 [ [ WITH ] [ OWNER [=] user_name ]

 [ TEMPLATE [=] template ]

 [ ENCODING [=] encoding ]

 [ LC_COLLATE [=] lc_collate ]

 [ LC_CTYPE [=] lc_ctype ]

 [ TABLESPACE [=] tablespace ]

 [ CONNECTION LIMIT [=] connlimit ] ]

PostgreSQL Configurations

Configurations save in postgresql.conf , Located in <postgresqlFolder>\12\data.

Configurations can be set:
Edit postgresql.conf file itself.

• Via postgres command in the command-line.
• Some parameters can be changed in individual SQL sessions with the SET command.
• Some parameters can be changed with “ALTER SYSTEM” command.
• ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
• ALTER SYSTEM RESET configuration_parameter
• ALTER SYSTEM RESET ALL

Configurations can be viewed:

• select * from pg_settings
• At pgAdmin.

PostgreSQL as Object-Relational Database Management System (ORDBMS)

ORDBMS = a relational database that supports some object oriented features.

Table inheritance

Create a table from a (table-)type.

create type person_type as (id integer, firstname text, lastname text);

create table person of person_type;

Create a table inherits from other table.

create table person (id integer, firstname text, lastname text);

create table person_with_dob ( dob date ) inherits (person);

CREATE TABLE City (

  CityID INT4,

  Cityname  varchar(50),

  Country text

);

CREATE TABLE Capital (

  CountryCode char(3)

) INHERITS (City);

SELECT * FROM City;

SELECT * FROM Capital;

INSERT INTO City (CityID, Cityname, Country) 

VALUES (1, 'Tel Aviv', 'Israel');

INSERT INTO Capital (CityID, Cityname, Country, CountryCode) 

VALUES (2, 'Jerusalem', 'Israel', 'ISR’);

SELECT * FROM City;

SELECT * FROM Capital;

SELECT * FROM ONLY City;

https://www.postgresql.org/docs/8.4/tutorial-inheritance.html

Definition of methods on the type

create table person (id integer, firstname text, lastname text);

create function fullname(p_row person) returns text

As

$$

    select concat_ws(' ', p_row.firstname, p_row.lastname);

$$

language sql;

select p.fullname from person p;

Function overloading

PostgreSQL allows more than one function to have the same name, so long as the arguments are different.

https://www.postgresqltutorial.com/plpgsql-function-overloading/

Complex types

Custom data types - Composite Types

PostgreSQL user has the ability to create his own data types based on existing ones (composite types, ranges, arrays, enumerations).

CREATE TYPE inventory_item AS (name text, id integer)

SELECT item.name FROM on_hand WHERE item.price > 9.99;

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

PostgreSQL Overview

PostgreSQL is an open source object-relational database system.

PostgreSQL is probably the most advanced database in the open source relational database market.
PostgreSQL uses and extends the SQL language combined with other features.

PostgreSQL superuser is the user named Postgres.

Supported platforms

PostgreSQL is available for all operating systems: Windows, Linux, Unix and macOS.

Supported Cloud vendors

Microsoft Azure

https://azure.microsoft.com/en-us/services/postgresql/

aws Amazon

https://aws.amazon.com/free/database/

IBM Cloud

https://www.ibm.com/cloud/databases-for-postgresql

ScaleGrid

https://scalegrid.io/postgresql.html

Google cloud SQL for PostgreSQL

https://cloud.google.com/sql/docs/postgres/

ElephantSQL PostgreSQL as a Service

https://www.elephantsql.com/

And more ...

Installation processes

Download PostgreSQL: https://www.postgresql.org/download

During the installation you will set:

• Database superuser (postgres) password.
• Port (default: 5432).

Client Tools

psql

psql is a command-line program.

psql can be used to enter SQL queries directly, or execute them from a file.

pgAdmin

pgAdmin is the PostgreSQL tool.

It's an Open Source administration and development platform for PostgreSQL.

pgAdmin is installed with PostgreSQL .

https://www.pgadmin.org/

A little bit of history

PostgreSQL evolved from the Ingres project at the University of California, Berkeley. At 1986 Michael Stonebraker and his colleagues developed Postgres.

Versions were released from time to time with more features, and in 1995 Initial release as Postgres95 was released.

In 1996 the project was renamed to PostgreSQL in order to reflect its support for SQL.

In 1997 the initial PostgreSQL release formed version 6.0.

Useful links

• PostgreSQL site: https://www.postgresql.org/
• postgresql.git: https://git.postgresql.org/gitweb/?p=postgresql.git
• PostgreSQL ODBC driver: https://www.progress.com/campaigns/datadirect/ppc/postgresql-odbc
• Configuration calculator: https://pgtune.leopard.in.ua/#/
• What is PostgreSQL? https://www.guru99.com/introduction-postgresql.html

IS DISTINCT FROM

In SQL, NULL is not equal (=) to anything (even to another null) and not different than any other value.

Therefore, If I select from a table, one time when a column is equal to a value and one time when it different than this value - the nulls records won't be selected in any case.

In some SQL languages there is a solution for that: IS DISTINCT FROM (or IS not DISTINCT FROM), that returns any value that it's not the given value - includes NULLs.

-- return all records:
select * from itaitable

-- return who is not DBA. NULL is unknown, so it won't be returned:
select * from itaitable where "Job" <> 'DBA’

-- return DBAs:
select * from itaitable where "Job" = 'DBA’

-- return who is not DBA or records that we don't know what is the value of "Job":
select * from itaitable where "Job" is distinct from 'DBA'

For SQL languages that don't support "IS DISTINCT FROM" we would write:

select * from itaitable where Job <> 'DBA’ or Job IS NULL

"IS DISTINCT FROM" is not available in SQL Server, Oracle, MySQL and more.

"IS DISTINCT FROM" is available in PostgreSQL, DB2.

PostgreSQL - translate encoding char to numbers and vice versa

select pg_encoding_to_char(6);
--Result: UTF8

select pg_char_to_encoding('UTF8');
--Result: 6

PostgreSQL - EXPLAIN - Show execution plan

EXPLAIN
Display the execution plan which PostgreSQL generates.
EXPLAIN ANALYZE
Display more statistics. Display actual run-time statistics.

In order to analyze INSERT, UPDATE, DELETE without affecting the data – run EXPLAIN ANALYZE in a transaction:

BEGIN;

    EXPLAIN ANALYZE sql_statement;

ROLLBACK;

CTE - SQL Server vs PostgreSQL

With myCTE AS (SELECT * FROM dbo.MyTable)

SELECT *

  FROM myCTE 

  WHERE Id = 1;

In SQL Server CTEs are processed with the main query.
In PostgreSQL CTEs are processed separately from the main query

Implications of this difference are:

1. A query that should touch a small amount of data instead reads a whole table and possibly spills it to a tempfile.
2. You cannot UPDATE or DELETE FROM a CTE term, because it’s more like a read-only temp table rather than a dynamic view.
3. In PostgreSQL, in this query:

With myCTE AS (SELECT * FROM dbo.MyTable)

SELECT *

  FROM myCTE 

  WHERE Id = 1;

WHERE clauses aren’t applied until the execution of the main query, and it’s different than:

With myCTE AS (SELECT * FROM dbo.MyTable WHERE Id = 1)

SELECT *

  FROM AllPosts;

PostgreSQL - relation "pg_stat_statements" does not exist

What I did:
select * from pg_stat_statements;
-- and pg_stat_statements is enabled (if not - check here)

Error message:
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

Cause:
pg_stat_statements is not set in postgresql.conf.
It probably looks like:
#shared_preload_libraries = '' # (change requires restart)
or with other libraries.

Solution:
1. Set pg_stat_statements in postgresql.conf; adding:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

2. Restart PostgreSQL service

Check it:
SHOW shared_preload_libraries;

PostgreSQL - relation "pg_stat_statements" does not exist

What I did:
select * from pg_stat_statements;

Error message:
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: select * from pg_stat_statements
                      ^
Cause:
pg_stat_statements is not available globally but can be enabled for a specific database with CREATE EXTENSION.

Solution:
Enable pg_stat_statements:

CREATE EXTENSION pg_stat_statements;

Check it:
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';