Probando cstore_fdw

Hola a todos como ya saben tengo mi pasión por los FDW de PostgreSQL, tengo un post previo referente a dump_fdw, el cual es muy útil para acceder a información almacenada en archivos de backups realizados por pg_dump.

En esta ocasión les muestro el uso de cstore_fdw, el cual es desarrollado por la empresa citusdata, el mismo implementa un mecanismo de almacenamiento columnar y utiliza el formato Optimized Row Columnar (ORC), el cual puede ser ventajoso en los casos donde se realizan análisis de datos, y las principales ventajas son:

  • Compresión:  Reduce considerablemente el espacio en disco y memoria.
  • Proyección de columnas: solamente lee las columnas necesarias para la consulta.

Les dejo un ejemplo de su uso:

Primero que todo lo descargamos el FDW desde aquí, la prueba se realizó en ubuntu 14.04 y para la compilación de la misma se necesita  protobuf-c-compiler y libprotobuf-c0-dev

Ejecutamos


make
sudo make install

Una vez compilada e instalada ya debemos tener la extensión disponible para crearla desde PostgreSQL, antes debemos indicarle en el archivo de configuración postgresql.conf en la variable shared_preload_libraries el valor de cstore_fdw, por ejemplo shared_preload_libraries = ‘cstore_fdw’ y se debe reiniciar el servidor para que tenga efecto.

Para este ejemplo se utiliza la base de datos de siempre Dell Store 2, creamos la extensión y el servidor tal y como lo exige el  estandar :

CREATE EXTENSION cstore_fdw;
CREATE SERVER cs_server FOREIGN DATA WRAPPER cstore_fdw;

Luego se crea la tabla foránea, en este caso se hizo una copia  la tabla customers, la cual tiene unos 20000 registros

CREATE FOREIGN TABLE customers_fdw
(customerid integer NOT NULL,
firstname character varying(50) NOT NULL,
lastname character varying(50) NOT NULL,
address1 character varying(50) NOT NULL,
address2 character varying(50) ,
city character varying(50) NOT NULL,
state character varying(50) ,
zip integer ,
country character varying(50) NOT NULL,
region smallint NOT NULL,
email character varying(50) ,
phone character varying(50) ,
creditcardtype integer NOT NULL,
creditcard character varying(50) NOT NULL,
creditcardexpiration character varying(50) NOT NULL,
username character varying(50) NOT NULL,
password character varying(50) NOT NULL,
age smallint ,
income integer ,
gender character varying(1) )
SERVER cs_server
OPTIONS (compression 'pglz');
ALTER FOREIGN TABLE customers_fdw
OWNER TO postgres;

Las options que tiene este FDW las puede revisar aquí, en este caso solo se vera compression donde se le especifica el método de compresión que va a utilizar.

Para llenarla de datos debemos decirle la fuente en este caso se generó un archivo CSV con los datos de la tabla customers:


copy customers  to '/tmp/customers.csv' with CSV

Y se cargó de datos con:


copy customers_fdw  from '/tmp/customers.csv' with CSV

Se recomienda actualizar las estadísticas de la tabla.

 

ANALYZE customers;

 

 

Y ya queda lista la tabla para ser utilizada, se ejecuta una consulta de prueba:

select firstname,lastname from customers_fdw

Haremos una consulta más compleja donde haya cálculos con funciones de agregados y veremos el plan de ejecuciòn y el tiempo, por ejemplo:

explain analyze select country, min(creditcardtype), avg(age) from customers_fdw group by 1

 

 

Resultado:


"HashAggregate (cost=237.59..237.60 rows=1 width=124) (actual time=14.388..14.394 rows=11 loops=1)"
" -> Foreign Scan on customers_fdw (cost=0.00..149.78 rows=11708 width=124) (actual time=1.649..5.112 rows=20000 loops=1)"
" CStore File: /var/lib/postgresql/9.3/main/cstore_fdw/17483/26051"
" CStore File Size: 1779667"
"Total runtime: 14.598 ms"

Si ejecutamos la misma consulta en la tabla origina de customers obtenemos:

"HashAggregate (cost=838.00..838.14 rows=11 width=11) (actual time=19.999..20.007 rows=11 loops=1)"
" -> Seq Scan on customers (cost=0.00..688.00 rows=20000 width=11) (actual time=0.006..3.337 rows=20000 loops=1)"
"Total runtime: 18.058 ms"

Y si le agregamos algún filtro a la consulta por ejemplo:

explain analyze
select country, min(creditcardtype), avg(age) from customers_fdw where customerid<9000 group by 1

el resultado es:

 "HashAggregate (cost=219.22..219.23 rows=1 width=124) (actual time=9.307..9.308 rows=1 loops=1)"</pre>
" -> Foreign Scan on customers_fdw (cost=0.00..189.95 rows=3903 width=124) (actual time=1.559..4.017 rows=8999 loops=1)"
" Filter: (customerid < 9000)"
" Rows Removed by Filter: 1001"
" CStore File: /var/lib/postgresql/9.3/main/cstore_fdw/17483/26051"
" CStore File Size: 1779667"
"Total runtime: 9.713 ms"

y para la tabla original es:

"HashAggregate (cost=552.29..552.35 rows=5 width=11) (actual time=11.463..11.464 rows=1 loops=1)"
" -> Index Scan using customers_pkey on customers (cost=0.29..484.79 rows=9000 width=11) (actual time=0.027..3.199 rows=8999 loops=1)"
" Index Cond: (customerid < 9000)"
"Total runtime: 12.525 ms"

Incluso utilizando el índice en la tabla original para la búsqueda puede ser más rápido la búsqueda en la tabla foránea sobre todo agregando un filtro. Podemos ver como remueve en la búsqueda unos 1001 registros y esto está dado porque que el cstore_fdw crea con la opción block_row_count(números de filas por bloques de columna, por defecto 10000) es decir hace bloques de columnas donde almacena el mínimo y máximo valor y así esto funciona bloques de exclusión, donde solamente busca en los bloques donde está la condición del where, las demás las ignora.

Se puede observar también cuanto ocupa la tabla foránea físicamente,”CStore File Size: 1779667″, también puede verse con la función cstore_table_size, aproximadamente 1.7 MB y si vemos cuanto ocupa la tabla original es aproximadamente 3.8 MB. Claro estos beneficios tiene un costo, como seguramente es un poco lenta la carga debido a que tiene que comprimir la información y tal vez otro inconveniente más, pero con estos beneficios en ocasiones se pueden obviar los contratiempos que nos puede traer el uso de este FDW. Desde mi modesto punto de vista está muy bueno y algunos escenarios puede resultar muy útil.

Espero que les haya resultado interesante este FDW, nos vemos en otra 😉


 

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s