Re: Size of PostgreSQL backup ./. Sybase DUMP

Matthias Apitz schrieb am 17.04.2021 um 08:59:
> As I said, the Sybase DUMP command dumps a single database, i.e. works
> more like pg_dump of PostgreSQL. Ofc, they're like apples and oranges,
> but the netto data of the tables must be written to disk, and as I said
> above in both cases compressed with gzip.
>
> Even more, the pg_basebackup does not only write the netto data of the
> database, but also system tables and configuration information of the
> PG server. That made me asking, why it's less than the half in size as
> the gzip'ed DUMP of Sybase.
>
> One reason I could imagine is, that Sybase's DUMP writes all the pages,
> even the unused pages into the output file. But this is a guess, because
> I never investigated the internals of Sybase's DUMP command.

Actually pg_basebackup writes the data of **all** databases, not just one.

I wonder if the difference is because Sybase uses a single container file
for each database. And that container file might be bigger than the actual
content due to deleting data, or dropping tables. Postgres uses one file
per table (multiple files if the table size exceeds 1GB). So if you drop
a table in Postgres the space is immediately freed.

I know that in SQL Server there is a way to shrink the database files
(=containers). If that is possible in Sybase as well, you could try
and compare the size then.