Обсуждение: Is postgresql's json strong consistency or eventually consistency?
I have searched all archives, but have no results. Please help me.
On Thu, 2025-01-16 at 15:25 +0300, anlex N wrote: > I have searched all archives, but have no results. Please help me. That's probably because the question doesn't make much sense. A single PostgreSQL instance (=cluster) enforces consistency, and it doesn't matter which data type you are using. So I guess the answer should be "strong consistency". Yours, Laurenz Albe
On 1/16/25 04:25, anlex N wrote: > I have searched all archives, but have no results. Please help me. Consistency of what? -- Adrian Klaver adrian.klaver@aklaver.com
Hello Adrian, Laurenz. Have you tried postgresql's json in your everyday life? How fast is it? how is it than mongodb?
On Thu, Jan 16, 2025 at 6:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/16/25 04:25, anlex N wrote:
> I have searched all archives, but have no results. Please help me.
Consistency of what?
--
Adrian Klaver
adrian.klaver@aklaver.com
On 1/23/25 19:46, anlex N wrote: > Hello Adrian, Laurenz. Have you tried postgresql's json in your everyday > life? How fast is it? how is it than mongodb? To get the answers you want it would be better to describe what you are trying to achieve by using JSON. > > On Thu, Jan 16, 2025 at 6:57 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 1/16/25 04:25, anlex N wrote: > > I have searched all archives, but have no results. Please help me. > > Consistency of what? > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Jan 16, 2025 at 2:02 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2025-01-16 at 15:25 +0300, anlex N wrote:
> I have searched all archives, but have no results. Please help me.
That's probably because the question doesn't make much sense.
A single PostgreSQL instance (=cluster) enforces consistency,
and it doesn't matter which data type you are using.
So I guess the answer should be "strong consistency".
I guess what the original author was trying to ask is how many databases have to acknowledge a transaction before it is considered committed, stuff that's called in mongodb write_concern and read_concern.
@anlex, PG at its heart is not a distributed system. When you talk about consistency in PG you normally mean foreign key consistency or similar, properties where data in one table is somehow related to data in another table. A slightly different meaning of consistency comes when you talk about transaction isolation levels.
All of this is different from Mongodb's read/write consistency. In Mongodb a write can go to one machine while the subsequent read can fetch data from another. So, you worry about linearizability, causal consistency and such things. The only slightly similar question in PG is "Can I see data somehow, on the master or a replica, that has been written to the database but where the master has not yet acknowledged the commit to the client?"
The short answer is, yes. As soon as the commit record has been written to the WAL, it is being transferred and replayed on streaming replicas. After that the master might still have to do things like waiting for a synchronous replica to acknowledge the transaction before the transaction becomes visible on the master. On a fast enough replica, the transaction can, hence, become visible before it's visible on the master.
You can control that behavior with the `synchronous_commit` setting in combination with `synchronous_standby_names`. You can tell the DB, for instance, a transaction should be considered as committed only when 3 out of 5 replicas have acknowledged it.
If you set synchronous_commit=remote_apply and configure N out of N replicas in synchronous_standby_names, that would likely give you a distributed database with strong consistency where you can direct writes to the master and read-only load to the replicas. But I guess nobody in his right mind would do that unless your database is rarely written to.
On Fri, Jan 24, 2025 at 4:48 AM anlex N <an0291170@gmail.com> wrote:
Hello Adrian, Laurenz. Have you tried postgresql's json in your everyday life? How fast is it? how is it than mongodb?
My honest opinion, JSON(B) in PG is great as data transfer format but not so much for storage. Here is an example. Up to last year I had a table in my DB with rows consisting of a bunch of numbers in many columns. Over maybe 15 years of business that table had grown to a few TB in size. Then the development team decided they needed more flexibility and started to fill a new table with the same information in JSON. After only 1 year of business that table is now in the same size range.
Another example, the developers came up with a new table to store the result of the JSON response they got from a 3rd party application. That worked for a while and the table behaved normally. Suddenly it started growing by a few GB a day. Turns out the 3rd party had decided to include an MB-sized picture in the JSON response. Our team then faithfully stored all of that crap unfiltered in the DB.
That is not an answer to your original question, of course. How fast it is depends very much on the use case. If you are talking about access methods alone, the ability of indexing JSON and such, then PG is on par with mongo if not better. But nobody but you can give you a definite answer relating to your situation.