search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
1.01k stars 153 forks source link

pglogical activity invisible to statistics collector? #217

Open felixge opened 5 years ago

felixge commented 5 years ago

Problem

It seems that rows inserted by pglogical are somehow invisible to the statistics collector. In particular, I'd expect this value to increment as pglogical inserts rows on a subscriber:

SELECT tup_inserted FROM pg_stat_database;

However, it remains static despite a stream of ~40 rows / second being inserted by pglogical into one of our tables.

I have confirmed that the statistics collector itself is not totally broken by manually inserting some rows in a table myself.

Question: Is this expected when using pglocial?

Environment Info

SELECT version(), pglogical.pglogical_version();
version pglogical_version
PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit 2.2.1
VictorSlavov commented 5 years ago

Hi, I observe the same on my system. We have a table which total space usage is much larger on the logical replication side than on the primary node (about 70 times). What I noticed is that modification statistics on the replica node are all zero. My theory is that because of this, no auto-analyze kicked in and space in table/indexes is not reused.

OS: SMP Debian 3.16.51-3 (2017-12-13) x86_64 GNU/Linux postgresql: 11.5-1.pgdg80+1 pglogical: 2.2.2-1.pgdg80+1

Regards, Victor

ksenobojca commented 1 year ago

Same issue with extra observation: https://stackoverflow.com/a/73831180

mzealey commented 11 months ago

We are still seeing this in pg16 docker container image with pglogical plugin installed. Unless I missed some psql config setting, it's annoying to have to run our own analysing scripts via cron rather than have this just working automatically.