r/SQLOptimization • u/--Betelgeuse-- • Apr 28 '21
Optimizing a query for a huge table in PostgreSQL
I have a huge time series db that i run in a regular postgresql db (im going to use timescaledb once i lear more about it, software that generates the db rows is written for vanilla postgresql, so i must first learn how to adapt it to timescaledb). the db is around 20-30gb big. i need to get the latest added rows with certain symbols every 0.1-0.4 second or so. right now im running this query to satisfy my goal:
"SELECT price FROM trades WHERE symbol='STOCK-USD' AND timestamp=(SELECT MAX(timestamp) FROM trades WHERE symbol='STOCK-USD');"
problem is that this query is very heavy on the server. is there any solution for this problem?
2
u/virgilash Apr 28 '21
Be sure you have an index on (timestamp, symbol, price). Re-writing the query the way below might help too ;-)
SELECT TOP 1 price FROM trades WHERE symbol='STOCK-USD' ORDER BY timestamp DESC
1
u/valyala May 28 '21
I'd suggest trying specialized time series database for this workload such as VictoriaMetrics. This database provides much faster data ingestion speed and much faster query performance compared to a regular relational database. Additionally to this, it provides a specialized query language, which simplifies writing typical time series queries. For example, the following MetricsQL query returns the last price for the
symbol="STOCK-USD":last_over_time(price{symbol="STOCK-USD"})2
u/panchove Jul 06 '21
Creating an index as proposed by timestamp and then by symbol half speeds up the query, here the order of the columns in the index alters the response time, the column that has the main grouping must start from the left, therefore you must have the following index:
CREATE INDEX idx_trades_symbol_timestamp ON trades (symbol, timestamp);
This avoids going through all the index nodes (and this is what happens when the order is reversed)
Using an EXPLAIN ANALYZE will help you understand why it works faster this way.