Thread: Learning to hack Postgres - Keeping track of ctids
Hi, I'm working on an idea to implement a graph database in Postgres. At the moment its just a learning exercise. What I'd like to do is store a reference to all the links from one record using an array type that stores links to all related tables. At first, I've succeeded in doing this using primary key Ids and this works fine. However, I'd like to be able to bypass the index lookup altogether by storing the ctids in my array instead of the primary key ids. Trouble of course is that ctids can get changed (like for instance vacuuming). So my question is: how can I keep my ctid references up to date - is there any way to detect when a ctid is changed? -- View this message in context: http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On 30 September 2016 at 04:15, Emrul <emrul@emrul.com> wrote: > Hi, > > I'm working on an idea to implement a graph database in Postgres. At the > moment its just a learning exercise. > > What I'd like to do is store a reference to all the links from one record > using an array type that stores links to all related tables. > > At first, I've succeeded in doing this using primary key Ids and this works > fine. However, I'd like to be able to bypass the index lookup altogether by > storing the ctids in my array instead of the primary key ids. > > Trouble of course is that ctids can get changed (like for instance > vacuuming). So my question is: how can I keep my ctid references up to date > - is there any way to detect when a ctid is changed? I expect that you'd have to teach the heapam code, vacuum, etc to do the required housekeeping. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Thanks Craig, I will look at that code. The alternative I'm considering is whether I can define my own index type on the column storing my array of primary key ids. The index would, for each primary key id just provide a reference to the ctids of the related records. In theory, I think this might be less work. However, I am yet to find a simple/bare index implementation that I can build upon. -- View this message in context: http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5923757.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
<p dir="ltr"><p dir="ltr">On 30 Sep. 2016 19:36, "Emrul" <<a href="mailto:emrul@emrul.com">emrul@emrul.com</a>> wrote:<br/> ><br /> > Thanks Craig, I will look at that code.<br /> ><br /> > The alternative I'm consideringis whether I can define my own index type on<br /> > the column storing my array of primary key ids. The indexwould, for each<br /> > primary key id just provide a reference to the ctids of the related records.<br /> ><br/> > In theory, I think this might be less work. However, I am yet to find a<br /> > simple/bare index implementationthat I can build upon.<p dir="ltr">I don't think indexes are fully pluggable yet. And I'd be surprised if itwere possible within GiST /GIN. Check out the 9.6 / v10 pg_am changes, which I think may be relevant.<p dir="ltr">Or itcould be an unrelated dead end because I know little about indexing.<p dir="ltr">><br /> ><br /> ><br /> >--<br /> > View this message in context: <a href="http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5923757.html">http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5923757.html</a><br />> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.<br /> ><br /> ><br /> > --<br /> >Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> > To make changes to your subscription:<br/> > <a href="http://www.postgresql.org/mailpref/pgsql-hackers">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/>
On Thu, Sep 29, 2016 at 4:15 PM, Emrul <emrul@emrul.com> wrote: > What I'd like to do is store a reference to all the links from one record > using an array type that stores links to all related tables. > > At first, I've succeeded in doing this using primary key Ids and this works > fine. However, I'd like to be able to bypass the index lookup altogether by > storing the ctids in my array instead of the primary key ids. I suspect you're going to find that this is very difficult and doesn't actually make anything any better than just using the primary key IDs. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
I suspect you're right. I've looked at the code and it will be very difficult (especially if I want to do it as an extension rather than patching Postgres) and with all the stuff I'd need to do to make it work you're also right that it probably won't improve upon just using primary key Ids. I've scrapped any further exploration; too hard and no real benefit. -- View this message in context: http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5924248.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Mon, Oct 3, 2016 at 4:30 PM, Emrul <emrul@emrul.com> wrote: > I suspect you're right. I've looked at the code and it will be very difficult > (especially if I want to do it as an extension rather than patching > Postgres) and with all the stuff I'd need to do to make it work you're also > right that it probably won't improve upon just using primary key Ids. Fortunately, primary keys are awesome, and are designed for exactly the problem you're trying to solve, so that's OK. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company