tutorial postgres example databases create comandos database postgresql rdbms postgresql-9.3

database - example - Funciones ocultas de PostgreSQL



psql tutorial (17)

Dado que postgres es mucho más sensato que MySQL, no hay tantos "trucos" para informar ;-)

El manual tiene algunos buenos consejos de performance .

Algunas otras cosas relacionadas con el rendimiento a tener en cuenta:

  • Asegúrate de que autovacuum esté activado
  • Asegúrate de haber revisado tu postgres.conf (tamaño efectivo de caché, búferes compartidos, memoria de trabajo ... muchas opciones para sintonizar).
  • Utilice pgpool o pgbouncer para mantener sus conexiones de base de datos "reales" al mínimo
  • Descubra cómo funciona EXPLAIN y EXPLAIN ANALYZE. Aprende a leer la salida.
  • CLUSTER ordena los datos en el disco según un índice. Puede mejorar drásticamente el rendimiento de las tablas grandes (principalmente) de solo lectura. La agrupación en clúster es una operación de una sola vez: cuando la tabla se actualiza posteriormente, los cambios no se agrupan.

Aquí hay algunas cosas que he encontrado útiles que no están relacionadas con la configuración o el rendimiento per se.

Para ver lo que está sucediendo actualmente:

select * from pg_stat_activity;

Buscar funciones misceláneas

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

Encuentra el tamaño de la base de datos:

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

Encuentra el tamaño de todas las bases de datos:

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

Encuentra el tamaño de tablas e índices:

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

O bien, para enumerar todas las tablas e índices (probablemente sea más fácil ver esto):

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;

Ah, y puedes anidar transacciones, deshacer transacciones parciales

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)

Me sorprende que esto no haya sido publicado todavía. ¿Algún truco interesante que conoces en Postgres? Las opciones de configuración poco conocidas y los trucos de escala / perf son especialmente bienvenidos.

Estoy seguro de que podemos vencer los 9 comentarios en el hilo correspondiente de MySQL :)

El truco más fácil para dejar que postgresql funcione mucho mejor (aparte de establecer y usar los índices adecuados, por supuesto) es simplemente para darle más RAM para trabajar (si aún no lo ha hecho). En la mayoría de las instalaciones predeterminadas, el valor de shared_buffers es demasiado bajo (en mi opinión). Puedes establecer

shared_buffers

en postgresql.conf. Divida este número por 128 para obtener una aproximación de la cantidad de memoria (en MB) que postgres puede reclamar. Si subes lo suficiente, esto hará volar postgresql. No te olvides de reiniciar postgresql.

En los sistemas Linux, cuando postgresql no se iniciará de nuevo, es probable que haya alcanzado el límite de kernel.shmmax. Establecerlo más alto con

sysctl -w kernel.shmmax=xxxx

Para que esto persista entre botas, agregue una entrada kernel.shmmax a /etc/sysctl.conf.

Un montón de trucos de Postgresql se pueden encontrar aquí :

Es conveniente cambiar el nombre de una base de datos anterior en lugar de mysql. Solo usando el siguiente comando:

ALTER DATABASE name RENAME TO new_name

Esta es mi lista de favoritos de las características menos conocidas.

DDL transaccional

Casi todas las declaraciones SQL son transaccionales en Postgres. Si desactivas la confirmación automática, lo siguiente es posible:

drop table customer_orders; rollback; select * from customer_orders;

Tipos de rango y restricción de exclusión

Que yo sepa, Postgres es el único RDBMS que le permite crear una restricción que verifica si dos rangos se superponen. Un ejemplo es una tabla que contiene precios de productos con una fecha "válida desde" y "válida hasta":

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

Características NoSQL

La extensión hstore ofrece un almacén de clave / valor flexible y muy rápido que se puede usar cuando las partes de la base de datos necesitan ser "sin esquema". JSON es otra opción para almacenar datos de una manera sin esquema y

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

El plan de ejecución para lo anterior en una tabla con 700,000 filas:

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

Para evitar la inserción de filas con rangos de validez superpuestos, se puede definir una restricción única simple (y eficiente):

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

infinito

En lugar de requerir una fecha "real" en el futuro, Postgres puede comparar las fechas hasta el infinito. Por ejemplo, cuando no se utiliza un rango de fechas, puede hacer lo siguiente

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

Expresiones de tabla común grabables

Puede eliminar, insertar y seleccionar en una sola declaración:

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;

Lo anterior eliminará todos los pedidos anteriores a 10 años, los moverá a la tabla archived_orders y luego mostrará las filas que se movieron.

Las Vistas Materializadas son bastante fáciles de configurar:

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;

Eso crea una nueva tabla, my_matview, con las columnas y los valores de my_view. Los desencadenantes o una secuencia de comandos cron se pueden configurar para mantener los datos actualizados, o si eres perezoso:

TRUNCATE my_matview; INSERT INTO my_matview SELECT * FROM my_view;

Las matrices son geniales una vez que las conoces. Digamos que le gustaría almacenar algunos hipervínculos entre páginas. Puede comenzar pensando en crear una tabla como esta:

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

Si necesitaras indexar la columna de la cola , y tuvieras, digamos 200,000,000 enlaces-filas (como wikipedia te daría), te encontrarás con una Tabla enorme y un Índice enorme.

Sin embargo, con PostgreSQL, puede usar este formato de tabla en su lugar:

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

Para obtener todos los encabezados de un enlace, puede enviar un comando como este (unnest () es estándar desde 8.4):

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

Esta consulta es sorprendentemente rápida cuando se compara con la primera opción (unnest () es rápido y el índice es mucho más pequeño). Además, su tabla e índice ocupará mucha menos memoria RAM y espacio en disco duro, especialmente cuando sus matrices son tan largas que se comprimen en una tabla de Toast. Las matrices son realmente poderosas.

Nota: mientras que unnest () generará filas fuera de una matriz, array_agg () agregará filas a una matriz.

Postgres tiene un servicio de manejo de fecha muy poderoso gracias a su soporte INTERVAL.

Por ejemplo:

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)

Puede convertir muchas cadenas en un tipo de INTERVALO.

Una base de datos se puede copiar con:

createdb -T old_db new_db

La documentación dice:

esto no está (todavía) destinado como una función de "COPIA DE BASE DE DATOS" de propósito general

pero funciona bien para mí y es mucho más rápido que

createdb new_db

pg_dump old_db | psql new_db

Una de las cosas que realmente me gusta de Postgres es algunos de los tipos de datos admitidos en las columnas. Por ejemplo, hay tipos de columna creados para almacenar direcciones de red y Arrays . Las funciones correspondientes ( Direcciones de red / Arrays ) para estos tipos de columnas le permiten realizar muchas operaciones complejas dentro de las consultas que tendría que hacer procesando los resultados a través del código en MySQL u otros motores de base de datos.

pgcrypto : más funciones criptográficas que proporcionan muchos módulos de criptografía de los lenguajes de programación, todos accesibles directamente desde la base de datos. Hace las cosas criptográficas increíblemente fáciles de Just Get Right.

1.) Cuando necesite agregar un aviso a la consulta, puede usar un comentario anidado

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

2.) Eliminar los espacios finales de todo el campo de text y varchar en una base de datos.

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.) Podemos usar una función de ventana para la eliminación muy efectiva de las filas duplicadas:

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

La versión optimizada de algunos PostgreSQL (con 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.) Cuando necesitamos identificar el estado del servidor, podemos usar una función:

SELECT pg_is_in_recovery();

5.) Obtenga el comando DDL de las funciones.

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

6.) Cambiar de forma segura el tipo de datos de columna en 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

Puede ver en la tabla anterior que he utilizado el tipo de datos - ''carácter variable'' para ''id''
columna. Pero fue un error, porque siempre estoy dando enteros como id. Entonces, usar varchar aquí es una mala práctica. Intentemos cambiar el tipo de columna a entero.

ALTER TABLE test ALTER COLUMN id TYPE integer;

Pero vuelve:

ERROR: la columna "id" no se puede convertir automáticamente para escribir un estado SQL entero: 42804 Sugerencia: especifique una expresión USING para realizar la conversión

Eso significa que no podemos simplemente cambiar el tipo de datos porque los datos ya están allí en la columna. Dado que los datos son de tipo ''carácter variable'', los postgres no pueden esperarlo como enteros, aunque solo ingresamos números enteros. Entonces, como postgres sugirió que podemos usar la expresión ''USING'' para convertir nuestros datos en enteros.

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

Funciona.

7.) Sepa quién está conectado a la Base de datos
Esto es más o menos un comando de monitoreo. Para saber qué usuario conectado a qué base de datos, incluidos su IP y Puerto, utilice el siguiente SQL:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

8.) Recargar los archivos de configuración de PostgreSQL sin reiniciar el servidor

Los parámetros de configuración de PostgreSQL se encuentran en archivos especiales como postgresql.conf y pg_hba.conf. A menudo, puede necesitar cambiar estos parámetros. Pero para que algunos parámetros surtan efecto, a menudo necesitamos volver a cargar el archivo de configuración. Por supuesto, reiniciar el servidor lo hará. Pero en un entorno de producción no se prefiere reiniciar la base de datos, que se está utilizando por miles, solo para establecer algunos parámetros. En tales situaciones, podemos volver a cargar los archivos de configuración sin reiniciar el servidor mediante la siguiente función:

select pg_reload_conf();

Recuerde, esto no funcionará para todos los parámetros, algunos cambios de parámetros necesitan un reinicio completo del servidor para que tenga efecto.

9.) Obtener la ruta del directorio de datos del clúster de la base de datos actual

Es posible que en un sistema, varias instancias (clúster) de PostgreSQL estén configuradas, generalmente, en diferentes puertos más o menos. En tales casos, encontrar qué directorio (directorio de almacenamiento físico) se usa y qué instancia es una tarea agitada. En tales casos, podemos usar el siguiente comando en cualquier base de datos en el clúster de nuestro interés para obtener la ruta del directorio:

SHOW data_directory;

La misma función se puede usar para cambiar el directorio de datos del clúster, pero requiere que el servidor se reinicie:

SET data_directory to new_directory_path;

10.) Encontrar un CHAR es DATE o no

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;

Uso: lo siguiente será verdadero

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.) Cambiar el propietario en PostgreSQL

REASSIGN OWNED BY sa TO postgres;

12.) PGADMIN PLPGSQL DEBUGGER

Bien explicado here

Almacenamiento de memoria para datos desechables / variables globales

Puede crear un espacio de tabla que viva en la RAM y tablas (posiblemente no registradas, en 9.1) en ese espacio de tablas para almacenar variables desechables / variables globales que le gustaría compartir a lo largo de las sesiones.

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

Cerraduras consultivas

Estos están documentados en un área oscura del manual:

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

En ocasiones es más rápido que adquirir multitud de bloqueos a nivel de fila, y se pueden usar para evitar casos donde FOR UPDATE no está implementado (como consultas CTE recursivas).

DUPDO

Yo empezare. Cada vez que cambio a Postgres desde SQLite, generalmente tengo algunos conjuntos de datos realmente grandes. La clave es cargar sus tablas con COPY FROM en lugar de hacer INSERTOS. Ver documentación:

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

El siguiente ejemplo copia una tabla al cliente utilizando la barra vertical (|) como delimitador de campo:

COPY country TO STDOUT WITH DELIMITER ''|'';

Para copiar datos de un archivo en la tabla de países:

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

Ver también aquí: Inserciones masivas más rápidas en sqlite3?

select pg_size_pretty(200 * 1024)

  • Mi favorito es generar_series: por fin, una manera limpia de generar conjuntos de filas ficticias.

  • Posibilidad de usar un valor correlacionado en una cláusula LIMIT de una subconsulta:

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

  • Abitlity para usar múltiples parámetros en agregados personalizados (no cubiertos por la documentación): vea el artículo en mi blog para ver un ejemplo.

  • Herencia ... Herencia múltiple de hecho (como en la "herencia" padre-hijo), no en la herencia de relación de 1 a 1 que muchos frameworks web implementan cuando se trabaja con postgres.

  • PostGIS (extensión espacial), un complemento maravilloso que ofrece un amplio conjunto de funciones de geometría y coordina el almacenamiento de la caja. Ampliamente utilizado en muchas bibliotecas de código abierto (por ejemplo, OpenLayers, MapServer, Mapnik, etc.) y definitivamente mucho mejor que las extensiones espaciales de MySQL.

  • Procedimientos de escritura en diferentes idiomas, por ejemplo, C, Python, Perl, etc. (hace que tu vida sea más fácil de codificar si eres un desarrollador y no un db-admin).

    Además, todos los procedimientos pueden almacenarse externamente (como módulos) y pueden invocarse o importarse en tiempo de ejecución mediante argumentos especificados. De esta forma, puede controlar el código fuente y depurar el código fácilmente.

  • Un catálogo enorme y completo sobre todos los objetos implementados en su base de datos (es decir, tablas, restricciones, índices, etc.).

    Siempre me resulta inmensamente útil ejecutar pocas consultas y obtener toda la información meta, por ejemplo, nombres de restricciones y campos en los que se han implementado, nombres de índice, etc.

    Para mí todo se vuelve extremadamente útil cuando tengo que cargar datos nuevos o hacer actualizaciones masivas en tablas grandes (desactivo automáticamente los desencadenantes y los dejo indexados) y luego los vuelvo a crear fácilmente una vez que el procesamiento ha finalizado. Alguien hizo un excelente trabajo al escribir algunas de estas consultas.

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

  • Varios esquemas en una base de datos, puede usarlo si su base de datos tiene una gran cantidad de tablas, puede pensar en esquemas como categorías. Todas las tablas (independientemente de su esquema) tienen acceso a todas las demás tablas y funciones presentes en el padre db.