0

I have a table with one column in a tablespace in PostgreSQL. This resulted in 3 data files as seen in the image below.

enter image description here

To test where the data stores, I added one row with only one value in my table, and the datafile '49565' went from 0kb to 8kb. I know that data is stored in pages of 8kb each which are inside the data files.

What I need to know is how many rows can fit in the '49565' data file. How do I get this value?

Second question. When I check on the tablespace I created, it shows two objects: the index and the table. Since the index row mentions the number '49565' I thought that this is the data file (1st image) for the index. So, why does the row I added in the table stored in that data file?

enter image description here

Sara Cobos
  • 11
  • 1
  • Rather then repeating the documentation I will point you at it [Storage](https://www.postgresql.org/docs/13/storage-file-layout.html). – Adrian Klaver Jun 08 '21 at 23:47

1 Answers1

0

Only one of these files is the table proper, the others could be the TOAST table and the TOAST index, or they could be indexes.

The size limit of the table's data file is 1GB. If you exceed that, a second segment 49565.1 will be created. So there is no size limit on the table.

How many rows fit into one data file depends on the size of the rows, but it is not a very important question. Best insert a large number of rows with realistic data and measure.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263