Using an AshPostgres resource with a Timescale Hypertable.

[jimsynz]

Not so much a request as just recording this here for posterity since I'm unlikely to have time to loop back and make it all work nicely. If you want to make a resource work as a hypertable you need to make the following changes to your resource:

resource do
  # Hypertables shouldn't have a primary key but disabling them will break a bunch of stuff (like relationship references)
  require_primary_key? false
end

attributes do
  # You don't need `updated_at` because these records should be write-once.
  created_timestamp :inserted_at
end

postgres do
  table "my_fancy_metric"

  references do
    # Don't try and create an FK constraint to any related tables because we don't have a primary key.
    reference :user, ignore?: true
  end

  custom_statements do

    # Timescale wants your `inserted_at` field to have a timezone, but Ash just assumes that everything is recorded in UTC and doesn't use the timestamptz type under the hood.
    # Storing the TZ is important because you may want to to do roll ups that are TZ specific (ie bucket by day in your user's timezone).
    # This should happen _before_ you create the hypertable.
    statement :add_timezone_to_inserted_at do
      up "ALTER TABLE \"my_fancy_metric\" ALTER COLUMN inserted_at TYPE timestamptz USING inserted_at AT TIME ZONE 'UTC'"

      down "ALTER TABLE \"my_fancy_metric\" ALTER COLUMN inserted_at TYPE timestamp USING inserted_at AT TIME ZONE 'UTC'"
    end
    
    # Convert the table into a hypertable.
    # You only need the `migrate_data` option if there is likely to be existing data in your table at the time of conversion.
    # Sadly there is no easy way to convert a table back from being a hypertable (maybe copying into a temporary table and recreating?)
    statement :create_hypertable do
      up "SELECT create_hypertable('my_fancy_metric', 'inserted_at', migrate_data => true)"
      down "SELECT 1"
    end
    
    # You _really_ want to enable compression on your table because this is what actually converts it into columnar data.
    # If you will be commonly performing queries which group by a particular attribute you should use the `compress_segmentby` option.
    statement :enable_compression do
      up "ALTER TABLE \"my_fancy_metric\" SET (timescaledb.compress, timescaledb.compress_segmentby = 'user_id');"
      down "ALTER TABLE \"my_fancy_metric\" SET (timescaledb.compress=false);"
    end

    # I know that we just enabled compression, but unless you add a compression policy, nothing will be compressed unless you manually ask for the hypertable to be compressed.
    statement :set_compression_policy do
      up "SELECT add_compression_policy('my_fancy_metric', INTERVAL '7 days');"
      down "SELECT remove_compression_policy('my_fancy_metric');"
    end
  end
end

Please don't just paste this in and expect it to work. You should get familiar with the Timescale docs and not just take my word for it.