Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize).

I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?

share|improve this question
I also want to know. – francs Sep 27 '13 at 7:13

There does not seem to be a generic, supported method, but there are some tricks that may be used in limited contexts to evaluate the progress of an individual query. Here are some of them.


When a SELECT or UPDATE query includes any nextval(sequence_name), or an INSERT has a destination column with a nextval as default, the current sequence value can be repeatedly queried in another session with SELECT sequence_name.last_value. It works because sequences are not bounded by transactions. When the execution plan is such that the sequence is incremented linearly during the query, it can be used as a progress indicator.


The pgstattuple contrib module provides functions that can peek directly at the data pages. It appears that when tuples are inserted into an empty table and not yet committed, they are counted in the dead_tuple_count field from the pgstattuple function.

Demo with 9.1: create an empty table

CREATE TABLE tt AS (n numeric);

Let's insert 10M rows into it:

INSERT INTO tt SELECT * FROM random() from generate_series(1,10000000);

In another session, check pgstattuple every second during the insert:

$ while true;
   do psql -Atc "select dead_tuple_count from pgstattuple('tt')";
   sleep 1;



It falls back to 0 when the insert is finished (all the tuples become visible and live).

This trick may also be used when the table is not freshly created, but the initial dead_tuple_count is likely to have a non-zero value and it may also change concurrently if other write activity such as autovacuum is going on (presumably? Not sure what level of concurrency to expect with autovacuum).

However it can't be used if the table is created by the statement itself (CREATE TABLE ... AS SELECT or SELECT * INTO newtable), since the creation is transactioned. The workaround would be to create the table with no rows (add LIMIT 0) and populate it in the next transaction.

Note that pgstattuple doesn't come free: it scans the entire table at every call. Also it's limited to superusers.

Custom counter

In Pavel Stehule's blog, he provides a counter function implemented in C that raises NOTICEs at specified numbers of executions. You have to combine the function with the query somehow to let the executor call it. Notices are sent during the query and they don't need a separate session, only a SQL client that displays them (psql being the obvious candidate).

Example of INSERT INTO reworked to raise notices:

/* transformation */
INSERT INTO destination_table
   SELECT (r).*
  FROM (SELECT counter(to_destination_table(_source), 1000, true) r
           FROM source _source) x

Related question on stackoverflow, for functions:
How to report progress from long-running PostgreSQL function to client

share|improve this answer

As Daniel Vérité mentioned there doesn't seem to be a generic solution. When loading data into a table from a file the following technique can be used to get the progress of the load.

COPY command console progress bar

Create an empty table.

CREATE TABLE mytest (n int);

Create a data file with 10 million lines for loading into the table.

$ seq 10000000 > /tmp/data.txt

Load data from file into the table and display a progress bar.

$ pv /tmp/data.txt | psql -c "COPY mytest FROM STDIN;"


enter image description here

How this works

By using the copy commands STDIN option we can feed in the data for the copy operation from another process. The pv command will output a file and track it's progress displaying a progress bar, ETA, total time elapsed and the rate of data transfer.

COPY command graphical progress bar

Using the same general technique we could display a progress bar in a graphical application or a web-based application. Using python for example the psycopg2 module lets you call the copy command with a file object of your choosing. You could then track how much of your file object has been read and display a progress bar.

share|improve this answer
I'd not come across the pv command before, and it wasn't installed on my Debian server by default, but it's in the repo. The description says "pv (Pipe Viewer) can be inserted into any normal pipeline between two processes to give a visual indication of how quickly data is passing through". A very useful command! – Richard Turner Jan 22 at 9:45

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.