Al parecer cuando instalamos una base de datos Postgres; se instala una base de datos de juguete; es decir que se debe trabajar mucho para dejarla a punto para producción. Los parametros por defecto son muy inferiores a lo que puede soportar realmente Postgres.
El primer paso para entender como mejorar la performance de su base de datos es entender el ciclo de vida de una query:
- La transmisión de la consulta a la base de datos back-end
- Análisis de cadena de consulta
- Planificación de consulta para optimizar la recuperación de datos
- Recuperación de datos de hardware
- Transmisión de resultados a cliente
El segundo paso es el analisis de cadena que tambien se puede mejorar haciendo un store procedure.
La planificación de la consulta es donde realmente comienza PostgreSQL hacer algún trabajo. En esta etapa se comprueba si la consulta está bien para la versión de PostgreSQL. También se analiza el SQL para determinar cuál es la forma más eficaz de recuperar sus datos. ¿Debemos utilizar un índice? y si es así ¿cuál? Estas son algunas de las decisiones de la base de datos hace que en este punto del proceso. Este paso puede ser eliminado si la consulta se prepara previamente.
Ahora que PostgreSQL tiene un plan de lo que cree que es la mejor manera de recuperar los datos, es el momento de ejecutar el plan. Si bien hay algunas opciones de optimización que ayudarían, este paso se optimiza principalmente por la configuración de hardware.
Y, finalmente, el último paso es la transmisión de los resultados al cliente. Si bien no hay ninguna opción de optimización real de este paso, se debe tener en cuenta que todos los datos que están estrayendo desde el disco y se envía a través del cable al cliente. Reducir al mínimo el número de filas y columnas para sólo aquello que es necesario a menudo puede aumentar el rendimiento.
Vamos a ver los parametros que hay que tocar para mejorar la performance de nuestra base. Se pueden cambiar los parametros de postgres de diferente formas pero la más facil es modificar el archivo postgresql.conf ejecutando SHOW config_file podremos saber donde se encuentra este archivo.
max_connections = <num> esta opción establece el número máximo de backend de base de datos para tener en un momento dado. Utilice esta función para asegurarse que no tenga tanto clientes y puedan matar la base.
shared_buffers = <num> La edición de esta opción es la forma más sencilla de mejorar el rendimiento de su servidor de base de datos. El valor por defecto es bastante bajo para la mayoría del hardware moderno. General se dice que esto se debe establecer en aproximadamente el 25% de la memoria RAM disponible en el sistema. Como la mayoría de las opciones que se describen aquí, simplemente tendrá que probar en diferentes niveles (tanto hacia arriba como hacia abajo) y ver lo bien que funciona en su sistema particular.
effective_cache_size = <num> Este valor es la cache de postgres. Esto se debe establecer la cantidad de memoria asignada a shared_buffers más la cantidad de caché del sistema operativo disponible. A menudo esto es más de 50% de la memoria total del sistema.
work_mem = <num> Esta opción se utiliza para controlar la cantidad de memoria utilizando en las operaciones de ordenación y tablas hash. Si bien es posible que necesite aumentar la cantidad de memoria se debe tener cuidado de no aumentarlo demasiado.
max_fsm_pages = <num> Esta opción ayuda a controlar el mapa de espacio libre. Cuando algo se elimina de una tabla no se elimina del disco inmediatamente, simplemente se marca como "libre" en el mapa de espacio libre. El espacio se puede volver a utilizar para cualquier inserción en la tabla. Si su configuración tiene una alta tasa de eliminaciones e inserta puede ser necesario aumento de este valor.
commit_delay = <num> y commit_siblings = <num> Estas opciones se utilizan en conjunto para ayudar a mejorar el rendimiento de escritura múltiples transacciones que están comitiando al mismo tiempo. Si hay una serie de backends commit_siblings activos en el instante en que la transacción se comitea, el servidor espera microsegundos commit_delay para tratar de confirmar las transacciones a la vez.
random_page_cost = <num>controla la forma en PostgreSQL considera las lecturas no secuenciales. Un valor más alto hace que sea más probable que un recorrido secuencial.
Dejo link:
http://wiki.postgresql.org/wiki/Performance_Optimization
