Thread: How can I know the disk space used by a table?

Hi everybody,
is there any way to have an estimate of the disk space used by a table?
Any system table to query, or something like this?
Using record size * record number is not always possible, if you use
a TEXT field the size used is 4+n, so it's dependent on inserted data.
best regards
Andrea Aime

"Andrea Aime" <aaime@comune.modena.it> writes:
> is there any way to have an estimate of the disk space used by a table?

(1) VACUUM foo;

(2) SELECT relpages * 8 FROM pg_class WHERE relname = 'foo';

This gives you the actual file size in kilobytes.  (The magic number 8
assumes you are using the default BLCKSZ of 8K, else adjust to match.)

You need the VACUUM step to be sure the relpages field is up to date;
it's not updated during normal operations.

            regards, tom lane

I have some code that's doing connection pooling for some servlets. I
turn autoCommit off and let each caller determine when to commit.

Hoever, commit() does not seem to be releasing read locks, causing
my periodic VACUUM task to block.

In the following code, after commit() is called, ps shoes the backend
state as "idle in transaction". If I turn autocommit on, the state is
"idle" Why the difference?

import java.sql.*;

public class ctest {
    public static void main(String argv[]) {
    try {
        String driverName = "org.postgresql.Driver";
        Class.forName(driverName).newInstance();

        Connection con = DriverManager.getConnection(
        "jdbc:postgresql://127.0.0.1/jackson","db","zzzz");

        con.setAutoCommit(false);

        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM s_image");
        rs.next();
        int count = rs.getInt(1);
        rs.close();

        con.commit();

        System.out.println(count);

        Thread.sleep(30000);


    } catch (Exception e) { e.printStackTrace(); }
    }
}

Robert,


VACUUM requires an exclusive lock on a table.  This exclusive lock
includes reads (i.e. selects) as well as writes.  Thus if you have
issued a SELECT statement and not committed after the select, the select
will lock the table and thus prevent the VACUUM from running.  (I know
this because I just went through my code cleaning up this same problem).

As for the difference between 'idle' and 'idle in transaction', if
autocommit if false, then when the commit() method is called it
automatically starts the next transaction with a begin (it essentially
does:  commit; begin;) thus 'idle in transaction' because a new
transaction is started.  With autocommit true, there are no explicit
transactions (i.e. begin calls), thus it is just in an 'idle' state.

thanks,
--Barry


Robert Berger wrote:
> I have some code that's doing connection pooling for some servlets. I
> turn autoCommit off and let each caller determine when to commit.
>
> Hoever, commit() does not seem to be releasing read locks, causing
> my periodic VACUUM task to block.
>
> In the following code, after commit() is called, ps shoes the backend
> state as "idle in transaction". If I turn autocommit on, the state is
> "idle" Why the difference?
>
> import java.sql.*;
>
> public class ctest {
>     public static void main(String argv[]) {
>     try {
>         String driverName = "org.postgresql.Driver";
>         Class.forName(driverName).newInstance();
>
>         Connection con = DriverManager.getConnection(
>         "jdbc:postgresql://127.0.0.1/jackson","db","zzzz");
>
>         con.setAutoCommit(false);
>
>         Statement stmt = con.createStatement();
>
>         ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM s_image");
>         rs.next();
>         int count = rs.getInt(1);
>         rs.close();
>
>         con.commit();
>
>         System.out.println(count);
>
>         Thread.sleep(30000);
>
>
>     } catch (Exception e) { e.printStackTrace(); }
>     }
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>