Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wa1HA-001TWi-1M for pgsql-general@arkaria.postgresql.org; Thu, 18 Jun 2026 01:10:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wa1H9-008PL0-0k for pgsql-general@arkaria.postgresql.org; Thu, 18 Jun 2026 01:10:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wa1H8-008PKr-1h for pgsql-general@lists.postgresql.org; Thu, 18 Jun 2026 01:10:34 +0000 Received: from mail-lj1-x22f.google.com ([2a00:1450:4864:20::22f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wa1H5-000000010Xx-1E93 for pgsql-general@lists.postgresql.org; Thu, 18 Jun 2026 01:10:33 +0000 Received: by mail-lj1-x22f.google.com with SMTP id 38308e7fff4ca-399735e64d7so5051071fa.0 for ; Wed, 17 Jun 2026 18:10:30 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781745029; cv=none; d=google.com; s=arc-20240605; b=bdei19MT1gtdl0Xt6ALHSD4pxXpToKhIwbU/3QxFEGI7X0njmZMM/06yReIevhlKiI 3LOec5atYh18XkckeWmHhjslfXDOHzoP8OoxxACtbngO/2PNxOWk4iYZZuIEr3RwIDwG yFki5V4xcjQaLMw63myOmaHdjRSLxcc/RsqokXi101Tn2xJRSHBqlXrCPFU5JwfM3NdV z15xDAk5ck76ScKTVH/aZH0Bwj33nmULfkKAJvmQ5NgbNMfiBT495ldtYyfA0b+nTFd6 W1ulbSsN5iZwW1cCcvyjuizKS5LWFM3d9Tq+q9AsPQeigkNuStxdOAsJIZDpalG/Nuzj Szgg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=i+DPbjqqil4YV+OKeTN04lAH1ntZwKnkyId3CTGJ6lQ=; fh=VY5cxTRU9C/i+vIC4GIDRboRKxL+rfl1Y0l8O1Oclek=; b=Jb9ma/SLloqdwifEbkiuZR4/6Ej7RdkPZiREOTjf87uhdjFcGT6EQs61hbxvf6jSqK iL0yOb3N6IGzdqiot9SMTMozV5ykLow9jiHBTK7hG/zkpTMgmQN6T0BJy9asNsii+K9x XxKg6jif7g5YHWyYE+G4QKUbgOAj0/FRq3ftdtQXe8Q8TixIQDWIF2oCqu2/TlrK9kw5 Uc4LMSXN4kjrq2t1DbLrxtSGe7gzCJFdPadl2qolWFOrUjyxqASzRtG3khUIeW44GRXf J5OKayc0PksSq3ifm5XNq79PEa6GFa6cSEXeRDZJy5uqMdA3gSNMKhnxGEYSDxlI2zTf 7mwQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ideasculptor.com; s=google; t=1781745029; x=1782349829; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=i+DPbjqqil4YV+OKeTN04lAH1ntZwKnkyId3CTGJ6lQ=; b=OSGLHWpKyW6NJe7qaahGFJQbzno0qRTKhikmsZoa7pu9B9n0vpHgB/z7UJbkRjYlZk ZMGqumlAqQ0N00VJZ+vJuPm4adY+KVjDwg7BXRkIof53knTH/B+eWPjUI1Lu7a9ZkNF4 9E7HiHLw/sRKfb+z145wQLhAp2HyRd+vqbnH+5PtsZbvbbLrLgE3gwriW5qeKtQQchfb gnSItR+jcopGKisoWlMzTa0aXJmDE3ejnIDJ1nYSItVIsmENhhIJwNdz4i3gzvkBzVB8 aJ9D+ItR+VwC+BCYsftimRbiLFrQeZLjRyNgvAKZO2JYBQj2qdm1G8jrvd/FhbI+4BVu RHcQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781745029; x=1782349829; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=i+DPbjqqil4YV+OKeTN04lAH1ntZwKnkyId3CTGJ6lQ=; b=rKHlOsOaTbq61az6MMQ7srq5qCK/FlICWhIbK1kQUpK8PRZzsWCJU6E0Z4QSu3uka0 2z7I6cKDylUJk2U2Y1TkKr0WAaWG0woggl0EbIhiX8e/iBz0ieyJIIRQcklKRCVrbHfZ 8u7Lqsm0Hlx9iz0yd4YQV+v6x0RjUbOxPt7BPe6+iwoq01zxw6OGL3UALraOCYyKCJdD L+VqP7nFrqQWJkWDTf5z/+yA3BlUFD+vj+qZ3hmlZowzaL++OeFEpXMhYd2MFhZCJfLe +V5aq9ioOlKwaxL4UhWC0UWw/aurLqh9STjP8As3yv3ALcNSLY9bHEyWHNT37I9Btr5Y Y+Tw== X-Forwarded-Encrypted: i=1; AFNElJ8LoeQIF6k9DJiCTWplg9xucPxDvmRY3DnZ5G7BKQKOvigGQPcFQbOoBHEjAFietKpOmkqtWPATZyOrFQu8@lists.postgresql.org X-Gm-Message-State: AOJu0YwY/KkpntJ8RCecIbOxKMmQI9rjLVRihiAJP8vJiI/Xhdym/N4F wmms5VpQLTCfxewc7fjn2HJFTAcz57seDUtFZ+h54+55mTxlVYMOiJQqZ3QYs6gopii/xMRpa0e aqA2KcaQpQfSS3wOWIbguPufqAKhnyXT4XwsACsVOoS7/zwcIJa9C0wQ= X-Gm-Gg: AfdE7cnCQHBlhNGrDuVmcL3wDJslLT5TFVdfmR/usoHaZF1n2O3s3DWTncdSm78txF/ 6GxiIU199Z1dEajWnsqHw4JdBwY04+Tne8f39Vic9gmHNTXjtiEugartZ7Yrmlq3OKQddgqdFSu IisersOmL9Ex6/dZSg/HxOHrwappWCm+LunevxwHh+E0iEgAS2+ovT++2rE9fcQN7Db++fABrSM xv5r0fZbak9KXKdcWprXSXvL02LUTmEz59FdDWQ+5CNja1PMj8xcmFxT58p2a0oJ+vUIt2FtilK 9sSPFozqz2mkkot5qHLxa9R5E0EudXU= X-Received: by 2002:a05:651c:514:b0:394:3b62:b6aa with SMTP id 38308e7fff4ca-39969afbb71mr17389551fa.12.1781745028690; Wed, 17 Jun 2026 18:10:28 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Gendler Date: Wed, 17 Jun 2026 18:10:16 -0700 X-Gm-Features: AVVi8CdjeqRupCyBgWHF_sJCn6iEddlMjTET901zE-s_FU7zYV7vX7r0FpIfBDY Message-ID: Subject: Re: Suitability of postgres for high cardinality high volume usecase? To: Brent Wood Cc: Sohum Banerjea , "pgsql-general@lists.postgresql.org" , Tim McEwan , Waseem Girach , "guyren.howe@theclimateriskgroup.com" Content-Type: multipart/alternative; boundary="00000000000084038c06547cd787" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000084038c06547cd787 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable My familiarity isn't terribly recent, but fits what Brent has described so well that I can't think of anything to add. In my case, it was building environmental sensors (hundreds of thousands of sensors per building delivering data with periods from 1 second to 10 seconds and extending back over years) with aggregation queries that need to be aligned with polygons from floorplans. I'd give similar advice about premature schema 'optimization' and definitely explore all of the window function and aggregation capabilities that Postgres offers. On Wed, Jun 17, 2026 at 4:54=E2=80=AFPM Brent Wood wrote: > Hi, > > We have a Timeseries database using Postgtres/Postgis/Timescale with > around 400 billion sensor readings from sensors deployed on research > vessels at sea since 1990 stored in it. Very performant. > > A different scenario to what you describe, as we are storing the sensor > readings as a timestamped hstore. We use this because the number of > readings per timestamp (and which readings they are) is highly variable. > You, however are describing a few fixed values per location. > > An example of how this is used: > > For a deepwater camera deployment we plot vessel & camera positions live > in QGIS. > The SQL extracts vessel & camera GPS lat & long coordinate values, > converts these to points & assembles them into linestrings so we can see > this on screen. > QGIS auto refreshes the layer every 5 seconds. > It is a hot query, retrieving only a few values from the entire database, > taking < 50ms (from the 400,000,000,000 readings in the db) > > This massively leverages Timescale indexes, which won't apply in your > case, but suggests you may not have any performance issues. > > One aspect I suggest you consider: > Even when indexed, spatial queries (point in poly) can take a while with > complex polygons (lots of vertices). > For frequent or slow spatial queries you can add an indexed boolean colum= n > representing each polygon & populate it with a flag as to whether each > record is inside or outside the specified polygon. > This runs the spatial query once & essentially caches the result for > future use. Much faster, and the approach might help with some non-spatia= l > queries as well. > > I also suggest you not get overly concerned about possible performance > issues requiring complex schemas & workarounds unless you know you need t= o. > Postgres is generally pretty quick, so try a simple implementation, run > some queries & find out if you have a performance issue that needs > resolving before assuming you do. At that stage you'll also have a much > better idea as to the specific problem which is a big help when looking a= t > fixing it. > > Postgres has 2 built in percentile functions, percentile_cont() & > percentille_disc() that may provide what you require. There is no median > function as such, but that is just a percentile call with a 0.5 parameter= . > > > Cheers, > > Brent Wood > > > > ------------------------------ > *From:* Sohum Banerjea > *Sent:* Wednesday, 17 June 2026 3:21 pm > *To:* pgsql-general@lists.postgresql.org < > pgsql-general@lists.postgresql.org> > *Cc:* Tim McEwan ; Waseem Girach < > waseem.girach@theclimateriskgroup.com>; > guyren.howe@theclimateriskgroup.com > *Subject:* Suitability of postgres for high cardinality high volume > usecase? > > Hello, > > I am trying to determine the suitability of Postgres for a significant > climate risk modelling project. > > We are batch processing a large (500 million) collection of > geographical points. For each point, we store ~6 dimensions of various > risks (total cardinality of several millions of floats per > geographical point). > > We need to perform various ad-hoc aggregations on geographical subsets > of the values associated with these points. These aggregations could > require median/percentiles, so they won't be as simple as mean/sum, > and we expect we may have to write custom aggregations for some cases. > > Because we may want to run computations that would use PostGIS > features (certainly polygon containment; potentially others), and > because our existing applications already use Postgres, we have some > degree of preference to do this in Postgres. > > I'd like to know if anyone here has successfully built a system to run > this sort of computation at this scale in Postgres. If so, what sort > of schema design did you use? Columnar stores referencing spatially > indexed row stores that contain the spatial references, sharded by > geographical region? What sort of throughput did you achieve? > > I'm also interested in any general observations folks may have about > this project. Perhaps we should use Clickhouse (for the main data) > together with Postgres (for the GIS computations)? Perhaps our float > dataset should live outside any kind of oltp/olap database at all? > Something else? > > And finally, if you have developed a system like this, are you > available to assist us with building this system on a consulting > basis? > > Thanks in advance, > =E2=80=94Sohum > > > > *Brent Wood * > Principal Technician - GIS and Spatial Data Management > +64-4-386-0529 > 301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand > Earth Sciences New Zealand > [image: Earth Sciences New Zealand] > The Institute of Geological and Nuclear Sciences Limited and the National > Institute of Water and Atmospheric Research Limited joined to become the > New Zealand Institute for Earth Science Limited. We are known as Earth > Sciences New Zealand. For more information on the Earth Sciences transiti= on click > here . > > *Notice:* This email and any attachments may contain information which is > confidential and/or subject to copyright or legal privilege, and may not = be > used, published or redistributed without the prior written consent of Ear= th > Sciences New Zealand. If you are not the intended recipient, please > immediately notify the sender and delete the email and any attachments. A= ny > opinion or views expressed in this email are those of the individual send= er > and may not represent those of Earth Sciences New Zealand. > > For information about how we process data and monitor communications > please see our privacy policy . > --00000000000084038c06547cd787 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
My familiarity isn't terribly recent, but fits what Br= ent has described so well that I can't think of anything to add.=C2=A0 = In my case, it was building environmental=C2=A0sensors (hundreds of thousan= ds of sensors per building delivering data with periods from 1 second to 10= seconds and extending back over years) with aggregation queries that need = to be aligned with polygons from floorplans. I'd give similar advice ab= out premature schema 'optimization' and definitely explore all of t= he window function and aggregation capabilities that Postgres offers.
=
On Wed, Jun 17, 2026 at 4:54=E2=80=AFPM Brent Wood <brent.wood@earthsciences.nz&= gt; wrote:
Hi,

We have a Timeseries database using Postgtres/Postgis/Timescale with around= 400 billion sensor readings from sensors deployed on research vessels at s= ea since 1990 stored in it. Very performant.

A different scenario to what you describe, as we are storing the sensor rea= dings as a timestamped hstore. We use this because the number of readings p= er timestamp (and which readings they are)=C2=A0 is highly variable. You, h= owever are describing a few fixed values per location.

An example of how this is used:

For a deepwater camera deployment we plot vessel & camera positions liv= e in QGIS.
The SQL extracts vessel & camera GPS lat & long coordinate values, = converts these to points & assembles them into linestrings so we can se= e this on screen.
QGIS auto refreshes the layer every 5 seconds.=C2=A0
It is a hot query, retrieving only a few values from the entire database, t= aking < 50ms (from the 400,000,000,000 readings in the db)=C2=A0

This massively leverages Timescale indexes, which won't apply in your c= ase, but suggests you may not have any performance issues.

One aspect I suggest you consider:
=E2=80=82=E2=80=82=E2=80=82=E2=80=82Even when indexed, spatial queries (poi= nt in poly) can take a while with complex polygons (lots of vertices).=C2= =A0=C2=A0
=E2=80=82=E2=80=82=E2=80=82=E2=80=82For frequent or slow spatial queries yo= u can add an indexed boolean column representing each polygon & populat= e it with a flag as to whether each record is inside or outside the =E2=80= =82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82specified= polygon.=C2=A0
=E2=80=82=E2=80=82=E2=80=82=E2=80=82This runs the spatial query once & = essentially caches the result for future use. Much faster, and the approach= might help with some non-spatial queries as well.

I also suggest you not get overly concerned about possible performance issu= es requiring complex schemas & workarounds unless you know you need to.= Postgres is generally pretty quick, so try a simple implementation, run so= me queries & find out if you have a performance issue that needs resolving before assuming you do. At that sta= ge you'll also have a much better idea as to the specific problem which= is a big help when looking at fixing it.

Postgres has 2 built in percentile functions, percentile_cont() & perce= ntille_disc() that may provide what you require. There is no median functio= n as such, but that is just a percentile call with a 0.5 parameter.=C2=A0


Cheers,

Brent Wood



From:=C2=A0Sohum Banerjea <sohum.banerjea@climaterisk.com.au><= br> Sent:=C2=A0Wednesday, 17 June 2026 3:21 pm
To:=C2=A0pgsql-general@lists.postgresql.org <pgsql-general@lists.po= stgresql.org>
Cc:=C2=A0Tim McEwan <tim.mcewan@theclimateriskgroup.com>; Was= eem Girach <waseem.girach@theclimateriskgroup.com>; guyren.howe@the= climateriskgroup.com <guyren.howe@theclimateriskgroup.com>
Subject:=C2=A0Suitability of postgres for high cardinality high volu= me usecase?
=C2=A0
Hello,

I am trying to determine the suitability of Postgres for a significant
climate risk modelling project.

We are batch processing a large (500 million) collection of
geographical points. For each point, we store ~6 dimensions of various
risks (total cardinality of several millions of floats per
geographical point).

We need to perform various ad-hoc aggregations on geographical subsets
of the values associated with these points. These aggregations could
require median/percentiles, so they won't be as simple as mean/sum,
and we expect we may have to write custom aggregations for some cases.

Because we may want to run computations that would use PostGIS
features (certainly polygon containment; potentially others), and
because our existing applications already use Postgres, we have some
degree of preference to do this in Postgres.

I'd like to know if anyone here has successfully built a system to run<= br> this sort of computation at this scale in Postgres. If so, what sort
of schema design did you use? Columnar stores referencing spatially
indexed row stores that contain the spatial references, sharded by
geographical region? What sort of throughput did you achieve?

I'm also interested in any general observations folks may have about this project. Perhaps we should use Clickhouse (for the main data)
together with Postgres (for the GIS computations)? Perhaps our float
dataset should live outside any kind of oltp/olap database at all?
Something else?

And finally, if you have developed a system like this, are you
available to assist us with building this system on a consulting
basis?

Thanks in advance,
=E2=80=94Sohum



Brent Wood
Principal Technician - GIS and Spatial= Data Management
+64-4-386-= 0529
301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
Earth Sciences New Zealand
The Institute of Geological and Nuclear Sciences Limited and the National I= nstitute of Water and Atmospheric Research Limited joined to become the New= Zealand Institute for Earth Science Limited. We are known as Earth Science= s New Zealand. For more information on the Earth Sciences transition click here.

Notice: This email and any attachments may contain information which= is confidential and/or subject to copyright or legal privilege, and may no= t be used, published or redistributed without the prior written consent of = Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and d= elete the email and any attachments. Any opinion or views expressed in this= email are those of the individual sender and may not represent those of Ea= rth Sciences New Zealand.

For information about how we process data and monitor communications please= see our priva= cy policy.
--00000000000084038c06547cd787--