When to update database schema

[kocio-pl]

I wonder when should we update database schema. Currently we're using hstore for the things not available through the tables, but the question is which features should stay this way and which ones should be available from the standard database. Do we have to decide case by case or there are some general rules?

Example of this problem is #2494.

[kocio-pl]

New PostgreSQL version is expected on November 9th, 2017:

https://www.postgresql.org/developer/roadmap/

It's probably good to know if we want some database schema changes by then, because it could make server imports easier (2 in 1).

[sommerluk]

I’m not sure. The OSM servers seem to prefer rather yet packaged software, so I’m not sure if they will install a new version when not available as an Ubuntu update. And does upgrading PostGre require at all a database reload?

[kocio-pl]

I have no better knowledge and it would be good to contact sysadmins, all I know is this statement (if I didn't misunderstood something): #1286 (comment).

[sommerluk]

Anyway, for the next database reload – whenever it comes – it might be nice to have more fine-graded z_order values, for example for highway=road, unclassified, residential which currently have all z = 330. Using different values will make no harm, and maybe it will be useful later on.

Maybe it is also possible to avoid secondary order arguments like CASE WHEN feature IN ('railway_INT-preserved-ssy', 'railway_INT-spur-siding-yard', 'railway_tram-service') THEN 0 ELSE 1 END by integrating this directly in the of z_order calculation.

[kocio-pl]

It looks like PostgreSQL 10 has been released just a few days ago:

https://www.postgresql.org/about/news/1786/

[pnorman]

I don't see that PostgreSQL releases matter. We're not going to be using features that require 10, we're still actively supporting 9.4.

We should look at a schema when we have enough things we want to do but can't without a schema change. Right now I'm not aware of anything on that list.

If we have enough things that are particularly difficult with the current schema we could also do a schema change. The only things we currently have are fairly minor.

When looking at this, I don't really consider the OSMF ops. If we specify any reasonable version requirements they'll be able to meet them. As long as we're not requiring frequent reloads, that's not an issue either.

[kocio-pl]

Thanks, so it looks like it will be ad hoc decision, not a general plan. Currently I see no problems that are blocked by database schema.