Paven,
SQL Server (derived from Sybase) and Oracle where designed in a time when databases used RAW devices directly thus did
notuse a filesystem. In contrast Postgres was designed to work with a filesystem; thus, a file only represents a
singledatabase object. There is no fragmentation in the likes of Oracle/Sybase/MSSQL where database objects of varying
extentsizes are interwoven within the same file. These database systems then need to identify the free space, coalesce
it,and return it to the free heap map — resulting in varying sizes of free space chunks. Those systems basically had
toreimplement all the features of a filesystem.
In Postgres you have free space in pages which also includes dead tuples on a page and completely free pages — this
representsthe bloat and the script provided on the wiki will give you an estimate of its size. You can also use
pgstattuplefrom contrib to get the information.
For deep inspection you can use pageinspect and pg_buffercache from contrib to view the internals workings… but you
probabledon’t want to start poking around the buffer cache on a live system.
I really think that you need to start talking with your storage vendor and figure out what is actually happening there.
As Mark pointed out; Postgres uses COW semantics to support MVCC; and you’d be surprised to know that some enterprise
storagevendors also use COW semantics in their storage system making fragmentation really hard to quantify with all the
layers. Your storage is a chorus of layers; the internal storage of Postgres (COW/MVCC), the filesystem used?, volume
management?,storage devices?, etc. — fragmentation happens at levels and there are tools there to assess/correct it
there.
Note that DBCC was never a selling point and neither is fsck; the fact that those tools are needed is a problem.
I think you also need to address your segmentation faults issue as that should not be occurring and is unacceptable.
Choose your filesystem and storage system wisely…
> On Jun 12, 2018, at 2:09 AM, pavan95 <pavan.postgresdba@gmail.com> wrote:
>
> Hi,
>
> Can we get any information regarding fragmentation/corruption in the
> database level from the system table
>
> "Information_schema.columns" ?
>
> Actually my strong belief is other databases like SQL server, Oracle &
> Mysql are providing some system views to detect corruption and fragmentation
> from the database. So thinking the same with the postgres.
>
> As I was asked to prepare a script well in advance to predict the
> forthcoming disasters
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>