0

The model is [(stock_id, period, ts), open, high, low, close, last, volume]

We write new prices for all stocks (120,000) each minute and delete old once when they go out of retention time. It doesn't matter if retention cleanup will happen automatically or we'll do daily cleanup process. Periods are 1 minute, 10 minutes, 1 day, 7 days and about 1,000 to 10,000 last points data retention for each period. Currently there are about 200,000,000 rows (40GB) in postgres table and the performance is sometimes bad, especially if autovacuum is triggered. When we query we usually pull the whole period to show chart, but sometimes we access specific dates.

We try to understand if there are some optimizations that can be done in postgresql itself or maybe some other database will work better. NoSQL databases that I consider for testing are MongoDB and Aerospike with the following document model. The document key would be stock_id in this case.

{
 1111: {
   "symbol": "aapl",
   "hist_1m": {
      12334: {
        "last": 123.1,
        "close": 123.2,
        ...
      }, ...
   },
   "hist_10m": {...},
   "hist_1d": {...},
   "hist_7d": {...},
 },
2222: {...} 

But I'm really not sure about performance of such model where each sub-hash will be 1000 to 10,000 hashes or maybe even more in the future. In aerospike there's per-map max size of write-block-size (1mb default), in Mongo the limit per document is 16mb but it doesn't explain the performance. How fast or efficient are individual additions to large collections in MongoDB or Aerospike? Are they happen in-place or require loading whole collection in memory and rewriting it back to disk, like it would be with postgresql jsonb column? In postgres we just do thousands of inserts and it's very fast. The performance issue happens because of nature of infinite insert/delete - gaps in the tablespace and autovacuum. Also very it takes quite a long time to do global backfills.

I even thought about timeseries DBs like Prometheus or InfluxDB but really don't think they're designed for realtime high-load querying.

Please suggest in which database/model you think is ideal for this purpose. I searched for existing question with the same requirement (as I think thousands of systems store similar historical data in some way).

Thanks.

2
  • 1
    Performance isn't generally a reason to change database systems, since when done right, they're all relatively equal in performance for most use cases. Your use cases sound pretty normal, and something that should be achievable in PostgreSQL. Did I read correctly that you're inserting 120,000 rows per minute? How often do you read data? What is the transaction isolation level you're using? You'll probably find the best path forward is to ask a question specifically regarding how to improve your performance issues that you're currently experiencing in PostgreSQL.
    – J.D.
    Jun 16 at 13:50
  • This is mostly about MongoDB NoSQL database. Performance is dependent upon various factors including, the database design (the structure of the data, size, volume, field types, indexes, etc.), the important operations/queries and the hardware related aspects like RAM, the type of storage drive, etc., and the installation. Its not clear what are 1111: and 12334: in the model, and the data structure matters much in querying, indexing and performance.
    – prasad_
    yesterday

1 Answer 1

0

The worst work you can give a database is to delete data. This is because the database will try to maintain copies of the original data for mvcc.

This gets a lot better when you can just drop partitions. Here is where Postgres with the timescaleDB extension comes to rescue. Drop the partition[s] that are out of the retention window.

When timescaleDB is implemented to support the cleaning, it should not be a problem running Postgres. It also gets rid of most of the vacuum problems since they are caused by the delete operations.

1
  • I'm using RDS so Timestale as extension is impossible. Doing daily partitions as a regular PostgreSQL partitions is impossible, we're talking about thousands of partitions and what's worse scanning all of them to get the chart. Hence I'm also looking at alternative DBs as well. Jun 17 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.