From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Martin Mueller <martinmueller(at)northwestern(dot)edu>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: a question about oddities in the data directory |
Date: | 2017-11-27 15:27:31 |
Message-ID: | f4d2a94e-4b66-57c8-d2e1-e055ab536b62@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Tauno Williams | 2017-11-27 15:42:19 | Re: a question about oddities in the data directory |
Previous Message | Martin Mueller | 2017-11-27 15:19:14 | a question about oddities in the data directory |