Example

You have a user table in Postgres:

CREATE SCHEMA tutorial;

CREATE TABLE tutorial.crud_users (
    id INT NOT NULL,
    username VARCHAR NOT NULL,
    CONSTRAINT crud_table_pkey PRIMARY KEY (id)
);

You insert new users and update usernames for existing users. Update is a destructive operation that overwrites data, so how do you keep track of what usernames a particular id has had over time?

Use a trigger

You can use triggers in Postgres to remember the historical values of your rows.

First create a new table to store the history, which you can think of as a logical stream of updates to the "crud_user" table. Each row in "stream_users" corresponds to a current or historical state of a row in "crud_users".

CREATE TABLE tutorial.stream_users (
    id INT,
    created_ts TIMESTAMP,
    username VARCHAR NOT NULL,
    CONSTRAINT stream_table_pkey PRIMARY KEY (id, created_ts)
);

Next, create a trigger on "crud_users".

CREATE OR REPLACE FUNCTION crud_users_insert_trigger_fnc()
  RETURNS trigger AS
$$
BEGIN
    INSERT INTO "tutorial"."stream_users" ( "id", "created_ts", "username")
    VALUES(NEW."id", now(), NEW."username");
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER user_insert_trigger
  AFTER INSERT OR UPDATE
  ON "tutorial"."crud_users"
  FOR EACH ROW
  EXECUTE PROCEDURE crud_users_insert_trigger_fnc();

The trigger inserts a new row in "stream_users" every time you insert or update a row in "crud_users". Thus, we keep track of all historical and current values of users.

Try it out

select * from tutorial.crud_users; -- should be empty
select * from tutorial.stream_users; -- should be empty

insert into tutorial.crud_users(id, username) values (1, 'joe');

update tutorial.crud_users set username='joe69' where id=1;

Now, If you delete a user from "crud_users" we will not insert anything into the "stream_users" table. To improve the design, we could add a state column to stream_users, which reflects the kind of change, e.g. created, updated or deleted.