Stuck: how can I disable automatic json array unwrapping?
I have json data which is based on arbitrary levels of hierarchy. The json objects I need to query are at an unknown depth in the document, which means I have to use the recursive wildcard member accessor (.**) The problem is, the path to these unknown depths also contain arrays, and when .** accessor encounters them, they're automatically unwrapped, so I end up with duplicate results for the same json object in data. My understanding of the mechanics may be incomplete but basically it is the situation explained here at the end of 9.16.2 here, just before 9.16.2.1 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS Using the strict mode solves my problem, but then I have another one: the data model producing this json model has optional fields, so it is perfectly OK for json data not to have some fields, and queries to return empty results in this case. In strict mode, using these optional fields results in an error. So I have duplicate data if I use .** (which I must), and errors if I use lax mode for json structure (which I must). Is there any way I can get strict mode behaviour from .** without using strict mode? Is there any other way of achieving the behaviour of // operator from XPath? The JsonPath page here refers to .. operator for JsonPath, which corresponds to .** as far as I can see. Am I looking at the wrong feature in postgres's json support? Cheers, Seref
Re: Stuck: how can I disable automatic json array unwrapping?
As always, the solution is in the postgresql documentation. Using exists() from a filter prior to accessing an optional field solves my problem. I can have both strict mode and access to optional fields without getting an error this way. On Fri, Oct 15, 2021 at 6:50 PM Seref Arikan wrote: > I have json data which is based on arbitrary levels of hierarchy. The json > objects I need to query are at an unknown depth in the document, which > means I have to use the recursive wildcard member accessor (.**) > > The problem is, the path to these unknown depths also contain arrays, and > when .** accessor encounters them, they're automatically unwrapped, so I > end up with duplicate results for the same json object in data. My > understanding of the mechanics may be incomplete but basically it is the > situation explained here at the end of 9.16.2 here, just before 9.16.2.1 > https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS > > Using the strict mode solves my problem, but then I have another one: the > data model producing this json model has optional fields, so it is > perfectly OK for json data not to have some fields, and queries to return > empty results in this case. In strict mode, using these optional fields > results in an error. > > So I have duplicate data if I use .** (which I must), and errors if I use > lax mode for json structure (which I must). > > Is there any way I can get strict mode behaviour from .** without using > strict mode? Is there any other way of achieving the behaviour of // > operator from XPath? The JsonPath page here refers to .. operator for > JsonPath, which corresponds to .** as far as I can see. Am I looking at the > wrong feature in postgres's json support? > > Cheers, > Seref > > >
Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance
Sorry, this was meant to go to the whole group: Very interesting!. Great work. Can you clarify how exactly you're running postgres in your tests? A specific AWS service? What's the test infrastructure that sits above the file system? On Thu, Jul 17, 2025 at 11:59 PM Pierre Barre wrote: > Hi everyone, > > I wanted to share a project I've been working on that enables PostgreSQL > to run on S3 storage while maintaining performance comparable to local > NVMe. The approach uses block-level access rather than trying to map > filesystem operations to S3 objects. > > ZeroFS: https://github.com/Barre/ZeroFS > > # The Architecture > > ZeroFS provides NBD (Network Block Device) servers that expose S3 storage > as raw block devices. PostgreSQL runs unmodified on ZFS pools built on > these block devices: > > PostgreSQL -> ZFS -> NBD -> ZeroFS -> S3 > > By providing block-level access and leveraging ZFS's caching capabilities > (L2ARC), we can achieve microsecond latencies despite the underlying > storage being in S3. > > ## Performance Results > > Here are pgbench results from PostgreSQL running on this setup: > > ### Read/Write Workload > > ``` > postgres@ubuntu-16gb-fsn1-1:/root$ pgbench -c 50 -j 15 -t 10 example > pgbench (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1)) > starting vacuum...end. > transaction type: > scaling factor: 50 > query mode: simple > number of clients: 50 > number of threads: 15 > maximum number of tries: 1 > number of transactions per client: 10 > number of transactions actually processed: 500/500 > number of failed transactions: 0 (0.000%) > latency average = 0.943 ms > initial connection time = 48.043 ms > tps = 53041.006947 (without initial connection time) > ``` > > ### Read-Only Workload > > ``` > postgres@ubuntu-16gb-fsn1-1:/root$ pgbench -c 50 -j 15 -t 10 -S > example > pgbench (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1)) > starting vacuum...end. > transaction type: > scaling factor: 50 > query mode: simple > number of clients: 50 > number of threads: 15 > maximum number of tries: 1 > number of transactions per client: 10 > number of transactions actually processed: 500/500 > number of failed transactions: 0 (0.000%) > latency average = 0.121 ms > initial connection time = 53.358 ms > tps = 413436.248089 (without initial connection time) > ``` > > These numbers are with 50 concurrent clients and the actual data stored in > S3. Hot data is served from ZFS L2ARC and ZeroFS's memory caches, while > cold data comes from S3. > > ## How It Works > > 1. ZeroFS exposes NBD devices (e.g., /dev/nbd0) that PostgreSQL/ZFS can > use like any other block device > 2. Multiple cache layers hide S3 latency: >a. ZFS ARC/L2ARC for frequently accessed blocks >b. ZeroFS memory cache for metadata and hot dataZeroFS exposes NBD > devices (e.g., /dev/nbd0) that PostgreSQL/ZFS can use like any other block > device >c. Optional local disk cache > 3. All data is encrypted (ChaCha20-Poly1305) before hitting S3 > 4. Files are split into 128KB chunks for insertion into ZeroFS' LSM-tree > > ## Geo-Distributed PostgreSQL > > Since each region can run its own ZeroFS instance, you can create > geographically distributed PostgreSQL setups. > > Example architectures: > > Architecture 1 > > > PostgreSQL Client >| >| SQL queries >| > +--+ > | PG Proxy| > | (HAProxy/| > | PgBouncer) | > +--+ >/\ > / \ >SynchronousSynchronous >ReplicationReplication > / \ >/\ > +---++---+ > | PostgreSQL 1 || PostgreSQL 2 | > | (Primary) |◄--►| (Standby) | > +---++---+ > || > | POSIX filesystem ops | > || > +---++---+ > | ZFS Pool 1 || ZFS Pool 2 | > | (3-way mirror)|| (3-way mirror)| > +---++---+ >/ | \ / | \ > / | \/ | \ > NBD:10809 NBD:10810 NBD:10811 NBD:10812 NBD:10813 NBD:10814 > ||| ||| > ++++++++++++ > |ZeroFS 1||ZeroFS 2||ZeroFS 3||ZeroFS 4||ZeroFS 5||ZeroFS 6| >
Re: PostgreSQL on S3-backed Block Storage with Near-Local Performance
Thanks, I learned something else: I didn't know Hetzner offered S3 compatible storage. The interesting thing is, a few searches about the performance return mostly negative impressions about their object storage in comparison to the original S3. Finding out what kind of performance your benchmarks would yield on a pure AWS setting would be interesting. I am not asking you to do that, but you may get even better performance in that case :) Cheers, Seref On Fri, Jul 18, 2025 at 11:58 AM Pierre Barre wrote: > Hi Seref, > > For the benchmarks, I used Hetzner's cloud service with the following > setup: > > - A Hetzner s3 bucket in the FSN1 region > - A virtual machine of type ccx63 48 vCPU 192 GB memory > - 3 ZeroFS nbd devices (same s3 bucket) > - A ZFS stripped pool with the 3 devices > - 200GB zfs L2ARC > - Postgres configured accordingly memory-wise as well as with > synchronous_commit = off, wal_init_zero = off and wal_recycle = off. > > Best, > Pierre > > On Fri, Jul 18, 2025, at 12:42, Seref Arikan wrote: > > Sorry, this was meant to go to the whole group: > > Very interesting!. Great work. Can you clarify how exactly you're running > postgres in your tests? A specific AWS service? What's the test > infrastructure that sits above the file system? > > On Thu, Jul 17, 2025 at 11:59 PM Pierre Barre wrote: > > Hi everyone, > > I wanted to share a project I've been working on that enables PostgreSQL > to run on S3 storage while maintaining performance comparable to local > NVMe. The approach uses block-level access rather than trying to map > filesystem operations to S3 objects. > > ZeroFS: https://github.com/Barre/ZeroFS > > # The Architecture > > ZeroFS provides NBD (Network Block Device) servers that expose S3 storage > as raw block devices. PostgreSQL runs unmodified on ZFS pools built on > these block devices: > > PostgreSQL -> ZFS -> NBD -> ZeroFS -> S3 > > By providing block-level access and leveraging ZFS's caching capabilities > (L2ARC), we can achieve microsecond latencies despite the underlying > storage being in S3. > > ## Performance Results > > Here are pgbench results from PostgreSQL running on this setup: > > ### Read/Write Workload > > ``` > postgres@ubuntu-16gb-fsn1-1:/root$ pgbench -c 50 -j 15 -t 10 example > pgbench (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1)) > starting vacuum...end. > transaction type: > scaling factor: 50 > query mode: simple > number of clients: 50 > number of threads: 15 > maximum number of tries: 1 > number of transactions per client: 10 > number of transactions actually processed: 500/500 > number of failed transactions: 0 (0.000%) > latency average = 0.943 ms > initial connection time = 48.043 ms > tps = 53041.006947 (without initial connection time) > ``` > > ### Read-Only Workload > > ``` > postgres@ubuntu-16gb-fsn1-1:/root$ pgbench -c 50 -j 15 -t 10 -S > example > pgbench (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1)) > starting vacuum...end. > transaction type: > scaling factor: 50 > query mode: simple > number of clients: 50 > number of threads: 15 > maximum number of tries: 1 > number of transactions per client: 10 > number of transactions actually processed: 500/500 > number of failed transactions: 0 (0.000%) > latency average = 0.121 ms > initial connection time = 53.358 ms > tps = 413436.248089 (without initial connection time) > ``` > > These numbers are with 50 concurrent clients and the actual data stored in > S3. Hot data is served from ZFS L2ARC and ZeroFS's memory caches, while > cold data comes from S3. > > ## How It Works > > 1. ZeroFS exposes NBD devices (e.g., /dev/nbd0) that PostgreSQL/ZFS can > use like any other block device > 2. Multiple cache layers hide S3 latency: >a. ZFS ARC/L2ARC for frequently accessed blocks >b. ZeroFS memory cache for metadata and hot dataZeroFS exposes NBD > devices (e.g., /dev/nbd0) that PostgreSQL/ZFS can use like any other block > device >c. Optional local disk cache > 3. All data is encrypted (ChaCha20-Poly1305) before hitting S3 > 4. Files are split into 128KB chunks for insertion into ZeroFS' LSM-tree > > ## Geo-Distributed PostgreSQL > > Since each region can run its own ZeroFS instance, you can create > geographically distributed PostgreSQL setups. > > Example architectures: > > Architecture 1 > > > PostgreSQL Client >| >| SQL queries >| >
