notes

Postgres

Table Of Content

Commands

  1. List databases 
     \list
 \l
  2. Connect to database 
     \connect database_name
  3. List all tables 
     \dt
  4. Describe table 
     \d table
  5. Show indexes 
     \di

Multiversion Concurency Control

  1. Problem to solve: we need to have atomicity, consistency and isolation in concurent environment.
  2. The easiest way to implement it is:
    • Shared read lock + not shared write lock
    • Dissadvantages: if one user want to write: the whole world would stop.
  3. There are two ways to implement MVCC:
    • Store revert log (Oracle, MsSQL)
    • Store all versions of the row (PostgreSQL). We will call one version as a tuple.
  4. PostgreSQL stores creation transaction ID (xmin) and expiration transaction id (xmax) for every tuple.
  5. PostgreSQL stores statuses for all transactions (CLOG).
  6. Having CLOG, xmin and xmax we could decide if tuple is visible for transaction or not.
  7. Visible tuples:
    • must have a creaation transaction id that:
      • is a commited transaction and
      • is less than the transaction counter stored at query start and
      • was not in-process at query start
    • must have an expire transaction id that:
      • is blanck or
      • is aborted or
      • is greater than the transaction counter stored at query start or
      • was in process at query start

Analize Query

EXPLAIN ANALYZE
    SELECT Conference.name, University.name
    FROM Conference
        JOIN Participant ON (Conference.conference_id = Participant.conference_id)
        JOIN Researcher ON (Participant.researcher_id = Researcher.researcher_id)
        JOIN University ON (Researcher.university_id = University.university_id);

  1. Get pg stats

     SELECT attname, n_distinct
 FROM pg_stats
 WHERE tablename='my-table-name';

  2. Update pg stats

     analize;

Analize