JCL

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:lectures:04

L: 08/03/2021

Master in Informatics and Computing Engineering
Database and Web Applications Laboratory
Instance: 2020/21


Lecture #4 :: 08/03/2021

Goals

By the end of this class, the student should be able to:

  • Describe the workload of the information system and the performance goals.
  • Develop the Database Physical Scheme.
  • Describe possible adjustments to the database to improve performance.
  • Describe PostgreSQL indexing.
  • Describe the possible adjustments to the logical schema of the database to improve performance.

Content

  1. Introduction
    • Query optimisation
    • EXPLAIN query plans
  2. Physical Database Schema
    • The workload
    • Queries
    • Updates
    • Performance
    • Physical Schema
  3. Select indexes
    • Indexes
    • Clustering
    • Examples
    • Index-only plans
  4. PostgreSQL
    • Indexes.
    • Full text search
  5. Database tuning
    • Tuning the Relational Schema
    • Denormalisation
    • Tuning queries and views
    • Horizontal decomposition

Bibliography

  • R. Ramakrishnan, J. Gehrke. Database Management Systems. McGRAW-Hill International Editions, 3rd Edition, 2003, ISBN=0-07-246563-8 (chapters 8, 12, 16, 20)
  • The PostgreSQL Global Development Group. PostgreSQL 13.2 Documentation. Online in http://www.postgresql.org/docs/13/static/index.html, last accessed on March 2021

Materials

Summary

  • Database Physical Schema. Indexes. PostgreSQL. Tuning the Conceptual Schema: denormalisation, vertical decomposition, tuning queries and views. Database tuning. By André Restivo.

LBAW, 2020/21

« Previous | Index | Next »

teach/lbaw/lectures/04.txt · Last modified: 04/03/2021 21:56 by Correia Lopes