Un Poco de Teoría

A veces cuando estamos manejando tablas con un numero grande de registros, varios millones o mas es necesario particionar las tablas para obtener un mejor desempeño a la hora de consultar dichas tablas. A este proceso se le suele denominar "Partitioning" y es le metodo usado para descomponer una enorme tabla (father) en un cojunto de tablas hijas (child).  

 Algunas de las enormes ventajas son: 

  • Desempeño mejorado para la obtención de registros (Querys). 
  • Desempeño mejorado para la actualización (update). 
  • Indices mas pequeños para cada tabla hija contra indices grandes y dificiles de colocar en memoria en una tabla grande. 
  • Eliminación rápida de registros. Empleando DROP TABLE en vez de DELETE.

 En postgresql el tipo de partitioning soportado se denomina particionado mediante herencia de tablas.Cada partición puede ser creada como una tabla hija de una única tabla padre. La tabla padre normalmente debe de ser una tabla vacía, que representara a todo el conjunto de datos. 

 Vamos a la practica!

En el siguiente ejercicio creare una tabla para recibi datos de posicion de un gps.

1.-- creo la tabla maestra
?
1
2
3
4
5
6
7
CREATE TABLE gps_positions (
    id serial,
    gps_id integer NOT NULL,
    lat double precision NOT NULL,
    lon double precision NOT NULL,
    date_time timestamp(0) without time zone NOT NULL,
);

 2.- creacion tablas hijas

Ahora creare las tablas hijas para esto creare una función para crearlas de manera automatica, por cada día creare una tabla hija.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE OR REPLACE FUNCTION gps_positions_insert_child()
RETURNS TRIGGER AS $$
DECLARE
_tablename text;
_start_date TIMESTAMP;
_end_date TIMESTAMP;
BEGIN
  
 --rango de fecha aceptado enm la tabla hija
 _start_date = (NEW."date_time"::date);
 _end_date   = _start_date  + INTERVAL '1 day';
 
 --Asigno el nombre a la tabla hija
 _tablename  = 'gps_positions'||to_char(_start_date, 'YYYYMMDD');
 
 --Verifico que la tabla exista
 PERFORM 1
 FROM   pg_catalog.pg_class c
 JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE  c.relkind = 'r'
 AND    c.relname = _tablename
 AND    n.nspname = 'public';
 
 --Si la tabla no existe la creo
 IF NOT FOUND THEN
  EXECUTE 'CREATE TABLE ' || _tablename || ' (
   CHECK ( date_time >=  ' || quote_literal(_start_date) || '
   AND date_time < '   || quote_literal(_end_date)   || ')
  ) INHERITS (gps_positions)';
 
  EXECUTE 'CREATE INDEX ' || _tablename||'_gps_id_index' || ' ON ' || _tablename || ' (gps_id)';
 
 END IF;
 
 --Se inserte el nuevo registro en la tabla  hija correspondiente
 EXECUTE 'INSERT INTO ' || _tablename || ' VALUES ($1.*)' USING NEW;
 
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

 Explicando un poco el código anterior básicamente esta función toma la fecha del campo date_time y la concadena al nombre de la maestra; con ese nombre se crea la tabla hija
es decir el bloque que crea la tabla hija quedaría de la siguiente manera:
?
1
2
3
CREATE TABLE gps_positions20180509 (
   CHECK (date_time >= '2018-05-09' AND date_time < '2018-05-10')
) INHERITS (gps_positions)

CHECK es quien define los valores permitidos en este caso el rango de fecha de 1 dia.
mientras que INHERITS  es quien le dice quien es su tabla padre

 3.- Se crea un trigger

 Finalmente se crea un trigger que es el que gatillara la función  que creamos anteriormente
?
1
2
3
CREATE TRIGGER gps_positions_trigger
 BEFORE INSERT ON gps_positions
 FOR EACH ROW EXECUTE PROCEDURE gps_positions_insert_child();

 4.- Pruebas
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
test=# INSERT INTO gps_positions (gps_id, lat, lon, date_time) VALUES (100, 7.096528,  -65.978240, now());
INSERT 0 0
  
test=# select * from gps_positions;
 id | gps_id |   lat    |    lon    |      date_time     
----+--------+----------+-----------+---------------------
  3 |    100 | 7.096528 | -65.97824 | 2018-05-09 23:14:31
(1 fila)
 
 
test=# select * from ONLY gps_positions;
 id | gps_id | lat | lon | date_time
----+--------+-----+-----+-----------
(0 filas)
 
test=# \dt
               Listado de relaciones
 Esquema |        Nombre         | Tipo  |  Dueño 
---------+-----------------------+-------+---------
 public  | gps_positions         | tabla | zelti
 public  | gps_positions20180509 | tabla | zelti
(2 filas)

Lo primero que hice fue hacer un INSERT en el primer SELECT muestra el registro insertado, sin embargo el segundo SELECT no devuelve cero registros, ya que en este le agregue el ONLY por lo que se ira contra la tabla maestra o padre, y no con las que tienen herencia. Al hacer un \dt para listar las tablas se puede ver la tabla que se creo automáticamente..

 Bien esto seria una de las maneras de crear tablas particionadas en PostgresSQL al menos la que yo uso ... Entre algunas recomendaciones que les podria dar seria crear un schema para las tablas hijas si no te llenera seguramente de muchas tablas el schema principal

 Bien Suerte!..