How postgreSQL handle the multiple concurrent requests to foreign tables?
If two data consumers want to access the same foreign table, do they have to wait and execute the query sequentially, or concurrency of queries is supported?
The following answer is mostly for the foreign data wrapper for PostgreSQL, postgres_fdw.
If you need information about other foreign data wrappers, that will vary with the implementation of the foreign data wrapper and the capabilities of the underlying data store. For example, to have concurrent (read) requests with file_fdw, you need a file system that allows two processes to open the file for reading simultaneously.
Concurrency of queries against the same foreign table is just like for local tables. It is the remote server that handles the SQL statements, locks modified rows until the transaction finishes, and similar.
So there can be arbitrarily many concurrent readers, and readers won't block writers and vice versa.
If you run UPDATEs or DELETEs with WHERE conditions than cannot be pushed down to the foreign server (check the execution plan), it can happen that you have more locks than when using a local table.
Imagine a query like this:
UPDATE remote_tab SET col = 0 WHERE <complicated condition that is true for only one row>;
On a local table, this would only lock a single row.
If the condition is too complicated to be pushed down to the foreign server, postgres_fdw will first run a query like this:
SELECT ctid, col FROM remote_tab FOR UPDATE;
That will retrieve and lock all rows of the table.
Then the WHERE condition will be applied locally, and the resulting row is updated on the foreign server:
UPDATE remote_tab SET col = 0 WHERE ctid = ...;
So in this case, concurrency and performance can suffer quite a lot.