2

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.

Improve this question
3
  • 1
    I would expect in this scheme that planning a query takes a considerable amount of time due to the number of partitions. The PostgreSQL manual states: ... up to perhaps a hundred partitions; don't try to use many thousands ... What queries do these tables serve and how do they perform? What drove the current partitionining design? As you seem to be removing data after 12 months, considering a partition strategy based on time might be very fruitful, you could drop (say weekly) partitions.
    – Feike Steenbergen
    Commented May 26, 2017 at 9:00
  • 1
    You should also give a functional description of what the tables are for.
    – CalZ
    Commented May 26, 2017 at 11:58
  • @Feike although queries are being executed against the actual partition tables and not the parent table, so query planning is not an issue, your partition suggestion sounds pretty reasonable. And the manual reference is also useful
    – Gonzalo Vasquez
    Commented May 26, 2017 at 13:14

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.