Postgresql – Postgres log files not rotating

Index

First and foremost, for your type of query this is the much better index:

CREATE INDEX de_tt_priceinfo_received_station_id_idx
  ON public.de_tt_priceinfo (station_id, received);  -- note the reversed order

Since the combination is supposed to be unique (I assume), I suggest a UNIQUE constraint on (station_id, receved) instead:

ALTER TABLE de_tt_priceinfo ADD CONSTRAINT de_tt_priceinfo_station_id_received
UNIQUE (station_id, received);

The index index_station_id is mostly superseded and can probably be dropped now.
The index de_tt_priceinfo_received_station_id_idx may still have its use.

Be sure to understand the logic behind all this:

• Multicolumn index and performance
• Is a composite index also good for queries on the first field?

Query

I would also consider the basic DISTINCT ON query:

SELECT DISTINCT ON (station_id)
       station_id, e5, e10, diesel, received
FROM   de_tt_priceinfo
WHERE  received <= '2014-09-25 08:45:12'::TIMESTAMPTZ
AND    station_id = ANY ('{0C91A93A-a-b-c-d, 578C44BB-a-b-c-d, 6F2F48A8-a-b-c-d
                         , 9982BE74-a-b-c-d, A24C612B-a-b-c-d, BEC3EF55-a-b-c-d
                         , F5137488-a-b-c-d}'::varchar[])
ORDER BY station_id, received DESC;

But since you seem to have a lot of rows per station, that's not going to shine. Instead:

SELECT *
FROM  (
   VALUES
     ('0C91A93A-a-b-c-d'::varchar)
    , ('578C44BB-a-b-c-d')
    , ('6F2F48A8-a-b-c-d')
    , ('9982BE74-a-b-c-d')
    , ('A24C612B-a-b-c-d')
    , ('BEC3EF55-a-b-c-d')
    , ('F5137488-a-b-c-d')
   ) s(station_id)
LEFT JOIN LATERAL (
    SELECT e5, e10, diesel, received
    FROM   de_tt_priceinfo
    WHERE  station_id = s.station_id
    AND    received <= '2014-09-25 08:45:12'::TIMESTAMPTZ
    ORDER  BY received DESC
    LIMIT  1
   )  p ON TRUE

This one should be dynamite in combination with above UNIQUE constraint (or an equivalent index).

Detailed explanation:

• Optimize GROUP BY query to retrieve latest record per user
• Select first row in each GROUP BY group?

Table definition

For a table with millions of rows it pays to optimize storage while easily possible. Makes everything smaller and faster.

That's how I would design it:

CREATE TABLE station (
   station_id serial PRIMARY KEY
 , station    text
 , CHECK (length(station) < 61) -- ?? optional, you decide 
);

CREATE TABLE priceinfo (
   priceinfo_id serial PRIMARY KEY
 , station_id   integer NOT NULL REFERENCES station ON UPDATE CASCADE
 , received     timestamptz NOT NULL DEFAULT now(),
 , e5           integer  -- price in 0.1 Cent
 , e10          integer  -- price in 0.1 Cent
 , diesel       integer  -- price in 0.1 Cent
 , CONSTRAINT priceinfo_station_id_received UNIQUE (station_id, received)
);

CREATE INDEX priceinfo_received_idx ON public.priceinfo (received);

The row size in priceinfo would be 60 bytes (24 heap tuple header + null bitmap; 32 bytes data; 4 bytes item identifier), as compared to 94 bytes (24 + 66 + 4) in your original table. That's assuming 16-character string like in your example. Everything will be ~ 36 % smaller (or more?) and considerably faster.

The crucial index on (station_id, received) is down to 8 bytes of data per index tuple instead of 32 bytes or even much more (!) - each plus overhead. In addition, handling integer numbers for station_id is generally faster than text with a COLLATION on top of it.

Details:

• Configuring PostgreSQL for read performance

Query would fetch station_id from station table first, which is cheap.

Prices are stored as integer numbers signifying 0.1 Cent. (4 bytes instead of 10 bytes for your original numeric(4,3) Multiply with 0.1 to get Cent or 0.001 to get € for display. Very simple and fast.

UUID

The string in the error message looks considerably longer and actually like a regular UUID number:

871828b4-37e5-419c-b7a5-cdbe1e1c0148

If so, use the uuid data type. Whether you adopt my design of keep your old. At least switch to the uuid data type for a big overall gain in every aspect:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

You probably have checkpoint_timeout set very high on the production system, and archive switches are being driven by checkpoint_segments, probably in conjunction with a low setting of archive_timeout.

But, checkpoint_segments has no effect during recovery. Restartpoints during recovery are driven exclusively by checkpoint_timeout. Since recovery can replay files far faster than it took production to create them, that means you have a huge accumulation of old xlog files waiting for a restartpoint to allow them to be cleared out.

One solution is to lower checkpoint_timeout by quite a bit for the system undergoing recovery, and then set it back once recovery has finished. This will cause restartpoints to happen more often, allowing the log files to be removed sooner.

Another option is to simply have a script delete the files from pg_xlog during recovery once a newer file has been restored and renamed. If the system crashes during recovery, it may then have to go refetch some of those deleted files from the archive.

The system was designed to tolerate the case where files are removed from the archive once they are fetched and so cannot be re-fetched. That is why it stores all the fetched files in pg_xlog until a restartpoint; so it has all the data it needs to restart the recovery if the recovery crashes. As long as you don't remove files from the archive, you can remove them from pg_xlog.

This situation will thankfully be improved in 9.5.