[openstreetmap/openstreetmap-website] Rename database tables (Issue #5619)

[Andy Allan]

For historical reasons, we've ended up with quite a few database tables names that aren't aligned with the model names. I think in every case we prefer the model names (since these are easier to change, they tend to have been changed already without renaming the underlying table).

```bash
andy at denali:~/src/openstreetmap-website$ grep -rn 'self\.table_name' app/models/
app/models/old_way.rb:24:  self.table_name = "ways"
app/models/old_way_tag.rb:16:  self.table_name = "way_tags"
app/models/way_tag.rb:15:  self.table_name = "current_way_tags"
app/models/tracepoint.rb:26:  self.table_name = "gps_points"
app/models/trace.rb:31:  self.table_name = "gpx_files"
app/models/way_node.rb:20:  self.table_name = "current_way_nodes"
app/models/relation_member.rb:21:  self.table_name = "current_relation_members"
app/models/node.rb:31:  self.table_name = "current_nodes"
app/models/way.rb:26:  self.table_name = "current_ways"
app/models/old_way_node.rb:20:  self.table_name = "way_nodes"
app/models/old_relation_tag.rb:16:  self.table_name = "relation_tags"
app/models/old_relation.rb:24:  self.table_name = "relations"
app/models/relation.rb:26:  self.table_name = "current_relations"
app/models/old_relation_member.rb:22:  self.table_name = "relation_members"
app/models/node_tag.rb:15:  self.table_name = "current_node_tags"
app/models/old_node.rb:30:  self.table_name = "nodes"
app/models/relation_tag.rb:15:  self.table_name = "current_relation_tags"
app/models/follow.rb:22:  self.table_name = "friends"
app/models/tracetag.rb:20:  self.table_name = "gpx_file_tags"
app/models/old_node_tag.rb:16:  self.table_name = "node_tags"
```

We might also want to rename certain tables that don't currently have a model, such as `changeset_subscribers` (to e.g. `changeset_subscriptions`) but I haven't made a list of these.

Having these mismatched table/model names doesn't provide any benefits to anyone, as far as I can tell. It also makes it harder for new developers to get started, since they might look at the database structure (e.g. #5308) and then get confused when they can't find the corresponding models. In particular, I found it very confusing when I first started, since we have a table called "nodes" and a "Node" model, but these do not correspond with each other!

So my proposal is to rename the tables to match the model names.

There is a zero-downtime approach to renaming tables in PostgreSQL, as described at https://brandur.org/fragments/postgres-table-rename . In short, we
* Rename the table, and create a view with the old name, in a transaction.
* Update any code that accesses that table (e.g. rails, cgimap, replication utilities) to use the new table name directly.
* Eventually, drop the view.

For a small number of renames, we will need to do this in multiple stages, due to conflicts (e.g. `nodes -> old_nodes` then `current_nodes -> nodes` some time later).

Before getting started on this, my main question is whether the OSMF operations team (e.g. @tomhughes @Firefishy ) see any problem with this, for example, any details about how the OSMF database replication works that would make this a non-starter. Or if there's any reasons that I haven't considered why this might not work.



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

Message ID: <openstreetmap/openstreetmap-website/issues/5619 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20250205/3b8838e6/attachment-0001.htm>