Hier sind zunächst einige Fragen:
- Können Bereiche Wertelücken enthalten?
- Warum brauchen wir eine Indexnotation für den Typ json?
- Kann ein Index mit häufigen Aktualisierungen kleiner werden als eine Tabelle? Und überhaupt nicht zu wachsen?
- Wie lange waren Sitzungen in idle_in_transaction inaktiv?
- Wie erstelle ich ein ER-Diagramm für Systemkatalogtabellen?
Client-Anwendungen
psql: \ dX - Festes Statistik-
Commit anzeigen : ad600bba
Neuer Befehl \ dX zeigt erweiterte Statistikobjekte als Liste an.
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics -[ RECORD 1 ]+------------------------------------------------ Schema | bookings Name | flights_from_to Definition | departure_airport, arrival_airport FROM flights Ndistinct | Dependencies | defined MCV | defined
Für jede Art von Statistik (Abhängigkeiten, Ndistinct, MCV) wird nur die Tatsache der Erfassung angezeigt. Die Werte selbst müssen in pg_statistic_ext_data betrachtet werden, auf das standardmäßig nur Superuser zugreifen können.
psql: \ dtS zeigt das Festschreiben von TOAST-Tabellen
: 7d80441d
Eine separate TOAST-Tabelle konnte zuvor mit dem Befehl \ d angezeigt werden. Es war jedoch nicht möglich, eine Liste solcher Tabellen mit dem Befehl \ dt oder \ dtS abzurufen. Die Auslassung wurde korrigiert. \ DtS zeigt jetzt TOAST-Tabellen als Servicetabellen an.
Aber es gibt eine Einschränkung. Alle TOAST-Tabellen befinden sich im Schema pg_toast, das wahrscheinlich nicht in search_path enthalten ist . Um die Liste zu erhalten, müssen Sie daher die entsprechende Vorlage angeben:
\dtS pg_toast.*165*
List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16529 | TOAST table | postgres pg_toast | pg_toast_16539 | TOAST table | postgres pg_toast | pg_toast_16580 | TOAST table | postgres
Die Vervollständigung der Psql-Registerkarte wurde für die Befehle CLOSE, FETCH, MOVE und DECLARE verbessert.
Commit: 3f238b88 Keine
zusätzliche Beschreibung erforderlich.
Dokumentation
Korrekturlesen und Bearbeiten der Dokumentation
verpflichten sich: 2a5862f0
Viele Leute bemerken, dass PostgreSQL eine ausgezeichnete Dokumentation hat. Aber es wird von den Entwicklern selbst geschrieben, die normalerweise nicht als Meister des Stifts gelten. Wie schaffen Sie es, eine hohe Qualität aufrechtzuerhalten? Es ist einfach. Wie bei jedem Schreiben benötigen Sie Redakteure und Korrektoren. Und so hat Justin Prizzby in den letzten zwei Jahren einen großen und wichtigen Job gemacht: das Korrekturlesen der Dokumentation. Das Ergebnis ist eine große Liste von 18 Patches. Und Mikael Paquier als Committer half ihm.
Und das ist nur eine große Verpflichtung. Und die Anzahl der kleinen Patches, die die Dokumentation verbessern, kann einfach nicht gezählt werden.
Verschiedenes
Parameter "Idle_session_timeout" - Erzwingen der Beendigung des Festschreibens von Leerlaufsitzungen
: 9877374b Der
neue Parameter " idle_session_timeout" gibt das Zeitlimit für Leerlaufsitzungen an. Wenn das festgelegte Zeitlimit überschritten wird, wird die Sitzung beendet. Der Parameter ist dem in 9.6 angezeigten idle_in_transaction_session_timeout sehr ähnlich, wirkt sich jedoch nur auf Sitzungen aus, in denen keine Transaktion gestartet wurde. Wenn Sie inaktive Sitzungen unterbrechen möchten, unabhängig davon, ob eine Transaktion in ihnen gestartet wird oder nicht, sollten daher beide Parameter festgelegt werden.
Es wird empfohlen, diesen Parameter auf Systemen, die Verbindungsabzieher oder postgres_fdw-Verbindungen verwenden, mit äußerster Vorsicht zu verwenden.
Der Parameter kann von jedem Benutzer für seine Sitzung festgelegt werden. Im folgenden Beispiel wird nach dem Festlegen des Parameters und dem Warten auf eine Sekunde ein Eintrag zum Beenden der Sitzung im Serverprotokoll angezeigt. Danach schlägt der Versuch, die Anforderung auszuführen, fehl, aber psql stellt automatisch eine neue Verbindung her:
SET idle_session_timeout = '1000ms';
--
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout ---------------------- 0
Beschreibung von depesz. Festschreiben von
GSS-Informationen im Serverprotokoll
: dc11f31a
Neue Sitzungsverbindungsnachricht im Serverprotokoll wurde mit GSS-Informationen aktualisiert, wenn diese Authentifizierungsmethode verwendet wurde.
pageinspect: Funktionen für Indizes GiST
commit: 756ab291
Für alle, die sich mit der Organisation und Speicherung von GiST-Indizes befassen möchten, bietet die Erweiterung pageinspect neue Funktionen .
Korrigieren Sie das EXPLAIN-Verhalten in Befehlen mit dem
Commit IF NOT EXISTS : e665769e
Ein Versuch, eine vorhandene Tabelle mit der Option IF NOT EXISTS zu erstellen, führt zu einer Warnung:
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
Das Abrufen eines Plans für einen solchen Befehl führt jedoch zu unerwarteten Ergebnissen. Es ist nur so, dass EXPLAIN einen Plan für SELECT ausgibt, den der Befehl erstellen kann, bevor er die Existenz der Tickets-Tabelle überprüft. Und keine Warnungen!
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN ----------------------------------------------------------------- Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
EXPLAIN ANALYZE schlägt fehl, anstatt zu warnen:
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
In Version 14 wurde das Verhalten vorhersehbar:
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
Gleiche Änderungen für den Befehl EXPLAIN [ANALYZE] CREATE MATERIALIZED VIEW IF NOT EXISTS.
Primär- und eindeutige Schlüssel zu Systemkatalogtabellen hinzugefügt Festschreiben
: dfb75e47 , 62f34097
Integritätsbeschränkungen zu Systemkatalogtabellen hinzugefügt: Primär- und eindeutige Schlüssel. Früher gab es eindeutige Indizes, jetzt werden Einschränkungen auf ihrer Grundlage vorgenommen.
So sieht es aus:
\d pg_class
Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | ... ... Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
Es gibt jedoch Ausnahmen: pg_depend, pg_shdepend. Diese beiden Tabellen haben zwei Indizes und beide sind nicht eindeutig. Es war nicht möglich, eine eindeutige Kombination von Spalten zu entwickeln. Ich muss zugeben, dass das Verständnis der Funktionsweise der Abhängigkeitstabelle keine triviale Aufgabe ist. Und die Unfähigkeit, einen Primärschlüssel zu erstellen, ist eine klare Bestätigung dafür.
Fremdschlüssel wurden jedoch nicht hinzugefügt. Dafür gibt es gute Gründe:
- Für eine Reihe von Tabellen können OID-Spalten 0 sein, wenn keine echte OID vorhanden ist, auf die Bezug genommen werden kann. Um einen Fremdschlüssel zu erstellen, müssen Sie 0 überall durch NULL ersetzen. Dies ist eine umfangreiche Umschreibearbeit, die Sie nicht übernehmen können.
- Einige Tabellen enthalten eine Spalte mit einem potenziellen Fremdschlüssel, nicht nur vom Typ oid, sondern auch von oid []. Es ist unmöglich, einen Fremdschlüssel aus einem Array zu erstellen.
Die ursprüngliche Idee des Patches bestand darin, ein Diagramm der Beziehungen zwischen den Tabellen im Systemkatalog auf der Grundlage von Informationen aus der Datenbank zu erstellen. Dies könnte automatisch durch externe Tools erfolgen. Schließlich kann das Erstellen eines Diagramms ohne Informationen zu Fremdschlüsseln nur manuell und mit regelmäßigen Änderungen nach jeder Version erfolgen, was äußerst unpraktisch ist.
Als Tom Lane feststellte, dass die Aufgabe des Patches unmittelbar nach dem Festschreiben nicht abgeschlossen war, leitete er eine neue Diskussion ein, in der er eine Kompromisslösung in Form einer Funktion vorschlug, die eine Liste von Fremdschlüsseln für alle Tabellen im Systemkatalog zurückgibt. Der Patch wurde nach dem Ende des Commitfestes im Januar verabschiedet, aber es ist logischer, ihn jetzt zu beschreiben.
Die Systemkatalogtabellen haben also keine Fremdschlüssel. Sie können jedoch Informationen darüber abrufen, indem Sie die Funktion pg_get_catalog_foreign_keys aufrufen. Die folgende Abfrage zeigt, wer auf pg_attribute verweist:
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt ----------------------+-----------------------+----------+-------- pg_attrdef | {adrelid,adnum} | f | f pg_constraint | {conrelid,conkey} | t | t pg_constraint | {confrelid,confkey} | t | f pg_index | {indrelid,indkey} | t | t pg_statistic_ext | {stxrelid,stxkeys} | t | f pg_statistic | {starelid,staattnum} | f | f pg_trigger | {tgrelid,tgattr} | t | f pg_partitioned_table | {partrelid,partattrs} | t | t (8 rows)
Überwachung
Parameter "Log_recovery_conflict_waits" - Protokollierung langer Wartezeiten zur Behebung von Wiederherstellungskonflikten
Commit: 0650ff23
Wenn der neue Parameter " log_recovery_conflict_waits" aktiviert ist , wird das Warten auf die Lösung von Wiederherstellungskonflikten durch den Startvorgang im Serverprotokoll protokolliert, wenn das Zeitlimit "deadlock_timeout" überschreitet.
Lassen Sie uns die Situation simulieren. Aktivieren Sie auf dem Replikat den Parameter, starten Sie die Transaktion und warten Sie:
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
Und jetzt zum Meister:
DELETE FROM t;
VACUUM t;
Nach einer Sekunde ( deadlock_timeout ) wird im Replikatprotokoll eine detaillierte Nachricht angezeigt, die den Konflikt beschreibt. Hier ist die Nummer des Konfliktprozesses und die LSN-Position, an der sie hängen geblieben sind, sowie der Dateiname (Tabellen lesen) mit der Blocknummer:
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot DETAIL: Conflicting process: 29119. CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
Nach weiteren 30 Sekunden Wartezeit ( max_standby_streaming_delay ) wird die Sitzung auf dem Replikat beendet, wie dies in solchen Fällen der Fall sein sollte.
Dies ist eine Fortsetzung der Arbeit, die im vorherigen Commitfest begonnen und beschrieben wurde.
Pg_stat_database-Ansicht - Statistiken zum Festschreiben von Benutzersitzungen hinzugefügt
: 960869da
Entwickler von Überwachungssystemen haben mehr Arbeit zu erledigen. Hilfreiche und coole Änderungen erwarten Benutzer von Überwachungssystemen!
In pg_stat_database wurden viele Spalten mit zusätzlichen Informationen zu Benutzersitzungen in jeder Datenbank im Cluster angezeigt:
- session_time - Gesamtzeit aller in dieser Datenbank verbrachten Sitzungen;
- active_time ― , ;
- idle_in_transaction_time ― ;
- sessions ― ;
- sessions_abandoned ― - ;
- sessions_fatal ― - FATAL;
- sessions_killed ― .
Beschreibung von depesz.
ps: Aktualisieren des Status von Prozessen, wenn ein Prüfpunkt ausgeführt wird Festschreiben
: df9274ad Sie können
Start- und Prüfzeigerprozesse überwachen, indem Sie pg_stat_activity abfragen. Es gibt jedoch drei Situationen, in denen die Ansicht pg_stat_activity nicht verfügbar ist und der Checkpointer funktioniert. Dies sind der Prüfpunkt am Ende des Absturzwiederherstellungsprozesses, der Prüfpunkt während des Serverstopps und der Neustartpunkt während des Replikationsstopps.
In diesen drei Situationen können Sie den Status der Start- und Checkpointer-Prozesse im Betriebssystem beispielsweise mit dem Dienstprogramm ps überwachen.
Ein typisches Beispiel ist die Notfallwiederherstellung. Am Ende führt der Startvorgang nach dem Überschreiben der Änderungen von WAL einen Prüfpunkt durch, was einige Zeit dauern kann. Der Status des Startvorgangs ändert sich jedoch nicht und zeigt "Wiederherstellen von NNN" an. Obwohl es nützlich wäre zu wissen, dass der Roll-Forward der Änderungen abgeschlossen ist und noch auf den Abschluss des Prüfpunkts gewartet werden muss. Der Status wird jetzt aktualisiert, um die Alarmstufe des Datenbankadministrators im Notfall zu senken.
pg_stat_statements: Als die
Commit- Statistiken gelöscht wurden : 2e0fedf0
Es besteht kein Zweifel, dass die pg_stat_statements-Statistiken regelmäßig gelöscht werden sollten. Was bringt es sonst, Informationen über Anfragen zu sammeln, die gestern, vor einer Woche, einem Monat, einem Jahr ausgeführt wurden ...
Aber woher wissen Sie, wann die Statistiken zuletzt zurückgesetzt wurden? Sehr einfach. Wir untersuchen pg_stat_statements_info:
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset -------------------------------+-------------------------- 2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset ---------+------------------------------- 0 | 2021-02-03 13:25:44.738468+03
Die Ansicht pg_stat_statements_info wurde in Version 14 eingeführt. Sie können über die Dealloc-Spalte im vorherigen Artikel lesen .
Beschreibung von depesz.
Noch nützlicher ist die Idee, nicht nur regelmäßig Statistiken zu sichern, sondern auch den Inhalt von pg_stat_statements vor jedem Speicherauszug zu speichern. In Gegenwart vieler regelmäßig durchgeführter Schnitte ist es dann möglich, Informationen für Zeitintervalle in der Vergangenheit zu erhalten. Dieser Ansatz wird von der Überwachungserweiterung pgpro_pwr verwendet .
COPY
begehen Fortschritt : 8a4f618e
Die Ansichtsfamilie pg_stat_progress_ * wurde aktualisiert! Sie können jetzt den Fortschritt des Befehls KOPIEREN überwachen.
Lassen Sie uns eine logische Kopie der Demo-Datenbank erstellen:
\! pg_dump -d demo -Fc -f demo.dump
Erweitern wir nun die Kopie in der Postgres-Datenbank in zwei Threads und sehen Sie sich während des Vorgangs die Ansicht pg_stat_progress_copy an:
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+------------------------- pid | 18771 datname | postgres relid | bookings.tickets bytes_processed | 19088527 bytes_total | 0 lines_processed | 189820 -[ RECORD 2 ]---+------------------------- pid | 18772 datname | postgres relid | bookings.boarding_passes bytes_processed | 14833287 bytes_total | 0 lines_processed | 567652
Die Spalte bytes_total wäre mit der Dateigröße gefüllt worden, als der Befehl COPY… FROM 'file' ausgeführt wurde. Im obigen Beispiel stammt der Download jedoch von einer Kopie von pg_dump, sodass die Größe nicht bekannt ist.
Der Status kann nicht nur zum Herunterladen (COPY… FROM), sondern auch zum Herunterladen (COPY… TO) von Daten überwacht werden.
Beschreibung von depesz.
Performance
Optimierung des Löschens des Puffercache-
Commits: d6ad34f3 , bea449c6
Für eine Reihe von Vorgängen müssen alle einer bestimmten Tabelle zugeordneten Puffer aus dem Puffercache entfernt werden. Diese Vorgänge umfassen Tabellenbefehle TRUNCATE und DROP, einen unterbrochenen Befehl CREATE TABLE AS SELECT und VACUUM, wenn leere Blöcke vom Ende einer Tabelle entfernt werden müssen.
Zum Entfernen wird der gesamte Puffercache gescannt, was bei großen Cache-Größen kostspielig sein kann. Für kleine Tabellen wird nun eine spezielle Struktur mit Informationen zu belegten Puffern im Speicher beibehalten, wodurch das Scannen des gesamten Puffercaches vermieden wird.
Tests haben gezeigt, dass mit shared_buffers von 100 GB oder mehr das Abschneiden von Tausenden von Tabellen mehr als 100-mal schneller ist.
Dies ist eine Fortsetzung der in Version 13 begonnenen Arbeit .
postgres_fdw: Stapelmodus zum Einfügen von Festschreibungsdatensätzen
: b663a413 Abrufen von
Daten aus externen Tabellen postgres_fdw verwendet den Stapelmodus . Datensätze werden vom externen Server in Stapeln von 100 übertragen (der Standardwert des Parameters fetch_size ). Dies ist erheblich schneller als das Senden nacheinander. Aber Arbeit Zeile für Zeile einfügen, ändern, löschen. Und deshalb sehr langsam.
Die FDW-API wurde zur Optimierung verbessert. Es stimmt, es wurde nur im Teil des Stapelmodus für Einfügevorgänge verbessert. Aktualisierungen und Löschungen dienen als zukünftige Referenz. Natürlich war postgres_fdw der erste Wrapper, der die neue API nutzte.
Lass uns nachsehen, was passiert ist. Konfigurieren Sie postgres_fdw für die Arbeit mit externen Tabellen in der Demo-Datenbank:
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
Die externe Tabelle befindet sich in der benachbarten Postgres-Datenbank:
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
Nehmen wir die Geschwindigkeit des Einfügens in die lokale Tabelle als Referenzgeschwindigkeit. Und aktivieren Sie das Timing für Messungen:
CREATE TABLE bookings_local (LIKE bookings);
\timing
In eine lokale Tabelle einfügen:
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788 Time: 165,653 ms
Und jetzt in eine externe Tabelle einfügen, ist der Stapelmodus deaktiviert. (Tatsächlich ist es aktiviert, standardmäßig beträgt die Stapelgröße 1 Zeile).
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788 Time: 6729,867 ms (00:06,730)
Fast 40 mal langsamer! Und dies ist zwischen den Basen desselben Clusters, wo es keine Netzwerkverzögerungen gibt.
Wiederholen wir das Experiment, setzen Sie jedoch die Stapelgröße (batch_size) auf 100.
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788 Time: 679,632 ms
Eine ganz andere Sache. Natürlich ist der Verlust durch lokales Einfügen immer noch spürbar, ~ 4-mal, aber immer noch nicht 40!
Und schlussendlich. Die Größe des Stapels zum Einfügen (batch_size) könnte in den Parametern des externen Servers festgelegt werden. Dann wäre sie für alle Tabellen gültig, in denen sie nicht explizit definiert ist.
Löschen von Indexzeilen von unten nach oben Festschreiben
: 9dc718bd , d168b666
Diese Optimierung versucht zu vermeiden, dass die Indexseite in UPDATE-Operationen bis zuletzt durch zwei geteilt wird, wenn sich die Indexspalten nicht geändert haben. Bevor Sie dem Index eine neue Version einer Zeile hinzufügen, müssen Sie prüfen, ob Sie unnötige Zeilen auf dieser Seite entfernen können. Wenn Sie beispielsweise eine Kette unnötiger doppelter Indexzeilen finden, die auf dieselbe Tabellenzeile verweisen, können Sie diese Zeilen löschen. Peter Geigan, der Autor des Patches, nannte diese "Bottom-up-Löschung".
Ein ähnliches Problem (um ein Indexwachstum zu vermeiden) wird durch die HOT-Update-Optimierung gelöst. Wenn das UPDATE keine der indizierten Spalten ändert, werden möglicherweise keine neuen Versionen der Zeilen in den Indizes erstellt. Und wenn es mehrere Indizes in der Tabelle gibt und sich die Spalte von nur einem ändert? In diesem Fall ist das HOT-Update kein Assistent.
Lassen Sie uns überprüfen, was "von unten nach oben löschen" kann. Nehmen wir für das Experiment eine Tabelle mit zwei separat indizierten Spalten und deaktivierter automatischer Reinigung.
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size --------+-------------+------------- 8192 | 16384 | 16384
Vor der Massenaktualisierung befindet sich eine Zeile in der Tabelle. Die Tabellengröße beträgt eine Seite, und beide Indizes belegen zwei Seiten (Serviceseite + Datenseite).
Jetzt ändern wir 100.000 Mal nur eine Spalte col2 und sehen uns die Größe der Tabelle und der Indizes an.
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 2121728 | 2260992
Diese Ergebnisse wurden in PostgreSQL 12 erhalten. Wie Sie sehen, funktionierte das HOT-Update nicht und beide Indizes wuchsen fast gleich groß.
Nun das gleiche Experiment in PostgreSQL 13:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 663552 | 2260992
Der t_col1-Index, bei dem es keine Änderungen gab, stieg viel weniger um das ~ 3,5-fache. Dies ist das Ergebnis einer bekannten Optimierung der Version 13: Index-Deduplizierung . Trotzdem ist er aufgewachsen.
Und zum Schluss wollen wir sehen, was in PostgreSQL 14 enthalten ist:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 16384 | 2260992
Wow! Im Index t_col1 gibt es nur noch eine Seite mit Daten. Das ist cool!
Wenn das Autovakuum eingeschaltet wäre, könnte es natürlich Zeit haben, einige der Deadlines während des Experiments zu löschen. Aber dafür ist das Experiment gedacht. Darüber hinaus hat Autovacuum unter realen Bedingungen mit häufigen Aktualisierungen (ein gutes Beispiel sind die Warteschlangentabellen) definitiv keine Zeit, alles rechtzeitig zu bereinigen.
Beschreibung von Viktor Egorov.
Die parallele Ausführung REINDEX GLEICHZEITIG
begehen: f9900df5
Im commitfest Artikel November ich bereits schrieb über die nicht-blockierende parallele Ausführung von INDEX GLEICHZEITIG CREATE. Eine ähnliche Optimierung ist jetzt für REINDEX CONCURRENTLY verfügbar.
Verfahrenssprachen
Prozeduren lassen sich schneller ausführen
Commit: ee895a65
Prozeduren wurden konzipiert, um Transaktionen abschließen zu können. Wenn eine solche Prozedur, die COMMIT ausführt, selbst mehrmals aufgerufen wird, beispielsweise in einer Schleife, werden bei jeder Iteration der Schleife alle Anweisungen innerhalb der Prozedur erneut analysiert.
Es gab keinen ernsthaften Grund, Befehle neu zu analysieren, was im Patch beseitigt wurde. Das Aufrufen von Prozeduren in einer Schleife erfordert jetzt weniger Arbeit und Ressourcen. Infolgedessen läuft es schneller.
PL / pgSQL: Zuweisungsoperator vollständig überarbeitetes
Commit: 844fe9f1 , c9d52984 , 1788828d , 1c1cbe27
Ohne weiteres:
DO $$
<<local>>
DECLARE
a bookings[];
x bookings;
BEGIN
/* */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00) NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00) NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00) DO
Jetzt können Sie in einem PL / pgSQL-Block Array-Elementen eines zusammengesetzten Typs sowie Array-Slices Werte zuweisen.
Zu diesem Zweck wurde der PL / pgSQL-Zuweisungsoperator komplett neu gestaltet. Und der Server-Parser hat gelernt, PL / pgSQL-Ausdrücke zu analysieren.
Um einen Ausdruck auszuwerten, müssen Sie keinen Befehl wie
«
SELECT expr mehr ausgeben
»
. Sie können dies leicht überprüfen, indem Sie sich die Fehlermeldung im folgenden Beispiel ansehen:
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a" LINE 1: 2 + 'a' ^ QUERY: 2 + 'a' CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
Das SELECT-Wort befindet sich nicht mehr in der QUERY-Zeile.
Reproduzieren
Replikatbehandlung von Konfigurationsparameteränderungen im
Commit des Assistenten : 15251c0a
Änderungen im Assistenten an Einstellungen, die sich auf die Größe des gemeinsam genutzten Speichers des Servers auswirken, bleiben bei physischen Replikaten nicht unbemerkt. Wenn ein WAL-Datensatz beim Replikat eintrifft, der besagt, dass sich diese Parameter geändert haben, wird das Replikat automatisch gestoppt und alle aktuellen Sitzungen werden beendet. Die Liste der Parameter finden Sie in der Dokumentation .
Es ist nicht sehr gut. Daher haben wir Folgendes getan: Wenn das Replikat eine Aufzeichnung der Änderungen der Parameter erhält, funktioniert es weiterhin, unterbricht jedoch die Replikation und gibt eine Nachricht an das Protokoll aus. Der Administrator kann warten, bis wichtige Sitzungen beendet sind, die Parameter in den Konfigurationsdateien so aktualisieren, dass sie mit den Werten auf dem Master übereinstimmen, und die Replikation unterbrechen. Das Replikat wird sofort danach gestoppt. Da die Konfigurationsänderungen jedoch bereits vorgenommen wurden, kann es sofort gestartet werden, um Ausfallzeiten zu minimieren.
Ändern Sie den Befehl "restore_command", ohne das Server-
Commit neu zu starten : 942305a3
Fortsetzung der in Version 13 übernommenen Arbeit von Sergei Kornilov . Dann wurde es möglich, die Parameter zu ändern, ohne den Server neu zu starten primary_conninfo , primary_slot_name und wal_receiver_create_temp_slot .
Jetzt wurde ihnen restore_command hinzugefügt .
Server
Verbesserte Verwendung des
Commits für erweiterte Statistiken : 25a9e54d
Erweiterte Statistiken werden jetzt in mehr Fällen verwendet, um die Kardinalität von Bedingungen in Abfragen zu bewerten. Insbesondere werden erweiterte Statistiken jetzt verwendet, wenn verschiedene Bedingungen, für die erweiterte Statistiken einzeln verwendet werden können, über ODER kombiniert werden.
In diesem Beispiel werden erweiterte Statistiken für die Abflug- und Ankunftsflughäfen erfasst. Und dann zählen wir die Anzahl der Flüge zwischen Sheremetyevo und Pulkovo oder in die entgegengesetzte Richtung.
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
Die genaue Anzahl der Flüge beträgt 610. Vergleichen Sie mit den Schätzungen des Planers in den Versionen 13 und 14.
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13:
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14:
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
Wie Sie sehen können, ist die Schätzung in Version 14 nahezu genau.
Allgemeine Infrastruktur zur Unterstützung der Indexnotation für
Commits für Datentypen : c7aba7c1 , 0ec5f7e7 , 676887a3 Die
Indexnotation wird für die Arbeit mit Arrays verwendet. Lassen Sie uns zum Beispiel das Element mit Index 3 finden:
SELECT (ARRAY[10,20,30,40,50])[3];
array ------- 30
Es gibt jedoch auch andere Datentypen, bei denen diese Syntax bequem zu verwenden wäre. Zunächst sprechen wir über Json. Mit der Idee, die Indexnotation für json zu unterstützen, begann Dmitry Dolgovs lange Arbeit an diesem Patch.
Und jetzt, einige Jahre später, erschien eine solche Unterstützung. Der erste Patch erstellt die erforderliche Indexnotationsinfrastruktur für beliebige Datentypen. Der zweite Patch fügt dem hstore-Typ eine Indexnotation hinzu, der dritte dem json b- Typ .
Anstelle von Sonderfunktionen und Operatoren können Sie jetzt die erforderlichen Teile aus dem json-Wert extrahieren. Die Telefonnummer finden Sie in den Kontaktdaten eines der Tickets:
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------------------------------------- contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"} phone | "+70844502960"
Die Indexnotation kann auch zum Schreiben in jsonb verwendet werden. Fügen wir die Adresse dem zuvor gefundenen Kontakt von Irina Antonova hinzu:
UPDATE tickets
SET contact_data['address'] =
'{"city": "",
"street": " ",
"building": "7"
}'::jsonb
WHERE ticket_no = '0005432000994';
Beachten Sie, dass die Adresse selbst zusammengesetzt ist und Sie auch die Indexnotation verwenden können, um auf Teile davon zu verweisen:
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]---------------------------------------------------------------- address | {"city": "", "street": " ", "building": "7"} city | "" street | " " building | "7" phone | "+70844502960" email | "antonova.irina04121972@postgrespro.ru"
Es ist sehr bequem!
(Klarstellung. Alle Kontakte in der Demo-Datenbank sind fiktiv und es gibt keinen solchen Mitarbeiter in Postgres Pro.)
Beschreibung für hstore von depesz.
SQL-Befehle
Mehrbereichsdatentypen
verpflichten: 6df7a969
Jeder Bereich jetzt Datentyp hat seinen eigenen Mehrbereichsdatentyp. Dieser Typ ist im Wesentlichen ein Array von einzelnen Bereichen. Bereiche innerhalb eines Mehrbandtyps dürfen sich nicht überlappen, es können jedoch Lücken zwischen den Bereichen bestehen.
Reguläre Bereiche sind zusammenhängende Wertebereiche des entsprechenden Subtyps: in4range-Bereich für int-Subtyp, timestamptz-Bereich für timestamp-Subtyp usw. Aber was ist, wenn Sie Bereiche mit Lücken an einigen Stellen speichern müssen? Hier kommen Multi-Bands zur Rettung.
Angenommen, wir möchten die Commitfest-Zeiten für jede PostgreSQL-Version in der Tabelle speichern. Ein einzelnes Commitfest kann als ein Monat dauern. Aber wie kann man alle fünf Commitfests einer Version darstellen?
Der Bereich für den Subtyp timestamptz heißt tstzrange und der Multirange ist tstzmultirange. Die verfügbaren Typen sind in der Dokumentation beschrieben . Erstellen Sie eine Tabelle:
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
Um Werte zu bilden, verwenden wir den Konstruktor:
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
Die Liste der Funktionen und Operatoren für die Arbeit mit Multiband-Typen enthält dieselben wie für normale Bänder sowie diejenigen, die nur für Multibänder vorgesehen sind.
Zum Beispiel können wir herausfinden, an welcher Version von PostgreSQL die Entwickler-Community im letzten neuen Jahr gearbeitet hat:
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version --------- 14
Oder Start- und Enddatum für die Arbeit an Version 13:
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper ------------------------+------------------------ 2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
Es können neue benutzerdefinierte Mehrbereichstypen erstellt werden. Dies ist nützlich in Fällen, in denen kein eingebautes Band und kein entsprechender Multiband-Typ vorhanden ist. Es wird derselbe Befehl CREATE TYPE… AS RANGE verwendet, in dem Sie einen Namen für den automatisch generierten Multi-Range-Typ angeben können.
Zum Beispiel interessieren uns Bereiche und Multiränge von Zeit, Subtyp Zeit. Um einen Bereich zu erstellen, benötigen Sie eine Funktion, die die Differenz zwischen zwei Werten der Typzeit berechnet:
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
Wir erstellen einen Typ für den Zeitbereich und gleichzeitig für den Mehrbereich:
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
Jetzt kann die Arbeitszeit durch den folgenden Ausdruck gebildet werden:
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours ------------------------------------------- {[09:00:00,13:00:00),[14:00:00,18:00:00)}
Beschreibung von depesz.
Ltrim- und rtrim-Funktionen für binäre Zeichenfolgen werden festgeschrieben
: a6cf3df4 Mit der Funktion btrim können Sie
auch Bytes am Anfang und Ende einer Bytea-Zeichenfolge gleichzeitig trimmen. Mit den neuen Funktionen ltrim und rtrim für binäre Zeichenfolgen können Sie jetzt jede Kante separat trimmen.
GRANTED BY-Phrase in den Befehlen GRANT und REVOKE
das Commit: 6aaaa76b
Zur Kompatibilität mit dem SQL-Standard in den Befehlen GRANT und REVOKE wurde die optionale Phrase GRANTED BY hinzugefügt. Beispielsweise:
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
Der Rollenname in GRANTED BY muss mit der aktuellen Rolle übereinstimmen. Es wird also nicht funktionieren, Rechte für eine andere Rolle auszugeben / wegzunehmen. Der Satz wurde hinzugefügt, um dem Standard zu entsprechen.
Dies ist eine Fortsetzung der im Commitfest-Artikel vom September beschriebenen Arbeit .
Systemadministration
initdb --no-Anweisungen festschreiben
: e09155bd
Das Dienstprogramm initdb wird zum Initialisieren des Clusters verwendet. Am Ende seiner Arbeit wird eine Anweisung zum Starten eines Clusters angezeigt:
Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
Dies ist jedoch nicht immer der Fall. In Debian-Paketverteilungen wird beispielsweise das Dienstprogramm pg_ctlcluster zum Starten des Clusters verwendet, nicht pg_ctl. Und ihre Parameter sind unterschiedlich.
Mit der neuen Option --no-Anweisungen gibt initdb keine Startempfehlung mehr an, die Paketverteilungen nutzen können.
pg_dump: Wiederherstellen einer einzelnen Partition als eigenständiges Tabellen-
Commit: 9a4c0e36 , 9eabfe30
Wenn eine partitionierte Tabelle in der logischen Kopie von pg_dump enthalten ist, kann keine separate Partition von einer solchen Kopie als unabhängige Tabelle wiederhergestellt werden. Unmittelbar nach dem Befehl CREATE TABLE kommt der Befehl ALTER TABLE… ATTACH PARTITION, der in einer solchen Situation nicht nur unnötig ist, sondern auch mit einem Fehler endet. Die übergeordnete Tabelle wurde nicht wiederhergestellt.
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
... CREATE TABLE public.t_p1 ( id integer ); ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001); ...
Jetzt ALTER TABLE… ATTACH PARTITION-Befehle für alle Partitionen werden separat und nach allen Befehlen zum Erstellen von CREATE TABLE-Partitionen entladen. Daher wird beim Wiederherstellen einer einzelnen Partition, die mit der Option -t angegeben wurde, nur der Befehl CREATE TABLE ausgeführt, wodurch die Partition als unabhängige Tabelle wiederhergestellt werden kann.
Das ist alles für jetzt. Wir warten auf das letzte Commitfest am 14. März .