On Thu, Mar 10, 2011 at 10:31 AM, Dave White <dwh...@companioncorp.com>wrote:
> > We are considering using rowids as a unique and stable ID for all records. > I know that in order to do this, we need to prevent recycling by using > "primary key autoincrement". > > The concern is that over time, as records are added and removed, there will > appear large gaps in the rowids of a table. As I understand it, sqlite uses > bit-arrays internally for selections, and those bit-arrays represent a list > of rowids. If this is the case, then large gaps in rowids, will cause > bit-arrays to expand, and potentially slow down selections. > > 1. Do I understand this correctly? > No. I think you may be confusing SQLite and PostgreSQL. And even PostgreSQL compresses the gaps. > 2. If necessary, I can manually recycle rowids to fill in these > gaps, and maintain a separate UID that will never be re-used. For the sake > of selection performance, is this a better solution? > Best to use an INTEGER PRIMARY KEY as an alias for your ROWID. > 3. Assuming a record isn't removed, can I rely on a rowid to remain > stable? Or can it ever be changed by some event, for example a Vacuum? Do > rowid gaps ever get "compressed"? > You can rely on an INTEGER PRIMARY KEY being stable. > > Thanks > dw > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
