Fonctionnalités cachées de PostgreSQL

Puisque postgres est beaucoup plus sain que MySQL, il n’ya pas beaucoup de “trucs” à rapporter 😉

Le manuel contient de bons conseils de performance .

Quelques autres aspects liés à la performance à garder à l’esprit:

• Assurez-vous que l’autovacuum est activé
• Assurez-vous d’avoir parcouru votre postgres.conf (taille effective du cache, tampons partagés, mémoire de travail, etc.).
• Utilisez pgpool ou pgbouncer pour limiter au minimum vos “vraies” connexions à la firebase database
• Découvrez comment fonctionne EXPLAIN et EXPLAIN ANALYZE. Apprenez à lire la sortie.
• CLUSTER sortinge les données sur disque en fonction d’un index. Peut considérablement améliorer les performances de grandes tables (principalement) en lecture seule. Le clustering est une opération unique: lorsque la table est mise à jour par la suite, les modifications ne sont pas mises en cluster.

Voici quelques points que j’ai trouvés utiles et qui ne sont pas liés à la configuration ou à la performance en soi.

Pour voir ce qui se passe actuellement:

select * from pg_stat_activity; 

Rechercher des fonctions diverses:

 select * from pg_proc WHERE proname ~* '^pg_.*' 

Trouver la taille de la firebase database:

 select pg_database_size('postgres'); select pg_size_pretty(pg_database_size('postgres')); 

Trouver la taille de toutes les bases de données:

 select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database; 

Trouver la taille des tables et des index:

 select pg_size_pretty(pg_relation_size('public.customer')); 

Ou, pour répertorier toutes les tables et tous les index (probablement plus faciles à visualiser):

 select schemaname, relname, pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) as size from (select schemaname, relname, 'table' as type from pg_stat_user_tables union all select schemaname, relname, 'index' as type from pg_stat_user_indexes) x; 

Oh, et vous pouvez imbriquer des transactions, annuler des transactions partielles ++

 test=# begin; BEGIN test=# select count(*) from customer where name='test'; count ------- 0 (1 row) test=# insert into customer (name) values ('test'); INSERT 0 1 test=# savepoint foo; SAVEPOINT test=# update customer set name='john'; UPDATE 3 test=# rollback to savepoint foo; ROLLBACK test=# commit; COMMIT test=# select count(*) from customer where name='test'; count ------- 1 (1 row) 

L’astuce la plus simple pour permettre à postgresql de bien mieux fonctionner (en dehors de la définition et de l’utilisation des index appropriés bien sûr) est juste de lui donner plus de RAM avec lequel travailler (si vous ne l’avez pas déjà fait). Sur la plupart des installations par défaut, la valeur de shared_buffers est bien trop faible (à mon avis). Vous pouvez définir

shared_buffers

dans postgresql.conf. Divisez ce nombre par 128 pour obtenir une approximation de la quantité de mémoire (en Mo) que postgres peut réclamer. Si vous le montez suffisamment, cela fera voler le postgresql. N’oubliez pas de redémarrer postgresql.

Sur les systèmes Linux, lorsque postgresql ne redémarrera pas, vous aurez probablement atteint la limite kernel.shmmax. Placez-le plus haut avec

 sysctl -w kernel.shmmax=xxxx 

Pour que cela persiste entre les démarrages, ajoutez une entrée kernel.shmmax dans /etc/sysctl.conf.

Un tas de trucs de Postgresql peuvent être trouvés ici :

• http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

Grâce à sa prise en charge d’INTERVAL, Postgres dispose d’un outil de traitement de données très puissant.

Par exemple:

 select NOW(), NOW() + '1 hour'; now | ?column? -------------------------------+------------------------------- 2009-04-18 01:37:49.116614+00 | 2009-04-18 02:37:49.116614+00 (1 row) select current_date ,(current_date + interval '1 year')::date; date | date ---------------------+---------------- 2014-10-17 | 2015-10-17 (1 row) 

Vous pouvez convertir plusieurs chaînes en un type INTERVAL.

COPIE

Je vais commencer. Chaque fois que je passe à Postgres à partir de SQLite, j’ai généralement de très gros ensembles de données. La clé est de charger vos tables avec COPY FROM plutôt que de faire des INSERTS. Voir la documentation:

http://www.postgresql.org/docs/8.1/static/sql-copy.html

L’exemple suivant copie une table sur le client en utilisant la barre verticale (|) comme délimiteur de zone:

 COPY country TO STDOUT WITH DELIMITER '|'; 

Pour copier des données d’un fichier dans la table de pays:

 COPY country FROM '/usr1/proj/bray/sql/country_data'; 

Voir aussi ici: Inserts en vrac plus rapides dans sqlite3?

• Mon favori de loin est generate_series : enfin un moyen propre de générer des lignes factices.

• Possibilité d’utiliser une valeur corrélée dans une clause LIMIT d’une sous-requête:

   SELECT ( SELECT exp_word FROM mytable OFFSET id LIMIT 1 ) FROM othertable 

• Abitlity d’utiliser plusieurs parameters dans des agrégats personnalisés (non couverts par la documentation): consultez l’article sur mon blog pour un exemple.

Une des choses que j’aime beaucoup avec Postgres est certains des types de données pris en charge dans les colonnes. Par exemple, il existe des types de colonnes pour stocker les adresses réseau et les tableaux . Les fonctions correspondantes ( adresses réseau / tableaux ) pour ces types de colonnes vous permettent d’effectuer de nombreuses opérations complexes dans les requêtes que vous devez effectuer en traitant les résultats via du code dans MySQL ou d’autres moteurs de firebase database.

Les tableaux sont vraiment cool une fois que vous les connaissez. Disons que vous souhaitez stocker des hyperliens entre les pages. Vous pourriez commencer par penser à créer un tableau comme celui-ci:

 CREATE TABLE hyper.links ( tail INT4, head INT4 ); 

Si vous aviez besoin d’indexer la colonne de la queue , et que vous aviez, disons, 200 000 000 de lignes de liens (comme Wikipédia vous le donnerait), vous vous retrouveriez avec une énorme table et un énorme index.

Cependant, avec PostgreSQL, vous pouvez utiliser ce format de table à la place:

 CREATE TABLE hyper.links ( tail INT4, head INT4[], PRIMARY KEY(tail) ); 

Pour obtenir toutes les têtes d’un lien, vous pouvez envoyer une commande comme celle-ci (unnest () est standard depuis 8.4):

 SELECT unnest(head) FROM hyper.links WHERE tail = $1; 

Cette requête est étonnamment rapide lorsqu’elle est comparée à la première option (unnest () est rapide et l’index est beaucoup plus petit). De plus, votre table et index consumnt beaucoup moins de mémoire RAM et d’espace HD, en particulier lorsque vos tableaux sont si longs qu’ils sont compressés dans une table de pain grillé. Les tableaux sont vraiment puissants.

Remarque: alors que unnest () génère des lignes à partir d’un tableau, array_agg () regroupe les lignes dans un tableau.

Les vues matérialisées sont assez faciles à configurer:

 CREATE VIEW my_view AS SELECT id, AVG(my_col) FROM my_table GROUP BY id; CREATE TABLE my_matview AS SELECT * FROM my_view; 

Cela crée une nouvelle table, my_matview, avec les colonnes et les valeurs de my_view. Les déclencheurs ou un script cron peuvent alors être configurés pour garder les données à jour, ou si vous êtes paresseux:

 TRUNCATE my_matview; INSERT INTO my_matview SELECT * FROM my_view; 

• Héritage..infirme l’inheritance multiple (comme dans “l’inheritance” parent-enfant, pas l’inheritance de relation 1 pour 1 que de nombreux frameworks Web implémentent lorsqu’ils travaillent avec postgres).

• PostGIS (extension spatiale), un merveilleux ajout qui offre un ensemble complet de fonctions géomésortingques et coordonne le stockage hors de la boîte. Largement utilisé dans de nombreuses librairies géographiques open-source (par exemple, OpenLayers, MapServer, Mapnik, etc.) et nettement meilleur que les extensions spatiales de MySQL.

• Écrire des procédures dans différents langages, par exemple C, Python, Perl, etc. (cela simplifie le code si vous êtes un développeur et non un administrateur de firebase database).

  De plus, toutes les procédures peuvent être stockées en externe (sous forme de modules) et peuvent être appelées ou imscopes à l’exécution par des arguments spécifiés. De cette façon, vous pouvez contrôler le code source et déboguer facilement le code.

• Un vaste catalogue complet sur tous les objects implémentés dans votre firebase database (c.-à-d. Tables, contraintes, index, etc.).

  Je trouve toujours extrêmement utile d’exécuter quelques requêtes et d’obtenir toutes les méta-informations, par exemple les noms de contraintes et les champs sur lesquels elles ont été implémentées, les noms d’index, etc.

  Pour moi, tout devient extrêmement pratique lorsque je dois charger de nouvelles données ou effectuer des mises à jour massives dans de grandes tables (je désactiverais automatiquement les déclencheurs et les index) et les recréer facilement après la fin du traitement. Quelqu’un a fait un excellent travail d’écriture de ces questions.

  http://www.alberton.info/postgresql_meta_info.html

• Plusieurs schémas sous une seule firebase database, vous pouvez l’utiliser si votre firebase database contient un grand nombre de tables, vous pouvez considérer les schémas comme des catégories. Toutes les tables (quel que soit leur schéma) ont access à toutes les autres tables et fonctions présentes dans la firebase database parent.

Vous n’avez pas besoin d’apprendre à déchiffrer “expliquer l’parsing”, il existe un outil: http://explain.depesz.com

 select pg_size_pretty(200 * 1024) 

pgcrypto : plus de fonctions cryptographiques que les modules de cryptage de nombreux langages de programmation, toutes accessibles directement depuis la firebase database. Cela rend les choses cryptographiques incroyablement faciles à faire juste.

Une firebase database peut être copiée avec:

createdb -T old_db new_db

La documentation dit:

ce n’est pas (encore) conçu comme une installation “COPY DATABASE” à usage général

mais cela fonctionne bien pour moi et est beaucoup plus rapide que

createdb new_db

pg_dump old_db | psql new_db

Stockage de la mémoire pour les données jetables / variables globales

Vous pouvez créer un espace de table qui réside dans la mémoire vive et des tables (éventuellement non combinées dans 9.1) dans cet espace de stockage pour stocker les données / variables globales à partager entre les sessions.

http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/

Serrures de conseil

Ceux-ci sont documentés dans une zone obscure du manuel:

http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

Il est parfois plus rapide que l’acquisition de multitudes de verrous au niveau de la ligne et ils peuvent être utilisés pour contourner les cas où FOR UPDATE n’est pas implémenté (comme les requêtes CTE récursives).

Ceci est ma liste de favoris de fonctionnalités moins connues.

DDL transactionnel

Presque toutes les instructions SQL sont transactionnelles dans Postgres. Si vous désactivez la validation automatique, les éléments suivants sont possibles:

 drop table customer_orders; rollback; select * from customer_orders; 

Types de plage et contrainte d’exclusion

À ma connaissance, Postgres est le seul SGBDR qui vous permet de créer une contrainte qui vérifie si deux plages se chevauchent. Un exemple est une table qui contient des prix de produits avec une date “valide à partir de” et “valide jusqu’à”:

 create table product_price ( price_id serial not null primary key, product_id integer not null references products, price numeric(16,4) not null, valid_during daterange not null ); 

Fonctionnalités NoSQL

L’extension hstore offre un magasin clé / valeur flexible et très rapide qui peut être utilisé lorsque des parties de la firebase database doivent être “sans schéma”. JSON est une autre option pour stocker des données d’une manière sans schéma et

 insert into product_price (product_id, price, valid_during) values (1, 100.0, '[2013-01-01,2014-01-01)'), (1, 90.0, '[2014-01-01,)'); -- querying is simply and can use an index on the valid_during column select price from product_price where product_id = 42 and valid_during @> date '2014-10-17'; 

Le plan d’exécution de ce qui précède sur une table de 700 000 lignes:

 Index Scan using check_price_range on public.product_price (cost=0.29..3.29 rows=1 width=6) (actual time=0.605..0.728 rows=1 loops=1) Output: price Index Cond: ((product_price.valid_during @> '2014-10-17'::date) AND (product_price.product_id = 42)) Buffers: shared hit=17 Total runtime: 0.772 ms 

Pour éviter d’insérer des lignes avec des plages de validité qui se chevauchent, une contrainte unique simple (et efficace) peut être définie:

 alter table product_price add constraint check_price_range exclude using gist (product_id with =, valid_during with &&) 

Infini

Au lieu d’exiger une “vraie” date dans le futur, Postgres peut comparer les dates à l’infini. Par exemple, lorsque vous n’utilisez pas une plage de dates, vous pouvez effectuer les opérations suivantes

 insert into product_price (product_id, price, valid_from, valid_until) values (1, 90.0, date '2014-01-01', date 'infinity'); 

Expressions communes inscriptibles

Vous pouvez supprimer, insérer et sélectionner dans une seule déclaration:

 with old_orders as ( delete from orders where order_date < current_date - interval '10' year returning * ), archived_rows as ( insert into archived_orders select * from old_orders returning * ) select * from archived_rows; 

Ce qui précède supprimera toutes les commandes de plus de 10 ans, les déplacera vers la table archived_orders , puis affichera les lignes déplacées.

1.) Lorsque vous avez besoin d’append une notification à la requête, vous pouvez utiliser un commentaire nested

 SELECT /* my comments, that I would to see in PostgreSQL log */ a, b, c FROM mytab; 

2.) Supprimez les espaces de fin de tous les champs text et varchar d’une firebase database.

 do $$ declare selectrow record; begin for selectrow in select 'UPDATE '||c.table_name||' SET '||c.COLUMN_NAME||'=TRIM('||c.COLUMN_NAME||') WHERE '||c.COLUMN_NAME||' ILIKE ''% '' ' as script from ( select table_name,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name LIKE 'tbl%' and (data_type='text' or data_type='character varying' ) ) c loop execute selectrow.script; end loop; end; $$; 

3.) Nous pouvons utiliser une fonction de fenêtre pour supprimer très efficacement les lignes en double:

 DELETE FROM tab WHERE id IN (SELECT id FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id FROM tab) x WHERE x.row_number > 1); 

Une version optimisée de PostgreSQL (avec ctid):

 DELETE FROM tab WHERE ctid = ANY(ARRAY(SELECT ctid FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid FROM tab) x WHERE x.row_number > 1)); 

4.) Lorsque nous devons identifier l’état du serveur, nous pouvons utiliser une fonction:

 SELECT pg_is_in_recovery(); 

5.) Récupère la commande DDL des fonctions.

 select pg_get_functiondef((select oid from pg_proc where proname = 'f1')); 

6.) Changer le type de données de colonne en toute sécurité dans PostgreSQL

 create table test(id varchar ); insert into test values('1'); insert into test values('11'); insert into test values('12'); select * from test --Result-- id character varying -------------------------- 1 11 12 

Vous pouvez voir dans le tableau ci-dessus que j’ai utilisé le type de données – «caractère variable» pour «id»
colonne. Mais c’était une erreur, car je donne toujours des nombres entiers comme id. L’utilisation de varchar est donc une mauvaise pratique. Essayons donc de changer le type de colonne en entier.

 ALTER TABLE test ALTER COLUMN id TYPE integer; 

Mais ça revient:

ERREUR: la colonne «id» ne peut pas être convertie automatiquement pour saisir un état SQL entier: 42804 Astuce: spécifiez une expression USING pour effectuer la conversion

Cela signifie que nous ne pouvons pas simplement modifier le type de données car les données sont déjà présentes dans la colonne. Puisque les données sont de type ‘variable en caractères’, les postgres ne peuvent pas s’attendre à ce qu’elles soient des nombres entiers bien que nous n’ayons entré que des entiers. Alors maintenant, comme postgres a suggéré, nous pouvons utiliser l’expression ‘USING’ pour convertir nos données en entiers.

 ALTER TABLE test ALTER COLUMN id TYPE integer USING (id ::integer); 

Ça marche.

7.) Savoir qui est connecté à la firebase database
C’est plus ou moins une commande de surveillance. Pour connaître l’utilisateur connecté à quelle firebase database, y compris leur adresse IP et leur port, utilisez le code SQL suivant:

 SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ; 

8.) Recharger les fichiers de configuration PostgreSQL sans redémarrer le serveur

Les parameters de configuration de PostgreSQL sont situés dans des fichiers spéciaux tels que postgresql.conf et pg_hba.conf. Souvent, vous devrez peut-être modifier ces parameters. Mais pour que certains parameters prennent effet, il faut souvent recharger le fichier de configuration. Bien sûr, le redémarrage du serveur le fera. Mais dans un environnement de production, il n’est pas préférable de redémarrer la firebase database, qui est utilisée par des milliers, simplement pour définir certains parameters. Dans de telles situations, nous pouvons recharger les fichiers de configuration sans redémarrer le serveur en utilisant la fonction suivante:

 select pg_reload_conf(); 

Rappelez-vous que cela ne fonctionnera pas pour tous les parameters, certaines modifications de parameters nécessitent un redémarrage complet du serveur à prendre en compte.

9.) Obtenir le chemin du répertoire de données du cluster de firebase database en cours

Il est possible que dans un système, plusieurs instances (cluster) de PostgreSQL ™ soient généralement configurées dans différents ports. Dans de tels cas, rechercher quel répertoire (répertoire de stockage physique) est utilisé par quelle instance est une tâche mouvementée. Dans de tels cas, nous pouvons utiliser la commande suivante dans n’importe quelle firebase database du cluster de notre intérêt pour obtenir le chemin du répertoire:

 SHOW data_directory; 

La même fonction peut être utilisée pour modifier le répertoire de données du cluster, mais cela nécessite un redémarrage du serveur:

 SET data_directory to new_directory_path; 

10.) Trouver un CHAR est DATE ou non

 create or replace function is_date(s varchar) returns boolean as $$ begin perform s::date; return true; exception when others then return false; end; $$ language plpgsql; 

Utilisation: le suivant retournera True

 select is_date('12-12-2014') select is_date('12/12/2014') select is_date('20141212') select is_date('2014.12.12') select is_date('2014,12,12') 

11.) Changer le propriétaire dans PostgreSQL

 REASSIGN OWNED BY sa TO postgres; 

12.) PGADMIN PLPGSQL DEBUGGER

Bien expliqué ici

Il est pratique de renommer une ancienne firebase database plutôt que mysql. Juste en utilisant la commande suivante:

 ALTER DATABASE name RENAME TO new_name