Re: Increased size of database dump even though LESS consumed storage

Guten Tag Thorsten Schöning,
am Dienstag, 9. Februar 2021 um 10:56 schrieben Sie:

> Any explanation for the increased dump size? Thanks!

While I don't have a detailed explanation myself, there's the
following hint [in the docs][1]:

> For the custom archive format, this specifies compression of
> individual table-data segments[...]

https://www.postgresql.org/docs/10/app-pgdump.html

I've changed the layout of my tables entirely and most likely have
changed the "individual table-data segments" as well. So I did some
further testing and got the following numbers. It's always OLD vs. NEW
schema for the schema documented earlier:

> --compress=0: 25  vs. 20  GiB
> --compress=?: 5,6 vs. 6,5 GiB
> --compress=9: 5,5 vs. 6,4 GiB

"?" means default settings, meaning "--compress" has been REMOVED from
the shell command.

As can be seen, pretty much as soon as compression comes into play,
the new table layout is less efficient, even though in OLD and NEW
schema exactly the same "bytea" were used, have been placed at the end
of their individual tables and alike. But things become more
interesting with another version of my schema, which as well indicates
a strong relationship of dump size, compression and table layout/size.

I've changed the new table "datagram" to be a partitioned one with
partitions containing rows per year and per half-year. Each partition
contains far less rows than before this way and while I only tested
with "--compress=9" this time, the numbers are quite interesting:

> unpartitioned:     6,4 GiB
> half-yearly parts: 4,8 GiB
> yearly parts:      4,8 GiB

The interesting part this time is that the table layout for all
partitions is the same like before, only the number of rows per table
is different. Though, the number of rows overall is the same like
before, the same data, IDs etc. Though, this time the dump really is
smaller than with the OLD schema containing far more data because of
duplicate IDs and stuff.

I wouldn't have expected table layout to be that important.

Mit freundlichen Grüßen

Thorsten Schöning

--
Thorsten Schöning
AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister
fürIT und TK 

E-Mail: Thorsten.Schoening@AM-SoFT.de
Web:    http://www.AM-SoFT.de/

Telefon: 05151-  9468- 0
Telefon: 05151-  9468-55
Fax:     05151-  9468-88
Mobil:    0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH i.G., Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB neu - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.

Mit freundlichen Grüßen

Thorsten Schöning


Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil:
Webseite: https://www.am-soft.de

AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister
fürIT und TK 

AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0

Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80

CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1

Büro Dallgow-Döberitz
Tel: 03322 507 020

Büro Kloster Lehnin
Tel: 033207 566 530

PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0

Büro Neuruppin
Tel: 033932 606 090

ACI EDV Systemhaus Dresden GmbH
Dresden
Tel: 0351 254 410

Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0

Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7

Büro Liebenwalde
Tel: 033054 810 00

HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97

Bitstore IT – Consulting GmbH
NL Piesteritz
Piesteritz
Tel: 03491 644 868 6

Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0

MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3

Firmensitz: MF Computer Service - Bitstore Gütersloh GmbH, Gildestraße 25, 33442 Herzebrock-Clarholz
Geschäftsführer Janine Galonska