PostgreSQL

20 October 2025 10:07

Like, what is your use case

PostgreSQL

20 October 2025 10:05

Just don't.

(Every first newby wants to do that. Always unsucsessfuly.)

Postgres is a relational database — which means it's all about consistency, locking and predictable synchronisation.
This means that speed penalty in attempt to do that via networks — range from very large to enormous.
Highly skilled architect may get something useful out of it — when costs of hardware upgrade gets more than "very large". When there are nonlinear hardware costs increase vs speed — and that is very powerful server systems, hundreds of thousands of dollars.

You don't have neither the server that can't be upgraded nor knowledge of how to attempt to distribute something — so just don't.

PostgreSQL

20 October 2025 10:03

100GB is not that big at all (can fully fit in memory).
10TB is not an issue either, fits on a single HDD or a couple of fast nvme drives and assuming you have [covering] indexes and you optimize your queries you will probably not feel the difference

PostgreSQL

20 October 2025 09:56

How big is your database?

PostgreSQL

20 October 2025 09:53

Also double entry accounting FTW

PostgreSQL

20 October 2025 09:51

I am building an ERP (with a full accounting module), and I remember in the beginning, I was slapping SERIALIZABLE everywhere because I was scared of dirty/phantom reads, especially for things like stock operations 😀

PostgreSQL

20 October 2025 09:07

Hello,

I have a PostgreSQL database, and I want to make it a distributed database. What is the best way to achieve this?

PostgreSQL

19 October 2025 20:03

banks use select..for update to prevent race condition and read committed in most rdbms. It's not some new problem.

PostgreSQL

19 October 2025 19:28

First, if you had the defaults and did nothing special. You should only see the data AS IT WAS the INSTANT your query ran. This is what XMIN/XMAX help provide. The OPPOSITE of this IMNSHO is "Dirty Read" which MSFT SQL allows. You can NEVER trust a query that allows Dirty Reads (for banking or important data).

Second, we (or I) would need more detail on what exactly you are trying to accomplish.
If you are writing a routine that will "Update the Account Balance (a single field off of the main account record).
And assuming you are the only process allowed to do it. AND it's allowed to be "OFF" in between runs of your routine. Then you are probably fine. You SUM() all of the transactions and calculate the value, write it.

But if they transact WHILE your code is running, you won't see those. And you could be overstating or understanding your field. (And that's okay, IMO, because they should queue up a request for you to recalculate).

I can debug this and say "This feels safe". Mostly because we control the one place that updates this balance.
Now, if the logic is balance = balance + "RECENT" transactions. Then I start getting scared.
And if anyone else can update the balance and the above line is true. Then I am panicked.
Errors can be carried forward.

PostgreSQL

19 October 2025 14:57

I don't quite understand...

PostgreSQL

19 October 2025 14:03

REPEATABLE READ doesn't guarantee that no data will change! It just presents the data to your session like it was at the beginning of the transaction.

PostgreSQL

19 October 2025 13:39

Some thoughts (not an answer! I've worked in banking, but never in products related to actual accounts):
a) there's also REPEATABLE READ
b) you'll experience serialization errors (albeit less of them) in the lower isolation levels
c) you'll definitely want to make sure you've got your locking properly done (SELECT ... FOR [NO KEY] UPDATE etc.)
d) if a banking system could only run on _proper_ SERIALIZABLE, Oracle wouldn't be where it is... 🤪

PostgreSQL

17 October 2025 07:52

Both are proprietary products derived from Open Source Postgres.

There is no such thing as an "Enterprise License of PostgreSQL", as PostgreSQL is Free as in Free Beer and as in Free Speech.

You can buy _support_ for the Open Source Version, and you can buy the aforementioned products (and some more), but those are Closed Source Products, or at the very least you lose the freedom of choosing your support provider.

PostgreSQL

17 October 2025 04:51

Hi all,
Any opening for Powerbi developer role I am a Microsoft Certified Powerbi developer have total 5 years of experience
Can join in 3-4 days willing to Relocate PAN india

PostgreSQL

16 October 2025 08:20

Like it was for previous BIND results.

PostgreSQL

20 October 2025 10:07

Even if the system is multi-tenant?

PostgreSQL

20 October 2025 10:03

Fact is, hardware has became so cheap it's better to just add more than make your software complex

PostgreSQL

20 October 2025 10:01

Currently, the database is about 100 GB, but it’s expected to grow to 10 TB.

PostgreSQL

20 October 2025 09:55

I know there are some complexities and trade-offs, but I want to distribute the data because it’s large. When it’s not distributed, queries become slow — I have complex queries.

PostgreSQL

20 October 2025 09:53

Switched to SELECT … FOR UPDATE , the default isolation level, and “everything happens in a transaction, and we commit just once or rollback everything” and I haven’t yet looked back

PostgreSQL

20 October 2025 09:44

The "best way" is to not make it distributed. There are different extensions such as citus to shard your database, but you pay with having eventual consistency and/or your distributed system being as slow as the slowest node

PostgreSQL

19 October 2025 21:09

In fact, a lot of financial projects don't need even a single SELECT FOR UPDATE even at RC.

Typical CRUD and account transfers just don't need it.
(Massive updates or batch workers sometimes do need or get to use it, though).

PostgreSQL

19 October 2025 19:57

There is a database company with marketing based solely on poor understanding of isolation levels.

https://x.com/being_mudit/status/1973957092697337868

PostgreSQL

19 October 2025 18:22

READ COMMITTED isn't a problem.
Poor understanding of isolation levels and their glitches, however, is a problem.

It looks like you don't understand what's going on at all — so yes, better don't fall from SERIALIZABLE for any access of the database. In particular, don't do any reads on replicas. Also, always use a transaction for logically connected statements.

Then the most striking feature should be that any transaction may fail and you have to check failures on every step and make appropriate actions. Not a much of a trouble.

(Also, don't rely on a sequence order — they are not transactionally ordered, don't use DDL on a working database — they are sometimes also non-transactional — and don't use system fields like xmin/ctid/etc — for the same reason).

PostgreSQL

19 October 2025 14:46

It means before transaction get data?

PostgreSQL

19 October 2025 13:43

I have a service that is separate from our main project logic and I want to completely separate the payment from this logic. Then imagine that the transaction table is separate and the balance is also separate, meaning we have a balance record for each user. Do you think it's right that if I turn on repqtbe red, which guarantees that no more data will change, I should use this, okay, instead of the serial numbers.

PostgreSQL

19 October 2025 13:33

Friends, in your opinion, in a banking system that is concrete, if I use read commit in the database instead of serializable, is there a problem? I mean, I want to have a situation where each record is separate, for example, the balance of each person, so I don't run into the phantom problem.

PostgreSQL

17 October 2025 04:51

How to purchase Enterprise license of PostgreSQL and what is difference between PostgreSQL Pro & EDB

PostgreSQL

16 October 2025 11:18

Ok, update; seems like I've been looking at a log that had already been through a grep. There is (obviously) some "real" query on the following lines, which were filtered. In other words: NVM

PostgreSQL

16 October 2025 08:19

Nope.

But it is deleted at this point, so this seems plausible.