Skip to content

postgres_fdw support for Geo secondary node

Description

Geo secondaries have two postgresql databases - a read-only replica of the current production database, and a read-write database containing node-specific data (records of which files have been synced, etc).

In various places - e.g., https://gitlab.com/gitlab-org/gitlab-ee/issues/3269 but I've seen this pattern elsewhere - we need to perform queries that go across both datasets - projects with a corresponding project registry, etc.

Proposal

We can add the read-only secondary replica to the node-specific read-write database as a Foreign Data Wrapper. This will allow us to do queries in the secondary that make use of both datasets and give us the usual SQL niceties like temporary tables, etc, for otherwise-expensive queries.

I think we should add this support for %10.1 - even if we solve the specific issue in #3269 (closed) another way, this is going to be a recurring problem as we try to make Geo scale for GitLab.com

Ideally the setup would be handled entirely by omnibus?

/cc @stanhu @brodock @yorickpeterse

Links / references

https://robots.thoughtbot.com/postgres-foreign-data-wrapper covers fdw / rails integration quite well

Related issues

  • GeoNodeStatus calculates numbers inefficiently gitlab-org/gitlab-ee#3699
  • Geo repository sync worker attempts to sync repos on unhealthy shards in non-backfill conditions gitlab-org/gitlab-ee#3690
  • Improve GitLab Geo backfill so that it can be managed properly at scale gitlab-org/gitlab-ee#3487

Documentation blurb

Overview

What is it? Why should someone use this feature? What is the underlying (business) problem? How do you use this feature?

Use cases

Who is this for? Provide one or more use cases.

Feature checklist

Make sure these are completed before closing the issue, with a link to the relevant commit.

Edited by Toon Claes