CODEWITHSUNDEEP
postgresqlpostgres-fdw
madjardi
madjardi

Reputation: 5949

Use PostgreSQL pg_prewarm for a foreign table

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');

Upvotes: 2

Views: 1302

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

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.

Upvotes: 6

Related Questions