We have a 2TB PostgreSQL database, which contains two groups of tables that account for about 90% of the storage used. These two groups can be loosely described as 2 big tables partitioned, the first group has this table as parent:
CREATE TABLE avl_historico ( fecha timestamp with time zone NOT NULL, latitud double precision DEFAULT 0 NOT NULL, longitud double precision DEFAULT 0 NOT NULL, altitud double precision DEFAULT 0 NOT NULL, velocidad double precision DEFAULT 0 NOT NULL, cog double precision DEFAULT 0 NOT NULL, nsat integer DEFAULT 0 NOT NULL, tipo character(1), utc_hora time without time zone, fix_fecha date, imei bigint NOT NULL, registro timestamp with time zone, input1 integer DEFAULT 0, input2 integer DEFAULT 0, input3 integer DEFAULT 0, input4 integer DEFAULT 0, hdop double precision, adc double precision DEFAULT (-99), ignicion integer DEFAULT 1, adc2 double precision, power integer, driverid integer, ibutton2 integer, ibutton3 integer, ibutton4 integer, trailerid integer, adc3 double precision, adc4 double precision, horometro bigint, odometro bigint, panico integer DEFAULT 0, bateria double precision, bateriaint double precision );
It's children are built like this:
CREATE TABLE avl_historico_354898046636089 ( CONSTRAINT reports_avl_historico_only_354898046636089 CHECK ((imei = 354898046636089::bigint)) ) INHERITS (avl_historico);
The second "group" has this parent table:
CREATE TABLE historico ( imei bigint NOT NULL, utc timestamp without time zone NOT NULL, clase integer NOT NULL, valor double precision, registro timestamp with time zone DEFAULT now(), power integer DEFAULT (-1), voltaje double precision DEFAULT 0.0 );
And it's children:
CREATE TABLE historico_354676056027609 ( ) INHERITS (historico);
There are about 15.000 tables in each of these groups, and each table get's a new record inserted roughly about 40 secs. We only keep online the last 12 months, and older data gets
pg_dump'd and archived for later retrieval if necessary.
We guess there might be redesign considerations that may improve storage needs, speed and perhaps even management, but are clueless on what to do, what to look for, or what to search within our data.