Table of Contents

Brief

My goal is to make posts like this the SIMPLEST place on the internet to learn how to do things that caused me trouble. That way, if this is found, someone doesn’t have to do the same digging I had to do.

In this post, we’re going to run a real PostgreSQL instance inside a Docker container with a hard memory limit of 140MB. Not a toy database. A real, queryable, benchmarkable Postgres instance that you could run alongside your application on a cheap 500MB VPS, a service kiosk, or an embedded device.

I got interested in this because I wanted to run Postgres on a tiny VPS alongside other services, and every guide I found either said “just give it more RAM” or hand-waved through the configuration without explaining why each setting matters. So I dug into the PostgreSQL docs, tuned every relevant knob, and validated the whole thing actually works under load.

Everything in this post is reproducible. The full demo, including Docker Compose, the tuned config, and benchmark scripts, is available in this GitHub repo.

Why Would You Want This?

The most obvious reason: cheap VPS hosting. You can get a 500MB VPS for a few dollars a month, and if you want to run Postgres alongside your application and a reverse proxy, you need Postgres to be a good neighbor.

But VPS hosting isn’t the only use case. Think about service kiosks — those check-in terminals at airports, self-order screens at restaurants, or point-of-sale systems at retail stores. These devices often run on constrained hardware with 512MB–1GB of total RAM, and they need a local database to function when the network goes down. Running SQLite is the common answer, but if your backend already speaks Postgres and you want schema parity between the kiosk and the server, a minimal Postgres instance is a real option.

The same applies to embedded environments — industrial controllers, IoT gateways, or edge computing nodes that collect and process data locally before syncing upstream. These devices might be running on a Raspberry Pi or similar ARM boards with limited memory. If your edge application needs relational queries, transactions, or just the comfort of a real database engine, Postgres can fit if you tune it right.

And beyond any specific deployment target, understanding how PostgreSQL uses memory is valuable. Every setting we’re going to tune has a direct impact on how your database behaves. Whether you’re running on an airport kiosk, a Raspberry Pi, a 500MB VPS, or a 64GB production server, knowing what these knobs do helps you make better decisions.

The Setup

We’re using Docker Compose with a hard memory limit. This is important because it’s not just a suggestion — if Postgres exceeds 140MB, the kernel OOM-killer will terminate it. So we need to get this right.

Docker Compose

services:
  postgres:
    image: postgres:18-alpine
    container_name: postgres-minimal
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-appuser}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      POSTGRES_DB: ${POSTGRES_DB:-appdb}
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgres/postgresql.conf:/etc/postgresql/postgresql.conf:ro
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    ports:
      - "5432:5432"
    deploy:
      resources:
        limits:
          memory: 140M
        reservations:
          memory: 64M
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-appuser} -d ${POSTGRES_DB:-appdb}"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 15s

volumes:
  postgres_data:
    driver: local

A few things worth calling out:

• postgres:18-alpine — Alpine-based images are smaller and use less memory than the full Debian-based ones.
• deploy.resources.limits.memory: 140M — This is the hard ceiling. Docker enforces this via cgroups, and exceeding it means death.
• deploy.resources.reservations.memory: 64M — This tells Docker to reserve at least 64MB for the container. It won’t prevent scheduling the container on a host with less available memory, but it influences container placement on multi-container hosts.
• command: postgres -c config_file=... — We mount our custom config and tell Postgres to use it. Without this, it uses the default config baked into the image, which assumes way more memory than we have.

Environment Variables

Create a .env file next to your docker-compose.yml:

POSTGRES_USER=appuser
POSTGRES_PASSWORD=changeme
POSTGRES_DB=appdb

The Configuration: Every Setting Explained

Here’s the full postgresql.conf. We’re going to go through every single setting and explain why it’s set the way it is.

Connections

max_connections = 25
superuser_reserved_connections = 3

max_connections = 25

This is one of the most impactful settings for memory. Every PostgreSQL connection is a forked OS process, not a thread. Each idle connection costs roughly 5–10MB of memory (process overhead + stack). So 25 connections could theoretically consume 125–250MB just in connection overhead.

In practice, most connections are idle most of the time, so the actual cost is lower. But this is your lever — if you’re hitting memory limits, this is the first thing to reduce.

If your application needs more than ~20 simultaneous connections, the answer isn’t to raise this number. The answer is to put PgBouncer in front of Postgres. PgBouncer multiplexes many application connections onto a small number of server connections and adds only a few MB of overhead. It’s the standard solution for connection management at any scale, but it’s especially critical when memory is tight.

superuser_reserved_connections = 3

This reserves 3 of those 25 connections for superuser access. If your application maxes out its connections, you can still connect as a superuser to diagnose the problem. It’s a safety net, and you should always keep it.

Memory

shared_buffers = 32MB
work_mem = 2MB
maintenance_work_mem = 16MB
huge_pages = off

shared_buffers = 32MB

This is PostgreSQL’s own internal page cache — a shared memory region where it keeps frequently accessed data pages. The canonical recommendation is 25% of available RAM. On a 140MB budget, 25% would be 35MB. We round down to 32MB to leave a bit more headroom for everything else.

Here’s the thing that trips people up: shared_buffers is not the only cache Postgres uses. The operating system also maintains its own page cache, and PostgreSQL benefits from that too. So even with “only” 32MB of shared_buffers, frequently accessed data may still be cached by the OS.

Don’t set this too low though. Below about 16MB and you’ll start seeing significant performance degradation because Postgres has to go to disk (or OS cache) for almost everything.

work_mem = 2MB

This controls how much memory is available for each individual sort or hash operation within a query. This is not per-connection — it’s per-operation. A single complex query with multiple sorts and hash joins can allocate work_mem several times simultaneously.

The safe formula for sizing this is:

work_mem = (available RAM - shared_buffers) / (max_connections * 3)
         ≈ (108MB - 32MB) / (25 * 3)
         ≈ 1MB

We go with 2MB, which is slightly aggressive but reasonable for simple OLTP workloads. If you’re running complex analytical queries with large sorts, you might need more, but then you probably shouldn’t be running on 140MB of RAM either.

If a sort exceeds work_mem, PostgreSQL spills to disk (temp files). It’s slower, but it won’t blow your memory budget.

maintenance_work_mem = 16MB

This is the memory budget for maintenance operations: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, etc. These operations happen one at a time (typically), so you can afford to give them more memory than work_mem. More memory here means faster vacuuming, which is important because you never want to skip vacuuming.

16MB is a reasonable value. The default is 64MB, which we can’t afford. If you have very large tables that vacuum slowly, consider bumping this up temporarily for manual maintenance during off-hours.

huge_pages = off

Huge pages are a Linux kernel feature that uses larger memory pages (2MB instead of 4KB) to reduce page table overhead. They’re great for large shared_buffers allocations (multiple GB), but on a 140MB container they’re unnecessary overhead. They also require specific OS-level configuration that most VPS hosts don’t provide by default.

WAL & Checkpoints

wal_buffers = 4MB
min_wal_size = 80MB
max_wal_size = 256MB
checkpoint_completion_target = 0.9

wal_buffers = 4MB

The WAL (Write-Ahead Log) is PostgreSQL’s crash recovery mechanism. Every change is written to the WAL before it’s applied to the data files. wal_buffers is the in-memory buffer for WAL data before it’s flushed to disk.

The default is about 3% of shared_buffers, which would give us ~1MB. We bump it to 4MB because WAL writes are sequential and fast, and having a slightly larger buffer reduces the frequency of WAL flushes during write-heavy workloads. This setting has minimal memory impact because it’s shared across all connections.

min_wal_size = 80MB and max_wal_size = 256MB

These control how much WAL data is kept on disk between checkpoints. This is disk space, not memory, so it doesn’t directly affect our 140MB budget. But it affects performance.

min_wal_size prevents PostgreSQL from aggressively recycling WAL files when write volume is low. max_wal_size triggers a checkpoint when the WAL reaches this size, preventing unbounded disk usage.

80MB to 256MB is a reasonable range for a small instance. If you have very write-heavy workloads, you might want to increase max_wal_size, but keep in mind this increases recovery time after a crash (more WAL to replay).

checkpoint_completion_target = 0.9

During a checkpoint, PostgreSQL writes all dirty pages from shared_buffers to disk. This can cause I/O spikes if done all at once. This setting tells Postgres to spread the checkpoint I/O across 90% of the checkpoint interval, smoothing out the I/O load.

0.9 is the recommended value for almost all workloads. The default in recent PostgreSQL versions is already 0.9, but we set it explicitly to be clear about our intent.

Query Planner

effective_cache_size = 128MB
random_page_cost = 1.1
effective_io_concurrency = 100

effective_cache_size = 128MB

This is the most misunderstood PostgreSQL setting. It does not allocate any memory. It’s purely a hint to the query planner about how much total cache (shared_buffers + OS page cache) is likely available.

We set it to 128MB because even though our container is limited to 140MB, the OS page cache outside the container can still cache disk reads. This tells the planner “hey, there’s probably about 128MB of cache available total, so index scans are relatively cheap.”

If you set this too low, the planner will avoid index scans in favor of sequential scans, which is usually the wrong tradeoff on modern hardware.

random_page_cost = 1.1

This tells the query planner how expensive a random disk read is relative to a sequential read. The default is 4.0, which assumes spinning hard drives where seeking is expensive.

On SSDs (which is what most VPS providers give you) or containerized volumes, random reads are nearly as fast as sequential reads. Setting this to 1.1 tells the planner to favor index scans, which is almost always the right choice on SSDs.

effective_io_concurrency = 100

This controls how many concurrent disk I/O operations PostgreSQL can issue. The default is 16, which is conservative for modern SSDs. SSDs handle concurrent I/O much better than spinning disks, so 100 is appropriate for most SSD-backed storage.

This setting affects bitmap heap scans — a higher value lets PostgreSQL prefetch more pages concurrently, improving scan performance. It doesn’t significantly affect memory usage.

Background Workers & Autovacuum

max_worker_processes = 2
max_parallel_workers = 2
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1

autovacuum = on
autovacuum_max_workers = 1
autovacuum_vacuum_cost_delay = 20ms

max_worker_processes = 2

This limits the total number of background worker processes. Each worker is a separate OS process that consumes memory. The default is 8, which is way too many for our budget.

With 2 workers, Postgres won’t try to heavily parallelize queries, which keeps memory usage predictable. On a small instance, the overhead of spawning parallel workers often outweighs the benefit anyway.

max_parallel_workers = 2 and max_parallel_workers_per_gather = 1

These control parallel query execution. max_parallel_workers is the global cap, and max_parallel_workers_per_gather controls how many workers a single query can spawn.

With 1 worker per gather, a single query can at most spawn one additional worker. This limits the memory blast radius of any individual query.

max_parallel_maintenance_workers = 1

Same idea, but for maintenance operations like CREATE INDEX CONCURRENTLY. One worker keeps the memory footprint small.

autovacuum = on

Never, ever turn autovacuum off. Autovacuum is what prevents table bloat and transaction ID wraparound. If wraparound gets close, PostgreSQL will refuse new write transactions entirely until you run a manual VACUUM — effectively taking your database offline for writes. Even on a 140MB instance, autovacuum stays on.

autovacuum_max_workers = 1

One autovacuum worker is sufficient for a low-to-moderate write workload on a small instance. Each vacuum worker allocates its own maintenance_work_mem, so keeping this at 1 saves 16MB of potential memory usage per additional worker.

autovacuum_vacuum_cost_delay = 20ms

This throttles the autovacuum worker, making it pause for 20ms after consuming a certain amount of I/O “cost credits.” The default is 2ms in modern Postgres. We increase it to 20ms to make autovacuum less aggressive, reducing its impact on foreground query performance.

The tradeoff is that vacuuming takes longer. On a small instance with moderate writes, this is fine. If you’re inserting millions of rows per day, you might need to lower this.

Logging

log_destination = 'stderr'
logging_collector = off
log_min_duration_statement = 500
log_line_prefix = '%t [%p] %u@%d '

log_destination = 'stderr' and logging_collector = off

We send logs to stderr and let Docker handle log collection. This avoids PostgreSQL managing its own log files inside the container, which would consume disk space and add complexity.

log_min_duration_statement = 500

This logs any statement that takes longer than 500ms. On a small instance, if a query is taking more than half a second, you probably want to know about it. This is invaluable for catching slow queries without the overhead of logging everything.

log_line_prefix = '%t [%p] %u@%d '

This formats log lines with a timestamp, process ID, username, and database. Makes it much easier to correlate logs with specific connections when debugging.

Miscellaneous

dynamic_shared_memory_type = posix

dynamic_shared_memory_type = posix

PostgreSQL needs shared memory for inter-process communication. posix uses POSIX shared memory objects (shm_open), which is the most portable and well-supported option in Docker containers. The alternatives (sysv, mmap) can have issues with Docker’s default security profiles.

The Memory Budget

Here’s where all these settings land us:

With all 25 connections active, the worst case approaches ~130MB. On a real workload most connections are idle most of the time, so typical usage sits well below the 140MB limit.

Verifying It Works

Once you have the container running, you can verify the configuration was loaded correctly.

Connection Test

> ./scripts/connection-test.sh
Connecting to localhost:5432 as appuser…

                                            version                                            
-----------------------------------------------------------------------------------------------
 PostgreSQL 18.3 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit
(1 row)

         name         | setting | unit |                               short_desc                               
----------------------+---------+------+------------------------------------------------------------------------
 effective_cache_size | 16384   | 8kB  | Sets the planner's assumption about the total size of the data caches.
 maintenance_work_mem | 16384   | kB   | Sets the maximum memory to be used for maintenance operations.
 max_connections      | 25      |      | Sets the maximum number of concurrent connections.
 max_worker_processes | 2       |      | Maximum number of concurrent worker processes.
 shared_buffers       | 4096    | 8kB  | Sets the number of shared memory buffers used by the server.
 wal_buffers          | 512     | 8kB  | Sets the number of disk-page buffers in shared memory for WAL.
 work_mem             | 2048    | kB   | Sets the maximum memory to be used for query workspaces.
(7 rows)


Connection test passed.

The setting values might look odd — they’re in the unit shown in the unit column. shared_buffers = 4096 in units of 8kB is 4096 * 8KB = 32MB. PostgreSQL stores everything internally in 8KB page units.

Memory Check

> ./scripts/memory-check.sh   
22:13:07               \033[0;32m  22.0 MB\033[0m  /  140 MB limit  (\033[0;32m15%\033[0m)

  PostgreSQL internals:
 db_size | active_connections | shared_buffers | work_mem 
---------+--------------------+----------------+----------
 7678 kB |                  9 | 4096B          | 2048B
(1 row)

At idle with 2 connections, we’re sitting at about 62% of our memory limit. Plenty of headroom.

Benchmarking Under Load

The demo repo includes a bench.sh script that wraps pgbench — PostgreSQL’s built-in benchmarking tool. It runs two passes: a read-only (SELECT-heavy) run and a read-write (TPC-B-like) run.

./scripts/bench.sh          # scale=10, 5 clients, 30s each
./scripts/bench.sh 5 3 60   # smaller data set, fewer clients, longer run

Benchmark Execution

❯ ./scripts/bench.sh 5 3 60
================================================================
 PostgreSQL minimal-memory benchmark
 Target: localhost:5432/appdb
 Scale: 5  |  Clients: 3  |  Duration: 60s
================================================================

[1/3] Initialising pgbench tables (scale=5)…
dropping old tables...
creating tables...
generating data (client-side)...
vacuuming...                                                                              
creating primary keys...
done in 0.36 s (drop tables 0.02 s, create tables 0.00 s, client-side generate 0.22 s, vacuum 0.04 s, primary keys 0.07 s).

[2/3] Read-only benchmark (3 clients, 60s)…
pgbench (18.3)
starting vacuum...end.
progress: 5.0 s, 18242.9 tps, lat 0.164 ms stddev 0.043, 0 failed
progress: 10.0 s, 18254.4 tps, lat 0.164 ms stddev 0.039, 0 failed
progress: 15.0 s, 18621.6 tps, lat 0.161 ms stddev 0.023, 0 failed
progress: 20.0 s, 18574.8 tps, lat 0.161 ms stddev 0.027, 0 failed
progress: 25.0 s, 18568.9 tps, lat 0.162 ms stddev 0.025, 0 failed
progress: 30.0 s, 18511.7 tps, lat 0.162 ms stddev 0.033, 0 failed
progress: 35.0 s, 18646.4 tps, lat 0.161 ms stddev 0.026, 0 failed
progress: 40.0 s, 18643.7 tps, lat 0.161 ms stddev 0.025, 0 failed
progress: 45.0 s, 18594.8 tps, lat 0.161 ms stddev 0.025, 0 failed
progress: 50.0 s, 18690.9 tps, lat 0.160 ms stddev 0.030, 0 failed
progress: 55.0 s, 18694.5 tps, lat 0.160 ms stddev 0.021, 0 failed
progress: 60.0 s, 18487.0 tps, lat 0.162 ms stddev 0.035, 0 failed
transaction type: <builtin: select only>
scaling factor: 5
query mode: simple
number of clients: 3
number of threads: 3
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1112721
number of failed transactions: 0 (0.000%)
latency average = 0.162 ms
latency stddev = 0.030 ms
initial connection time = 6.800 ms
tps = 18546.411782 (without initial connection time)

[3/3] Read-write benchmark (3 clients, 60s)…
pgbench (18.3)
starting vacuum...end.
progress: 5.0 s, 1873.4 tps, lat 1.598 ms stddev 3.267, 0 failed
progress: 10.0 s, 2218.4 tps, lat 1.353 ms stddev 3.551, 0 failed
progress: 15.0 s, 1914.7 tps, lat 1.557 ms stddev 4.355, 0 failed
progress: 20.0 s, 1896.7 tps, lat 1.592 ms stddev 4.091, 0 failed
progress: 25.0 s, 1949.5 tps, lat 1.537 ms stddev 3.289, 0 failed
progress: 30.0 s, 2368.4 tps, lat 1.268 ms stddev 2.952, 0 failed
progress: 35.0 s, 2161.3 tps, lat 1.386 ms stddev 3.181, 0 failed
progress: 40.0 s, 2375.8 tps, lat 1.265 ms stddev 3.173, 0 failed
progress: 45.0 s, 1926.0 tps, lat 1.558 ms stddev 3.397, 0 failed
progress: 50.0 s, 2816.2 tps, lat 1.065 ms stddev 2.801, 0 failed
progress: 55.0 s, 1917.0 tps, lat 1.565 ms stddev 4.074, 0 failed
progress: 60.0 s, 1461.5 tps, lat 2.050 ms stddev 5.726, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 5
query mode: simple
number of clients: 3
number of threads: 3
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 124398
number of failed transactions: 0 (0.000%)
latency average = 1.447 ms
latency stddev = 3.638 ms
initial connection time = 7.454 ms
tps = 2072.519593 (without initial connection time)

Done.

The memory usage is stable,

(At Start)
Watching 'postgres-minimal' memory every 2s — Ctrl-C to stop.

22:24:53               19.9 MB  /  140 MB limit  ([0m)

  PostgreSQL internals:
 db_size | active_connections | shared_buffers | work_mem 
---------+--------------------+----------------+----------
 7678 kB |                  9 | 4096B          | 2048B
(1 row)


(Mid Test Under Max Load)
22:25:56               58.7 MB  /  140 MB limit  ([0m)

  PostgreSQL internals:
 db_size | active_connections | shared_buffers | work_mem 
---------+--------------------+----------------+----------
 82 MB   |                 12 | 4096B          | 2048B
(1 row)

The key thing to watch during the benchmark is memory usage. Run ./scripts/memory-check.sh --watch in another terminal while the benchmark runs to see live memory consumption. Even under load with 5 concurrent clients, memory should stay well under the 140MB limit.

Running Alongside Other Services

On a 500MB VPS

On a 500MB VPS, a rough allocation might look like:

Keep an eye on the host’s swap usage. Even a little swap activity under a database workload causes latency spikes. If you see swapping, reduce max_connections further or add PgBouncer.

On a Kiosk or Embedded Device

For kiosks and embedded devices, the memory picture looks a little different. These systems typically have one application connecting to the database, so the connection count is much lower — you could drop max_connections to 5 or even 3 and reclaim significant memory.

On a Raspberry Pi 4 (1GB model) or similar ARM board, a budget might look like:

The key difference from a VPS is that kiosks and edge devices usually have one user and one application, so you’re not worried about concurrent connections. You’re worried about the database surviving reboots, power loss, and network outages — which is exactly what PostgreSQL’s WAL gives you out of the box. That crash recovery is the main reason to pick Postgres over SQLite in these environments.

For truly constrained devices (256MB–512MB total RAM), you can squeeze even further: drop shared_buffers to 16MB, max_connections to 5, and maintenance_work_mem to 8MB. You’ll sacrifice some query performance, but the database will still function correctly.

What You Give Up

Let’s be honest about the tradeoffs:

1. Limited concurrency — 25 connections isn’t many. For anything beyond a simple application, you’ll need PgBouncer.
2. No heavy analytics — Complex queries with large sorts or hash joins will spill to disk frequently with only 2MB of work_mem.
3. Slower maintenance — VACUUM and CREATE INDEX are slower with only 16MB of maintenance_work_mem.
4. No parallel queries — With 2 worker processes, you’re essentially running single-threaded queries.

These are all acceptable tradeoffs for a small application, side project, or embedded deployment. If you’re running a high-traffic production system, you need more RAM. But for a personal project, a staging environment, a kiosk that needs offline data persistence, or a low-traffic production app, this configuration handles real workloads just fine.

Takeaways

1. PostgreSQL is surprisingly flexible — You can run it on very little memory if you understand what each setting does. From VPS servers to kiosks to Raspberry Pis, Postgres can fit where you need it.
2. Connections are the biggest memory hog — Each connection is a forked process. Keep max_connections low and use PgBouncer if you need more.
3. shared_buffers at 25% of RAM is the starting point — But leave room for the OS page cache, which Postgres also benefits from.
4. effective_cache_size doesn’t allocate memory — It’s just a planner hint. Don’t be afraid to set it higher than your shared_buffers.
5. Never disable autovacuum — Just throttle it and limit it to 1 worker.
6. Measure, don’t guess — Use the memory check script to see actual usage under your real workload.

The full demo with Docker Compose, the tuned config, benchmark scripts, and memory monitoring is available at this GitHub repo.