Can i have different types of _id field?
97 views
Skip to first unread message
Leon Pajak
Apr 10, 2014, 4:47:14 AM4/10/14
to mongod...@googlegroups.com
Hi,
I am in the middle of moving my table from PostgreSQL to collection in MongoDB.
In PostgreSQL's table my id field is integer.
In MongoDB _id field will be ObjectId,
The moving process will last few weeks ( i need to change a lot of code). Right now i am inserting data to PostgreSQL (every new row gets autogenerated integer ID) and i am moving new rows to MongoDB collection. When i stop use PostgreSQL and start to insert data direcly to MongoDB the autogenerated _id will be ObjectID.
My question is what type should i set for MongoDB _id when i copying data from PostgreSQL?
Should it be:
a) _id : integer copied from PostgreSQL integer
b) _id : ObjectId generated from PostgreSQL integer id
c) no matter, it is possible to have different types of _id field values
I am in the middle of moving my table from PostgreSQL to collection in MongoDB.
In PostgreSQL's table my id field is integer.
In MongoDB _id field will be ObjectId,
The moving process will last few weeks ( i need to change a lot of code). Right now i am inserting data to PostgreSQL (every new row gets autogenerated integer ID) and i am moving new rows to MongoDB collection. When i stop use PostgreSQL and start to insert data direcly to MongoDB the autogenerated _id will be ObjectID.
My question is what type should i set for MongoDB _id when i copying data from PostgreSQL?
Should it be:
a) _id : integer copied from PostgreSQL integer
b) _id : ObjectId generated from PostgreSQL integer id
c) no matter, it is possible to have different types of _id field values
s.molinari
Apr 10, 2014, 8:22:56 AM4/10/14
to mongod...@googlegroups.com
Disclaimer: I am also learning Mongo.
But from what I have learned......
....as long as the Postgre id field is definitely unique among the documents you are saving that field in, you can use it for the _id field in Mongo. The value in the field doesn't matter. It simply must be unique in each document. If it weren't, you'd get an error from Mongo anyway, once you tried to store a duplicate id.
But STOP!!! The answer doesn't end there.
There are other possible concerns you'll need to think about with implementing incrementing integers as _ids.
1. The _id field can never be changed. Not an issue in your case, but still good to know.
2. You'll have to insure the id continues to increment in your application, as Mongo doesn't support an auto-incrementing _id field (or any auto-incrementing field for that matter). Some info on that here. http://docs.mongodb.org/manual/tutorial/create-an-auto-incrementing-field/
3. Your collection isn't ready for distributed computing/ data. I believe using an incrementing integer _id field is going to possibly cause issues with sharding too, should you start to get a lot of data in any collection with that kind of id. But for sure, as you can see from the link above, incrementing the _id field means at least one extra call to a "centralized" counter collection for the next increment. This would basically kill advantages of having a distributed system (both for your web application and for the database).
There are ways to decentralize such a counter collection too, like with using number batches, but that just makes everything for something as simple as an id even more complicated.
So, my tip would be, do some logic to use mongo's built in OID and then re-relate your data accordingly. Or, if you need the keys for historical purposes, do my first suggestion and also save the old Postgre ids in another field or in a "relation" collection. It might slow down your migration process, but it will help benefit your use of Mongo in the future.
If I may ask, why are you migrating away from PostgreSQL?
Scott
p.s. looking forward to the experts either shooting my answer down or approving it or something in between. It is like taking a test. Hahaha....lol! But a great way to learn.
But from what I have learned......
....as long as the Postgre id field is definitely unique among the documents you are saving that field in, you can use it for the _id field in Mongo. The value in the field doesn't matter. It simply must be unique in each document. If it weren't, you'd get an error from Mongo anyway, once you tried to store a duplicate id.
But STOP!!! The answer doesn't end there.
There are other possible concerns you'll need to think about with implementing incrementing integers as _ids.
1. The _id field can never be changed. Not an issue in your case, but still good to know.
2. You'll have to insure the id continues to increment in your application, as Mongo doesn't support an auto-incrementing _id field (or any auto-incrementing field for that matter). Some info on that here. http://docs.mongodb.org/manual/tutorial/create-an-auto-incrementing-field/
3. Your collection isn't ready for distributed computing/ data. I believe using an incrementing integer _id field is going to possibly cause issues with sharding too, should you start to get a lot of data in any collection with that kind of id. But for sure, as you can see from the link above, incrementing the _id field means at least one extra call to a "centralized" counter collection for the next increment. This would basically kill advantages of having a distributed system (both for your web application and for the database).
There are ways to decentralize such a counter collection too, like with using number batches, but that just makes everything for something as simple as an id even more complicated.
So, my tip would be, do some logic to use mongo's built in OID and then re-relate your data accordingly. Or, if you need the keys for historical purposes, do my first suggestion and also save the old Postgre ids in another field or in a "relation" collection. It might slow down your migration process, but it will help benefit your use of Mongo in the future.
If I may ask, why are you migrating away from PostgreSQL?
Scott
p.s. looking forward to the experts either shooting my answer down or approving it or something in between. It is like taking a test. Hahaha....lol! But a great way to learn.
Russell Bateman
Apr 10, 2014, 8:58:04 AM4/10/14
to mongodb-user
Scott and Leon,
This is a pretty thorough answer. I have used the solution of simply creating an oid field separate from _id (leaving MongoDB's _id alone to do its job) in collections that were the target of just such a migration (from Oracle in my case) for a long time and it works fine. As long as you create an additional index including it, your queries for data based on that field don't suffer in any way.
MongoDB just rocks.
Cheers,This is a pretty thorough answer. I have used the solution of simply creating an oid field separate from _id (leaving MongoDB's _id alone to do its job) in collections that were the target of just such a migration (from Oracle in my case) for a long time and it works fine. As long as you create an additional index including it, your queries for data based on that field don't suffer in any way.
MongoDB just rocks.
Russ
--To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/db5fc499-eea6-4a23-9d36-36c3e4c12cc8%40googlegroups.com.
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
Leon Pajak
Apr 13, 2014, 3:52:02 PM4/13/14
to mongod...@googlegroups.com
Thx, for you advices. It helped me. I am very close to prepare my finnal schema.
Scott - i am moving from PostgreSQL to Mongo, because i have to much data in one table in PgSQL. I need to shard it.
Scott - i am moving from PostgreSQL to Mongo, because i have to much data in one table in PgSQL. I need to shard it.
s.molinari
Apr 14, 2014, 3:05:09 AM4/14/14
to mongod...@googlegroups.com
Ahhh....ok. Interesting. Then you definitely want to avoid using an auto-incrementing _id field, if scaling is the issue.
Scott
Reply all
Reply to author
Forward
0 new messages