Handling Postgres Inherited tables

Questions / Help
,
1

Hi,

To manage our masive transaction data and keep it to easy to query those data, we are saving our data with table like

create table stock (
   stock_id integer,
   transaction_date date,
   product_id integer,
   beginning_bal numeric,
   incoming_bal numeric,
   outgoing_bal numeric,
   ending_bal numeric,
   warehouse_id integer,
   period_id integer
)

Since this table contains millions of data, we made partition of this data based on warehouse_id and period_id. We are using inherit table feature of PostgreSQL and made those table as follows:

create table stock_warehouse_id_period_id INHERITS stock;

In case we have Warehouse_ID 1000001, 1000002 and Period_ID 101,102; the actual inheritad table created will be

stock_1000001_101
stock_1000002_101
stock_1000001_102
stock_1000002_102

And use those partition table to save our actual data, based on warehouse dan period info, instead of the stock table. We are using the stock table as a template only.

Since the variation of warehouse and period combination will be huge, while using Ecto, we need to map each of those table variation to its own models.

Is there any chance to make it running with single Ecto models (let say stock schema as mapping to stock tables) and all of CRUD activities should be done and depends on Warehouse_ID and Period_ID supplied ?

Need your advice please.

Thanks for reading

1 Like
2

Ecto supports using dynamic sources (table names) for schemas, e.g:

defmodule Stock do
  use Ecto.Schema

  schema "that_doesnt_matter" do
    ...
  end
end

# pulls data from "actual_table" and puts into a Stock struct
stocks = Repo.all({"actual_table", Stock})

# manipulates one of the extracted records
changeset = do_something(Enum.at(stocks, 0))

# persists back to the table the record was read from
Repo.update(changeset)

While there are no built-in facilities to dispatch to the correct table in a scheme that you described, I’m pretty sure devising a solution using this should be perfectly possible and not that complex. Additionally Ecto.put_meta/2 with the :source option can be used to modify source data of a struct - this can be useful in inserts to route to the proper table.

The {name, schema} tuples are queryables - this means you can use them in Ecto queries wherever you would use a schema name.

5 Likes