PostgreSQL

27 October 2025 18:48

also: If you use JavaScript in the browser somewhere that numbers there are treated as floats and truncated as such

PostgreSQL

27 October 2025 17:06

so numeric will give number in output (for typeorm) right? if I will change just 'float' to 'numeric' is it enough? or need to check the behaviour by the way?

PostgreSQL

27 October 2025 16:52

thank you guys, I guess, I will change just that columns which is using for money (price, discount, amount, etc), thank you for advice

PostgreSQL

27 October 2025 16:47

If it where me, I would use numeric at the DB level and BigDecimal in JS for anything handling money.

It upto you to make a judgement call on mixing things.

PostgreSQL

27 October 2025 16:45

As Someone who made this mistake, trust me fixing it was NICE.

You simply cannot use Real/Float because they are not exact.
if you use Numeric, you will be in good shape. The math will always work.

Yes, it's bigger.
The only other way is more complicated. Use a BigInt with an IMPLIED decimal point.
(The COBOL Solution).

PostgreSQL

27 October 2025 16:37

The biggest issue with using real or double precision types is they can't hold exact base 10 fractions, eg: 0.1 will loose some exactness. Which means you need to compute any money based maths at a higher number of decimal places and then round stuff.

Numeric and float can interop it'll just depend what you're storing where.

But life is much easier if you just use Numeric and something like BigDecimal in your app layer.

PostgreSQL

27 October 2025 16:33

That likely depends on your client library. But the numeric type is what you need to keep exactness.

PostgreSQL

27 October 2025 16:26

hi, for floating numbers, what the best use like for discount, price, purchase double precision or decimal?

PostgreSQL

26 October 2025 12:05

With that level of problem understanding I'd suggest to run fast from any attempts to implement cache.

Also, find middle+ architect of OLTP databases to review your architecture decisions on a regular basis.

(Also, switch to at least REPEATABLE READ in postgres. Maybe even better to SERIALIZABLE. Read committed has some non-trivial behaviour.)

PostgreSQL

26 October 2025 11:54

Coherent cache is a really tough task, with high probability of bugs. Considering money accounts and OLTP load — I'd suggest to avoid it until after it would be absolutely necessary.

Just don't. Why do you think it would be faster at lower hardware cost? Even that goal is very non-trivial and usually isn't met.

PostgreSQL

26 October 2025 08:59

Only if you serialize transactions, have only one connection and know that nothing else will edit these

PostgreSQL

26 October 2025 03:43

Also, 32 cores, 64GB, 4 SSD (NVME) is my new laptop!!! Just got Telegram working on it!
Don't even have PG installed yet. LOL

PostgreSQL

26 October 2025 02:45

Sorry to say that, but 96 cores/256G RAM is a pretty mean server, not a huge one now.
(If it'd be 32-40 cores and two sockets — I would say low-tier mean).

PostgreSQL

23 October 2025 20:03

Ask amazon support. Isn't a managed database price is paid exactly to delegate such questions to their stuff?

PostgreSQL

23 October 2025 17:05

Hi all,

We are running an rds instance with 600GB data and recently we are facing high CPU issues as we checked the performance insight on AWS it's showing COMMIT is causing high CPU.

Anyone have any suggestions

PostgreSQL

27 October 2025 17:18

I don't know typeorm. I would hope it would map to the BigDecimal JS type. There is no point use numeric and the DB level and floating point at the app level.

PostgreSQL

27 October 2025 16:55

You might need to for quantity too, depending on usecases and where you do math.

PostgreSQL

27 October 2025 16:49

It's also important when dealing with money, to know what form of rounding you need to use where.

PostgreSQL

27 October 2025 16:45

The other fun thing, is the numeric rounding function does slightly different things to the double precision rounding function. Which would be important in money situations.

PostgreSQL

27 October 2025 16:43

I agree with you, but now the biggest problem, I already integrated float (double precision) into project, and I didn't see any strange behavior in production, because the code is checking the number, rounding and etc before inserting to the table. Yes, unfortunatle I didn't know about their big differences, and now when I'm asking chatgpt, it says need to use decimal or numeric instead of double precision. Is it big dileme if I will use numeric just for new tables and new columns for the future? cause I don't want to change whole project(

PostgreSQL

27 October 2025 16:33

I used float in typeorm (double precision in postgre) for all my projects, and now I need to integrate purchase transactions, can I make it as hybrid in this situation?

PostgreSQL

27 October 2025 16:32

is it giving number as string in output?

PostgreSQL

26 October 2025 14:09

Double that, plus a bit.
With the RAID and the boatload of storage!
And enough open slots for more storage.
Again, we usually shoot for 5 years of service.

Upside of PG is that we spun up some cloud based services for lower tier stuff. Very cost effective!
Oracle wanted a kings ransom to do any of it.

PostgreSQL

26 October 2025 11:58

Before that i will presist in to the hard for get it again i will give in cache if it is in else i get it again from database

PostgreSQL

26 October 2025 08:59

You have, right now, two sources of truth that happen to be equal right now, but this is not guaranteed

PostgreSQL

26 October 2025 07:57

I have a question
Look, I'm working on a payment gateway now.
When a client requests a transaction, I save it in the database.
Then I put it in the cache because when I want to verify, if it's in the cache, it takes it from the cache. If it's not, it takes it from the main database.
And the data is consistent. The program is concerned about the integrity of the data, even in the cache.
Do you think my use of the cache here is correct?
And do you think there's anything wrong with me concentrating these factors?

PostgreSQL

26 October 2025 03:41

Yeah, the $$$ said otherwise. LOL.

But the points still stand. Doing things the way PG likes is what gives you the best performance.
"You cannot out EXERCISE a bad diet..."
-AND-
"BAD CODE can crush any server..."

PostgreSQL

25 October 2025 22:01

We just took a VERY performant Oracle solution to PG. To be safe, we bought MANY TIMES the CPUs, and 4x the memory (96 cores, 192 threads and 256GB of ram).

We are FINE now. But quickly, the UPDATE xxx SET VALUE = VALUE + 1 where...
Was CRUSHING.

TRUNCATE TABLE creates locks until the code finishes. Ughh.

And no function caching. So we used a materialized view. (But updating it 10-20,000 times per day with 4000 rows to update only 1 row, and 1 column on each refresh is a bit overkill).

That said. Overall it works quite well. We fixed all of the above issues using the PG way of doing things.
And we are humming along.


The worst performance we see is 10% total CPU usage. And during a PG_DUMP, we have to turn off routines that DROP tables, and truncate tables. (Also, we have BILLIONS of rows in many tables, and 100s of Millions in our core few tables).

Just do everything the way PG likes, and things go well.

The only other issue we have, is that we have MILLIONS of calls to 15ms procedures that do decent work.
AND if we leave pg_stat_statements running, we start suffering The Observer Effect from LOCKING LOCKS.

The mere locking of these records to collect stats (pg_watch, node_exporter) was driving locks, and any spike in search activity from the web can trigger a cascade of locking issues.

Turning off pg_stat_statements... We now rarely hit 2-3% on the CPU. And we never cascade locks like that.

AGAIN. Design for PG. And test. We tested on various levels of AWS Hardware.
Our INSANELY HUGE DB server was SIMPLE Insurance, and the client accepted that.

In hindsight, half the machine would be plenty good!

PostgreSQL

23 October 2025 19:58

Checking fk constraints maybe. Or running triggers?

PostgreSQL

20 October 2025 10:36

I never touched it — so I don't know.