PostGreSQL datafile

[neiljo10]

I am new to PostgreSQL and wish to know how the datafiles are managed and how do they append more files when a file hits limit. 

[slonkar · Accepted solution]

Hi @neiljo10,

The data file size limit in postgres is 1GB, once it reaches this size, it creates a new files with names as <oid>.1, <oid>.2.

The data files are stored in the <data_directory>/base/<database_oid>/<table_oid> location, so if the limit execeeds, the files will be like :

<data_directory>/base/<database_oid>/<table_oid>

<data_directory>/base/<database_oid>/<table_oid>.1

<data_directory>/base/<database_oid>/<table_oid>.2 

...

..

.. so on

Please let us know if you have any queries on this.

Regards,

Sudhir

[neiljo10]

So does the appending happens automatically or any command is required for it ?

Is there any size limit to a database or table ?

Is there anyway we can extend this limit from 1GB ?

@slonkar 

[slonkar]

Hi @neiljo10,

Please refer answers to your queries :

1) So does the appending happens automatically or any command is required for it ?

- It happens automatically, postgres takes care of it internally.

2) Is there any size limit to a database or table ?

- The Databse size is unlimited and tables size is 32TB maximum. However, these are subjected disk space, memory, refer below link :

https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database...

3) Is there anyway we can extend this limit from 1GB ?

- You can extend the data files size only at the building of the Postgres, i.e. building/installing it from the source code, using --withsegsize option, refer below link :

https://postgrespro.com/docs/postgresql/9.5/install-procedure

Regards,

Sudhir