Estimate Oracle tablespace required before loading data

pg_default and pg_global locations are "hardcoded".

pg_default lives in:

select setting||'/base' from pg_settings where name='data_directory';

and pg_global lives in:

select setting||'/global' from pg_settings where name='data_directory';

src/backend/commands/tablespace.c says so:

 * There are two tablespaces created at initdb time: pg_global (for shared
 * tables) and pg_default (for everything else).  For backwards compatibility
 * and to remain functional on platforms without symlinks, these tablespaces
 * are accessed specially: they are respectively
 *          $PGDATA/global/relfilenode
 *          $PGDATA/base/dboid/relfilenode

Please also note, that exposing location of data directory is a - not so terrible but still - security hole.

app01@postgres=> show data_directory;
ERROR:  must be superuser to examine "data_directory"

Assuming that you did your homework correctly and these tables listed are indeed the way space is used (ie. there is no other table consuming the space) then what you have now is state and you ask about history. The state of the database now is a 16GB file with ~6Gb accounted for and 10Gb unexplained. How did it end up in this state? we cannot answer. There are a number of avenues that could lead up to this:

• the database could had been created with a 16Gb data file size.
• there could had been one or more big tables once that now are dropped
• some operation needed temporary space and could had grown the database to 16Gb size and leave it so

You could consult the history captured in the default administrative trace and in the ERRORLOG and see if some of the info captured can shed light on the mystery.

If I'd venture a guess I would say you have a maintenance task of some sort, perhaps a table rebuild, that runs periodically. Rebuild operations require a great amount of data space as all the data exist for a while duplicated (old index being rebuild and new index being created) and, when the default OFFLINE option is used, they require a similarly great amount of log space as the entire operation is a single transaction. A log of 6Gb, data of approximately 6Gb size and a DB with +10Gb space would point toward that ~6Gb data being rebuild, maybe once manually, or maybe periodically by an index rebuild maintenance task.

Now about your real question: is there a better way to do this? Not an easy one. Such pattern of high churn with large range deletes is indeed very unhealthy. If you can prove that it causes real problems (sorry, but a 16Gb data file is not really a problem, and fragmentation is more often than not a red herring) then there are solutions, the most effective being using a partitioning sliding window for efficient deallocation via switch-out+truncate (The link contains the exact how-to). This is a heavy impact change that would ripple everywhere, including the application code, so do consider all the aspects. See How To Decide if You Should Use Table Partitioning.