Database Reference
In-Depth Information
The Postgres optimizer uses the relpages and the reltuples values to calculate the average
rows per block, also known as the average tuple density.
If we assume that the average tuple density remains constant over time, then we can
calculate the number of rows by using the following formula:
Row Estimate = Number of Data Blocks * Rows per Block
We put in a little code to handle the case that the reltuples or relpages fields are zero.
The Postgres optimizer actually works a little harder than we do in that case, so our estimate
isn't a very good one.
There's more...
The good thing about the aforementioned recipe is that it returns a value in about the same
time no matter how big the table is. The bad thing about it is that pg_relation_size()
requests a lock on the table, and so if any other user has an AccessExclusiveLock on the
table, then the table size estimate will wait for the lock to be released before returning a value.
Err... so what is an AccessExclusiveLock? When an SQL maintenance action, such as changing
the datatype of a column, PostgreSQL will lock out all other actions on that table. The typical
case for me is where I issue some form of SQL maintenance action, such as an ALTER TABLE,
and then the statement takes much longer than I thought it would. At that point, I think, "Oh,
was that table bigger than I thought? How long will I be waiting?". Yes, it's better to calculate
that beforehand, but hindsight doesn't get you out of the hole you are in right now. So we need
a way to calculate the size of a table without needing the lock.
So my solution is to look at the operating system files that Postgres uses to store data,
and figure out how large they are.
Now, this can get somewhat difficult. If the table is locked, it's probably doing something to
the table, and so trying to look at the files might well be fruitless, or might give wrong answers.
Anyway, here goes:
First, get some details on the table from pg_class
SELECT reltablespace, relfilenode FROM pg_class
WHERE oid = 'orders'::regclass;
Second, confirm the databaseid in which the table resides
SELECT oid as databaseid FROM pg_database
WHERE datname = current_database();
Together reltablespace , databaseid , and relfilenode are the three things we need
to locate the underlying data files within the data directory.
If tablespaceid is zero, then the files will be
$PGDATADIR/base/{databaseid}/{relfilenode}*
 
Search WWH ::




Custom Search