I have a Postgres 9.6 database system with streaming replication and read-only query access enabled on the replica. I find that the replica tablespace data (not WAL data) is consuming about 1.5x as much disk space as taken up on the primary server. For example, comparing a subset of one tablespace's files and block sizes via ls -1s on the primary that show the block size followed by filename, and I've added a total size at the bottom:
5348 7822674
3491 7822682
417 7822757
5179 8031140
30156 8039057
2259 8518948
10274 8518957
33 8623406
4507 8862041
==============
61664
with the replica:
5685 7822674
3713 7822682
441 7822757
5617 8031140
30501 8039057
2425 8518948
10869 8518957
37 8623406
4201 8862041
==============
63489
That's a difference of 1825 blocks. The replication setup also uses wal-e for WAL management, if that makes any difference. I'd like to understand how/why the replica is consuming more space, so I can try to reduce it to roughly the same size as the primary if possible.
Per comment from Laurenz, here is some info on a particular OID 15339026, which on the primary server I see this on the filesystem:
369271 15339026
358366 15339026.1
175925 15339026.2
670 15339026_fsm
64 15339026_vm
while on the replica I see:
382853 15339026
372893 15339026.1
183681 15339026.2
677 15339026_fsm
65 15339026_vm
Finding the relation for this page on the primary:
select * from pg_class where relfilenode = 15339026;
-[ RECORD 1 ]-------+--------------------
relname | _hyper_3_1942_chunk
relnamespace | 469072
reltype | 15339028
reloftype | 0
relowner | 16402
relam | 0
relfilenode | 15339026
reltablespace | 0
relpages | 331706
reltuples | 5.29004e+06
relallvisible | 316256
reltoastrelid | 15339029
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 12
relchecks | 1
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | t
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relfrozenxid | 1134732248
relminmxid | 228663310
relacl |
reloptions |
I get identical results on the replica.
I've never used pageinspect before so I'm not sure what would be helpful here, but here's output from the first block, on the primary server:
select lp, lp_off, lp_flags, lp_len, t_xmin, t_xmax, t_field3, t_ctid, t_infomask2, t_infomask, t_hoff, t_bits, t_oid
from heap_page_items(get_raw_page('_timescaledb_internal._hyper_3_1942_chunk', 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid
----+--------+----------+--------+-----------+--------+----------+--------+-------------+------------+--------+------------------+-------
1 | 31 | 2 | 0 | | | | | | | | |
2 | 13 | 2 | 0 | | | | | | | | |
3 | 2136 | 1 | 260 | 467839546 | 0 | 1 | (0,3) | 32780 | 11011 | 32 | 1111100010000000 |
4 | 14 | 2 | 0 | | | | | | | | |
5 | 45 | 2 | 0 | | | | | | | | |
6 | 7792 | 1 | 193 | 464502185 | 0 | 1 | (0,6) | 32780 | 11011 | 32 | 1111100110000000 |
7 | 2736 | 1 | 212 | 467821842 | 0 | 1 | (0,7) | 32780 | 11011 | 32 | 1111010001110000 |
8 | 10 | 2 | 0 | | | | | | | | |
9 | 24 | 2 | 0 | | | | | | | | |
10 | 7392 | 1 | 193 | 465037836 | 0 | 1 | (0,10) | 32780 | 11011 | 32 | 1111100110000000 |
11 | 3 | 2 | 0 | | | | | | | | |
12 | 1640 | 1 | 200 | 469440374 | 0 | 1 | (0,12) | 32780 | 11011 | 32 | 1111010001110000 |
13 | 6792 | 1 | 193 | 466600342 | 0 | 1 | (0,13) | 32780 | 11011 | 32 | 1111100110000000 |
14 | 6992 | 1 | 193 | 465685006 | 0 | 1 | (0,14) | 32780 | 11011 | 32 | 1111100110000000 |
15 | 46 | 2 | 0 | | | | | | | | |
16 | 17 | 2 | 0 | | | | | | | | |
17 | 5392 | 1 | 267 | 467317747 | 0 | 1 | (0,17) | 32780 | 11011 | 32 | 1111100010000000 |
18 | 7192 | 1 | 193 | 465339489 | 0 | 1 | (0,18) | 32780 | 11011 | 32 | 1111100110000000 |
19 | 12 | 2 | 0 | | | | | | | | |
20 | 29 | 2 | 0 | | | | | | | | |
21 | 2952 | 1 | 1729 | 467559218 | 0 | 1 | (0,21) | 32780 | 11011 | 32 | 1111111011110000 |
22 | 6 | 2 | 0 | | | | | | | | |
23 | 7 | 2 | 0 | | | | | | | | |
24 | 1840 | 1 | 294 | 468678381 | 0 | 1 | (0,24) | 32780 | 11011 | 32 | 1111100110000000 |
25 | 26 | 2 | 0 | | | | | | | | |
26 | 6344 | 1 | 241 | 467175163 | 0 | 1 | (0,26) | 32780 | 11011 | 32 | 1111100110000000 |
27 | 38 | 2 | 0 | | | | | | | | |
28 | 21 | 2 | 0 | | | | | | | | |
29 | 7592 | 1 | 193 | 464839803 | 0 | 1 | (0,29) | 32780 | 11011 | 32 | 1111100110000000 |
30 | 1072 | 1 | 295 | 469466849 | 0 | 1 | (0,30) | 32780 | 11011 | 32 | 1111100110000000 |
31 | 7992 | 1 | 193 | 464174359 | 0 | 1 | (0,31) | 32780 | 11011 | 32 | 1111100110000000 |
32 | 960 | 1 | 107 | 469498282 | 0 | 1 | (0,32) | 12 | 2819 | 32 | 1111001000000000 |
33 | 34 | 2 | 0 | | | | | | | | |
34 | 2400 | 1 | 336 | 467839107 | 0 | 1 | (0,34) | 32780 | 11011 | 32 | 1111110011110000 |
35 | 30 | 2 | 0 | | | | | | | | |
36 | 18 | 2 | 0 | | | | | | | | |
37 | 47 | 2 | 0 | | | | | | | | |
38 | 6592 | 1 | 193 | 466777754 | 0 | 1 | (0,38) | 32780 | 11011 | 32 | 1111100110000000 |
39 | 6240 | 1 | 101 | 467306332 | 0 | 1 | (0,39) | 32780 | 11011 | 32 | 1111001000000000 |
40 | 6040 | 1 | 199 | 467312262 | 0 | 1 | (0,40) | 32780 | 11011 | 32 | 1111010001110000 |
41 | 51 | 2 | 0 | | | | | | | | |
42 | 39 | 2 | 0 | | | | | | | | |
43 | 5936 | 1 | 103 | 467312328 | 0 | 1 | (0,43) | 32780 | 11011 | 32 | 1111001000000000 |
44 | 43 | 2 | 0 | | | | | | | | |
45 | 5664 | 1 | 272 | 467312396 | 0 | 1 | (0,45) | 32780 | 11011 | 32 | 1111100010000000 |
46 | 4688 | 1 | 423 | 467357142 | 0 | 1 | (0,46) | 32780 | 11011 | 32 | 1111110111110000 |
47 | 1368 | 1 | 271 | 469440923 | 0 | 1 | (0,47) | 32780 | 11011 | 32 | 1111100010000000 |
48 | 52 | 2 | 0 | | | | | | | | |
49 | 40 | 2 | 0 | | | | | | | | |
50 | 672 | 1 | 286 | 470581084 | 0 | 1 | (0,50) | 32780 | 11011 | 32 | 1111100110000000 |
51 | 5112 | 1 | 275 | 467321529 | 0 | 1 | (0,51) | 32780 | 11011 | 32 | 1111100010000000 |
52 | 496 | 1 | 175 | 471138476 | 0 | 1 | (0,52) | 32780 | 11011 | 32 | 1111100010000000 |
53 | 50 | 2 | 0 | | | | | | | | |
54 | 0 | 0 | 0 | | | | | | | | |
55 | 0 | 0 | 0 | | | | | | | | |
56 | 0 | 0 | 0 | | | | | | | | |
57 | 0 | 0 | 0 | | | | | | | | |
58 | 0 | 0 | 0 | | | | | | | | |
59 | 0 | 0 | 0 | | | | | | | | |
60 | 0 | 0 | 0 | | | | | | | | |
61 | 0 | 0 | 0 | | | | | | | | |
62 | 0 | 0 | 0 | | | | | | | | |
63 | 0 | 0 | 0 | | | | | | | | |
64 | 0 | 0 | 0 | | | | | | | | |
65 | 0 | 0 | 0 | | | | | | | | |
66 | 0 | 0 | 0 | | | | | | | | |
When I run the same query on the replica, the output is almost the same: just the t_field3 column has 0 in place of every 1.
I see no errors in the logs on the replica other than
canceling statement due to conflict with recovery
which I expect now and then.
Additional context for answer
Per the answer from Laurenz re: file system compression, these systems are on ZFS with compression enabled. It didn't occur to me that the replica would achieve such wildly different compression results as the primary as they are configured identically, but this is the case. I compared the sizes using du -A on the primary:
# compressed size
du -hs dat
55G dat
# uncompressed size
du -Ahs dat
158G dat
with the replica:
# compressed size
du -hs dat
74G dat
# uncompressed size
du -Ahs dat
158G dat
and indeed the uncompressed sizes are the same while the compressed sizes are different. Looks like I'll have to contend with different effective disk size resource requirements on the replica.
pageinspect. Look for errors in the log. Querypg_classto find what object the files belong to. Query those objects. This looks buggy.canceling statement due to conflict with recoverywhich I expect now and then. I've added some more details about one particular relation, however I've never usedpageinspectbefore so I'm not sure what type of query I should use to produce helpful information. Do you have a particular query in mind that you could share?