Fonctionnalités cachées de PostgreSQL

Question

Je suis surpris que cela n'ait pas encore été posté. Des astuces intéressantes que vous connaissez dans Postgres? Les options de configuration obscures et les astuces de mise à l'échelle / performance sont particulièrement bienvenues.

Je suis sûr que nous pouvons battre les 9 commentaires sur le fil de discussion MySQL correspondant :)

Answer 1

Depuis postgres est beaucoup plus sain d'esprit que MySQL, il n'y a pas que de nombreux "trucs" à faire rapport sur ;-)

Le manuel a quelques belles performances de conseils.

Quelques autres liées à la performance de choses à garder à l'esprit:

• Assurez-vous que autovacuum est allumé
• Assurez-vous que vous avez traversé votre postgres.conf (à compter de la taille du cache, partagée, de tampons, de travail mem ... beaucoup d'options à régler).
• Utilisation pgpool ou pgbouncer de garder votre "vrai" connexions de base de données à un minimum
• Apprenez comment EXPLIQUER et EXPLIQUER ANALYSER des oeuvres. Apprendre à lire la sortie.
• CLUSTER sortes de données sur le disque en fonction d'un indice. Peut considérablement améliorer les performances de large (pour la plupart) tables en lecture seule. Le Clustering est un temps de fonctionnement: lorsque la table est par la suite mis à jour, les modifications ne sont pas regroupés.

Voici quelques choses que j'ai trouvé utiles qui ne sont pas la configuration ou lié à la performance en soi.

Pour voir ce qui est le cas actuellement:

select * from pg_stat_activity;

Recherche divers fonctions:

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

Trouver la taille de la base de données:

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, à la liste de toutes les tables et les index (probablement plus facile de faire une vue de ceci):

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, la restauration partielle des transactions++

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)

Answer 2

La meilleure astuce pour laisser postgresql effectuer beaucoup mieux (en dehors de l'installation et l'utilisation correcte des indices de cours), c'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 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) postgres peut prétendre. Si vous il suffit de ce faire postgresql mouche. N'oubliez pas de redémarrer postgresql.

Sur les systèmes Linux, quand postgresql ne pas recommencer, vous aurez probablement frapper le noyau.shmmax limite. Le mettre plus haut avec

sysctl -w kernel.shmmax=xxxx

Pour faire de cette persistent entre les bottes, ajouter un noyau.shmmax entrée dans /etc/sysctl.conf.

Tout un tas de Postgresql astuces peuvent être trouvés ici:

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

Answer 3

Postgres dispose d’un outil de traitement de l’heure et de la date / heure très puissant grâce à la prise en charge INTERVAL.

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)
 

Vous pouvez convertir de nombreuses chaînes en un type INTERVAL.

Answer 4

COPIE

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

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

L'exemple suivant copie d'une table pour le client à l'aide de la barre verticale (|) comme délimiteur de champ:

COPY country TO STDOUT WITH DELIMITER '|';

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

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

Voir aussi ici: http://stackoverflow.com/questions/364017/faster-bulk-inserts-in-sqlite3/759866#759866

Answer 5

• Mon favori de loin est generate_series : enfin un moyen propre de générer des ensembles de 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
   

• Possibilité d'utiliser plusieurs paramètres dans des agrégats personnalisés (non couverts par la documentation): voir l'article de mon blog pour un exemple.