Hur man arbetar med scheman och främmande nycklar i PostgreSQL

I PostgreSQL används scheman för att organisera och strukturera databaser. Ett schema fungerar som en container för tabeller, vyer och andra databasobjekt. När du skapar ett schema utan att ange ett specifikt namn kommer objekt som tabeller som standard att hamna i det offentliga schemat, kallat "public". Om du vill skapa tabeller i ett specifikt schema måste du ange både schema- och tabellnamn.

För att skapa en tabell i ett specifikt schema, som exempelvis ett schema som heter dbschema, kan du använda följande syntax:

sql
Copy code
CREATE TABLE dbschema.employees (

    emp_id INT PRIMARY KEY, 
    first_name VARCHAR(30), 
    last_name VARCHAR(30) NOT NULL, 
    department_name VARCHAR(20)
);

För att lägga till data i denna tabell används kommandot INSERT INTO:

sql
Copy code
INSERT INTO dbschema.employees (emp_id, first_name, last_name, department_name) 

VALUES (67, 'Aisha','Bassi','Finance'), 

       (106, 'Fatima','Chandra','IT'), 
       (28, 'Laila', 'Kapoor', 'Sales');

För att visa den inlagda datan använder vi en vanlig SELECT-fråga:

sql
Copy code
SELECT * FROM dbschema.employees;

När du inte längre behöver ett schema, kan det raderas, men för att göra detta måste schema vara tomt. Om du vill ta bort ett schema som innehåller data, kan du lägga till nyckelordet CASCADE:

sql
Copy code
DROP SCHEMA dbschema CASCADE;

För att skapa restriktioner för användarnas aktiviteter i databasen kan du skapa ett schema som ägs av en viss användare. Syntaxen för att skapa ett schema som ägs av en användare är:

sql
Copy code
CREATE SCHEMA schema_name AUTHORIZATION user_name;

sql
Copy code
SET search_path TO dbschema, public;

Med detta kommando får dbschema högsta prioritet, och alla tabeller som skapas utan att specificera ett schema kommer automatiskt att hamna i dbschema.

sql
Copy code
SHOW search_path;

Detta kommando visar den aktuella sökvägen för scheman i PostgreSQL. Om du har satt dbschema som första schema i sökvägen, kan tabeller från detta schema refereras utan att ange schema-namnet.

Skapande av tabeller med främmande nycklar

Främmande nycklar är viktiga för att definiera relationer mellan tabeller i en databas. En främmande nyckel är en kolumn i en tabell som refererar till en primärnyckel i en annan tabell. En sådan relation kallas för en referensintegritet. För att skapa tabeller med främmande nycklar, börjar man med att definiera den främmande nyckeln i en tabell. Här är ett exempel där vi har två tabeller: clients och sales, där sales innehåller en främmande nyckel som refererar till clients:

sql
Copy code
CREATE TABLE clients(

    customer_id INT PRIMARY KEY, 
    first_name VARCHAR(25), 
    last_name VARCHAR(25) NOT NULL, 
    email VARCHAR(55) UNIQUE
);
CREATE TABLE sales (
    sales_id INT PRIMARY KEY, 
    date_of_purchase DATE, 
    email VARCHAR(55) UNIQUE, 
    customer_id INT, 
    CONSTRAINT FK_clients_sales FOREIGN KEY(customer_id) REFERENCES clients(customer_id)
);

Den främmande nyckeln definieras genom att använda FOREIGN KEY och refererar till primärnyckeln i den relaterade tabellen, i detta fall clients(customer_id). För att bevara dataintegriteten kan vi också definiera specifika handlingar för när en rad i föräldratabellen (t.ex. clients) tas bort eller uppdateras. PostgreSQL erbjuder flera alternativ för vad som ska hända med barnrader i en refererande tabell (t.ex. sales):

• CASCADE - Om en rad i föräldratabellen tas bort eller uppdateras, tas även de relaterade raderna i barn-tabellen bort eller uppdateras.

• SET NULL - Om en rad tas bort i föräldratabellen sätts den relaterade främmande nyckeln i barn-tabellen till NULL.

• RESTRICT - Förhindrar att rader tas bort eller uppdateras om det finns relaterade rader i barn-tabellen.

• NO ACTION - Ingen åtgärd utförs om en rad tas bort eller uppdateras i föräldratabellen.

• SET DEFAULT - Sätter främmande nyckelvärdet till standardvärdet om en rad tas bort eller uppdateras.

Här är ett exempel på att skapa tabeller med dessa regler:

sql
Copy code
CREATE TABLE parent (
    id SERIAL PRIMARY KEY, 
    name VARCHAR(50)
);
CREATE TABLE child (
    id SERIAL PRIMARY KEY, 
    parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE, 
    value VARCHAR(50)
);

När en rad tas bort från parent-tabellen, tas de relaterade raderna i child-tabellen bort automatiskt på grund av ON DELETE CASCADE-regeln.

Sammanfattningsvis är det viktigt att förstå hur scheman fungerar i PostgreSQL och hur de kan användas för att strukturera databasen på ett effektivt sätt. För att undvika konflikter i objekt som har samma namn i olika scheman, kan du använda search_path för att ange vilken ordning PostgreSQL ska följa när den söker efter tabeller och andra objekt. Genom att använda främmande nycklar kan du definiera relationer mellan tabeller och säkerställa dataintegriteten i din databas.

Hur vakuumering i PostgreSQL påverkar prestanda och databasens effektivitet

Vakuumering (VACUUM) används för att frigöra eller återta det upptagna utrymmet, vilket gör att det kan användas igen eller returneras till operativsystemet genom att ta bort dessa döda tuples. Denna process gör utrymme tillgängligt för nya data, infogningar eller uppdateringar. Det säkerställer att databasen inte blir onödigt stor och ineffektiv. Regelbunden vakuumering ser till att databasens lagringsutrymme utnyttjas effektivt. VACUUM kan också köras med kommandot ANALYZE, vilket hjälper till att både frigöra utrymme och uppdatera statistiken för databasen samtidigt, vilket optimerar databasens prestanda.

En annan viktig aspekt av vakuumering är att den förhindrar Transaction ID (XID) Wraparound. I PostgreSQL tilldelas varje transaktion ett transaktions-ID, kallat XID. Det finns ett tak på 2 miljarder transaktions-ID:n, och när detta tak nås måste de äldre ID:n "frysas" för att förhindra att det uppstår wraparound. Regelbunden vakuumering säkerställer att detta sker korrekt och förhindrar datakorruption eller systemavbrott.

Vakuumeringen upprätthåller också en Visibility Map (VM), som spårar sidor i en tabell som inte innehåller döda tuples. Denna map hjälper till att snabba upp index-bara sökningar. Det finns två typer av vakuumering i PostgreSQL: VACUUM och VACUUM FULL, och det finns en betydande skillnad mellan dem.

VACUUM FULL är mer aggressiv och återtar det oanvända utrymmet, vilket gör att operativsystemet kan återanvända det. Denna operation kräver exklusiva lås på tabellen och kan blockera andra operationer under dess körning, vilket gör att den inte är lämplig för stora tabeller i produktionsmiljöer. VACUUM FULL används när tabeller blivit kraftigt bloatade och när ett betydande utrymme behöver återtas. Syntaxen för denna operation är VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [table_name]. Med FULL optionen skrivs hela tabellen om för att återta allt oanvänt utrymme. FREEZE förhindrar XID wraparound, VERBOSE ger detaljer om processen, och ANALYZE uppdaterar databasens statistik för att optimera planeringen av frågor.

Autovacuum är en annan funktion i PostgreSQL som automatiskt utför vakuumering i bakgrunden beroende på databasens konfiguration och arbetsbelastning. Autovacuum är lättviktigt och påverkar inte databassystemets prestanda på ett negativt sätt. Parametrar som autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, och autovacuum_analyze_scale_factor styr när autovacuum ska starta, vilket antal döda rader som krävs för att trigga vakuum, samt hur stor del av tabellen som måste vakuumeras. Trots autovacuumets fördelar krävs manuell vakuumering efter större uppdateringar eller raderingar för att säkerställa att databasens utrymme återtas på ett korrekt sätt. För databaser med stora dataset bör autovacuum-parametrarna vara aggressiva.

Om vakuumering inte utförs regelbundet kan det få allvarliga konsekvenser för databasen. Tabeller kan växa onödigt stora och bli bloated, vilket gör att frågor blir långsammare och mer resurshungriga. Död tuples kommer fortfarande att scannas under frågaexekveringen, vilket försämrar prestandan. Transaktions-ID-wraparound kan också inträffa om transaktions-ID:n når sitt tak, vilket kan leda till datakorruption eller systemavbrott. Indexbloat är en annan konsekvens av ineffektiv vakuumering, där indexer blir större än nödvändigt, vilket gör indexbaserade frågor ineffektiva.