cloud: Load submissions from PostgreSQL into BigQuery

[spbnick]

To increase throughput of loading submissions into BigQuery, switch to loading them in big chunks from PostgreSQL, but still using load jobs.

The streaming mechanism is somewhat troublesome in our case, as it needs its buffers flushed before any DELETE or UPDATE operations can be done on the table, there's no force flushing, and it could take up to 90 minutes to have everything flushed. The TRUNCATE operation which would've suited us fine also has problems with this currently. This prevents us from always loading using streaming, as this would break tests which needs to empty the database repeatedly.

Doing this will also help us to move closer to making the BigQuery dataset public, as data in PostgreSQL will be largely de-duplicated, making partitioning of BigQuery more viable, and reducing the cost of queries.

[spbnick]

OK, here are the requirements:

• Pick up from the current state and reliably transfer the three-or-so months of data we're missing in BigQuery right now
• Be able to recover from transfer failures, and not lose data in case something went wrong on a previous attempt
• Avoid loading duplicate data as much as possible, but prefer duplicates over missing data.
• Transfer data only after it's outside the "editing window", to reduce duplicates of objects

And here's the plan:

• Assume a certain "editing window", but don't implement it yet (a matter for another issue and time), say two weeks
• Support dumping data between two timestamps in database client and drivers
• Remove rows older than the maximum timestamp, minus, say, an hour, from BigQuery, so we make sure we don't have incomplete data for the maximum timestamp there.
• Add a Cloud Function triggered on a daily schedule, say at midday, which:
  • For each object type:
    • Queries the maximum _timestamp from BigQuery, and puts it into start_ts
    • Calculates the timestamp for two weeks ago and puts it into end_ts
    • Caps end_ts at a week from start_ts
    • Dumps data from PostgreSQL with _timestamp > start_ts AND _timestamp <= end_ts (note the equals!), with metadata.
    • Loads the dump into BigQuery, preserving metadata

[spbnick]

We need to figure out how we're going to handle inclusive/exclusive boundaries.

[spbnick]

Perhaps we could just implement start < timestamp <= end and be done with that for now.

[spbnick]

OK, this has been just implemented in the PR above:

• Support dumping data between two timestamps in database client and drivers

However, it doesn't actually support dumping object types separately, and takes one timestamp for all types (for either boundary). This seems to be simpler and easier to manage and maintain. Correspondingly, we need to change our plan:

• Remove rows older than the maximum timestamp, minus, say, an hour, from BigQuery, so we make sure we don't have incomplete data for the maximum timestamp there.
• Add a Cloud Function triggered on a daily schedule, say at midday, which:
  • Queries the maximum _timestamp across all object types from BigQuery, and puts it into after_ts.
  • Calculates the timestamp for two weeks ago and puts it into until_ts
  • Caps until_ts at a week from after_ts
  • Dumps data from PostgreSQL with _timestamp > after_ts AND _timestamp <= until_ts (note the equals!), with metadata.
  • Loads the dump into BigQuery, preserving metadata

[spbnick]

OK, everything is written. Now I need to write a test for that archival Cloud Function, and make sure everything works.

[spbnick]

Tests are written, implementation is fixed, waiting for CI before merging #598

[spbnick]

The #598 is merged, and everything after 2024-07-06T06:00:00+00:00 is removed from production BigQuery (the last modified time was 2024-07-06T06:27:19.430720+00:00). Similar is done to playground.