You don’t need a 64-core EPYC and half a terabyte of RAM to run a database that does actual work. I’ve watched devs throw cloud credits at bloated RDS instances because they think PostgreSQL won’t run on anything smaller. That’s rubbish. I’ve run production databases on old workstations and power-sipping SBCs for years. The hardware is almost never the choke point. Your schema design, query patterns, and how you configure things matter a hell of a lot more.
This piece is for engineers who want to wring every drop of performance out of limited resources. Maybe you’re self-hosting on a Pi, running a home lab on a decade-old Dell, or deploying to a cheap VPS with 1GB of RAM. The principles don’t change. I’ll walk through disk layout, memory tuning, handling connections, and backup strategies that keep a database snappy when the hardware is tight.
Start With the Storage Stack
Disk I/O kills databases on modest hardware faster than anything else. If your storage is slow, no amount of clever query optimization is going to save you. First decision: SSD versus spinning rust. Get an SSD. Even a bargain-bin SATA SSD will embarrass a 10K RPM enterprise drive on random reads and writes. Running a Raspberry Pi? Boot from an external USB 3.0 SSD. That microSD card will corrupt under sustained writes eventually, and its random I/O performance is just awful.
Filesystem choice matters too. ext4 with noatime is the boring, safe default. Disable barriers if your drive has a power-safe write cache—but test that assumption. XFS can handle parallel writes better, so give it a look if your workload is write-heavy. Avoid Btrfs and ZFS on low-memory boxes. Their copy-on-write and checksumming chew through RAM and CPU cycles. You can tune ZFS to behave in a small footprint, but it’s a wrestling match. Unless you really need snapshots, stick to ext4.
Keep your data directory off the OS partition. If logs balloon in /var, you don’t want the database keeling over because it can’t write a checkpoint. Put the WAL on a separate drive if you can. Not always possible with a single-disk system, but even a cheap USB stick for WAL reduces contention.
I/O Scheduling and Mount Options
The Linux I/O scheduler still matters for spinning drives. Use deadline or mq-deadline. For SSDs, none is the right call—it passes requests straight to the device with no reordering nonsense. Set your queue depth sensibly; an overly deep queue on a slow disk just causes latency spikes. Mount with noatime,nodiratime so you aren’t doing metadata writes on every read. Toss in discard for SSDs if you trust the drive’s TRIM, or just run fstrim from a cron job.
Memory Tuning: Less Is More
Most databases ship with memory settings aimed at real servers. On a machine with 1GB or 2GB of RAM, you have to be miserly. For PostgreSQL, set shared_buffers to 15–25% of total RAM. Ignore the old advice of 25% if you’re under 1GB. Crank it too high and the OS starts swapping, and swap is a database performance death sentence. effective_cache_size tells the planner how much memory the OS might use for file caching. Set it to 50–75% of RAM. It doesn’t allocate anything; it’s just a hint for the query planner.
For MySQL or MariaDB, innodb_buffer_pool_size is your main knob. 50–70% of RAM on a dedicated database box. On a shared machine, go lower. Watch innodb_log_file_size. Too small, and checkpointing happens constantly, hammering the disk. Too large, and crash recovery drags on forever. 256MB is a decent starting point for moderate write loads on small hardware.
work_mem in PostgreSQL or sort_buffer_size in MySQL controls memory per sort or hash operation. And these are per-operation, per-connection. Set work_mem to 64MB, get 20 concurrent connections all doing sorts, and you’ve just eaten 1.28GB. You’ll OOM hard. Keep work_mem low—1–4MB—and let the database spill to disk for large sorts. It’s slower, but at least it’s predictable.
Swap Is Not Evil
Lots of guides scream at you to disable swap entirely. Don’t do that. A small swap file—512MB to 1GB—lets the kernel shuffle truly idle pages out of RAM. What you have to prevent is the database process itself getting swapped out. Set vm.swappiness=1 on modern kernels. That tells Linux to prefer reclaiming page cache over swapping anonymous pages. If your database process starts swapping, you’ve either botched the memory config or you simply don’t have enough RAM for the workload.
Connection Management and Pooling
Databases on modest hardware can’t stomach hundreds of direct connections. Every connection grabs memory for buffers and session state. Forking a new PostgreSQL backend for each connection gets expensive fast. Use a connection pooler. PgBouncer in transaction mode is pretty much the gold standard. It multiplexes dozens or even hundreds of client connections onto just a few database connections. Set your pool size to 2–3 times your CPU threads. Go beyond that and you’re just context-switching yourself into the ground.
For MySQL, ProxySQL works, or lean on the connection pooling baked into your application framework. Avoid persistent connections in PHP without a pooler. You’ll slam into max_connections because Apache kept a connection open for a request that finished ten minutes ago.
Set max_connections deliberately. Don’t leave it at the default 100. For a low-RAM PostgreSQL instance, 20–30 direct connections is plenty if you have a pooler. Each connection reserves work_mem for sorts, so fewer connections means you can allocate more memory per operation.
Query Patterns and Schema Design
No hardware tweak fixes garbage queries. On modest hardware, a missing index turns a 5ms lookup into a 30-second sequential scan that thrashes your disk. Run EXPLAIN ANALYZE on every query that runs regularly. Learn to read query plans. An index scan that fetches 90% of the rows is actually worse than a sequential scan because it causes random I/O. The planner knows this, but it only works with accurate statistics. Run ANALYZE regularly, or make sure autovacuum is doing its job.
Normalize your schema, but don’t be religious about it. Joins cost CPU and memory. If you’re hitting a small table that never changes, think about a materialized view. For write-heavy tables, partial indexes cut down on index size and maintenance overhead. Index only the columns you filter on—not every column you select.
Batch writes whenever you can. Single-row inserts in a loop generate a separate transaction and fsync for every row. Use multi-row inserts, or COPY in PostgreSQL. If your application can stomach a few seconds of data loss on a crash, set synchronous_commit = off. That groups commits into batches and slashes fsync calls. Pair that with a battery-backed SSD and the risk gets real small.
Vacuuming and Maintenance
PostgreSQL’s autovacuum is non-negotiable, but it can get a bit thuggish on small hardware. Tune it down. Set autovacuum_max_workers = 1 on a 2-core machine. Dial back autovacuum_vacuum_cost_limit to throttle I/O. Keep an eye on transaction ID wraparound, but don’t lose sleep. A well-configured autovacuum will handle it. For MySQL, keep innodb_purge_threads low and watch the undo log size.
Backups That Don’t Cripple the Server
Running pg_dump or mysqldump on a live database under load is a great way to cause table locks or long transactions that bloat storage. Logical backups are only okay for very small databases. Anything over a few gigabytes, use physical backups. pg_basebackup streams a consistent snapshot. Pair it with WAL archiving for point-in-time recovery. On modest hardware, WAL archiving via archive_command to an external drive or a network share works without piling on CPU load.
Schedule backups during quiet periods. And test your restores. A backup you’ve never restored isn’t a backup—it’s a hope. Automate the restore test to a spare machine or container.
Monitoring Without Overhead
Heavy monitoring agents eat the very resources you’re trying to guard. Use the database’s own statistics views. pg_stat_statements in PostgreSQL tracks query performance with barely any overhead. Enable it. Query it directly instead of running a separate exporter. For system metrics, iostat, vmstat, and a dumb cron job that logs to a file can replace a whole Prometheus stack. You don’t need Grafana dashboards to tell if your disk is sweating.
If you want a lightweight monitoring daemon, look at collectd with the PostgreSQL plugin. It’s written in C and has a footprint you’ll barely notice.
FAQ
Can I run PostgreSQL on a Raspberry Pi 4 with 2GB RAM?
Yes. Boot from an SSD, set shared_buffers = 256MB, effective_cache_size = 1GB, work_mem = 2MB, and stick PgBouncer in front. It’ll handle dozens of queries per second for a small app, assuming your queries are indexed and you aren’t expecting real-time analytics on large datasets.
My database slows to a crawl after a few days of uptime. What gives?
Check for bloat. PostgreSQL tables and indexes can puff up if autovacuum can’t keep pace. Query pg_stat_user_tables and look at dead tuple counts. MySQL’s InnoDB can fragment over time. A scheduled OPTIMIZE TABLE or VACUUM FULL during a maintenance window sorts it out. Also check memory usage—a slow leak in your app’s connection pool can quietly exhaust RAM.
Is SQLite a better fit for single-user stuff on low-end hardware?
Often, yes. SQLite needs zero config, no separate process, and its read performance in WAL mode is excellent. Single writer, a few readers? SQLite will stomp a client-server database on the same hardware because there’s no IPC overhead. Not great for high concurrency, but for personal projects, embedded stuff, or a small web app with light write volumes, it’s a solid pick.
What’s the single biggest bang-for-the-buck config change I can make?
Move your data directory to a dedicated SSD if you’re still on an HDD or SD card. The leap in random I/O performance papers over a multitude of config sins. After that, get connection pooling in place.