Re: a question about oddities in the data directory

Hi,

I think you need to read this:

https://www.postgresql.org/docs/current/static/storage-file-layout.html

On 11/27/2017 04:19 PM, Martin Mueller wrote:
> Apologies if I asked this question before.
>
> I’m a newcomer to Postgres, having migrated from Mysql.  Information
> about tables seems harder to get at in Postgres. That data directory
> on my machine is suspiciously large—well over 100 GB.  The directory
> Postgres/var-9.5/base/ contains a number of subdirectories with
> Arabic numerals.

Each of those directories stores data for a single database. Each object
(including databases) has a 32-bit identifier, mapping it to object on
the filesystem.

databases: SELECT oid, datname FROM pg_database;
tables etc.: SELECT relfilenode, relname FROM pg_class;

> Directory 16385 has a subdirectory 17463 with a size of 1.07 GB.
That's not a subdirectory, but a datafile segment.

> But there are also 17 subdirectories with names from 17463.1 to
> 17.463.17.  There are also other entries with similar forms of
> duplication and suspiciously identical file sizes of 1.07GB.
>

Again, those are files, not subdirectories. Large datafiles are split
into 1GB segments, so for example 10GB table with relfilenode 17463 will
be stored in files 17463, 17463.1, 17463.2, ..., 17463.9

> Is this normal behavior?  Where in the postgres documentation do I
> read up on this? Postgres strikes me as superior to MySQl, especially
> with regard to string functions and regular expressions, but it’s
> harder to look under the hood.

https://www.postgresql.org/docs/current/static/storage-file-layout.html

> How, for instance, do I figure out what number corresponds to the
> table that I know as ‘earlyprinttuples
>

SELECT relfilenode FROM pg_class WHERE relname = 'earlyprinttuples';


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services