PostgreSQL 11 (2018-10-18 => 2023-11-09) Increased robustness and performance for partitioning

See also

Sortie de PostgreSQL 11 le jeudi 18 octobre 2018

PostgreSQL 11 est sortie. La principale nouveauté est le partitionnement logique. 

BEGIN;
--
-- Create model LogFace
--
CREATE TABLE log_face (
    id serial NOT NULL,
    id_web_service varchar(200) NOT NULL,
    type_log varchar(200) NOT NULL,
    logdate timestamp with time zone NOT NULL,
    argument1 varchar(200) NOT NULL,
    argument2 varchar(200) NOT NULL,
    fmr integer NOT NULL,
    return_user_data boolean NOT NULL,
    response_code integer NOT NULL CHECK ("response_code" >= 0)
) PARTITION BY RANGE (logdate);

--
-- Create index web_service_logdate_idx on field(s) id_web_service, logdate of model logface
--

CREATE INDEX "id_idx" ON "log_face" ("id");
CREATE INDEX "log_face_logdate_2307a2_brin" ON "log_face" USING brin ("logdate");
CREATE INDEX "web_service_logdate_idx" ON "log_face" ("id_web_service", "logdate");
CREATE INDEX "log_face_id_web_service_ab94475f" ON "log_face" ("id_web_service");
CREATE INDEX "log_face_id_web_service_ab94475f_like" ON "log_face" ("id_web_service" varchar_pattern_ops);
COMMIT;

BEGIN;
    CREATE TABLE log_face_y2018 PARTITION OF log_face FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
    CREATE TABLE log_face_y2019 PARTITION OF log_face FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
    CREATE TABLE log_face_default PARTITION OF log_face DEFAULT;
COMMIT;