Share via

I’m looking for a management-level view on using postgres_fdw with Azure Database for PostgreSQL – Flexible Server, specifically for a “one server / many databases” scenario.

Jerry Kimball 0 Reputation points
2026-04-30T20:06:04.01+00:00

Could you help with a brief explanation of:

What postgres_fdw is in this context (at a high level)?

Whether enabling/configuring it is fully supported by Microsoft on Azure Flexible Server.

The main pros and cons of using postgres_fdw for cross-database access on a single server.

Any recommended or advised usage patterns (or anti-patterns) we should be aware of.

Azure Database for PostgreSQL

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Manoj Kumar Boyini 16,815 Reputation points Microsoft External Staff Moderator
    2026-04-30T22:58:53.7066667+00:00

    Hi @Jerry Kimball

    postgres_fdw is a PostgreSQL extension that allows one database to query tables in another database by defining them as foreign tables. Even when both databases are on the same Flexible Server, PostgreSQL treats them as isolated units, so the interaction happens through a separate connection and behaves like remote access, rather than a native cross-database join.

    This extension is supported on Azure Flexible Server and can be enabled using CREATE EXTENSION postgres_fdw; with appropriate permissions. From a platform perspective, Microsoft supports the availability of the extension and basic connectivity. However, performance characteristics and query behavior depend on PostgreSQL execution and how the queries are designed.

    In a “one server / many databases” setup, cross-database queries will still involve connection overhead and data movement during execution. While PostgreSQL can push down certain operations (such as filters and some joins), the effectiveness depends on the query structure, so performance may vary.

    In practice, postgres_fdw works best for read-heavy scenarios such as reporting, aggregations, or accessing smaller reference datasets across databases. It can simplify occasional cross-database access without requiring data duplication.

    At the same time, there are trade-offs to consider. Since this is not a true local operation, large joins or frequent queries across databases can introduce noticeable overhead. There is also additional operational complexity around managing foreign servers, user mappings, and permissions, and schema changes in one database can impact dependent queries in another.

    For more demanding or large-scale workloads, it is generally recommended to consider alternatives such as consolidating into a single database with schemas, or using data movement approaches for more predictable performance.

    For more details, please refer to:
    https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-extensions https://www.postgresql.org/docs/current/postgres-fdw.html

    Please let us know if you have any questions and concerns.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

  2. AI answer

    2026-04-30T20:06:20.4366667+00:00

    In this scenario, postgres_fdw is a PostgreSQL extension that lets one Azure Database for PostgreSQL flexible server instance query data that physically resides in another PostgreSQL server or database as if it were local tables. It effectively turns the server into a federated database engine: local “foreign tables” act as proxies to remote tables, and queries against those foreign tables are transparently pushed to the remote database.

    On Azure Database for PostgreSQL – Flexible Server, postgres_fdw is a supported extension. The service supports both incoming and outgoing connections to any PostgreSQL server, including other databases on the same flexible server instance, provided that networking allows it. The documentation explicitly calls out postgres_fdw as supported and gives networking guidance for using it between flexible servers.

    Main pros for cross-database access on a single server:

    • Unified query surface: Applications or reporting tools can run a single SQL query that joins data across databases without implementing cross-database logic in the application layer.
    • Federated architecture: Enables a “hub” database that can aggregate or report on data from multiple tenant or workload databases while keeping each database logically separate.
    • Reuse of PostgreSQL capabilities: Uses native PostgreSQL features and query planning, so no extra middleware layer is required.

    Main cons and trade-offs:

    • Performance overhead: Every remote access involves network and protocol overhead, even when databases are on the same server instance. Poorly designed cross-database joins can become a bottleneck.
    • Operational complexity: Requires managing foreign servers, user mappings, and permissions. Troubleshooting performance or failures can be more complex than with purely local tables.
    • Dependency coupling: The “consumer” database becomes dependent on the availability and schema stability of the “provider” databases. Changes in remote schemas must be coordinated.

    Recommended usage patterns and cautions for a one-server/many-databases design:

    • Use for targeted, read-heavy scenarios: Reporting, reference-data lookups, or administrative queries across databases are good candidates. Keep cross-database write operations limited and well-controlled.
    • Combine with network best practices: When using postgres_fdw across servers, the guidance is to deploy with virtual network integration so servers can communicate securely and predictably. Even on a single server, the same principle of controlled connectivity applies when extending to other servers later.
    • Avoid over-centralizing all logic through FDW: For high-scale multitenant systems, the broader guidance is to choose deployment modes and patterns (for example, sharding or Azure Cosmos DB for PostgreSQL) that are designed for scale, rather than relying heavily on cross-database queries for core transaction paths.
    • Plan for security and isolation: Treat foreign tables as an extension of the remote database’s security surface. Ensure that roles and row-level security (if used) are designed so that cross-database access does not weaken tenant isolation.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.