Re: Archiving Data to Another DB? - Mailing list pgsql-general

On 04/11/2018 11:59 AM, Kumar, Virendra wrote:
> Does this apply to SELECT calls as well or only for DMLs.

Easy enough to test:

test=# \d projection

Foreign table "public.projection"
   ...

Server: fdw_test_server
FDW options: (schema_name 'public', table_name 'projection')

test=# select count(*) from projection ;
  count
-------
  28430
(1 row)

With log_statement = 'all':

select * from projection ;

postgres-2018-04-11 13:04:33.871 PDT-0LOG:  statement: select * from 
projection ;
postgres-2018-04-11 13:04:33.872 PDT-0LOG:  statement: START TRANSACTION 
ISOLATION LEVEL REPEATABLE READ
postgres-2018-04-11 13:04:33.872 PDT-0LOG:  execute <unnamed>: DECLARE 
c1 CURSOR FOR
         SELECT line_id, p_item_no, c_id, method, year, qty, sub_method, 
proj_note, item_key, pot_ct, trial, ts_insert, ts_update, user_insert, 
user_update, link_key, v_number FROM public.projection
postgres-2018-04-11 13:04:33.872 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.873 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.874 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.875 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.876 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.877 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.878 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.879 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.880 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.881 PDT-0LOG:  statement: FETCH 100 FROM c1
postgres-2018-04-11 13:04:33.882 PDT-0LOG:  statement: FETCH 100 FROM c1

...

test=# explain analyse select * from projection ;
                                                       QUERY PLAN 

----------------------------------------------------------------------------------------------------------------------
  Foreign Scan on projection  (cost=100.00..115.34 rows=178 width=435) 
(actual time=0.844..163.493 rows=28430 loops=1)
  Planning time: 0.077 ms
  Execution time: 164.735 ms
(3 rows)


> 
> I am planning to use postgres_fdw but if it is going by one row at a 
> time there will be a lot of round trip and defeat the purpose.
> 
> Regards,
> 
> Virendra.
> 
> *From:*Don Seiler [mailto:don@seiler.us]
> *Sent:* Wednesday, April 11, 2018 2:53 PM
> *To:* Adrian Klaver
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: Archiving Data to Another DB?
> 
> On Wed, Apr 11, 2018 at 1:38 PM, Don Seiler <don@seiler.us 
> <mailto:don@seiler.us>> wrote:
> 
> Yeah, I saw the same with a 132 row insert. Now imagine that with a 
> monthly 50 million row insert or delete. :p Thanks for the confirmation!
> 
> I went back to look at the postgres logs on my dev server. These logs 
> are rotated once they hit 2G in size. One typical log from the middle of 
> my test last night hit that in 13 minutes and had over 5.2 million 
> DELETE calls. There a quite a few logs like this. That would not be fun 
> for the disk space on the log volume, either.
> 
> Don.
> 
> -- 
> 
> Don Seiler
> www.seiler.us <http://www.seiler.us>
> 
> 
> ------------------------------------------------------------------------
> 
> This message is intended only for the use of the addressee and may contain
> information that is PRIVILEGED AND CONFIDENTIAL.
> 
> If you are not the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please erase all copies of the message
> and its attachments and notify the sender immediately. Thank you.


-- 
Adrian Klaver
adrian.klaver@aklaver.com