1

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.

2
  • 1
    Examine it with pageinspect. Look for errors in the log. Query pg_class to find what object the files belong to. Query those objects. This looks buggy. Jan 28, 2021 at 20:59
  • I see no errors in the logs on the replica other thancanceling statement due to conflict with recovery which I expect now and then. I've added some more details about one particular relation, however I've never used pageinspect before 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?
    – msqr
    Jan 29, 2021 at 3:28

1 Answer 1

2

Looking at the size of your files, I see that they are all wrong. All these file sizes should be multiples of 8192, and the segments like 15339026 should be exactly 1GB in size.

You must be using some operating system file compression facility or a weird file system that does not show the correct file size. That also explains the discrepancy between primary and standby.

1
  • Thanks for that observation: yes the file system is ZFS with compression enabled. I added more context to the question. The compression ratios achieved are quite different between the primary and replica, which I didn't expect.
    – msqr
    Jan 29, 2021 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.