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

20 October 2025 10:33

I will follow your advice, thank you both very much.

Okay, I will take a look at them.

But I also want to ask you about ScyllaDB — if you know it… is it strong in this area?

PostgreSQL

20 October 2025 10:29

You’re right — handling a single node, especially in SQL, is much better than dealing with multiple nodes, because the latter involves certain trade-offs that we need to be very careful about.

PostgreSQL

20 October 2025 10:21

Generally — not.

Well, at least it's simpler to handle on one node than on several ones.

PostgreSQL

20 October 2025 10:17

@ella_conan read this talk

PostgreSQL

20 October 2025 10:16

I provide analytical services for some large companies, and we’re still in the early stages. The current data is just for testing, but once we start running analytics for real companies, the data volume will be huge.

In fact, I’m using Elasticsearch, but I want to test whether PostgreSQL would be faster. The data for just one month in Elasticsearch reached 60 TB.

PostgreSQL

20 October 2025 10:13

Sure, but the db does not need to fit in ram

PostgreSQL

20 October 2025 10:12

10TB of storage is 200€ (HDD) or 900€ (3x 4T SSD)
10TB of memory is ~37k€ for just the sticks, and good luck finding a system that can fit allo of that in a single node

PostgreSQL

20 October 2025 10:09

Crap, I didn't read all the conversation. As usual I do.

Byt trust me, that was just a guess!

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.

PostgreSQL

20 October 2025 10:29

I will follow your advice. Thank you both very much!

PostgreSQL

20 October 2025 10:29

Btw, often analytical data could be cut to unconnected pieces. Multi tenants, archives, etc...

Than it doesn't need anything distributed.

Also, maybe the postgres isn't the first choice for OLAP. Rather powerful in algorithms, but not that concentrated on repeatable aggregates grazing.

Maybe you'd try to look at kafka or clickhouse, to start with?

PostgreSQL

20 October 2025 10:19

I know it’s difficult to handle this amount of data on a single node, that’s why I asked for a reliable and powerful way to distribute it.

PostgreSQL

20 October 2025 10:17

Btw, just don't. Not for relational databases. It's 2025, even for a 50TB database — the cost of SSD and x8 penalty (x2 space reserve, x2 RAID, x2 standby/spare) is a few man-months. It just doesn't payback itself.

PostgreSQL

20 October 2025 10:15

At pgday naples i had the chance to attend this very interesting talk about huge databases https://pgday.at/wp-content/uploads/2025/09/manydata.pdf

PostgreSQL

20 October 2025 10:13

Fact is, attempts to use split hardware and add more parallelisation penalty is much more expensive than buying it in one piece.

PostgreSQL

20 October 2025 10:10

10TB also can fully fit in memory.

PostgreSQL

20 October 2025 10:07

Let me guess — it barely fits on a powerful smartwatch...