0

I have a schema that looks like this:

create table items (
    id serial primary key
);

create table revisions (
    id serial primary key,
    item_id int not null references items(id),
    name text not null,
    property_a text,
    property_b text,
    property_c text
);

create table deltas (
  id serial primary key,
  item_id int not null references items(id) on delete cascade,
  old_revision_id int not null references revisions(id) on delete cascade,
  new_revision_id int not null references revisions(id) on delete cascade
);

This schema can't be changed.

We need to support the rare operation of deleting from items or revisions with the delete cascading to the deltas table. These deletes rarely happen compared to inserting/selecting data from the deltas table (but at least ~20-30 times a day).

A major problem is that the deltas table is very big so these cascades were very slow (taking > 30 seconds). So, we added separate indexes to item_id, old_revision_id, and new_revision_id which turned the cascading delete into a sub-millisecond operation. Side note: on our actual table, there are other columns and other indexes to support our application's normal query patterns.

This introduced a new problem: the combined size of these indexes for the deltas table is now ~7-8 times the size of the actual table and the rate of growth is pretty high since the deltas table has a lot of writes to it.

It seems silly to use many large indexes to support such a rare operation. We have a couple of options to solve this:

  1. Leave it with multiple indexes and deal with the disk size as a separate problem.
  2. Re-architect the application to avoid doing any deletes for these tables to prevent the cascades from happening in the first place.
  3. Change our indexes.

For #3, I read in the Postgres docs that we might be able to use a multi-column GIN index on (item_id, old_revision_id, new_revision_id) which could support the cascading delete like delete from changeset_deltas where old_revision_id = <deleted_revision_id>:

A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

Am I correct in understanding this and will it make a difference rather than 3 separate indexes or should we take a different approach?

4
  • "This introduced a new problem: the combined size of these indexes for the deltas table is now ~7-8 times the size of the actual table" Is this just a matter of bloat? If you reindex the indexes, how much do they shrink? – jjanes Apr 21 '20 at 17:26
  • When you say 7-8 times the size, is that multiples of the size of the real actual table, or of the stripped down table which you show? – jjanes Apr 21 '20 at 17:30
  • @jjanes if the table data is 1 GB, the indexes add up to about ~6.5 GB (with 3 indexes for cascading deletes + the other indexes on the table). The table is much bigger than 1 GB in reality, just wanted to show the math. – TheCloudlessSky Apr 21 '20 at 19:48
  • Also, reindexing doesn't help as we get roughly the same size of indexes if we drop/recreate them on a copy of this database. – TheCloudlessSky Apr 21 '20 at 19:50
1

The GIN index certainly can be used for the cascading delete (you will need to create extension btree_gin before creating the index).

How much space it will save you is hard to predict, it would depend on how much duplication there is in the values of those columns.

Having 3 separate GIN indexes will also work, and is likely slightly smaller than the one combined index. Each value in a combined index has to "remember" which column it came from, which takes up extra space.

If the btree indexes are bloated, whatever is causing that might cause the gin index to get bloated as well.

2
  • Thanks. I think as long as we can get a smaller size rather than having 3 large btree indexes, that's our intention to using the GIN index. We just need to try it out on a copy of this database to see the disk usage/performance. – TheCloudlessSky Apr 21 '20 at 19:56
  • Just thought I'd let you know that on a copy of the database, we created a multi-column GIN index and it was 50% smaller than one of the btree indexes! So, a major improvement in size. The cascading deletes have the same level of performance as the individual btree indexes too, so we're going to go with this solution. Thanks! – TheCloudlessSky Apr 22 '20 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.