Migrate URLs with old primary keys to new primary keys

Question

We have a database which we obtained via a crawling, where every row contains a primary key X, and an unique number Y obtained from the crawling procedure (the primary key of the original source). For historical reasons, we are using X to create the URLs of each object (e.g. http://example.com/object/X).

This database was in Mysql, and we are migrating the data to Postgres. To that end, we decided to crawl the original source from scratch (for other reasons considered) to the new database. This means that we now have a new primary key Z associated with the unique numbers Y.

The problem is that if we migrate the database on production, the links become http://example.com/object/Z, which point to a different object than http://example.com/object/X (since X!=Z => different Y value).

Naturally we don't want this to happen since 1. search engines will point to the wrong object and 2. old links pointing to object X1->Y1, start point to object Z1->Y2 != Y1.

This problem already suggests that we want URLs constructed from Y. The question now is how to make this migration.

I see two approaches to the problem:

1. using the old database, create a redirect of the link to a link that only depends on Y http://example.com/object/X -> http://example.com/object1/Y, wait 1 month, and then migrate the database along with a change in the URLs to the ones using Y.

2. create a new column in the new database old_pk, and perform a migration from the old database to the new database, by inserting the old X to the row with equal Y, and then to http://example.com/object/X -> http://example.com/object_/Y (to avoid these problems in the future)

The 1. has the disadvantage that the whole production has to wait for the links to migrate, and will not maintain old URLs for eternity. 2 has the disadvantage it will take time to migrate since the database contains +600.000 entries, and the new database will contain legacy data. I'm more inclined to 2., but I would like a second opinion:

1. does this reasoning makes sense?
2. are there other approaches that we are not seeing?

Answer 1

The 'easiest' solution I can currently think of would be to also migrate the key Y from the old database and continue using it for the URL. You'd then have to add some y++ for every new entry. Maybe that's not the most elegant approach, but probably less clumsy than writing 600.000 301-redirects – which would have been my second suggestion.

– Edit–

According to Matt Cutts there is no limit to the number of 301-redirects (https://www.youtube.com/watch?v=r1lVPrYoBkA). So if you're technically able to actually generate and handle those hundrets of thousands of 301s, then you should probably go for that option. At the same time you could then even also start using seo-friendly urls…