How to detect delay of Pubsub to Bigquery subscription?

[alexbsolutions]

How can one compute delay between pub sub publish time and arrival to bigquery when using pub/sub to bigquery subscription (not through dataflow)?
I have publish_time from pub sub metadata. And a field in BQ in the table where data flows that auto populates with CURRENT_TIMESTAMP

I was hoping to just check the timestamp difference but noticed that quite often current_timestamp is earlier than publish_time.

It's about 350 messages/s and BQ table is partitioned by _PARTITIONTIME / hour if this makes any difference. Im also wondering what is expected delay?

[ricconoel · Accepted solution]

Hi,

There is a similar question in this public issue tracker and this is an expected behavior. Unfortunately there is no expected fixed value for the delay.

[alexbsolutions]

hi there, 

thanks so much for getting back to me on this. I was wondering what is expected delay between message arriving to pubsub and appearing in Bigquery when using this method?

I can see that im getting delay of about few minutes. If this is expected level of delay is there an alternative to deliver pubsub data to bigquery with lesser delay?

Thanks, 

Alex

[ricconoel]

Delay in minutes seems too much. I suggest if you have a support package you can contact GCP support to let them check this for you and they will provide some suggestions on how to possible speed things up.