How would I track all price changes in a db in order to get the price of 'x' product on 'y' date

I would need to track a products price changes so that I can query the db for a product price at a given date. The information is used in a system that calculates historical audits so it must return the correct price for the correct product based on the date of purchase.

I would prefer to use postgres in building the db.

I need with the design of the database but any and all best practice suggestions are also welcome.

If I understand the scenario appropriately, you should define a table that retains a Price time series; therefore, I agree, this has a lot to do with the temporal aspect of the database you are working with.
Business rules

Let us start analyzing the situation from the conceptual level. So, if, in your business domain,

• a Product is purchased at one-to-many Prices,

• each Price of purchase becomes Current at an exact StartDate, and

• the Price EndDate (which indicates the Date when a Price ceases to be Current) is equal to the StartDate of the immediately subsequent Price,

then that means that

• there are no Gaps between the distinct Periods during which the Prices are Current (the time series is continuous or conjunct), and

• the EndDate of a Price is a derivable datum.

The IDEF1X diagram shown in Figure 1, although highly simplified, depicts such a scenario:

Expository logical layout

And the following SQL-DDL logical-level design, based on said IDEF1X diagram, illustrates a feasible approach that you can adapt to your own exact needs:

-- At the physical level, you should define a convenient 
-- indexing strategy based on the data manipulation tendencies
-- so that you can supply an optimal execution speed of the
-- queries declared at the logical level; thus, some testing 
-- sessions with considerable data load should be carried out.

CREATE TABLE Product (
    ProductNumber INT      NOT NULL,
    Etcetera      CHAR(30) NOT NULL,
    --
    CONSTRAINT Product_PK PRIMARY KEY (ProductNumber)
);

CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    Amount        INT  NOT NULL, -- Retains the amount in cents, but there are other options regarding the type of use.
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT AmountIsValid_CK    CHECK       (Amount >= 0)
);

The Price table has a composite PRIMARY KEY made up of two columns, i.e., ProductNumber (constrained, in turn, as a FOREIGN KEY that makes a reference to Product.ProductNumber) and StartDate (pointing out the particular Date in which a certain Product was purchased at a specific Price).

In case that Products are purchased at different Prices during the same Day, instead of the StartDate column, you may include one labeled as StartDateTime that keeps the Instant when a given Product was purchased at an exact Price. The PRIMARY KEY would then have to be declared as (ProductNumber, StartDateTime).

As demonstrated, the aforementioned table is an ordinary one, because you can declare SELECT, INSERT, UPDATE and DELETE operations to manipulate its data directly, hence it (a) permits avoiding the installation of additional components and (b) can be used in all the major SQL platforms with some few adjustments, if necessitated.
Data manipulation samples

To exemplify some manipulation operations that appear useful, let us say that you have INSERTed the following data in the Product and Price tables, respectively:

INSERT INTO Product
    (ProductNumber, Etcetera)
VALUES
    (1750, 'Price time series sample'); 

INSERT INTO Price
    (ProductNumber, StartDate, Amount)
VALUES
    (1750, '20170601', 1000),
    (1750, '20170603', 3000),   
    (1750, '20170605', 4000),
    (1750, '20170607', 3000);

Since the Price.EndDate is a derivable data point, then you must to obtain it via, precisely, a derived table that can be created as a view in order to produce the “full” time series, as exemplified below:

CREATE VIEW PriceWithEndDate AS

    SELECT  P.ProductNumber,
            P.Etcetera AS ProductEtcetera,
           PR.Amount   AS PriceAmount,
           PR.StartDate,
           (
                SELECT MIN(StartDate)
                      FROM Price InnerPR
                     WHERE P.ProductNumber   = InnerPR.ProductNumber
                       AND InnerPR.StartDate > PR.StartDate
           ) AS EndDate
        FROM Product P
        JOIN Price   PR
          ON P.ProductNumber = PR.ProductNumber;

Then the following operation that SELECTs directly from that view

SELECT ProductNumber,
         ProductEtcetera,
         PriceAmount,
         StartDate,
         EndDate
    FROM PriceWithEndDate 
ORDER BY StartDate DESC;

supplies the next result set:

ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         4000  2017-06-07  NULL      -- (*) 
         1750  Price time series…         3000  2017-06-05  2017-06-07
         1750  Price time series…         2000  2017-06-03  2017-06-05
         1750  Price time series…         1000  2017-06-01  2017-06-03

-- (*) A ‘sentinel’ value would be useful to avoid the NULL marks.

Now, let us assume that you are interested in getting the w

StackExchange Database Administrators Q#176935, answer score: 14