Skip to main content
Software Engineering

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

8
  • Thanks, great ideas, Lets just say i want to do it on one server. and I need to update my PostgreSQL data in case of changes, now I have the current price of sth, and I need to compare that price to like 30,000 different numbers, then update the records, I'm more worried about this comparison, is there any way that I can efficiently do this? And also the price can change every second, so a lot of processing. Commented Feb 18, 2024 at 6:33
  • You said we have a PostgreSQL table containing diverse prices. I assume we have an index on each price column. You go on to say "I need to compare that price to like N = 30,000 different numbers". I reject the premise. The index takes you, in O(log N) time, right to the place where the numbers of interest are. The whole point of an RDBMS index is you ignore nearly all of the stored numbers, focusing on just the ones of interest. // I usually choose postgres for a production setting. I really like "sqlite+pysqlite:///:memory:" for unit tests. I hear good things about pg-mem. Go benchmark! Commented Feb 18, 2024 at 16:55
  • I feel like we are not on the same page here, so I have only ONE number, and I have 30,000 numbers on my PostgreSQL records, and all the numbers are float numbers, so does the indexing make any noticable differences since it's numbers? and as I said I'm more worried about the comparison on the scale. Commented Feb 18, 2024 at 17:12
  • I'm thinking you have not benchmarked your PoC yet. (Also, we don't use IEEE-754 float for prices, we use scaled integers.) The PoC code issues SELECT * FROM accounts WHERE price = 1234; and it immediately gets back zero or more rows. This is true whether SELECT COUNT(*) FROM accounts; gives an answer of 30,000 or three billion. The interesting thing for performance is does that WHERE clause return one row, or a lot of matching rows? We call this selectivity, and it really matters, whether we're using an RDBMS or any other technology. For lots of matches, you need to do lots of work. Commented Feb 18, 2024 at 17:19
  • Yes your right, Awwwh I did not know that, But the problem is my prices can be from no decimal value to 8, so I should use a default 10^8 for all my prices(for scaled integers)??? doesn't it make the processes harder? and for the second part, yes it can return many rows: SELECT * FROM signals WHERE stoploss > 23510.1533; then need to update them all. Are you suggesting doing it with Postgresql update method and seeing if the results are ok? what if not? Commented Feb 18, 2024 at 17:37