PostgreSQL 9.1 - aneb stále vpřed
Po roce je tu nová verze PostgreSQL - respektive máme tu betu - a je tudíž jasné, co v 9.1 bude a nebude. V podstatě až na příkaz MERGE, který bude až v 9.2, byly plány pro tuto verzi splněny. PostgreSQL nyní podporuje COLLATION podle ANSI/SQL. Nechybí ani podpora SQL/MED (čtení externích csv souborů). Celkově změn není tolik jako v 9.0, ale stále je jich dost - stále se přidávají nové funkce. Během roku bylo akceptováno několik set patchů (zdrojový kód PostgreSQL přesáhl magickou hranici jednoho miliónu řádků). Uživatelé PostGISu uvítají zásadní rozšíření GiST indexů KNNGIST a zatím nevídaný pokrok ve vývoji PL/Pythonu. Pro enterprise řešení má význam nová implementace úrovně izolace transakcí SERIALIZABLE. Pro ukládání dočasných dat jsou zajímave tzv unlogged tables.
Tím nejdůležitějším, co stalo během posledního roku, byl přechod z CVS na GIT. Uživatele samozřejmě vůbec nezajímá, který systém pro správu zdrojových kódů projekt používá. Nicméně způsob, jakým se udržují zdrojové kódy je naprosto zásadní pro vývojáře, pro komunitu. Výběru GITu předcházelo několik let diskuzí. I tak vlastní konverze nebyla úplně jednoduchá a bezbolestná. Vývojáři PostgreSQL rozhodně nechtěli přijít o historii uloženou v CVS repozitáři a o na CVS napojenou infrastrukturu, což se ukázalo jako problém během pilotního projektu. Několik měsíců vývojáři z PostgreSQL core týmu participovali na vývoji GITu - na dopilování konverze z CVS.
SQL
Drobnou ale zásadní změnou je přechod na tzv ANSI SQL řetězce. Počínaje 9.1 PostgreSQL implicitně nepodporuje escape sekvence v řetězcích (pozn. v konfiguraci lze povolit). Pokud jsou potřeba escape sekvence, musí se použít tzv rozšířené řetězce (extended strings):
postgres=# SELECT e'aa\taa'; ?column? ------------ aa aa (1 row)
Hodnocení 9.1 není úplně jednoduché. Většinu novinek (snad vyjma KNNGIST) bych neoznačil jako převratné, nebo přelomové. Některé nové funkce jsou systémovou náhradou předchozích improvizací. Jiné aktuálně nepřinášejí výraznou "přidanou hodnotu", ale otevírají vrátka pro budoucí použití (např. infrastruktura proSQL/MED). Kdybych chtěl šokovat, tak bych dal na první místo podporu funkčních závislostí v klauzuli GROUP BY. Stará poučka, že atribut se musí nacházet buď v agregační funkci nebo v klauzuli GROUP BY už neplatí:
-- sloupec id musí být primárním klíčem tabulky zamestnanci
postgres=# SELECT * FROM zamestnanci;
id | jmeno | prijmeni
----+-------+----------
1 | Pavel | Stehule
2 | Tomas | Marny
(2 rows)
postgres=# SELECT * FROM mzdy;
zamestnanec_id | vyplaceno | castka
----------------+------------+----------
1 | 2010-06-01 | 10000.00
1 | 2010-07-01 | 10020.00
2 | 2010-05-01 | 8020.00
2 | 2010-06-01 | 12020.00
2 | 2010-07-01 | 13020.00
(5 rows)
-- dotaz napsaný s využitím funkční závislosti
postgres=# SELECT jmeno, prijmeni, sum(castka)
FROM zamestnanci
JOIN
mzdy
ON id = zamestnanec_id
GROUP BY id;
jmeno | prijmeni | sum
-------+----------+----------
Pavel | Stehule | 20020.00
Tomas | Marny | 33060.00
(2 rows)
Zatím jediná podporovaná funkční závislost je závislost na primárním klíči. Po této funkci dlouho volali uživatelé MySQL. Nejsem si úplně jistý, jestli budou spokojeni. PostgreSQL stále nedovolí napsat dotaz s agregační funkcí libovolně.
Podporou tzv COLLATION PostgreSQL dohání ostatní databáze v podpoře multi jazykových aplikací. Nyní lze definovat locale na úrovni sloupce. Data v konkrétním sloupci lze řadit jednou podle českých pravidel, a podruhé podle jiných pravidel. Drobným zádrhel je platformová závislost identifikátoru COLLATE (Microsoft používá jiný systém než unixová locales). Naštěstí lze vytvářet vlastní COLLATE a tím rozdíl v názvech mezi jednotlivými platformami překlenout.
postgres=# CREATE COLLATION czech FROM "cs_CZ.utf8";
CREATE COLLATION
postgres=# CREATE TABLE lide(prijmeni VARCHAR COLLATE czech);
CREATE TABLE
postgres=# \d lide
Table "public.lide"
Column | Type | Modifiers
----------+-------------------+---------------
prijmeni | character varying | collate czech
postgres=# show lc_collate ;
lc_collate
------------
en_US.utf8
(1 row)
postgres=# SELECT * FROM lide ORDER BY 1;
prijmeni
-----------
Crha
Holý
Hynek
Chromečka
(4 rows)
postgres=# SELECT * FROM lide ORDER BY prijmeni COLLATE "en_US.utf8";
prijmeni
-----------
Chromečka
Crha
Holý
Hynek
(4 rows)
Jediným omezením je, že všechna data v databázi musí mít stejné kódování - tedy nelze mít jeden sloupec s kódováním latin2, další s latin1 a třetí s UTF8. I z tohoto důvodu se doporučuje preferovat kódování UTF8 (pozn. oblíbené C collate je použitelné pouze pro 8bit kódování. Jeho obdobou pro UTF8 je ucs_basic collation).
Uživatelé čekali na možnost použít příkazy INSERT, UPDATE, DELETE v CTE dotazech. Ta možnost tu nyní je:
-- zřetězení SQL příkazů (DML statements pipelining)
postgres=# SELECT * FROM foo;
a
----
30
20
10
(3 rows)
postgres=# SELECT * FROM deleted;
a
---
(0 rows)
postgres=# WITH t1 AS (DELETE FROM foo RETURNING *),
t2 AS (INSERT INTO deleted
SELECT * FROM t1 RETURNING *)
SELECT max(a) FROM t2;
max
-----
30
(1 row)
postgres=# SELECT * FROM foo;
a
---
(0 rows)
postgres=# SELECT * FROM deleted;
a
----
30
20
10
(3 rows)
-- recursive UPDATE
postgres=# WITH RECURSIVE t AS (SELECT *, 0 AS ll
FROM xx
WHERE parent IS NULL
UNION ALL
SELECT xx.*, ll + 1
FROM xx
JOIN
t
ON t.id = xx.parent) UPDATE xx SET level = ll
FROM t
WHERE t.id = xx.id;
UPDATE 5
postgres=# SELECT * FROM xx;
id | parent | level
----+--------+-------
0 | | 0
1 | 0 | 1
2 | 0 | 1
3 | 1 | 2
4 | 3 | 3
(5 rows)
Popis přístupu k externím zdrojům je obsahem části SQL standardu SQL/MED. 9.1 obsahuje pilotní implementaci přístupu k tzv cizím tabulkám - podporován je zatím pouze přístup k textovým souborům ve formátu čitelném příkazem COPY.
postgres=# COPY psc TO '/tmp/psc.data';
COPY 3356
postgres=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
postgres=# CREATE FOREIGN DATA WRAPPER file_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator;
CREATE FOREIGN DATA WRAPPER
postgres=# CREATE FOREIGN TABLE tbl (
psc numeric,
posta text
)
SERVER file_server
OPTIONS (format 'text', filename '/tmp/psc.data');
CREATE FOREIGN TABLE
postgres=# SELECT * FROM tbl WHERE psc = 25601;
psc | posta
-------+-----------------
25601 | Benešov u Prahy
(1 row)
Time: 43.562 ms
postgres=# EXPLAIN SELECT * FROM tbl WHERE psc = 25601;
QUERY PLAN
---------------------------------------------------------
Foreign Scan on tbl (cost=0.00..86.39 rows=4 width=64)
Filter: (psc = 25601::numeric)
Foreign File: /tmp/psc.data
Foreign File Size: 66424
(4 rows)
postgres-# \det+
List of foreign tables
Schema | Table | Server | Options
--------+-------+-------------+--------------------------------------
public | tbl | file_server | {format=text,filename=/tmp/psc.data}
(1 row)
9.1 obsahuje pouze základní infrastrukturu potřebnou pro SQL/MED. Handlery pro další typy externích zdrojů přijdou na řadu v následující verzi - existuje prototyp pro přístup k dalším PostgreSQL databázím - a je tedy jen otázkou času, kdy někdo napíše handler pro MySQL nebo DBI. Externí zdroje jsou, podle standardu SQL/MED, přístupné pouze pro čtení.
Moje oblíbené funkce array_to_string a string_to_array konečně podporují NULL, a to prostřednictvím třetího parametru, který specifikuje řetězec, kterým se nahradí NULL:
postgres=# SELECT array_to_string(array[1,null,1],',','-');
array_to_string
-----------------
1,-,1
(1 row)
postgres=# SELECT string_to_array('1,-,1',',','-')::int[];
string_to_array
-----------------
{1,NULL,1}
(1 row)
Pro aplikační programátory jsou k dispozici nové funkce pro práci s řetězci - left, right, reverse, concat a concat_ws. Poslední dvě zmiňované funkce můžete znát z MySQL. V PostgreSQL se chovají úplně stejně. O něco složitější funkce “formát” je navržena k sestavování řetězců různých hlášení, zápisů do logu, varování atd. Kromě toho ji lze použít k bezpečnému sestavení SQL řetězce, který se použije v dynamickém SQL. K tomu slouží formátovací tagy %I, %L, které zajistí správné escapování hodnoty, tak aby výsledný SQL řetězec byl syntakticky správný a bezpečný vůči SQL injection. Funkce podporuje i poziční tagy:
postgres=# SELECT format('%s %s', 'Hello', 'World');
format
-------------
Hello World
(1 row)
postgres=# SELECT format('INSERT INTO %I(%I) VALUES(%L)',
'moje tabulka'::regclass, 'muj sloupec','nějaká hodnota');
format
--------------------------------------------------------------------
INSERT INTO "moje tabulka"("muj sloupec") VALUES('nějaká hodnota')
(1 row)
Trochu oklikou se dostávám k původní implementaci klauzule USING v PL/pgSQL příkazu EXECUTE. Parametrické dynamické SQL (klauzule USING) a funkce format se dobře doplňují. Při správném použití je prakticky vyloučené napsat aplikaci napadnutelnou útokem typu SQL injection.
Jazyk PL/pgSQL byl rozšířen o cyklus FOREACH - iteraci nad polem s možností iterací po řádcích. Alespoň z mého pohledu je PL/pgSQL kompletní. Nenapadá mne, co by ještě mělo být do tohoto jazyka přidáno - přičemž by zůstal rozumný poměr mezi užitkem pro uživatele a velikostí patche (uvítám diskuzi na toto téma - o co rozšířit PL/pgSQL?). Pokud budou v budoucnu nějaké změny, tak nejspíš nebudou pro uživatele příliš viditelné a myslím si, že minimálně v následující verzi žádné nebudou:
CREATE FUNCTION sum_values(VARIADIC int[]) RETURNS int8 AS $$
DECLARE
s int8; x int;
BEGIN
FOREACH x IN ARRAY $1
LOOP
s := s + x;
END LOOP;
RETURN s;
END;
$$ LANGUAGE plpgsql;
Příkaz FOREACH s klauzulí SLICE umožňuje iterovat přes vícerozměrná pole. Iteraci přes položky typu ROW nebo RECORD lze řešit pomocí knihovny PLToolbox.
Funkce v PLPerlu mohou mít parametry typu RECORD. Parametry typu pole se interpretu perlu nyní předávají v binárním formátu - což by mělo vést k lepšímu výkonu (odpadne serializace a deserializace) a k eliminaci problémů s vícerozměrnými poli. Změn v PLPythonu je tolik, že je nebudu jmenovat, kromě snad té nejdůležitější - funkce v PLPythonu je validována již v době registrace. Až do verze 9.0 integrace Pythonu pokulhávala za integrací Perlu. Myslím si, že počínaje 9.1 je podpora uložených procedur v Pythonu na vyšší úrovni než podpora externích uložených procedur v Perlu.
Implementace úrovně SERIALIZABLE v PostgreSQL odpovídala ANSI SQL 92, ale už nikoliv aktuálnímu pojetí standardu a implementaci v ostatních db (a také potřebám vývojářů). To působilo problémy aplikačním vývojářům při portaci svých aplikací pro PostgreSQL. Dalším nepříjemným důsledkem bylo špatné hodnocení PostgreSQL v komerčních testech, z kterých pg vycházela jako db, která chybně implementuje transakce. To by se nyní mělo změnit a to díky práci Kevina Grittnera a Dana Portse. Nová implementace detekuje zda-li došlo nebo nedošlo k race-condition a v případě, že došlo, stornuje dotčenou transakci. Úroveň izolace REPEATABLE READ se nezměnila (v předchozích verzích byly úrovně REPEATABLE READ a SERIALIZABLE totožné). Nově mají transakce v úrovni SERIALIZABLE o poznáná větší režii, takže platí: co nejkratší transakce, pokud uvnitř transakce nedochází k modifikaci dat, tak je vhodné transakci označit jako READ ONLY, a konečně - vyvarovat se používání klauzulí FOR UPDATE, FOR SHARE - jelikož jsou v SERIALIZABLE úrovni zbytečné. Také je nutné dávat pozor na počet aktivních spojeních, která zustanou ve stavu "idle in transaction". Nepříjemné důsledky jsou o poznání horší než u předchozích verzí. Podpora úrovně SERIALIZABLE zatím nebyla přidána do Hot StandBy režimu, takže v případě, že se používá binární replikace, je nejvyšší možná úroveň izolace transakcí REPEATABLE READ.
Minimum uživatelů zná mechanismus tzv. rules. S jejich pomocí lze do jisté míry předefinovat SQL příkazy - lze např. vytvořit aktualizovatelné pohledy. Rules je mechanismus, který vystupuje z šera dávnověku Postgresu - ještě z dob, kdy procedurální rozšíření a triggery byly v SQL tabu. Díky tzv INSTEAD TRIGGERům by se PostgreSQL mohlo zbavit rules - minimálně jeho uživatelé. INSTEAD TRIGGERY jsou klasické triggery definované nad pohledem. Jejich prostřednictvím lze snadno vytvořit aktualizovatelné pohledy. Dovedu si představit několik málo situací, kdy se to může hodit. Naučil jsem se nebát se triggerů, ale snažím se triggery psát tak, abych uvnitř triggerů pokud možno co nejméně modifikoval data (to neplatí pro audit tabulky plněné triggery):
postgres=# \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
Administrace
Administrace externích modulů by se měla zjednodušit díky tzv. rozšířením (extensions). V podstatě jde o propracovanější evidenci externích zákaznických funkcí - náhradu za dříve používané install a uninstall skripty. Práce s těmito skripty nebyla úplně přímočará a při troše smůly mohli mít běžní uživatelé problémy (zejména při upgrade databáze). Extensions jsou pouze evidencí - není to nástroj odpovídající oráklovským packages nebo modulům z DB2 (dle ANSI). Primárním cílem je zjednodušit instalaci externích modulů:
postgres=# CREATE EXTENSION citext;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------+---------+--------+--------------------------------------------------
citext | 1.0 | public | data type for case-insensitive character strings
(1 row)
postgres=# DROP EXTENSION citext ;
DROP EXTENSION
postgres=# select * from pg_available_extensions;
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+----------------------------------------------------------------------
citext | 1.0 | | data type for case-insensitive character strings
chkpass | 1.0 | | data type for auto-encrypted passwords
file_fdw | 1.0 | | foreign-data wrapper for flat file access
cube | 1.0 | | data type for multidimensional cubes
pg_stat_statements | 1.0 | | track execution statistics of all SQL statements executed
pg_buffercache | 1.0 | | examine the shared buffer cache
dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
earthdistance | 1.0 | | calculate great-circle distances on the surface of the Earth
xml2 | 1.0 | | XPath querying and XSLT
moddatetime | 1.0 | | functions for tracking last modification time
dict_int | 1.0 | | text search dictionary template for integers
lo | 1.0 | | Large Object maintenance
pageinspect | 1.0 | | inspect the contents of database pages at a low level
pgcrypto | 1.0 | | cryptographic functions
pgstattuple | 1.0 | | show tuple-level statistics
pg_freespacemap | 1.0 | | examine the free space map (FSM)
btree_gist | 1.0 | | support for indexing common datatypes in GiST
dblink | 1.0 | | connect to other PostgreSQL databases from within a database
intarray | 1.0 | | functions, operators, and index support for 1-D arrays of integers
adminpack | 1.0 | | administrative functions for PostgreSQL
unaccent | 1.0 | | text search dictionary that removes accents
intagg | 1.0 | | integer aggregator and enumerator (obsolete)
pgrowlocks | 1.0 | | show row-level locking information
pg_trgm | 1.0 | | text similarity measurement and index searching based on trigrams
btree_gin | 1.0 | | support for indexing common datatypes in GIN
tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab
autoinc | 1.0 | | functions for autoincrementing fields
isn | 1.0 | | data types for international product numbering standards
timetravel | 1.0 | | functions for implementing time travel
fuzzystrmatch | 1.0 | | determine similarities and distance between strings
tsearch2 | 1.0 | | compatibility package for pre-8.3 text search functions
insert_username | 1.0 | | functions for tracking who changed a table
hstore | 1.0 | | data type for storing sets of (key, value) pairs
ltree | 1.0 | | data type for hierarchical tree-like structures
seg | 1.0 | | data type for representing line segments or floating-point intervals
test_parser | 1.0 | | example of a custom parser for full-text search
refint | 1.0 | | functions for implementing referential integrity (obsolete)
(37 rows)
Vytváření binárních online záloh zjednoduší nová aplikace pg_basebackup. Pro zajištění replikace a zálohování je nutné mít status REPLICATION a odpovídající záznam v pg_hba.conf.
postgres=# ALTER ROLE pavel REPLICATION; ALTER ROLE
Upravený soubor pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident local replication pavel ident
Změny v postgres.conf (minimálně jeden wal sender a aktivovaný reim archivace nebo Hot Standby):
wal_level = archive # minimal, archive, or hot_standby archive_mode = on # allows archiving to be done max_wal_senders = 1 # max number of walsender processes archive_command = '/bin/true' # command to use to archive a logfile segment wal_keep_segments = 100 # in logfile segments, 16MB each; 0 disables
Po provedení příkazu mám v adresáři zaloha_pg funkční zálohu db clusteru:
[pavel@nemesis ~]$ /usr/local/pgsql91/bin/pg_basebackup -D ~/zaloha_pg/ -c fast -P -v -xxlog start point: 0/C000020 34932/18547 kB (100%) 1/1 tablespaces ( )00000001000000000000000C) xlog end point: 0/C000094 pg_basebackup: base backup completed.
pg_basebackup lze použít nejen na lokální db (pokud je přístup k databázi, tak lze použít pg_basebackup).
pg_dump umožňuje vytvářet zálohu v tzv "directory" formátu (volba -Fd). Výsledkem zálohování není jeden soubor nýbrž adresář, kde je záloha každé tabulky umístěna v separátním souboru:
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | f1 | table | pavel
public | foo | table | pavel
(2 rows)
[pavel@nemesis xy]$ /usr/local/pgsql/bin/pg_dump -Fd postgres -f zaloha
[pavel@nemesis xy]$ cd zaloha/
[pavel@nemesis zaloha]$ ls -l
total 12
-rw-rw-r--. 1 pavel pavel 28 Mar 25 14:58 2712.dat.gz
-rw-rw-r--. 1 pavel pavel 31 Mar 25 14:58 2713.dat.gz
-rw-rw-r--. 1 pavel pavel 2506 Mar 25 14:58 toc.dat
Pokud chceme nějakým způsobem upravit data, tak zvlášť u větších a velkých databází je uložení do separátních souborů nutnost.
Mezi drobné maličkosti, které potěší, je rozšířený autocomplete v psql. Dalším malým zpříjemněním je zvětšení limitu u položek typu VARCHAR bez nutnosti změn v datových souborech - změní se pouze položka v jedné systémové tabulce.
Počínaje verzí 9.0 PostgreSQL podporuje asynchronní replikaci založenou na exportu transakčního logu. 9.1 rozšiřuje nabídku intergované replikace o synchronní replikaci. Synchronní a asynchronní replikaci lze kombinovat. Synchronní replikace je navázána na transakci - způsob zajištění transakce je určen systémovou proměnnou synchronous_replication(boolean).
Vývojáři pracující s uloženými procedurami možná ocení metapříkaz \sf pro zobrazení zdrojového textu funkce. Perličkou je možnost nastavit výchozí řádek pro externí editor v metapříkazu \ef (editace funkce):
postgres=# \sf+ f
CREATE OR REPLACE FUNCTION public.f(a integer)
RETURNS integer
LANGUAGE plpgsql
1 AS $function$
2 BEGIN
3 RETURN a + 1;
4 END;
5 $function$
Výkon
Unlogged tables jsou tabulky, které nejsou jištěny transakčním logem - podporují transakce, ale pokud dojde k havárii, tak je jejich obsah smazán, protože jej nelze ověřit vůči transakčnímu logu. V určitých případech se tento typ tabulek může hodit - ať už to jsou různé ETL úlohy nebo klasická správa HTTP sessions. V další verzi (9.2) by se nad tímto typem tabulek měly implementovat tzv globální dočasné tabulky. Jelikož se změny obsahu v těchto tabulkách nezapisují do transakčního logu, jsou všechny operace - INSERT, UPDATE, DELETE, COPY několikanásobně rychlejší. Pro zjištění rychlosti jsem použil databázi PSC, která je ve formátu SQL.
Pokud jsem tuto databázi neupravil, tak že INSERTy byly zapouzdřeny v transakci - pak import této db trval cca 40 sec (po vložení do transakce cca 2sec):
[pavel@nemesis Downloads]$ time psql.5491 postgres -c '\i databaze_psc_2007-07-01.sql' -q psql.5491:databaze_psc_2007-07-01.sql:9: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "psc_pkey" for table "psc" real 0m39.954s user 0m0.239s sys 0m0.128s
Pokud jsem tabulku vytvořil jako unlogged:
CREATE UNLOGGED TABLE psc ( psc numeric(11) NOT NULL default '0', posta varchar(64) NOT NULL default '', PRIMARY KEY (psc) );
tak se import zkrátil cca na dvě sec (aniž bych musel použít explicitní transakci):
[pavel@nemesis Downloads]$ time psql.5491 postgres -c '\i databaze_psc_2007-07-01.sql' -q psql.5491:databaze_psc_2007-07-01.sql:11: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "psc_pkey" for table "psc" real 0m2.146s user 0m0.067s sys 0m0.057s
Jednou ze změn, která není viditelná, ale potěší, je schopnost planneru optimalizovat dotaz <t>SELECT max(id) FROM tab</t>, tak aby se použil index nad sloupcem id, bez ohledu na to, zda-li je tabulka tab tabulkou nebo partition.
postgres=# EXPLAIN SELECT max(a) FROM data;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Aggregate (cost=10000009955.25..10000009955.26 rows=1 width=4)
-> Merge Append (cost=10000000168.77..10000009449.25 rows=202400 width=4)
Sort Key: public.data.a
-> Sort (cost=10000000168.75..10000000174.75 rows=2400 width=4)
Sort Key: public.data.a
-> Seq Scan on data (cost=10000000000.00..10000000034.00 rows=2400 width=4)
-> Index Scan Backward using a_00000_a_idx on a_00000 data (cost=0.00..2780.26 rows=100000 width=4)
-> Index Scan Backward using a_00001_a_idx on a_00001 data (cost=0.00..2780.26 rows=100000 width=4)
postgres=# explain SELECT a FROM data ORDER BY 1 limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Limit (cost=85.89..86.35 rows=10 width=4)
-> Result (cost=85.89..9366.37 rows=202400 width=4)
-> Merge Append (cost=85.89..9366.37 rows=202400 width=4)
Sort Key: public.data.a
-> Sort (cost=85.86..91.86 rows=2400 width=4)
Sort Key: public.data.a
-> Seq Scan on data (cost=0.00..34.00 rows=2400 width=4)
-> Index Scan using a_00000_a_idx on a_00000 data (cost=0.00..2780.26 rows=100000 width=4)
-> Index Scan using a_00001_a_idx on a_00001 data (cost=0.00..2780.26 rows=100000 width=4)
(9 rows)
Skutečně, v 9.1 si planner mnohem lépe rozumí s partitioningem. Co se bohužel nezměnilo je způsob vytváření partitions. Je docela možné, že na to dojde v 9.2.
Problém, který se stále vrací je přetížení IO při checkpointu. Vyřeší se, a s růstem kapacit RAM se po nějakém čase objeví znovu - při 16GB RAM se na ext3 vyskytují (testováním potvrzeno viz linux-filesystems-and-postgres) problémy s latencí dotazů (a např. na XFS latence dotazů je cca desetinásobně menší). Greg Smith s Robertem Haasem navrhli a realizovali řešení, které by mělo umožňovat bezproblémový provoz PostgreSQL i na ext3. Jelikož mám všude ext4 tak to, jak se jim to povedlo, nemohu posoudit. Při mém testování se ukazuje 9.1 o fous (cca 5%) rychlejší než 9.0 při velmi intenzivní zátěži.
Jeden z externích modulů pg_trgm je nyní možné využít i pro indexaci dotazů s podmínkou:
WHERE sloupec LIKE '%něco%'
Není to úplně zadarmo - index nad sloupcem s trigramy je několikanásobně větší než klasický index (to může působit problémy u velkých tabulek) - do nedávna jsem nevěřil, že je něco takového vůbec možné.
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
postgres=# CREATE INDEX ON psc USING gin (posta gin_trgm_ops);
CREATE INDEX
postgres=# SELECT * FROM psc WHERE posta LIKE '%Běl%';
psc | posta
-------+-------------------
34526 | Bělá nad Radbuzou
56905 | Bělá nad Svitavou
29421 | Bělá pod Bezdězem
79085 | Bělá pod Pradědem
38743 | Bělčice
78315 | Bělkovice-Lašťany
75364 | Bělotín
58261 | Česká Bělá
33152 | Dolní Bělá
50781 | Lázně Bělohrad
53343 | Rohovládova Bělá
51703 | Skuhrov nad Bělou
(12 rows)
Time: 3.209 ms
postgres=# EXPLAIN SELECT * FROM psc WHERE posta LIKE '%Běl%';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on psc (cost=12.26..34.47 rows=34 width=19)
Recheck Cond: ((posta)::text ~~ '%Běl%'::text)
-> Bitmap Index Scan on psc_posta_idx (cost=0.00..12.25 rows=34 width=0)
Index Cond: ((posta)::text ~~ '%Běl%'::text)
(4 rows)
postgres=# drop INDEX psc_posta_idx;
DROP INDEX
postgres=# SELECT * FROM psc WHERE posta LIKE '%Běl%';
psc | posta
-------+-------------------
34526 | Bělá nad Radbuzou
56905 | Bělá nad Svitavou
29421 | Bělá pod Bezdězem
79085 | Bělá pod Pradědem
38743 | Bělčice
78315 | Bělkovice-Lašťany
75364 | Bělotín
58261 | Česká Bělá
33152 | Dolní Bělá
50781 | Lázně Bělohrad
53343 | Rohovládova Bělá
51703 | Skuhrov nad Bělou
(12 rows)
Time: 8.071 ms
Trigramy lze použít i pro podporu operátoru ILIKE:
postgres=# CREATE INDEX ON psc USING gin (posta gin_trgm_ops); CREATE INDEX postgres=# SELECT * FROM psc WHERE posta ILIKE '%běl%'; psc | posta -------+------------------- 34526 | Bělá nad Radbuzou 56905 | Bělá nad Svitavou 29421 | Bělá pod Bezdězem 79085 | Bělá pod Pradědem 38743 | Bělčice 78315 | Bělkovice-Lašťany 75364 | Bělotín 58261 | Česká Bělá 33152 | Dolní Bělá 50781 | Lázně Bělohrad 53343 | Rohovládova Bělá 51703 | Skuhrov nad Bělou (12 rows) Time: 5.428 ms postgres=# DROP INDEX psc_posta_idx; DROP INDEX postgres=# SELECT * FROM psc WHERE posta ILIKE '%běl%'; psc | posta -------+------------------- 34526 | Bělá nad Radbuzou 56905 | Bělá nad Svitavou 29421 | Bělá pod Bezdězem 79085 | Bělá pod Pradědem 38743 | Bělčice 78315 | Bělkovice-Lašťany 75364 | Bělotín 58261 | Česká Bělá 33152 | Dolní Bělá 50781 | Lázně Bělohrad 53343 | Rohovládova Bělá 51703 | Skuhrov nad Bělou (12 rows) Time: 23.874 ms
GiST index dosud nepodporoval operaci ORDER BY. Pomocí tohoto indexu bylo možné filtrovat, ale nebylo možné řadit filtrovaná data. GiST umožňoval rychlé dotazy typu - najdi všechny objekty z okolí x km, ale už neumožnil efektivní zpracování dotazu typu - najdi 10 nejbližších objektů. Implementací algoritmu k nearest neighbourhood - KNNGIST umožňuje využít GiST index i pro tento typ úloh:
SELECT position <-> point(500,500) FROM test ORDER BY position <-> point(500,500) LIMIT 10;
PostgreSQL je pravděpodobně první databází, která implementuje KNN algoritmus.
Výše zmíněné rozšíření lze použít nejen pro geodata. Hubert Lubaczewski prezentoval použití KNNGiST i pro dohledání nejpodobnějších slov na základě vzdálenosti trigramů viz http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-knngist:
postgres=# SELECT *, similarity(posta, 'benesov')
FROM psc
ORDER BY posta <-> 'benesov'
LIMIT 10;
psc | posta | similarity
-------+--------------------+------------
51237 | Benecko | 0.333333
67506 | Benetice | 0.307692
74722 | Dolní Benešov | 0.294118
79312 | Horní Benešov | 0.294118
51206 | Benešov u Semil | 0.263158
25601 | Benešov u Prahy | 0.263158
67953 | Benešov u Boskovic | 0.238095
38282 | Benešov nad Černou | 0.227273
33041 | Bezvěrov | 0.214286
68333 | Nesovice | 0.214286
(10 rows)
postgres=# EXPLAIN SELECT *, similarity(posta, 'benesov')
FROM psc
ORDER BY posta <-> 'benesov'
LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.00..0.87 rows=10 width=19)
-> Index Scan using psc_posta_idx on psc (cost=0.00..291.76 rows=3356 width=19)
Order By: ((posta)::text <-> 'benesov'::text)
(3 rows)
Fantazii se meze nekladou - myslím si, že především uživatelé PostGISu kvůli KNNGiSTu přejdou na 9.1 velice brzo.
CSPUG
Neziskové sdružení CSPUG vzniklo za účelem zajišťování propagačních a osvětových aktivit ohledně PostgreSQL, SQL, atd. Letošní P2D2 bylo premiérové v tom, že jeho organizace byla výlučně záležitost CSPUGu. V P2D2 chceme pokračovat, počítá se ovšem i s jinými pravidelnými akcemi. Pokud byste měli zájem podpořit CSPUG nebo chuť se zapojit, staňte členy sdružení.
Na čem se pracuje?
Vývojáři PostgreSQL byli v posledních čtyřech letech pod určitým psychologickým tlakem - počínaje 8.0 přicházelo více patchů než se dařilo zpracovávat - při stávající složitosti kódu a požadavku na kvalitu je docela běžné, že se patch dostal do kódu po cca dvou letech a jeho autor musel kód několikrát přepsat. Tento tlak vedl ke změnám ve vývojového procesu, změně systému pro správu kódu. Počínaje 8.4 se počet čekajících patchů začal snižovat. 9.1 je výjmečná v tom, že se podařilo zpracovat všechny patche. Počet a složitost patchů, které se přesouvají do 9.2 je "nezvykle" nižší a menší. Od 9.2 bych čekal větší úpravy v planneru - hodně se diskutuje a multidimenzionálních statistikách - hledá se způsob jak zpřístupnit a zjednodušit konfiguraci replikace. Ze seznamu patchů připravené pro 9.2 lze jmenovat podporu SQL příkazu MERGE, podporu generického typu RANGE, a desítky drobných úprav a vylepšení. Od 9.2 nečekám, že přinese zásadní nové funkce, ale že se bude snáze konfigurovat a používat. Postupně vznikají nástroje, které by mají pomoci s administrací serveru a ty jsou začleňovány do jádra - viz např. pg_basebackup. Dalším nástrojem pro administraci replikovaných serverů je repmgr. Diskutuje se o způsobu přístupu k souborovému systému - diskutuje se možnost použítí mmap. Je připraven patch, který obsahuje podporu typu JSON. Diskutuje se o interních optimalizacích, diskutuje se o podpoře "skutečných" uložených procedur - s možností explicitního řízení transakcí, s podporou multirecordsetů.
Díky podpoře CZ.NICu mohu pracovat na implementaci podpory jazyka pro vývoj uložených procedur definovaného standardem SQL/PSM. Tento programovací jazyk by měl doplnit (nikoliv nahradit) jazyk PL/pgSQL, který vychází z jazyka PL/SQL fy. Oracle (vycházím ze svých zkušeností s PL/pgSQL). Na rozdíl od PL/pgSQL je moje implementace SQL/PSM mnohem statičtější, což na jednu stranu umožňuje důkladnější kontroly při registraci funkce (na všechny potenciální chyby se přijde během registrace nebo prvního spuštění), příčemž ale uživatel bude nucen častěji použít dynamické SQL. Absolutní kompatibilita je u SQL databází iluzí a platí to i pro uložené procedury. Standard je věc jedna - věc druhá je fakt, že standard nepopisuje určité specifické konstrukce, vzory, které jsou i z historických důvodů implementovány různě. Přirovnal bych to k C - existuje standard, ale trochu složitější aplikace se programují jinak v OS UNIX a jinak v OS MS Windows. Totéž lze říci i o jazyku SQL/PSM. Nemusíte se učit s každou databází nový jazyk, ale vždy musíte respektovat specifika té či oné databáze (s pomocí speciálních knihoven lze minimalizovat rozdíly v kódu pro jednotlivé db).
Poznávacím znamením SQL/PSM je obsluha chyb pomocí tzv. handlerů:
create or replace function test38()
returns int as $$
begin
declare aux int;
declare s int default 0;
declare done boolean default false;
declare c1 cursor for select a from footab;
declare continue handler for not found set done = true;
open c1;
fetch c1 into aux;
while not done do
set s = s + aux;
fetch c1 into aux;
end while;
return s;
end;
$$ language psm0;
Výše uvedený kód (tělo funkce) spustíte na MySQL, v DB2 a v řadě dalších databází. Implementace SQL/PSM v PostgreSQL umožňuje použít i další vzor, který je častý v DB2 (využití magických proměnných SQLSTATE nebo SQLCODE):
create or replace function test28(a int)
returns int as $$
begin
declare sqlstate char(5);
declare aux int;
declare s int default 0;
declare cx cursor for select footab.a from footab;
open cx;
fetch cx into aux;
while sqlstate = '00000' do
set s = s + aux;
fetch cx into aux;
end while;
close cx;
return s;
end;
$$ language psm0;
Ve standardu nalezneme komfortní konstrukci FOR (používá se ještě jednodušeji než v FOR v PL/pgSQL):
create or replace function test37()
returns int as $$
begin
declare s int default 0;
for select * from footab do
set s = s + a;
end for;
return s;
end;
$$ language psm0;
Aktuálně je kompletní implementace základní části standardu SQL/PSM. Nula na konci názvu znamená, že se jedná o prototyp. Před nasazením do produkce je nutné provést revizi. Mým cílem je možnost používat PSM v produkčních prostředích někdy koncem roku 2011, možná v půli roku 2012 (měl by být k dispozici spolu s příští verzí PostgreSQL (tj 9.2)).
Související články
- Novinky 2006 (PostgreSQL 8.2)
- Slon nezapomíná (co nás čeká v PostgreSQL 8.3)
- PostgreSQL v roce 2009 (PostgreSQL 8.4)
- PostgreSQL 9.0 - nový začátek
- PostgreSQL 9.2 (2012)
- PostgreSQL 9.3 (2013)
- PostgreSQL 9.4 (2014): transakční sql json databáze
- PostgreSQL 9.5 (2015) držte si klobouky, zrychlujeme
- PostgreSQL 9.6 (2016) odteď paralelně
- PostgreSQL 10 (2017) - drsně rozběhnutý slon
- PostgreSQL 11 (2018)
- PostgreSQL 12 (2019)
- PostgreSQL 13 (2020)
- PostgreSQL 14 (2021)
- PostgreSQL 15 (2022)
- PostgreSQL 16 (2023)
- PostgreSQL 17 (2024)