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

On Mon, 2017-11-27 at 15:19 +0000, Martin Mueller wrote:

 Apologies if I asked this question before.
Is this normal behavior? 

Yes.

 Where in the postgres documentation do I read up on this?

Start here - https://www.postgresql.org/docs/10/static/admin.html

string functions and regular expressions, but it’s harder to look
under the hood.

I would strongly recommend staying out from under the hood.  PostgreSQL
has really great tools from visualizing what is going on in the
database(s);  use the tools.

--
Meetings Coordinator, Michigan Association of Railroad Passengers
537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010
E-mail: awilliam@whitemice.org GPG#D95ED383 Web: http://www.marp.org

The directory "base" contains filenames(numbers) that correspond to the
oid of the postgreSQL databases. Those databases have subdirectories
that contain the filenames(numbers) that correspond to the oid of objects
(sequences, tables, etc) in those databases.

You may find the attached queries helpul in navigating those directories

On Mon, Nov 27, 2017 at 10:27 AM, Tomas Vondra <tomas.vondra@2ndquadrant.com

wrote:

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

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.