PostgreSQL – Storing Historical Data in Database vs On Disk

Your table is big, and so is any index spanning the whole table. Assuming that:

• only new data (with timestamp = now()) is entered
• existing rows are neither changed nor deleted.
• you have data since 2012-01-01 but queries are predominantly on the current year (?)

I would suggest a partial, multi-column (covering!) index:

CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

Only include the time range that is queried regularly. Effectiveness deteriorates over time with new entries. Recreate the index from time to time. (You may need to adapt your queries.) See linked answer below.

The last column value is only included to get index-only scans out of this. Aggressive autovacuum setting may help by keeping the visibility map up to date, like @jjanes already mentioned.

The partial index should fit into RAM more easily and stay there longer.

You may need to include this WHERE condition in queries to make the planner understand the index is applicable to the query, like:

SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411 
AND   "timestamp" > '2014-06-11 0:0' 
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

Since your query is summing a lot of rows (rows=13578), this is going to take some time, even with an index-only scan. It shouldn't be anywhere near 50 seconds, though. Less than a second on any halfway decent hardware.

Related _{(but ignore CLUSTER and FILLFACTOR, both are irrelevant if you can get index-only scans out of this)}:

• Index optimization with dates

Aside:
Since you currently have an index on (prop_id, "timestamp"), the additional index on just (prop_id) may cost more than it's worth:

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

Your method is easily improved, however you're mostly there.

When you run this

pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

Instead do

pg_dump [OLD_DATABASE] --schema-only --section pre-data > pre.sql

Then load your dumped data, produced by this (from all the servers)

pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql

But, then you need to add the add the constraints.. So go back to your original database and run

pg_dump [OLD_DATABASE] --schema-only --section post-data > post.sql

And, then load post.sql. This will add the constraints.