agora inbox for [email protected]help / color / mirror / Atom feed
using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions 6+ messages / 5 participants [nested] [flat]
* using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions @ 2024-02-01 12:46 Evan Czaplicki <[email protected]> 2024-02-01 16:01 ` Re: using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions Tom Lane <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Evan Czaplicki @ 2024-02-01 12:46 UTC (permalink / raw) To: [email protected] I noticed that OpenSSL has a CRYPTO_set_mem_functions <https://www.openssl.org/docs/man3.2/man3/CRYPTO_set_mem_functions.html; function: If no allocations have been done, it is possible to “swap out” the default > implementations for OPENSSL_malloc(), OPENSSL_realloc() and OPENSSL_free() > and replace them with alternate versions. > But a different technique is used in contrib/pgcrypto/openssl.c <https://github.com/postgres/postgres/blob/REL_16_STABLE/contrib/pgcrypto/openssl.c#L52-L56; to work around the different allocation system of OpenSSL: To make sure we don't leak OpenSSL handles on abort, we keep OSSLCipher > objects in a linked list, allocated in TopMemoryContext. We use the > ResourceOwner mechanism to free them on abort. I see the particulars might have changed on the master branch though! CRYPTO_set_mem_functions must be called *before* any uses of malloc/free though, so I believe it needs to be called right after the OPENSSL_init_ssl <https://www.openssl.org/docs/man3.2/man3/OPENSSL_init_ssl.html; calls (e.g. in src/backend/libpq/be-secure-openssl.c <https://github.com/postgres/postgres/blob/REL_16_STABLE/src/backend/libpq/be-secure-openssl.c#L102; and src/interfaces/libpq/fe-secure-openssl.c <https://github.com/postgres/postgres/blob/REL_16_STABLE/src/interfaces/libpq/fe-secure-openssl.c#L85...;) for this to be possible. Would it be desirable to do this? If not, why is the TopMemoryContext approach a better option? I do not understand the code quite well enough to evaluate the tradeoffs myself yet! Best, Evan P.S. Searcthing all time with this query <https://www.postgresql.org/search/?m=1&q=CRYPTO_set_mem_functions&l=&d=-1&s=r;, I found one thread in 2018 that mentions CRYPTO_set_mem_functions, but in a different capacity. I hope I did not miss some other mention of it on the email lists! ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions 2024-02-01 12:46 using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions Evan Czaplicki <[email protected]> @ 2024-02-01 16:01 ` Tom Lane <[email protected]> 2024-02-02 08:57 ` Re: using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions Evan Czaplicki <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2024-02-01 16:01 UTC (permalink / raw) To: Evan Czaplicki <[email protected]>; +Cc: [email protected] Evan Czaplicki <[email protected]> writes: > I noticed that OpenSSL has a CRYPTO_set_mem_functions > <https://www.openssl.org/docs/man3.2/man3/CRYPTO_set_mem_functions.html; > function: >> If no allocations have been done, it is possible to “swap out” the default >> implementations for OPENSSL_malloc(), OPENSSL_realloc() and OPENSSL_free() >> and replace them with alternate versions. > But a different technique is used in contrib/pgcrypto/openssl.c >> To make sure we don't leak OpenSSL handles on abort, we keep OSSLCipher >> objects in a linked list, allocated in TopMemoryContext. We use the >> ResourceOwner mechanism to free them on abort. > Would it be desirable to do this? If not, why is the TopMemoryContext > approach a better option? I do not understand the code quite well enough to > evaluate the tradeoffs myself yet! Seems to me that these address different purposes. If we put in a CRYPTO_set_mem_functions layer, I doubt that we'd have any good idea of which allocations are used for what. So we could not replace what pgcrypto is doing with a simple MemoryContextReset (even if we cared to assume that freeing an OSSLCipher involves only free() operations and no other resources). I think the only real win we'd get from such a layer is that OpenSSL's allocations would be better exposed for accounting purposes, eg the pg_backend_memory_contexts view. That's not negligible, but I don't find it a compelling reason to do the work, either. regards, tom lane ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions 2024-02-01 12:46 using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions Evan Czaplicki <[email protected]> 2024-02-01 16:01 ` Re: using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions Tom Lane <[email protected]> @ 2024-02-02 08:57 ` Evan Czaplicki <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Evan Czaplicki @ 2024-02-02 08:57 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] Thank you for the explanation! I looked into what OpenSSL does when freeing the underlying OSSLCipher resources, and as you say, it is more than just a free. There is sometimes aditional values to free, and they go through and overwrite the data in a special way regardless. So I see why the TopMemoryContext approach is needed either way. My particular interest was in using the BIGNUM implementation from OpenSSL within my project, but the BIGNUM frees also do more than just freeing. So it seems there is no way around the TopMemoryContext in my case either. I could imagine that there may be some effect on the particular character of memory fragmentation that comes with their malloc/free vs palloc/pfree, but that's definitely outside of my personal purposes and outside of my expertise to evaluate even at a high level! So thank you again for helping me understand this! Happy to have a much clear understanding of the TopMemoryContext approach! Evan On Thu, Feb 1, 2024 at 5:01 PM Tom Lane <[email protected]> wrote: > Evan Czaplicki <[email protected]> writes: > > I noticed that OpenSSL has a CRYPTO_set_mem_functions > > <https://www.openssl.org/docs/man3.2/man3/CRYPTO_set_mem_functions.html; > > function: > > >> If no allocations have been done, it is possible to “swap out” the > default > >> implementations for OPENSSL_malloc(), OPENSSL_realloc() and > OPENSSL_free() > >> and replace them with alternate versions. > > > But a different technique is used in contrib/pgcrypto/openssl.c > > >> To make sure we don't leak OpenSSL handles on abort, we keep OSSLCipher > >> objects in a linked list, allocated in TopMemoryContext. We use the > >> ResourceOwner mechanism to free them on abort. > > > Would it be desirable to do this? If not, why is the TopMemoryContext > > approach a better option? I do not understand the code quite well enough > to > > evaluate the tradeoffs myself yet! > > Seems to me that these address different purposes. If we put in a > CRYPTO_set_mem_functions layer, I doubt that we'd have any good idea > of which allocations are used for what. So we could not replace what > pgcrypto is doing with a simple MemoryContextReset (even if we cared > to assume that freeing an OSSLCipher involves only free() operations > and no other resources). I think the only real win we'd get from > such a layer is that OpenSSL's allocations would be better exposed > for accounting purposes, eg the pg_backend_memory_contexts view. > That's not negligible, but I don't find it a compelling reason to > do the work, either. > > regards, tom lane > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Suitability of postgres for high cardinality high volume usecase? @ 2026-06-17 03:21 Sohum Banerjea <[email protected]> 2026-06-17 23:53 ` Re: Suitability of postgres for high cardinality high volume usecase? Brent Wood <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Sohum Banerjea @ 2026-06-17 03:21 UTC (permalink / raw) To: [email protected]; +Cc: Tim McEwan <[email protected]>; Waseem Girach <[email protected]>; [email protected] 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, —Sohum ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suitability of postgres for high cardinality high volume usecase? 2026-06-17 03:21 Suitability of postgres for high cardinality high volume usecase? Sohum Banerjea <[email protected]> @ 2026-06-17 23:53 ` Brent Wood <[email protected]> 2026-06-18 01:10 ` Re: Suitability of postgres for high cardinality high volume usecase? Sam Gendler <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Brent Wood @ 2026-06-17 23:53 UTC (permalink / raw) To: Sohum Banerjea <[email protected]>; [email protected] <[email protected]>; +Cc: Tim McEwan <[email protected]>; Waseem Girach <[email protected]>; [email protected] <[email protected]> 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 column 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-spatial queries as well. I also suggest you not get overly concerned about possible performance issues requiring complex schemas & workarounds unless you know you need to. 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 at 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 <[email protected]> Sent: Wednesday, 17 June 2026 3:21 pm To: [email protected] <[email protected]> Cc: Tim McEwan <[email protected]>; Waseem Girach <[email protected]>; [email protected] <[email protected]> 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, —Sohum 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 [Earth Sciences New Zealand]<https://earthsciences.nz; 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 transition click here<https://niwa.co.nz/about-niwa/science-sector-reforms;. 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 Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and delete 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 Earth Sciences New Zealand. For information about how we process data and monitor communications please see our privacy policy<https://earthsciences.nz/privacy-policy;. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suitability of postgres for high cardinality high volume usecase? 2026-06-17 03:21 Suitability of postgres for high cardinality high volume usecase? Sohum Banerjea <[email protected]> 2026-06-17 23:53 ` Re: Suitability of postgres for high cardinality high volume usecase? Brent Wood <[email protected]> @ 2026-06-18 01:10 ` Sam Gendler <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Sam Gendler @ 2026-06-18 01:10 UTC (permalink / raw) To: Brent Wood <[email protected]>; +Cc: Sohum Banerjea <[email protected]>; [email protected] <[email protected]>; Tim McEwan <[email protected]>; Waseem Girach <[email protected]>; [email protected] <[email protected]> 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 PM Brent Wood <[email protected]> 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 column > 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-spatial > queries as well. > > I also suggest you not get overly concerned about possible performance > issues requiring complex schemas & workarounds unless you know you need to. > 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 at > 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 <[email protected]> > *Sent:* Wednesday, 17 June 2026 3:21 pm > *To:* [email protected] < > [email protected]> > *Cc:* Tim McEwan <[email protected]>; Waseem Girach < > [email protected]>; > [email protected] <[email protected]> > *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, > —Sohum > > > > *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] <https://earthsciences.nz; > 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 transition click > here <https://niwa.co.nz/about-niwa/science-sector-reforms;. > > *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 Earth > Sciences New Zealand. If you are not the intended recipient, please > immediately notify the sender and delete 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 Earth Sciences New Zealand. > > For information about how we process data and monitor communications > please see our privacy policy <https://earthsciences.nz/privacy-policy;. > ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-06-18 01:10 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-02-01 12:46 using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions Evan Czaplicki <[email protected]> 2024-02-01 16:01 ` Tom Lane <[email protected]> 2024-02-02 08:57 ` Evan Czaplicki <[email protected]> 2026-06-17 03:21 Suitability of postgres for high cardinality high volume usecase? Sohum Banerjea <[email protected]> 2026-06-17 23:53 ` Re: Suitability of postgres for high cardinality high volume usecase? Brent Wood <[email protected]> 2026-06-18 01:10 ` Re: Suitability of postgres for high cardinality high volume usecase? Sam Gendler <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox