Skip to content

VPC Subnet insertion query is not efficient #1221

@bnaecker

Description

@bnaecker

In the query for inserting a VPC Subnet, we look for the existence of overlapping IP subnets from other VPC Subnets already in the same VPC. That's done here, with a call to the CockroachDB function inet_contains_or_equals. That is correct, but pretty inefficient if the VPC starts to get a lot of subnets. We're using an index on the vpc_subnet table on the vpc_id column, but we'll still need to do a full scan of that index. That's better than a table scan, but not great.

We could instead store the first and last address of each VPC Subnet's IP subnets (both v4 and v6). A unique index on each of these would be great, and would support fast lookups of overlapping subnets. An existing subnet containing the candidate is one where the first and last addresses are before and after the first address of the candidate, respectively. An existing subnet contained by the candidate is one where the first address is between the candidate's first and last address.

Metadata

Metadata

Assignees

No one assigned

    Labels

    databaseRelated to database accessenhancementNew feature or request.networkingRelated to the networking.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions