I want to add rows to a (projected) very huge table, but switch 2 values for references in other tables. The catch is that the referenced tables should be updated automatically when new values arrive. I have a hunch about how to do it, but don't know how to execute.
There's a lengthy explanation below, but feel free to skip to the step by step diagram, which is pretty self-explanatory. Please keep in mind that this should happen entirely on the database, without relying on the application.
I'll try to describe the full problem as well as I can here, along with the reasoning for the specific issue I'm trying to solve. If I'm solving the wrong thing, please call me out.
Note: I'll be using PostgreSQL 10 for this, and even though it might not be the best tool, I'm restricted to it for this problem.
The core of this application is a table containing, essentially, readings, where the readings come from (origin) and which actor is responsible for these readings.
However, I don't have much control over how many or which origins and actors there are. I must assume that I'll be recording new values for these fields as they come. (Of course the application layer will have some control over the process, but assume I have no control over it)
What I do know, though, is that they are usually medium-sized (40~200) strings.
And this is basically the whole motivation of the problem: the only "useful" values in the core table will be the readings, which are much smaller in size, while the actor and origin are mostly annotation. They will be occasionally useful, so I can't just discard them, but it seems extremely wasteful, storage-wise, to store their values for each row, not to mention having a lot less rows per page. The table is projected to contain on the order of tens of billions of rows, so this could be significant.
The application layer, however, shouldn't be aware of this optimization. As far as it's concerned, it should just
INSERT INTO readings VALUES (reading, origin_name, actor_name);, sometimes one row at a time, sometimes multiple, either through an SQL client or an ORM.
The order of magnitude for the "origin" values will be 3 to 4 times smaller than the readings table, while "actors" should be no more than a few dozens.
Even if having separate tables is not necessary to solve my problem, I'd need a table for actors anyway, to annotate a couple of useful stuff.
Even though I said rows could be inserted one at a time, mostly they'll be inserted by batch through an ORM, on the order of thousands per query. This could be important in terms of overhead if the solution involves running the check-create-substitute-insert for each row.
CREATE TABLE origins ( origin_id serial PRIMARY KEY, origin_name VARCHAR (255) NOT NULL, ); CREATE TABLE actors ( actor_id serial PRIMARY KEY, actor_name VARCHAR (255) NOT NULL, actor_comments VARCHAR (255) NOT NULL, ); CREATE TABLE readings ( id bigserial PRIMARY KEY, reading integer NOT NULL, -- Not a single value in actual table origin_id integer NOT NULL, actor_id integer NOT NULL, CONSTRAINT origin_id_fkey FOREIGN KEY (origin_id) REFERENCES origins (origin_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT actor_id_fkey FOREIGN KEY (actor_id) REFERENCES actors (actor_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, );
Idea - Create trigger after
INSERT statement on
Basically, I would create a support table in which the raw values will be inserted, and trigger a function after each
INSERT statement. It should get a unique set of both the origins and actors, add new values to their respective tables, return the reference id and swap the values for them. Then just insert into the actual table and clear the contents of this support one.
I feel like this would solve the problem, but am concerned about potential race conditions and general inefficiency of the whole process. I suppose this could happen in a single query, maybe a trigger modifying the
So, is this solution good enough or am I looking at it the wrong way?