Postgres Pro
Downloads
Home   >   mailing lists

Thread: DB Files

DB Files

From
Andy Hartman
Date:
I created a  new table (V16) and then used SimplySql to take data from mssql to the new Postgres table. The table is 212gig in size. Myquestion comes from the files created on the OS(Windows2022 server) I can see lots of files with the last being:

2474695.143

They are all 1,048,576kb 

Is this normal behaviour and could I have done something to use fewer files and larger ones?


This table is created in a separate tablespace on a dedicated drive on the windows file system.

 I'm just getting involved in this PostgreSql instance 

THanks.

Re: DB Files

From
Adrian Klaver
Date:
On 11/15/24 06:27, Andy Hartman wrote:
> I created a  new table (V16) and then used SimplySql to take data from 
> mssql to the new Postgres table. The table is 212gig in size. Myquestion 
> comes from the files created on the OS(Windows2022 server) I can see 
> lots of files with the last being:
> 
> 2474695.143
> 
> They are all 1,048,576kb
> 
> Is this normal behaviour and could I have done something to use fewer 
> files and larger ones?

Read:

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

[...]

"When a table or index exceeds 1 GB, it is divided into gigabyte-sized 
segments. The first segment's file name is the same as the filenode; 
subsequent segments are named filenode.1, filenode.2, etc. This 
arrangement avoids problems on platforms that have file size 
limitations. (Actually, 1 GB is just the default segment size. The 
segment size can be adjusted using the configuration option 
--with-segsize when building PostgreSQL.) In principle, free space map 
and visibility map forks could require multiple segments as well, though 
this is unlikely to happen in practice."

[...]

> 
> 
> This table is created in a separate tablespace on a dedicated drive on 
> the windows file system.
> 
>   I'm just getting involved in this PostgreSql instance
> 
> THanks.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: DB Files

From
Torsten Förtsch
Date:
PG normally splits table data into 1GB chunks. The number before the dot is called the filenode. You can translate it into a table name by

select oid::regclass::text from pg_class where relfilenode='2474695';

I believe there is an option to change that chunk size but you'd have to recompile Postgres. The setting you need to change is called segment_size.


On Fri, Nov 15, 2024 at 3:27 PM Andy Hartman <hartman60home@gmail.com> wrote:
I created a  new table (V16) and then used SimplySql to take data from mssql to the new Postgres table. The table is 212gig in size. Myquestion comes from the files created on the OS(Windows2022 server) I can see lots of files with the last being:

2474695.143

They are all 1,048,576kb 

Is this normal behaviour and could I have done something to use fewer files and larger ones?


This table is created in a separate tablespace on a dedicated drive on the windows file system.

 I'm just getting involved in this PostgreSql instance 

THanks.

Re: DB Files

From
Andy Hartman
Date:
Thanks... I just found that myself... so normal behavior then...

On Fri, Nov 15, 2024 at 10:47 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/15/24 06:27, Andy Hartman wrote:
> I created a  new table (V16) and then used SimplySql to take data from
> mssql to the new Postgres table. The table is 212gig in size. Myquestion
> comes from the files created on the OS(Windows2022 server) I can see
> lots of files with the last being:
>
> 2474695.143
>
> They are all 1,048,576kb
>
> Is this normal behaviour and could I have done something to use fewer
> files and larger ones?

Read:

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

[...]

"When a table or index exceeds 1 GB, it is divided into gigabyte-sized
segments. The first segment's file name is the same as the filenode;
subsequent segments are named filenode.1, filenode.2, etc. This
arrangement avoids problems on platforms that have file size
limitations. (Actually, 1 GB is just the default segment size. The
segment size can be adjusted using the configuration option
--with-segsize when building PostgreSQL.) In principle, free space map
and visibility map forks could require multiple segments as well, though
this is unlikely to happen in practice."

[...]

>
>
> This table is created in a separate tablespace on a dedicated drive on
> the windows file system.
>
>   I'm just getting involved in this PostgreSql instance
>
> THanks.

--
Adrian Klaver
adrian.klaver@aklaver.com