Caractéristiques cachées de PostgreSQL [fermé]

le le plus facile truc pour laisser postgresql effectuer beaucoup mieux (en dehors de la mise en place et l'utilisation des index appropriés, bien sûr) est juste pour lui donner plus de RAM pour travailler avec (si vous ne l'avez pas déjà fait). Sur la plupart des installations par défaut, la valeur de shared_buffers est trop basse (à 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) postgres peut prétendre. Si vous êtes assez haut cela fera voler postgresql. N'oubliez pas de redémarrer postgresql.

sur les systèmes Linux, lorsque postgresql ne redémarre pas, vous avez probablement touché le noyau.shmmax limite. Plus haut avec

sysctl -w kernel.shmmax=xxxx

pour que cela persiste entre les bottes, ajouter un noyau.shmmax entrée dans /etc/sysctl.conf.

un groupe entier des astuces Postgresql peuvent être trouvées ici :

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

Le

Postgres est doté d'une très grande facilité de manipulation datetime grâce à son support D'intervalle.

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 lancer plusieurs cordes à un type D'intervalle.

COPIE

je commence. Chaque fois que je passe à Postgres à partir de SQLite, j'ai habituellement quelques très grands ensembles de données. La clé est de charger vos tables avec la copie de plutôt que de faire des INSERTS. Voir documentation:

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

l'exemple suivant copie une table au client en utilisant la barre verticale ( / ) comme champ délimiteur:

COPY country TO STDOUT WITH DELIMITER '|';

Pour copier les données à partir d'un fichier dans le pays de table:

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

voir aussi ici: encarts en vrac plus rapides en sqlite3?

• mon préféré de loin est generate_series : enfin une façon propre de générer des faux rowsets.

• 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 paramètres dans les agrégats personnalisés (pas couverts par la documentation): voir l'article de mon blog pour un exemple.

une des choses que J'aime vraiment chez Postgres est certains des types de données supportés dans les colonnes. Par exemple, il existe des types de colonnes conçues pour stocker les adresses réseau et les tableaux . Les fonctions correspondantes ( adresses réseau / tableaux ) pour ces types de colonne vous permettent de faire beaucoup d'opérations complexes à l'intérieur des requêtes que vous auriez à faire en traitant les résultats par le code dans MySQL ou d'autres moteurs de base de données.

tableaux sont vraiment cool Une fois que vous apprenez à les connaître. Disons que vous aimeriez stocker quelques Hyperliens entre les pages. Vous pourriez commencer par penser à créer une Table comme ceci:

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

si vous aviez besoin d'indexer la colonne queue , et que vous aviez, disons 200.000.000 de liens-lignes (comme wikipedia vous le donnerait), vous vous trouveriez avec une table énorme et un Index énorme.

cependant, avec PostgreSQL, vous pourrait utiliser ce format de tableau à la place:

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

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

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

Cette requête est étonnamment rapide lorsqu'elle est comparée avec la première option (unnest() est rapide et l'Index de façon plus petits). De plus, votre Table et votre Index prendront beaucoup moins de mémoire vive et D'espace HD, surtout quand vos tableaux sont si longs qu'ils sont compressés à un Toast De Table. Les tableaux sont vraiment puissant.

Note: tandis que unnest() générera des lignes à partir d'un tableau, array_agg() agrégera des 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;

qui crée une nouvelle table, my_matview, avec les colonnes et les valeurs de my_view. Les Triggers 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..infact héritage Multiple (comme dans "héritage" parent-enfant " pas 1-to-1 relation héritage que de nombreux cadres Web mettent en œuvre en travaillant avec postgres).

• PostGIS (spatial extension), un merveilleux add-on qui offre un ensemble complet de fonctions de géométrie et de stockage de coordonnées hors de la boîte. Largement utilisé dans beaucoup de géo-fichiers open-source (par exemple OpenLayers, MapServer, Mapnik, etc) et certainement bien mieux que les extensions spatiales de MySQL.

• procédures D'écriture dans différentes langues, par exemple C,Python, Perl, etc (rend votre vie plus facile à coder si vous êtes un développeur et non un administrateur-db).

  toutes les procédures peuvent aussi être stockées extérieurement (en tant que modules) et peuvent être appelées ou importées à l'exécution par des arguments spécifiés. De cette façon, vous pouvez contrôler le code source et déboguer le code facilement.

• un catalogue énorme et complet sur tous les objets implémentés dans votre base de données (c.-à-d. tables,contraintes,index,etc.).

  je trouve toujours qu'il est 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 quand je dois charger de nouvelles données ou faire des mises à jour massives dans les grandes tables (Je désactiverais automatiquement les déclencheurs et drop indexes) et puis les recréer à nouveau facilement après le traitement a terminé. Quelqu'un a fait un excellent travail en écrivant une poignée de ces questions.

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

• plusieurs schémas sous une base de données, vous pouvez l'utiliser si votre base de données a un grand nombre de tableaux, vous pouvez penser à des schémas en tant que Catégories. Toutes les tables (indépendamment de son schéma) ont accès à toutes les autres tables et fonctions présentes dans le db parent.

vous n'avez pas besoin d'apprendre à déchiffrer la sortie "expliquer analyser", il y a un outil: http://explain.depesz.com

select pg_size_pretty(200 * 1024)

pgcrypto : plus de fonctions cryptographiques que les modules cryptographiques de nombreux langages de programmation, toutes accessibles directement à partir de la base de données. Cela rend les choses cryptographiques incroyablement faciles à obtenir juste.

une base de données peut être copiée avec:

createdb -T old_db new_db

la documentation dit:

ce n'est pas (encore) destiné à un usage général "base de données de copie" installation

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

createdb new_db

pg_dump old_db | psql new_db

mémoire de stockage de données / variables globales

vous pouvez créer une tablespace qui vit dans la mémoire vive, et des tables (éventuellement non enregistrées, en 9.1) dans cette tablespace pour stocker des données Jet-away/variables globales que vous souhaitez partager entre les sessions.

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

avis serrures

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 d'acquérir des multitudes de serrures de niveau de rangée, et ils peuvent être utilisés pour travailler autour des cas où pour la mise à jour n'est pas mis en œuvre (tels que les requêtes CTE récursives).

ce sont mes favoris liste de caractéristiques moins connues.

Transactional DDL

presque toutes les instructions SQL sont transactionnelles dans Postgres. Si vous éteignez autocommit ce qui suit est possible:

drop table customer_orders;
rollback;
select *
from customer_orders;

types de plages et contrainte d'exclusion

à ma connaissance Postgres est le seul RDBMS qui vous permet de créer une contrainte qui vérifie si deux gammes se chevauchent. Un exemple est une table qui contient les prix des produits avec un "valide du" et "valide jusqu'à" date:

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
);

NoSQL features

l'extension hstore offre un stock de clés/valeurs flexible et très rapide qui peut être utilisé lorsque des parties de la base de données 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 pour le ci-dessus sur un tableau avec 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 &&)

l'Infini

au lieu d'exiger une date" réelle " loin dans le futur Postgres peut comparer des dates à l'infini. Par exemple: lorsque vous n'utilisez pas une plage de dates, vous pouvez faire ce qui suit

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

Écriture d'expressions de table communes

vous pouvez supprimer, insérer et sélectionner dans un seul énoncé:

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 le tableau archived_orders et affichera ensuite les lignes qui ont été déplacées.

1.) Lorsque vous avez besoin ajouter un avis à la requête, vous pouvez utiliser le commentaire imbriqué

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

2.) Supprimer les espaces de fuite de tous les champs text et varchar dans une base de données.

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 dupliquées:

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 avons besoin d'identifier l'état du serveur, alors nous pouvons utiliser une fonction:

SELECT pg_is_in_recovery();

5.) Obtenir des fonctions de commande DDL.

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

6.) Changer en toute sécurité le type de données de colonne 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 - 'variant de caractère’ pour' id’

colonne. Mais c'était une erreur, parce que je donne toujours des entiers comme id. Donc, en utilisant varchar voici un une mauvaise pratique. Alors essayons de changer le type de colonne en entier.

ALTER TABLE test ALTER COLUMN id TYPE integer;

Mais il retourne:

erreur: la colonne "id" ne peut pas être utilisée automatiquement pour taper un entier SQL état: 42804 Hint: spécifiez une expression D'utilisation pour effectuer le conversion

cela signifie que nous ne pouvons pas simplement changer le type de données parce que les données sont déjà là dans la colonne. Puisque les données sont de type ‘variant de caractère’ postgres ne peut pas l'attendre comme entier bien que nous ayons entré des entiers seulement. Donc maintenant, comme l'a suggéré postgres, nous pouvons utiliser l'expression’ USING ' pour lancer nos données en nombres entiers.

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

Ça Marche.

7.) Savoir qui est connecté à la base de données

C'est plus ou moins une commande de surveillance. Pour savoir quel utilisateur s'est connecté à quelle base de données y compris leur IP et Port utiliser le SQL suivant:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

8.) Rechargement De La Configuration PostgreSQL fichiers sans redémarrage serveur

Les paramètres de configuration de

PostgreSQL sont situés dans des fichiers spéciaux comme postgresql.conf et pg_hba.conf. Souvent, vous aurez besoin de changer ces paramètres. Mais pour certains paramètres prennent effet, nous avons souvent besoin de recharger le fichier de configuration. Bien sûr, redémarrer le serveur le fera. Mais dans un environnement de production il n'est pas préférable de redémarrer la base de données, qui est utilisée par des milliers, juste pour définir certains paramètres. 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, cela ne fonctionne pas pour tous les paramètres, certains paramètres les changements nécessitent un redémarrage complet du serveur pour prendre effet.

9.) Obtenir le chemin du répertoire de données du groupe de bases de données

il est possible que dans un système, plusieurs instances(cluster) de PostgreSQL soient configurées, généralement, dans différents ports ou ainsi. Dans de tels cas, trouver quel répertoire(répertoire de stockage physique) est utilisé par quelle instance est une tâche trépidante. Dans de tels cas, nous pouvons utiliser la commande suivante dans n'importe quelle base de données du groupe qui nous intéresse pour obtenir le chemin du répertoire:

SHOW data_directory;

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

SET data_directory to new_directory_path;

10.) Trouver une 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;

Usage: la suite sera de retour Vrai

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 commode de renommer une ancienne base de données plutôt que mysql peut faire. Il suffit d'utiliser la commande suivante:

ALTER DATABASE name RENAME TO new_name