Home • Explore • Blog • CI
☰



postgresql
68th chunk of `doc/src/sgml/ddl.sgml`
94f59924887fae4b8f765240e58a248cb4e255986acc63510000000100000fa3
 </para>

   <para>
    Sub-partitioning can be useful to further divide partitions that are
    expected to become larger than other partitions.
    Another option is to use range partitioning with multiple columns in
    the partition key.
    Either of these can easily lead to excessive numbers of partitions,
    so restraint is advisable.
   </para>

   <para>
    It is important to consider the overhead of partitioning during
    query planning and execution.  The query planner is generally able to
    handle partition hierarchies with up to a few thousand partitions fairly
    well, provided that typical queries allow the query planner to prune all
    but a small number of partitions.  Planning times become longer and memory
    consumption becomes higher when more partitions remain after the planner
    performs partition pruning.  Another
    reason to be concerned about having a large number of partitions is that
    the server's memory consumption may grow significantly over
    time, especially if many sessions touch large numbers of partitions.
    That's because each partition requires its metadata to be loaded into the
    local memory of each session that touches it.
   </para>

   <para>
    With data warehouse type workloads, it can make sense to use a larger
    number of partitions than with an <acronym>OLTP</acronym> type workload.
    Generally, in data warehouses, query planning time is less of a concern as
    the majority of processing time is spent during query execution.  With
    either of these two types of workload, it is important to make the right
    decisions early, as re-partitioning large quantities of data can be
    painfully slow.  Simulations of the intended workload are often beneficial
    for optimizing the partitioning strategy.  Never just assume that more
    partitions are better than fewer partitions, nor vice-versa.
   </para>
  </sect2>

 </sect1>

 <sect1 id="ddl-foreign-data">
  <title>Foreign Data</title>

   <indexterm>
    <primary>foreign data</primary>
   </indexterm>
   <indexterm>
    <primary>foreign table</primary>
   </indexterm>
   <indexterm>
    <primary>user mapping</primary>
   </indexterm>

   <para>
    <productname>PostgreSQL</productname> implements portions of the SQL/MED
    specification, allowing you to access data that resides outside
    PostgreSQL using regular SQL queries.  Such data is referred to as
    <firstterm>foreign data</firstterm>.  (Note that this usage is not to be confused
    with foreign keys, which are a type of constraint within the database.)
   </para>

   <para>
    Foreign data is accessed with help from a
    <firstterm>foreign data wrapper</firstterm>. A foreign data wrapper is a
    library that can communicate with an external data source, hiding the
    details of connecting to the data source and obtaining data from it.
    There are some foreign data wrappers available as <filename>contrib</filename>
    modules; see <xref linkend="contrib"/>.  Other kinds of foreign data
    wrappers might be found as third party products.  If none of the existing
    foreign data wrappers suit your needs, you can write your own; see <xref
    linkend="fdwhandler"/>.
   </para>

   <para>
    To access foreign data, you need to create a <firstterm>foreign server</firstterm>
    object, which defines how to connect to a particular external data source
    according to the set of options used by its supporting foreign data
    wrapper. Then you need to create one or more <firstterm>foreign
    tables</firstterm>, which define the structure of the remote data. A
    foreign table can be used in queries just like a normal table, but a
    foreign table has no storage in the PostgreSQL server.  Whenever it is
    used, <productname>PostgreSQL</productname> asks the foreign data wrapper
    to fetch data from the external source, or transmit data to the external
    source in the case of update commands.
   </para>

   <para>
    Accessing remote
Title: Declarative Partitioning Overhead and Foreign Data Introduction
Summary
This section discusses the overhead of partitioning during query planning and execution, noting the planner's ability to handle hierarchies with a few thousand partitions when pruning is effective. It cautions that longer planning times and higher memory consumption arise with more remaining partitions. The section advises that data warehouse workloads may benefit from more partitions than OLTP workloads. The text transitions to introducing foreign data access in PostgreSQL, which allows accessing data outside PostgreSQL using regular SQL queries via foreign data wrappers. It explains the need for foreign servers and tables to define connections and structures for remote data access, which triggers data fetching or transmission by the foreign data wrapper during queries or updates.