[Tile-serving] [openstreetmap/osm2pgsql] New middle tables experiments (Issue #1970)

[Jochen Topf]

I have opened PR #1969 which shows my current work on a new database formats for the so-called "middle" tables, ie. `planet_osm_nodes/ways/rels` which contain the data needed to update the database from change files.

But the middle tables can also contain useful information to do specialized processing of the OSM data. The new format makes them not only smaller but also easier to use. We'd be interested to get some feedback from the community on the new format.
We hope to make the new middle table format "official" at some point, but want to be sure we have the right format first.
(And of course we'll be supporting the old format for a while next to the new format.)

This is how the tables look like:
```
=> \d planet_osm_nodes
                        Table "public.planet_osm_nodes"
┌──────────────┬─────────────────────────────┬───────────┬──────────┬─────────┐
│    Column    │            Type             │ Collation │ Nullable │ Default │
├──────────────┼─────────────────────────────┼───────────┼──────────┼─────────┤
│ id           │ bigint                      │           │ not null │         │
│ lat          │ integer                     │           │ not null │         │
│ lon          │ integer                     │           │ not null │         │
│ created      │ timestamp without time zone │           │          │         │
│ version      │ integer                     │           │          │         │
│ changeset_id │ integer                     │           │          │         │
│ user_id      │ integer                     │           │          │         │
│ tags         │ jsonb                       │           │ not null │         │
└──────────────┴─────────────────────────────┴───────────┴──────────┴─────────┘
Indexes:
    "planet_osm_nodes_pkey" PRIMARY KEY, btree (id)

=> \d planet_osm_ways
                        Table "public.planet_osm_ways"
┌──────────────┬─────────────────────────────┬───────────┬──────────┬─────────┐
│    Column    │            Type             │ Collation │ Nullable │ Default │
├──────────────┼─────────────────────────────┼───────────┼──────────┼─────────┤
│ id           │ bigint                      │           │ not null │         │
│ created      │ timestamp without time zone │           │          │         │
│ version      │ integer                     │           │          │         │
│ changeset_id │ integer                     │           │          │         │
│ user_id      │ integer                     │           │          │         │
│ nodes        │ bigint[]                    │           │ not null │         │
│ tags         │ jsonb                       │           │ not null │         │
└──────────────┴─────────────────────────────┴───────────┴──────────┴─────────┘
Indexes:
    "planet_osm_ways_pkey" PRIMARY KEY, btree (id)
    "planet_osm_ways_nodes_bucket_idx" gin (planet_osm_index_bucket(nodes)) WITH (fastupdate=off)

=> \d planet_osm_rels
                        Table "public.planet_osm_rels"
┌──────────────┬─────────────────────────────┬───────────┬──────────┬─────────┐
│    Column    │            Type             │ Collation │ Nullable │ Default │
├──────────────┼─────────────────────────────┼───────────┼──────────┼─────────┤
│ id           │ bigint                      │           │ not null │         │
│ created      │ timestamp without time zone │           │          │         │
│ version      │ integer                     │           │          │         │
│ changeset_id │ integer                     │           │          │         │
│ user_id      │ integer                     │           │          │         │
│ members      │ jsonb                       │           │ not null │         │
│ tags         │ jsonb                       │           │ not null │         │
└──────────────┴─────────────────────────────┴───────────┴──────────┴─────────┘
Indexes:
    "planet_osm_rels_pkey" PRIMARY KEY, btree (id)
    "planet_osm_rels_planet_osm_index_node_members_idx" gin (planet_osm_index_node_members(members)) WITH (fastupdate=off)
    "planet_osm_rels_planet_osm_index_way_members_idx" gin (planet_osm_index_way_members(members)) WITH (fastupdate=off)

=> \d planet_osm_users
           Table "public.planet_osm_users"
┌────────┬─────────┬───────────┬──────────┬─────────┐
│ Column │  Type   │ Collation │ Nullable │ Default │
├────────┼─────────┼───────────┼──────────┼─────────┤
│ id     │ integer │           │ not null │         │
│ name   │ text    │           │ not null │         │
└────────┴─────────┴───────────┴──────────┴─────────┘
Indexes:
    "planet_osm_users_pkey" PRIMARY KEY, btree (id)
```

This is with with `-x/--extra-attributes` option. Without that the `created`, `version`, `changeset_id`, and `user_id` are missing in all tables and the `planet_osm_users` table will not be created.

If you want to try this, check out the branch from PR #1969 and compile it. You'll get the old format by default, you need the special command line options described in the PR to use it.

The new format is also slight more efficient. The database size drops from 256 GB for the planet to 237 GB. It is much much better if you use `-x/--extra-attributes`. In that case database size drops from nearly 400 GB to about 260 GB.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1970
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/issues/1970 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230527/3b6fb6bf/attachment-0001.htm>