Re: postgres external table

Tom Lane wrote:
> I'm finding it hard to visualize a use-case for that. We must postulate
> that the table is so big that you don't want to import it, and yet you
> don't feel a need to have any index on it. Which among other things
> implies that every query will seqscan the whole table. Where's the
> savings?
>

I've mainly seen it used for data loading where there's some sort of
transformation going on, typically to cleanup junk fields that would
fail a constraint or derive new columns. If you have external tables,
there's no need to load the data into a temporary table if all you're
going to do is modify a few things and then write the result to
somewhere else. Most of these use cases process the whole file anyway,
so having to do a whole scan isn't an issue. I used to run an app that
imported gigabytes a day of text files dumped from another server that
used a weird date format I had to process via pl/pgsql function. Having
to pass them through COPY and then INSERT processed versions to
somewhere else was really a drag, given that there was no use for the
intermediate data.

It also can be handy for bootstrapping apps that are converting stuff
out of a legacy system too. Just make the mainframe/whatever dump a new
text file periodically into where the external table looks for its data,
and you skip having to schedule reloads when the content changes. Can
make your life easier while running the two systems in parallel initially.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com