Partion ChangesetMD tables

changesetmd-partition.sql

UPDATE osm_changeset_state SET update_in_progress = 1; -- lock out ChangesetMD from updating
ALTER TABLE osm_changeset RENAME TO old_osm_changeset;

CREATE TABLE osm_changeset (LIKE old_osm_changeset INCLUDING CONSTRAINTS);

CREATE TABLE osm_changeset_0m AS SELECT * FROM old_osm_changeset WHERE id >= 0 AND id < 10000000;
ALTER TABLE osm_changeset_0m ADD CHECK (id >= 0 AND id < 10000000);
CREATE UNIQUE INDEX osm_changeset_0m_pkey ON osm_changeset_0m (id) WITH (fillfactor = 100);
ALTER TABLE osm_changeset_0m ADD PRIMARY KEY USING INDEX osm_changeset_0m_pkey;
CREATE INDEX ON osm_changeset_0m (user_id) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_0m (user_name) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_0m USING BRIN (created_at) WITH (pages_per_range = 64);
CREATE INDEX ON osm_changeset_0m USING gist (geom) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_0m USING gin (tags);
CREATE INDEX osm_changeset_0m_open_idx ON osm_changeset_0m (id) WITH (fillfactor = 100) WHERE (open);
CREATE INDEX osm_changeset_0m_tags_created_by ON osm_changeset_0m ((tags->'created_by') text_pattern_ops) WITH (fillfactor=100) WHERE (tags->'created_by' IS NOT NULL);
ALTER TABLE osm_changeset_0m INHERIT osm_changeset;
ANALYZE osm_changeset_0m;

CREATE TABLE osm_changeset_10m AS SELECT * FROM old_osm_changeset WHERE id >= 10000000 AND id < 20000000;
ALTER TABLE osm_changeset_10m ADD CHECK (id >= 10000000 AND id < 20000000);
CREATE UNIQUE INDEX osm_changeset_10m_pkey ON osm_changeset_10m (id) WITH (fillfactor = 100);
ALTER TABLE osm_changeset_10m ADD PRIMARY KEY USING INDEX osm_changeset_10m_pkey;
CREATE INDEX ON osm_changeset_10m (user_id) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_10m (user_name) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_10m USING BRIN (created_at) WITH (pages_per_range = 64);
CREATE INDEX ON osm_changeset_10m USING gist (geom) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_10m USING gin (tags);
CREATE INDEX osm_changeset_10m_open_idx ON osm_changeset_10m (id) WITH (fillfactor = 100) WHERE (open);
CREATE INDEX osm_changeset_10m_tags_created_by ON osm_changeset_10m ((tags->'created_by') text_pattern_ops) WITH (fillfactor=100) WHERE (tags->'created_by' IS NOT NULL);
ALTER TABLE osm_changeset_10m INHERIT osm_changeset;
ANALYZE osm_changeset_10m;

CREATE TABLE osm_changeset_20m AS SELECT * FROM old_osm_changeset WHERE id >= 20000000 AND id < 30000000;
ALTER TABLE osm_changeset_20m ADD CHECK (id >= 20000000 AND id < 30000000);
CREATE UNIQUE INDEX osm_changeset_20m_pkey ON osm_changeset_20m (id) WITH (fillfactor = 100);
ALTER TABLE osm_changeset_20m ADD PRIMARY KEY USING INDEX osm_changeset_20m_pkey;
CREATE INDEX ON osm_changeset_20m (user_id) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_20m (user_name) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_20m USING BRIN (created_at) WITH (pages_per_range = 64);
CREATE INDEX ON osm_changeset_20m USING gist (geom) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_20m USING gin (tags);
CREATE INDEX osm_changeset_20m_open_idx ON osm_changeset_20m (id) WITH (fillfactor = 100) WHERE (open);
CREATE INDEX osm_changeset_20m_tags_created_by ON osm_changeset_20m ((tags->'created_by') text_pattern_ops) WITH (fillfactor=100) WHERE (tags->'created_by' IS NOT NULL);
ALTER TABLE osm_changeset_20m INHERIT osm_changeset;
ANALYZE osm_changeset_20m;

CREATE TABLE osm_changeset_30m AS SELECT * FROM old_osm_changeset WHERE id >= 30000000 AND id < 40000000;
ALTER TABLE osm_changeset_30m ADD CHECK (id >= 30000000 AND id < 40000000);
CREATE UNIQUE INDEX osm_changeset_30m_pkey ON osm_changeset_30m (id) WITH (fillfactor = 100);
ALTER TABLE osm_changeset_30m ADD PRIMARY KEY USING INDEX osm_changeset_30m_pkey;
CREATE INDEX ON osm_changeset_30m (user_id) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_30m (user_name) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_30m USING BRIN (created_at) WITH (pages_per_range = 8);
CREATE INDEX ON osm_changeset_30m USING gist (geom) WITH (fillfactor = 100);
CREATE INDEX ON osm_changeset_30m USING gin (tags);
CREATE INDEX osm_changeset_30m_open_idx ON osm_changeset_30m (id) WITH (fillfactor = 100) WHERE (open);
CREATE INDEX osm_changeset_30m_tags_created_by ON osm_changeset_30m ((tags->'created_by') text_pattern_ops) WITH (fillfactor=100) WHERE (tags->'created_by' IS NOT NULL);
ALTER TABLE osm_changeset_30m INHERIT osm_changeset;
ANALYZE osm_changeset_30m;

CREATE TABLE osm_changeset_40m AS SELECT * FROM old_osm_changeset WHERE id >= 40000000 AND id < 50000000;
ALTER TABLE osm_changeset_40m ADD CHECK (id >= 40000000 AND id < 50000000);
CREATE UNIQUE INDEX osm_changeset_40m_pkey ON osm_changeset_40m (id) WITH (fillfactor = 90);
ALTER TABLE osm_changeset_40m ADD PRIMARY KEY USING INDEX osm_changeset_40m_pkey;
CREATE INDEX ON osm_changeset_40m (user_id) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_40m (user_name) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_40m (created_at) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_40m USING gist (geom) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_40m USING gin (tags);
CREATE INDEX osm_changeset_40m_open_idx ON osm_changeset_40m (id) WITH (fillfactor = 90) WHERE (open);
CREATE INDEX osm_changeset_40m_tags_created_by ON osm_changeset_40m ((tags->'created_by') text_pattern_ops) WITH (fillfactor=90) WHERE (tags->'created_by' IS NOT NULL);
ALTER TABLE osm_changeset_40m INHERIT osm_changeset;
ANALYZE osm_changeset_40m;

CREATE TABLE osm_changeset_50m AS SELECT * FROM old_osm_changeset WHERE id >= 50000000 AND id < 60000000;
ALTER TABLE osm_changeset_50m ADD CHECK (id >= 50000000 AND id < 60000000);
CREATE UNIQUE INDEX osm_changeset_50m_pkey ON osm_changeset_50m (id) WITH (fillfactor = 90);
ALTER TABLE osm_changeset_50m ADD PRIMARY KEY USING INDEX osm_changeset_50m_pkey;
CREATE INDEX ON osm_changeset_50m (user_id) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_50m (user_name) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_50m (created_at) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_50m USING gist (geom) WITH (fillfactor = 90);
CREATE INDEX ON osm_changeset_50m USING gin (tags);
CREATE INDEX osm_changeset_50m_open_idx ON osm_changeset_50m (id) WITH (fillfactor = 90) WHERE (open);
CREATE INDEX osm_changeset_50m_tags_created_by ON osm_changeset_50m ((tags->'created_by') text_pattern_ops) WITH (fillfactor=90) WHERE (tags->'created_by' IS NOT NULL);
ALTER TABLE osm_changeset_50m INHERIT osm_changeset;
ANALYZE osm_changeset_50m;

CREATE OR REPLACE FUNCTION osm_changeset_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF (NEW.id >= 50000000 AND NEW.id < 60000000) THEN
    INSERT INTO osm_changeset_50m VALUES (NEW.*);
  ELSEIF (NEW.id >= 40000000 AND NEW.id < 50000000) THEN
    INSERT INTO osm_changeset_40m VALUES (NEW.*);
  ELSEIF (NEW.id >= 30000000 AND NEW.id < 40000000) THEN
    INSERT INTO osm_changeset_30m VALUES (NEW.*);
  ELSEIF (NEW.id >= 20000000 AND NEW.id < 30000000) THEN
    INSERT INTO osm_changeset_20m VALUES (NEW.*);
  ELSEIF (NEW.id >= 10000000 AND NEW.id < 20000000) THEN
    INSERT INTO osm_changeset_10m VALUES (NEW.*);
  ELSEIF (NEW.id >= 0 AND NEW.id < 10000000) THEN
    INSERT INTO osm_changeset_0m VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'ID out of range. Fix the osm_changeset_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_osm_changeset_trigger
  BEFORE INSERT ON osm_changeset
  FOR EACH ROW EXECUTE PROCEDURE osm_changeset_insert_trigger();

DROP TABLE old_osm_changeset;
ALTER TABLE osm_changeset ADD PRIMARY KEY (id);
ANALYZE osm_changeset;
UPDATE osm_changeset_state SET update_in_progress = 0; -- unlock