Re: querying while copying into a table and optimizations

On Fri, Oct 28, 2005 at 06:22:43PM -0000, davidgn(at)servidor(dot)unam(dot)mx wrote:
> This is postgresql 7.4
> I am trying to check that postgres is updating a table.
> I have a pretty large ascii table file (+- 210 Mb) which I am copying into a
> table with pgsql, but it is taking a long time, and any select query I do to the
> table returns me 0 rows

Presumably you're using COPY or doing multiple inserts inside a
transaction (hopefully the former for performance reasons). No
other transaction will be able to see any of the data until the
inserting transaction commits it (a single statement like COPY is
wrapped in its own transaction even if it occurs outside an explicit
transaction block). You could use contrib/pgstattuple to check on
the copy/insert's progress, but that won't allow you to query the
data itself.

> Is there any way to ask postgres to update the data more frequently or some way
> to make it end sooner?

By "update the data more frequently" I assume you mean "make the
data visible to other transactions more frequently." To do that
you could use multiple COPY statements and make sure each is committed
before beginning the next. One way to do that would be to write a
simple client that reads the file and issues a new "COPY tablename
FROM stdin" for each X number of lines. But are you sure you want
other transactions querying the table before it's completely loaded?
Are queries based on incomplete data acceptable for whatever you're
doing?

> I am a bit clueless as to what can I do to the configuration files to optimize
> this copy.

See "Populating a Database" in the "Performance Tips" chapter of
the documentation for some ideas.

http://www.postgresql.org/docs/8.0/interactive/populate.html

--
Michael Fuhr