2

I have many foreign tables imported by IMPORT FOREIGN SCHEMA:

CREATE USER MAPPING FOR myuser
   SERVER postgres
   OPTIONS ( user 'myuser', password 'mypass');
IMPORT FOREIGN SCHEMA public from server postgres INTO public;

I have many queries that join my local tables and foreign tables.

Q1: If I use pg_prewarm and the put the whole table in memory, it helps me not every time to take this table by a network.

Q2: I am worried if data changes on the foreign PostgreSQL server will be visible on my local server if the foreign table is cached.

Example: core_category is a foreign table

SELECT pg_prewarm(
    'core_category',
    -- "pre warm" pages of the last 1000 pages for 'mytable'
    first_block := (
        SELECT pg_relation_size('core_category') / current_setting('block_size')::int4 - 1000
    )
);
-- or
SELECT * FROM pg_prewarm('core_category', 'buffer');
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
madjardi
  • 5,649
  • 2
  • 37
  • 37
  • 1
    I don't understand. Where is the connection between `pg_prewarm` and foreign tables? – Laurenz Albe Oct 16 '18 at 07:40
  • core_category - it's foreign table. if I will put the whole table in a cache, and in the remote server it changed I use old version table? – madjardi Oct 16 '18 at 08:34
  • 1
    You cannot prewarm a foreign table since its data are not in the database. The error is `ERROR: fork "main" does not exist for this relation`. You'd have to make sure that the table is in cache on the foreign data source. – Laurenz Albe Oct 16 '18 at 09:07
  • it much helped, the question is closed – madjardi Oct 16 '18 at 09:22

1 Answers1

6

Using pg_prewarm on a foreign table does not make sense: since the table is not stored in PostgreSQL, PostgreSQL cannot load it into shared buffers or the file system cache.

Indeed, an attempt to do that will result in

ERROR: fork "main" does not exist for this relation

To speed up queries involving a foreign table, you'll have to get the foreign data source to cache the data in memory.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    the same applies when trying to warm up a partitioned table. Instead, try to warm up the target partition(s) – batero Nov 03 '22 at 16:51