Use native data types in database: datetime, jsonb

[dwt]

Hi there,

while working with the Raquel data to do some analytics, we discovered that we would have liked the datatypes to be more specific.

For example, the

• enqueued_at column is typed big int, even though it contains a timestamp (however, in milliseconds, so not quite).
• payloadcolumn is typed varchar, even though it contains json.

It's not that it is impossible to convert these into more usable formats (jsonb and timestamp) but the question is why? Wouldn't native types in the database make it easier to work with them? For example converting the milliseconds to dates in sql is surprisingly error prone.

[vduseev]

Hey @dwt,

Why text and int / bigint

The thinking behind this decision was simple:

• Keep Raquel as simple as possible at its core – no custom or complicated logic to handle specific databases
• Avoid any strong ties to a specific RDBMS (even though 70% users will just use it with Postgres)
• Make it absolutely trivial to migrate the jobs from SQL Server to PostgreSQL or MySQL (real-life journey of many large enterprises I have encountered).

For these reasons, as stated at the bottom of the README, everything is designed to be database agnostic:

Save yourself the pain of migrating between database vendors or versions. All timestamps are stored as milliseconds since epoch (UTC timezone). Payloads are stored as text. Job IDs are random UUIDs to allow migration between databases and HA setups.

How to work with these types comfortably

As you said, it's totally possible to convert them to native data types for each database. Perhaps, we could make the lives of developers easier by providing some ready-to-use examples for most popular DBs. Here is what I would add to the README:

-- Convert `jobs` table column data types to native data types in Postgres

SELECT
  id,
  queue,
  status,
  max_retry_count,
  attempts,
  error,
  error_trace,
  claimed_by,

  -- If you only ever store JSON in the jobs table
  payload::jsonb AS payload_json,

  -- Durations
  max_age::int             * INTERVAL '1 millisecond' AS max_age_interval,
  min_retry_delay::int     * INTERVAL '1 millisecond' AS min_retry_delay_interval,
  max_retry_delay::int     * INTERVAL '1 millisecond' AS max_retry_delay_interval,
  backoff_base::int        * INTERVAL '1 millisecond' AS backoff_base_interval,

  -- Timestamps
  to_timestamp(enqueued_at / 1000.0) AS enqueued_at_tz,
  to_timestamp(scheduled_at / 1000.0) AS scheduled_at_tz,
  to_timestamp(claimed_at  / 1000.0) AS claimed_at_tz,
  to_timestamp(finished_at / 1000.0) AS finished_at_tz,

FROM jobs;

What's next on the roadmap?

However, I do have another concern about the chosen data types: space consumption. We've been running Raquel in prod for more than half a year in a heavy load project. And the jobs table grows like a magic beans tree. It's actually scary even. So a bit project that schedules a lot of jobs can eat up the disk space quite quickly.

I'm currently analysing the dump from that prod instance to see what can be optimised. Some of the ideas are:

• Store payload as bytes
• Store status as small int
• Maybe it is actually worth it to add custom logic to store the payload as JSONB in Postgres, because Postgres is very efficient at handling and storing JSONB format.
• Introduce some kind of auto-cleanup mechanism

[dwt]

I really like documenting the correct way to work with these datatypes.

If you want to go for database specific types (like JSONB) without sacrificing generality, you have multiple ways to achieve this. Perhaps an approach like this might be enough:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import TIMESTAMP, DateTime, Time

# Workaround for sub second precision datetime / time / timestamp in mysql / mariadb
# @see https://mariadb.com/kb/en/library/microseconds-in-mariadb/

# The problem is that mysql / mariadb requires custom DDL-Syntax to use sub second precision,
# which cannot be enabled via a setting. Thus we need to actually generate different SQL. :/
# This takes care of this in a generic way


@compiles(DateTime, 'mysql')
def render_datetime_with_sub_second_precision(element, compiler, **kwargs):
    return "DATETIME(6)"

@compiles(Time, 'mysql')
def render_time_with_sub_second_precision(element, compiler, **kwargs):
    return "TIME(6)"

@compiles(TIMESTAMP, 'mysql')
def render_timestamp_with_sub_second_precision(element, compiler, **kwargs):
    return "TIMESTAMP(6)"

[dwt]

Regarding the auto cleanup mechanism: I like avoiding having to start its own service for this. One way to do this, would be to give the worker a small chance per request to do some cleanup, with the cleanup_chance being configurable to be sensible for the workload.

[vduseev]

@dwt, very nice! Thanks for the suggestions. I will look into both. I'm preparing for a huge milestone of announcing Raquel at PyCon Poland at the end of August this year. By that time it should iron out biggest issues and address everything we have found together so far.