Re: particionamiento horizontal postgresql

El 19/01/2010 15:42, jairo vides escribió:
> Estoy diseñando una base de datos y quiero implementarla en postgresql
> mi pregunta es:
> Alguien ha utilizado el concepto de particionamiento horizontal en
> postgresql? y como podria implementarlo?
> Intenten pero me dio problemas en el las tablas que estaban
> relacionadas con las que estaban particionadas.
>
>
> Jairo Vides.
> EL futuro incierto ahora es presente gracias a la fe y la esperanza.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEABECAAYFAkpbysEACgkQoDfr0iXJqDW1eQCgmr/u5p4m51x6E27uiauUtrwh
> 2mEAoLSDqCGdai6S7bqgY6lWKzABqTDT
> =KlzE
> -----END PGP SIGNATURE-----
> --
> TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda
>
Hay varias opciones para ello:
PostgreSQL Partitioning Docs
– http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
PostgreSQL Tablespaces Docs
– http://www.postgresql.org/docs/8.3/interactive/manage-ag-tablespaces.html
Con PL/Proxy se puede lograr esto también:
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy
http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/
http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-ii/

También se puede consultar la presentación de Edwin Grubbs
(edwin(at)grubbs(dot)org); en en link: http://mysql.meetup.com/284/

Ejemplo de Edwin:
1.Create a master table from which all the child tables will inherit.

CREATE TABLE shipment (
id SERIAL PRIMARY KEY,
address TEXT NOT NULL,
shipping_date TIMESTAMP NOT NULL);

2.Create child tables to serve as each partition of the master table using
table constraints to define the allowed key values in each partition.

CREATE TABLE shipment_part_2008 (
CHECK (shipping_date >= DATE '2008-01-01'
AND shipping_date < DATE '2009-01-01')
) INHERITS (shipment);
CREATE TABLE shipment_part_pre2008 (
CHECK (shipping_date < DATE '2008-01-01')
) INHERITS (shipment);

3.Create an index on the key column(s) for each partition.
CREATE INDEX shipping_date_2008 ON shipment_part_2008 (shipping_date);
CREATE INDEX shipping_date_pre2008 ON shipment_part_pre2008 (shipping_date);

4.Ensure that the constraint exclusion configuration parameter is enabled in
postgresql.conf so that queries will be optimized for partitioning (child
tables will not be searched for values they can't contain).
constraint_exclusion = on

5. Optionally, define a trigger or rule to redirect data inserted into
the master table
to the appropriate partition. An update trigger is not necessary.

CREATE OR REPLACE FUNCTION shipment_insert()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.shipping_date >= DATE '2008-01-01'
AND NEW.shipping_date < DATE '2009-01-01') THEN
INSERT INTO shipment_part_2008 VALUES (NEW.*);
ELSIF (NEW.shipping_date < DATE '2008-01-01') THEN
INSERT INTO shipment_part_pre2008 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the shipment_insert()
function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER shipment_insert_trigger
BEFORE INSERT ON shipment
FOR EACH ROW EXECUTE PROCEDURE shipment_insert();

INSERT INTO shipment (address, shipping_date) VALUES ('Alaska',
'2008-08-08');
INSERT INTO shipment (address, shipping_date) VALUES ('Texas',
'2007-07-07');
UPDATE shipment SET address = 'Dakota' WHERE address = 'Alaska';
SELECT * FROM ONLY shipment;
id | address | shipping_date
----+---------+---------------
(0 rows)
SELECT * FROM shipment;
id | address | shipping_date
----+---------+---------------------
1 | Dakota | 2008-08-08 00:00:00
2 | Texas | 2007-07-07 00:00:00
(2 rows)
SELECT * FROM shipment_part_2008;
id | address | shipping_date
----+---------+---------------------
1 | Dakota | 2008-08-08 00:00:00
(1 row)
SELECT * FROM shipment_part_pre2008;
id | address | shipping_date
----+---------+---------------------
2 | Texas | 2007-07-07 00:00:00
(1 row)

Éste es un ejemplo muy básico.
Saludos y espero que les sirva algo

--
-------------------------------------
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA&& DWH -- BI Apprentice

Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)
Universidad de las Ciencias Informáticas

Linux User # 418229

-- PostgreSQL --

http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org

-- DWH + BI --

http://www.tdwi.org

-------------------------------------------