Chapter 20

Relational Model

Presentation & objectives

This part of the track is dedicated to a major data model, relational data. Even if it is not the most appropriate choice for very large datasets, the relational data model is still predominant to store the data of most dynamic web-based applications.

Two topics are addressed and separated into two practical activities.

The first one is dedicated to database servers administration. A focus is given to the Postgresql Relational DataBase Management System (RDBMS). More precisely, by the end of this first activity, you will be able to:

  • monitor the process running your database server,
  • personalize some parameters of the server,
  • handle the storage of the databases,
  • control the access to the data(bases).

The second activity concerns query execution. The goal of the second part is to understand why some queries take more time to be executed than others. More precisely, you will be able to:

  • analyze a query execution plan,
  • define and use indexes to speed up query execution.

Activities

Part 1: DB administration

The first practical activity is dedicated to Postgresql administration. No preparation is needed to start the practical activity by a very useful documentations can be found here:

Here is the link to the first activity:

Part 2: Query execution

Warning

Before practicing, you have to read the following documentations that introduce the notions studied during the second practical activity:

The link to the second activity will be provided the 11th of February.

Here is the link to the second activity:

Knowledge Check

Answer these questions once you have finished this course module.

--- primary_color: steelblue secondary_color: lightgray text_color: black shuffle_questions: false shuffle_answers: true --- # Which step of the SQL query execution pipeline is responsible for checking table existence, column names, and user permissions? 1. [ ] A. Parsing 2. [ ] B. Validation 3. [x] C. Validation 4. [ ] D. Physical optimization # In PostgreSQL's physical optimization phase, which factor does *NOT* influence plan selection? 1. [ ]A. Data distribution from statistics 2. [ ] B. Presence or absence of indexes 3. [x] C. Index update costs 4. [ ] D. Estimated number of rows for predicates # In an EXPLAIN ANALYZE output, what does the `rows=` value represent? 1. [ ] A. The exact number of rows returned during execution 2. [x] B. A cost-based estimate derived from statistics 3. [ ] C. The number of rows stored in the table 4. [ ] D. The number of rows filtered out by WHERE clauses # Which operation is preferred when the outer side of a join has few rows and the inner side can be accessed quickly via an index? 1. [ ] A. Hash Join 2. [ ] B. Merge Join 3. [x] C. Nested Loop Join 4. [ ] D. Bitmap Heap Scan # Which type of index is specifically optimized for full-text search, JSONB fields, and array operators such as `@>` or `<@`? 1. [ ] A. B-tree 2. [ ] B. Hash 3. [x] C. GIN 4. [ ] D. GiST # In a query execution plan, which of the following is **NOT** an internal node operator described in the document? 1. [ ] Index Scan 2. [ ] Bitmap Index Scan 3. [x] Clustered Scan 4. [ ] Hash Join # PostgreSQL uses which structure to store statistics such as most common values, histograms, and null fractions? 1. [ ] pg_catalog_tables 2. [x] pg_stats 3. [ ] pg_index_store 4. [ ] system_explain_metadata