Sizing an OLTP TPC-E-like workload, Part 1

I used AI tools to size an OLTP workload on an EC2 system, with DBT-5, a TPC-E-like fair-use implementation. I provided a systematic and mechanical plan for running a series of tests to determine what the appropriate scale factor is on a system.

I pre-configured the database with some settings that are known to be needed to be changed, such as shared_buffers and max_wal_size but more on this at a later time when we characterize the system behavior further to be able to tune some of these settings better.  Remember, this is an iterative process when trying to figure it out for any workload.

I decided to use Claude Fable 5.1 for this exercise and fed in the following instructions:

1. Start with the minimum valid database size with 5000 customers.
2. Start testing with 1 user and increase by 1 user up to 2 times the number of logical processors.
3. Run all tests with a 1 hour steady state as that is expected to be long enough to establish a stable result.
4. Observe the TPC-E specification for valid transaction rates to determine what the appropriate database size is for the maximum observed transaction rate.
5. It is expected that we will have to continue bisecting the database size to find the highest legal transaction rate allowed for this system.
6. Continue to validate that the number of users that produced the most throughput continues to produce the most throughput at each database size.

The following chart illustrates the sum of all the testing from starting at a 5000 customer database, up to a 97,000 customer database:

We need to zoom in a little bit to see that the best result for this system is at 32,000 customers with 24 users:

It's worth mentioning that during this exercise, Claude also ran some smoke tests at various times to make sure everything was working.  There were some minor fixes, but a significant improvement was to actually spend the time to allow multiple Trade Results and Market Feed transactions to be handled concurrently.  It's been pointed out at least a couple times over the years that this test kit was only allowing 1 to be executed at a time.  So these fix-ups did require starting this exercise over more than a couple of times.

Thanks goes to Amazon for providing AWS credits and access to Kiro and Claude.

Performance Farm July 2026 Update

 

The PostgreSQL Performance Farm is still not quite a reality, but I think there is some hope on the horizon.

After some discussions with various folks at AWS about large scale OLTP testing with PostgreSQL on EC2, I was introduced to Farrah Campbell last spring who has been helping open source software maintainers.  She hooked me up with some credits that I used to sign up for Kiro Powers.  It's been quite a while since I have done any significant performance testing and the OLTP test kits we developed back in the OSDL days could use some updating, in particular this DBT-5 kit.

Shortly after that, I caught up with Mila Zhou about the AWS Open Source Credits Program because doing any large scale OLTP testing in the cloud is going to take a serious amount of credits.  As luck would have it, I was granted enough credits to start working on sizing up a system.

I'm currently thinking a TPC-E-like workload might be a good stress test for PostgreSQL on EC2 with its more balance i/o to processing requirements compared to a TPC-C, so I've started sizing up a r5b.4xlarge instance type with as many block devices as it can attach to

Keep an eye out here and on Blue Sky as I start posting updates over the coming weeks.

Acknowledged Individuals in the PostgreSQL Release Notes: 2026 Edition

I shared a chart, in 2022, showing where PostgreSQL contributor gifts are mailed to.  Here's an updated chart (click to zoom in.)

To quickly recap: the PostgreSQL community has been sending gifts to those who contribute directly to a new major release since version 12 came out.  There has now been 6 releases since then and version 19 is slated to be released this fall.

This chart can be construed as a gross indication of where code, testers, reviewers, bug reports, and documentation are coming from.  Thus it is interesting to see things like which countries have the most people, have been growing, or even to just see how many countries are involved with PostgreSQL.

But it's not an exact measure because this is based on the number of people who claim their gift.  Only about half the people in the release notes claim their gift.  It's not very easy to get in touch with everyone.  If you suspect you might be in the release notes or can help someone claim their gift, please take a look at the release notes (links can be found in the documentation or on the community wiki) and reach out to us by e-mail at contributor-gifts <at> postgresql.org to confirm.

PostgreSQL Performance Farm 2024 Progress Update

It feels like it was time to revisit the PostgreSQL Performance Farm that Tomas Vondra envisioned back in 2010.  Between that and several Google Summer of Code iterations starting in 2018, the project just didn't seem to gain enough traction.  See Ilaria Battiston's presentation in 2022 for a demo.

I spent a few days proofing whether something like BuildBot might work on a whim.  It was to see if I could get something basic working in order to get a feel for whether leveraging an established continuous integration framework might be worth the trade offs from building our own system.  The result was a system running 4 TPC-derived workloads that are simply running the tests quickly, not necessarily interestingly, just to produce data.

I added some additional desired functionality after a few more days:

• Trigger only when there are code changes.
• Define Buildbot worker specific test and PostgreSQL parameters.
• Overrides to the Buildbot worker defined test and PostgreSQL parameters.
• Submit patches against PostgreSQL and the test kits.

How I implemented the added functionality probably needs some review, but I liked how things were looking enough to spend a little more time on it.

Next I wanted to see how much effort is needed to mine the data, especially since the results of the tests are not captured in a structured way.   I'm pretty sure it would take me much longer than a few days to write a new BuildBot view, so I instead opted to try scripting something to munge data and plot metrics.  I declared success by being able to quickly do so.  Here's an example of results from one system vanillaleaf, a Buildbot worker, showing the DBT-2 test metric per commit per branch:

The PostgreSQL community has access to a couple of systems for processor and memory intensive workloads, thanks to Equinix Metal, OSUOSL and IBM.  Both systems are continuing to run small tests so if building an interface to peruse results is successful, then I'll have more to show in the near future.

Plotting against time

This is a short story about how quickly charts can be created between the handful of plotting engines that I've experimented with.

Over the years I've used a few different plotting engines for visualizing system characteristics for various performance work efforts.  The first requirement is to use tools that can be scripted as part of the work, then deciding between how the charts look and how long it took to generate the charts.

Here is an example of an automatically generated report from an OLTP test called DBT-2, where charts are generated from system statistics and from the benchmark itself: https://osdldbt.github.io/dbt-reports/dbt2/3-tier/report.html

What I want to illustrate with the above example is that there are 10 charts on the landing page with links that expand into potentially hundreds of additional charts.

The three engines that I timed are gnuplot with cairo png, R with bitmap, and Julia with VegaLite.  Let me show how quickly they perform.

 

 

 

R was generating plots 14 times slower than gnuplot and Julia with VegaLite was generating plots 53 times slower.  After 100 charts, that turns into 5 minutes, over an hour, and more than 4 hours respectively.

My plotting needs are pretty basic, but here are some additional links to see examples of the charts that can be produced by gnuplot, R and VegaLite.  Note that Julia is capable of using a number of different engines in addition to VegaLite, just like R, and that I'm just keeping the list of examples brief.

 

• gnuplot demo gallery https://gnuplot.sourceforge.net/demo_6.0/

• R Graph Gallery https://r-graph-gallery.com/all-graphs.html

• VegaLite examples: https://vega.github.io/vega-lite/examples/

What's your favorite plotting engine?