MySQL vs PostgreSQL on a 1GB RAM VPS: What’s Actually Usable (and the Settings That Make It)

The only question that matters on 1GB: what’s your bottleneck?

On a 1GB VPS you are always paying some kind of tax: memory pressure, disk latency, CPU steal, or connection overhead. The database choice matters, but not as much as your workload shape and whether you’ve capped the obvious footguns. If your app opens 200 connections, both MySQL and PostgreSQL will suffer. If your storage is slow and you force sync-heavy durability with tiny caches, both will feel like they’re running on a pager from 1999.

The goal is not “fast.” The goal is “predictable.” Predictable means:

• Memory usage has a ceiling you can explain.
• Disk writes are paced (or at least not spiky).
• Connections are bounded and pooled.
• Background maintenance does not surprise you at 3 a.m.

That’s the standard. Anything less is improv comedy, and your users did not buy tickets.

Usability verdict: who’s actually usable on 1GB?

If you want the straight answer

Both are usable on 1GB RAM for small workloads, but they fail differently:

• PostgreSQL is more sensitive to “too many connections” and memory-per-query surprises (sorts, hashes, work_mem). It’s extremely stable when you set hard limits and use pooling. It’s also better at not corrupting your mental model.
• MySQL (InnoDB) is more sensitive to mis-sized caches and flush behavior, and it can quietly burn memory in per-connection buffers if you let it. It can feel “snappier” for simple OLTP reads/writes when tuned conservatively, especially with low connection counts.

What I recommend (opinionated)

If you’re building a general web app, especially anything with reporting queries, migrations, and “we’ll add a feature later,” run PostgreSQL and put PgBouncer in front of it. The operational guardrails are clearer, and the ecosystem assumptions (migrations, constraints, transactional DDL behaviors) often reduce app-level weirdness.

If you’re running a simple key-value-ish OLTP workload with a framework that expects MySQL, or you already have MySQL operational muscle memory, run MySQL and cap connections hard. Just don’t pretend default configs are “small server friendly.” They’re “vendor benchmark friendly.”

One sentence to remember: On 1GB, PostgreSQL is a good citizen if you leash it; MySQL is a good citizen if you feed it the right portions.

Short joke #1: A 1GB VPS is like a studio apartment: you can host guests, but not if they all bring furniture.

Memory models: why “it fits” is not a plan

The 1GB budget you actually have

“1GB RAM” is marketing. Your kernel takes some. Your monitoring takes some. Your SSH sessions, cron, and log daemons take some. If it’s a modern distro, systemd services exist purely to remind you that entropy is real.

On a 1GB VPS, a realistic safe budget for the database process memory is often 500–750MB, depending on what else is running. If you run the app on the same VPS, cut that budget further. If you run Docker, cut it again, and then question your life choices.

PostgreSQL memory in one paragraph

PostgreSQL has shared memory (mostly shared_buffers) and a lot of per-backend memory: each connection is a process (unless you’re using newer options like --single modes, but you’re not). Per-query memory is governed by work_mem (sort/hash) and can multiply across concurrent operations. This is the classic trap: you set work_mem to something that looks reasonable, then a query uses several of them at once across multiple connections, and you go from “fine” to “OOM killer” in one deploy.

MySQL memory in one paragraph

MySQL with InnoDB has a big shared cache (innodb_buffer_pool_size) plus per-connection buffers (sort/join/read buffers) and internal memory (dictionary, adaptive hash, etc.). The buffer pool is usually the main lever, and you generally want it big enough to avoid constant disk reads but not so big you starve the OS page cache and everything else. On 1GB, the “right” buffer pool is usually smaller than your instincts.

Two rules that prevent most 1GB disasters

1. Cap connections so per-connection memory cannot multiply you into failure.
2. Reserve memory for the OS and filesystem cache; databases don’t get exclusive rights to RAM just because they’re dramatic.

Interesting facts & historical context (pick these up and sound wise in meetings)

1. PostgreSQL descends from the POSTGRES project at UC Berkeley (1980s), and the “SQL” part was layered on later—design DNA includes extensibility and correctness over “just ship it.”
2. MySQL became a web staple in the late 1990s/early 2000s largely because it was lightweight and easy to deploy, not because it was the best at transactions back then.
3. InnoDB was originally a third-party engine; it became the default in MySQL 5.5. That shift changed the operational game: crash recovery and MVCC became mainstream for MySQL users.
4. PostgreSQL’s vacuuming exists because MVCC keeps old row versions; if you don’t vacuum, you don’t just get bloat—you eventually get transaction ID wraparound risk.
5. MySQL historically had different durability defaults depending on settings like innodb_flush_log_at_trx_commit; many “MySQL is fast” stories were actually “MySQL was not syncing every commit.”
6. PostgreSQL’s WAL (write-ahead log) design is similar in concept to other serious databases: durability comes from writing intent to a log before data pages are flushed.
7. InnoDB’s doublewrite buffer exists to protect against partial page writes; it trades extra writes for fewer corruptions after crashes.
8. PostgreSQL’s planner can be extremely smart, but on tiny boxes it can also be extremely expensive if you ask it to sort huge intermediate results in memory it doesn’t have.
9. MySQL’s query cache used to be a “performance feature” that frequently became a performance bug; it was deprecated and removed for good reasons.

And one operations paraphrased idea I’ve seen survive every incident review: paraphrased idea from John Allspaw: reliability comes from designing systems that expect failure, not from pretending you can prevent it.

Baseline settings: MySQL on 1GB that doesn’t eat itself

When MySQL makes sense on 1GB

MySQL is fine here if your workload is mostly simple indexed queries, you keep connections low, and you don’t try to turn the VPS into a benchmark trophy. The win condition is stable latency under modest concurrency.

A sane MySQL/InnoDB memory target

Assume you can spare 600–700MB for mysqld on a database-only 1GB VPS. If you’re co-locating an app, drop that to 400–500MB. Now allocate:

• innodb_buffer_pool_size: typically 256–512MB
• innodb_log_file_size: modest (e.g., 64–128MB) to keep recovery time reasonable
• per-connection buffers: keep them small; cap connections

Baseline config snippet (MySQL 8-ish)

This is intentionally conservative. You can relax later; you can’t un-OOM a dead box.

cr0x@server:~$ sudo bash -lc 'cat >/etc/mysql/mysql.conf.d/99-vps-1gb.cnf <<"EOF"
[mysqld]
# Core
max_connections = 60
skip_name_resolve = 1

# InnoDB memory
innodb_buffer_pool_size = 384M
innodb_buffer_pool_instances = 1

# InnoDB durability / IO
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_io_capacity = 200
innodb_io_capacity_max = 400

# Redo log (MySQL 8 uses innodb_redo_log_capacity; keep modest)
innodb_redo_log_capacity = 256M

# Temp / sort behavior
tmp_table_size = 32M
max_heap_table_size = 32M
sort_buffer_size = 1M
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

# Avoid surprise thread memory
thread_cache_size = 16

# Slow query visibility
slow_query_log = 1
long_query_time = 0.5
log_slow_admin_statements = 1
EOF
systemctl restart mysql'
...output...

Notes that matter:

• max_connections is not negotiable on 1GB. If your app needs more, your app needs pooling.
• O_DIRECT helps avoid double-caching (InnoDB buffer pool + OS cache). On some VPS/storage combinations it’s a win; on others it’s “fine.” Test, but don’t obsess.
• Small per-connection buffers are the quiet hero. Big buffers per thread are how you wake up to OOM on a traffic spike.

Binary logs on 1GB: pick a stance

If you don’t need replication or point-in-time recovery, disable binary logs to reduce write pressure. If you need them, keep them but rotate aggressively and watch disk.

Baseline settings: PostgreSQL on 1GB that stays upright

When PostgreSQL makes sense on 1GB

PostgreSQL is great here if you value correctness, constraints, and predictable semantics, and you’re willing to manage connections. It’s not “heavy”; it’s honest about what work costs.

A sane PostgreSQL memory target

On a DB-only 1GB VPS, budget roughly 600–750MB for Postgres processes plus shared memory, and leave the rest for OS cache and sanity. Then:

• shared_buffers: 128–256MB (rarely more on 1GB)
• work_mem: 2–8MB (yes, that low; concurrency multiplies)
• maintenance_work_mem: 64–128MB (vacuum/index builds need it, but don’t set 512MB on 1GB unless you like paging)
• max_connections: keep it low, and use PgBouncer

Baseline config snippet (PostgreSQL 14–17 style)

cr0x@server:~$ sudo bash -lc 'PGVER=$(psql -V | awk "{print \$3}" | cut -d. -f1); \
CONF="/etc/postgresql/$PGVER/main/postgresql.conf"; \
cat >>"$CONF" <<"EOF"

# 1GB VPS baseline (conservative)
max_connections = 40

shared_buffers = 192MB
effective_cache_size = 512MB

work_mem = 4MB
maintenance_work_mem = 96MB

wal_buffers = 8MB
checkpoint_timeout = 10min
max_wal_size = 1GB
min_wal_size = 256MB
checkpoint_completion_target = 0.9

# Autovacuum: keep it on, but don’t let it stampede
autovacuum_max_workers = 2
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 1000
autovacuum_vacuum_cost_delay = 10ms

# Observability
log_min_duration_statement = 500
log_checkpoints = on
log_autovacuum_min_duration = 1000
EOF
systemctl restart postgresql'
...output...

Why these choices:

• effective_cache_size is a hint to the planner, not an allocation. On 1GB, don’t lie wildly.
• Small work_mem reduces OOM risk. If a specific query needs more, set it per-session for that job.
• Autovacuum is not optional. If you disable it to “save resources,” you’re just borrowing problems at a predatory interest rate.

Short joke #2: Disabling autovacuum on Postgres is like turning off the smoke alarm because it’s noisy.

Connections will kill you: pooling and hard caps

Why PostgreSQL feels “worse” under naive connection storms

Every PostgreSQL connection is a process. That’s not a moral failing; it’s a design that makes isolation and observability clean. On 1GB, it also means 150 connections is basically a denial-of-service attack you commissioned yourself.

PgBouncer is not “extra,” it’s the seatbelt

Put PgBouncer in transaction pooling mode for typical web traffic. It lets you keep max_connections low while serving higher client concurrency safely.

cr0x@server:~$ sudo bash -lc 'apt-get update -y && apt-get install -y pgbouncer'
...output...

cr0x@server:~$ sudo bash -lc 'cat >/etc/pgbouncer/pgbouncer.ini <<"EOF"
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
reserve_pool_size = 5

server_idle_timeout = 60
query_timeout = 30
log_connections = 1
log_disconnections = 1
EOF
systemctl restart pgbouncer'
...output...

For MySQL, pooling is still smart. Many apps “handle pooling” by opening and closing connections constantly, which is not pooling; it’s cardio. Use a real pooler in your app runtime, and keep server-side connections low.

Storage & I/O realities: the VPS disk is the hidden boss fight

On small VPS plans, your disk is often the slowest component and the least predictable. You might be on shared SSD with burst credits, or on “SSD” that behaves like a polite HDD. Databases make disk behavior visible because they write consistently and they fsync.

Durability settings are performance settings

When you run durable defaults (innodb_flush_log_at_trx_commit=1, Postgres synchronous commit, WAL fsync), you are explicitly choosing to wait for the storage to confirm writes. That’s correct. If you relax it, you’re trading correctness for speed.

On a 1GB VPS for hobby workloads, you might accept less strict durability. In production, be careful: you’ll only discover your true risk tolerance after you’ve lost data. That is not when you want to have the conversation.

Filesystem cache matters even when you have a buffer cache

Linux page cache still helps. It caches filesystem metadata, tablespaces, and frequently accessed pages the database hasn’t pinned. Starving the OS of memory is how you get random latency spikes even when the DB’s own cache is “big.” That’s why “give InnoDB 80%” is not a universal truth—especially not on 1GB.

Swap: the villain with a purpose

On 1GB, I generally prefer having some swap (even a small swapfile) to absorb brief spikes rather than triggering the OOM killer instantly. But if you’re constantly swapping, you’re already down. Swap is an airbag, not an engine.

Fast diagnosis playbook: find the bottleneck in minutes

This is the order that keeps you from wandering into a week-long tuning spiral.

First: is it memory pressure or I/O?

1. Check swap and memory: if swap is climbing fast or OOM kills happen, stop tuning SQL and cap connections / memory first.
2. Check I/O wait: high %iowait means you’re waiting on disk; your query plan may be fine, your disk may be sad.

Second: is the database overloaded or the app?

1. Connection count: are you running at/near max connections? If yes, you’re queueing and thrashing.
2. Slow queries: are a few queries dominating? If yes, fix those before “tuning the server.”

Third: is it checkpoints/flushes or lock contention?

1. PostgreSQL: check checkpoint frequency and autovacuum behavior; also look for locks blocking.
2. MySQL: check redo log pressure, buffer pool misses, and lock waits.

Fourth: verify storage and kernel constraints

1. Disk space: full disks cause chaos.
2. CPU steal: on shared hosts, your “CPU” might be mostly theoretical.

Practical tasks (with commands): observe, decide, act

These are the checks I run on small VPS incidents. Each one includes what the output means and what decision you make.

Task 1: Confirm RAM, swap, and pressure

cr0x@server:~$ free -m
              total        used        free      shared  buff/cache   available
Mem:            987         612          54          12         320         248
Swap:          1023         310         713

Meaning: Low available and non-trivial swap use indicates memory pressure. If swap is growing during peak, you’re heading toward latency spikes.

Decision: Reduce max connections, shrink DB caches slightly, add pooling, or add RAM. Don’t start by adding indexes.

Task 2: See if you’re I/O bound (high iowait)

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0 317440  55296  12000 310000    0    8   120   980  300  450 12  6 72 10  0
 2  1 317680  41000  11800 309500    0   64   400  2200  500  900 18  8 58 16  0

Meaning: wa (I/O wait) in the teens indicates disk is a bottleneck, especially when it correlates with latency.

Decision: Reduce write amplification (checkpoint/flush tuning), avoid huge sorts to disk, and consider faster storage or moving the DB.

Task 3: Identify top memory consumers

cr0x@server:~$ ps -eo pid,comm,rss,pmem --sort=-rss | head
 1823 postgres   98200  9.9
 1744 postgres   94120  9.5
 1602 postgres   90500  9.2
 1320 mysqld     82000  8.3
  911 node       61000  6.1

Meaning: Many Postgres backends each consuming ~90MB suggests per-connection memory + query work. MySQL shows as one big process.

Decision: For Postgres: reduce connections and work_mem; add PgBouncer. For MySQL: check buffer pool size and per-thread buffers.

Task 4: Check if the kernel is killing processes (OOM)

cr0x@server:~$ dmesg -T | tail -n 20
[Mon Dec 30 10:12:14 2025] Out of memory: Killed process 1823 (postgres) total-vm:1620400kB, anon-rss:210000kB, file-rss:1200kB, shmem-rss:0kB

Meaning: You hit the wall. Postgres got killed; you likely lost in-flight work, and the app may be retrying aggressively.

Decision: Immediately cap connections, lower memory settings, and stabilize. Then root-cause the spike (query, deploy, cron job).

Task 5: Confirm disk space and inode sanity

cr0x@server:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        25G   22G  2.1G  92% /

Meaning: 92% is not “full,” but it’s close enough for autovacuum/temp files/WAL to get you into trouble.

Decision: Clean logs, rotate, archive old binlogs/WAL, and keep at least a few GB free. On tiny disks, margin is life.

Task 6: Measure storage latency quickly

cr0x@server:~$ iostat -x 1 3
Device            r/s     w/s   r_await   w_await  aqu-sz  %util
vda              8.00   35.00     6.10    28.40    1.20   92.00

Meaning: High w_await and high %util means the disk is saturated on writes.

Decision: Reduce write bursts (Postgres checkpoints, MySQL flushing), and move heavy batch jobs off peak. If it’s consistently saturated, upgrade storage/plan.

Task 7: PostgreSQL—check connection count and states

cr0x@server:~$ sudo -u postgres psql -c "select state, count(*) from pg_stat_activity group by 1 order by 2 desc;"
   state   | count
-----------+-------
 idle      |    22
 active    |     9
 idle in transaction | 6
(3 rows)

Meaning: “idle in transaction” is a classic: sessions holding locks and bloat risk.

Decision: Fix the app transaction handling; set idle_in_transaction_session_timeout. Pooling helps but doesn’t fix bad code.

Task 8: PostgreSQL—find top slow queries by total time

cr0x@server:~$ sudo -u postgres psql -c "select query, calls, total_exec_time::int as total_ms, mean_exec_time::int as mean_ms from pg_stat_statements order by total_exec_time desc limit 5;"
                               query                                | calls | total_ms | mean_ms
--------------------------------------------------------------------+-------+----------+---------
 select * from events where user_id = $1 order by created_at desc    |  1200 |    98000 |      81
 update accounts set last_seen = now() where id = $1                 |  4500 |    42000 |       9
(2 rows)

Meaning: The first query is a candidate for an index like (user_id, created_at desc) and maybe selecting fewer columns.

Decision: Fix the top offenders before touching global settings. 1GB tuning won’t save a full table scan every request.

Task 9: PostgreSQL—check checkpoint pressure

cr0x@server:~$ sudo -u postgres psql -c "select checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_backend from pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_backend
-------------------+-----------------+--------------------+----------------
                18 |              42 |            8123456 |         456789
(1 row)

Meaning: Many requested checkpoints suggests WAL is filling quickly (write-heavy bursts), which can cause I/O spikes.

Decision: Increase max_wal_size modestly (disk permitting), tune checkpoint settings, and reduce bursty writes. Don’t set it huge on tiny disks.

Task 10: PostgreSQL—see if autovacuum is keeping up

cr0x@server:~$ sudo -u postgres psql -c "select relname, n_dead_tup, last_autovacuum from pg_stat_user_tables order by n_dead_tup desc limit 5;"
 relname  | n_dead_tup |        last_autovacuum
----------+------------+-------------------------------
 events   |     182345 | 2025-12-30 09:50:12.12345+00
 logs     |      93440 | 
(2 rows)

Meaning: Lots of dead tuples and missing last_autovacuum on a hot table suggests vacuum is falling behind or thresholds are off.

Decision: Adjust autovacuum scale factors per table, add indexes carefully, and ensure vacuum isn’t blocked by long transactions.

Task 11: MySQL—check current memory-relevant variables

cr0x@server:~$ sudo mysql -e "show variables where Variable_name in ('max_connections','innodb_buffer_pool_size','tmp_table_size','max_heap_table_size','sort_buffer_size','join_buffer_size');"
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 402653184 |
| join_buffer_size        | 262144    |
| max_connections         | 60        |
| max_heap_table_size     | 33554432  |
| sort_buffer_size        | 1048576   |
| tmp_table_size          | 33554432  |
+-------------------------+-----------+

Meaning: Confirms caps are applied; sizes look conservative.

Decision: If you see huge per-thread buffers or max_connections in the hundreds, fix that before chasing query plans.

Task 12: MySQL—check buffer pool hit rate and reads

cr0x@server:~$ sudo mysql -e "show global status like 'Innodb_buffer_pool_read%';"
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 184003211 |
| Innodb_buffer_pool_reads              | 1200345   |
+---------------------------------------+-----------+

Meaning: Some disk reads are normal. If Innodb_buffer_pool_reads climbs rapidly relative to requests, the working set doesn’t fit and you’re I/O bound.

Decision: If disk is fast enough, maybe fine. If not, increase buffer pool a bit (without starving OS), or reduce dataset/queries.

Task 13: MySQL—check temp tables going to disk

cr0x@server:~$ sudo mysql -e "show global status like 'Created_tmp%tables';"
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 18420  |
| Created_tmp_tables      | 40210  |
+-------------------------+--------+

Meaning: A large fraction of tmp tables on disk indicates sorts/joins spilling, which hurts on slow storage.

Decision: Add indexes, rewrite queries, or increase temp table limits slightly if memory permits. On 1GB, query fixes beat memory brute force.

Task 14: Confirm connection usage (MySQL)

cr0x@server:~$ sudo mysql -e "show global status like 'Threads_connected'; show global status like 'Max_used_connections';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 48    |
+-------------------+-------+
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 59    |
+----------------------+-------+

Meaning: You’re riding the cap. That’s either healthy queueing (if latency is okay) or a bottleneck (if requests time out).

Decision: If timeouts occur, add pooling and reduce concurrency at the app. Raising max_connections on 1GB is usually just raising the blast radius.

Task 15: Check CPU steal time (shared VPS reality check)

cr0x@server:~$ mpstat 1 3
Linux 6.1.0 (server) 	12/30/2025 	_x86_64_	(1 CPU)

12:10:01 AM  %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:10:02 AM  18.0  0.0  8.0    10.0  0.0   1.0   12.0   0.0    0.0  51.0

Meaning: %steal at 12% means the hypervisor is taking your CPU away. Your tuning can’t fight your landlord.

Decision: Consider a better plan/provider, reduce workload, or move the DB elsewhere. Don’t waste days micro-tuning queries while the host is oversold.

Task 16: Confirm latency from the database itself

cr0x@server:~$ sudo -u postgres psql -c "select now(), pg_sleep(0.1), now();"
              now              | pg_sleep |              now
-------------------------------+----------+-------------------------------
 2025-12-30 00:10:10.0101+00   |          | 2025-12-30 00:10:10.1109+00
(1 row)

Meaning: If this simple call is slow or stalls, the problem is system-level (I/O, CPU steal, memory thrash), not query-level.

Decision: Stop blaming the ORM and look at the host.

Three corporate mini-stories from the trenches

1) Incident caused by a wrong assumption: “It’s only 1GB, but it’s just a dev box”

They had a small internal service running on a 1GB VPS. “Internal” meant “only used by engineers,” which is a polite way of saying “no one measured it and everyone assumed it was fine.” They chose PostgreSQL, set max_connections to 200 because the app occasionally burst, and set work_mem to 64MB because a blog post said sorting in memory is faster.

It worked for weeks. Then a new dashboard shipped that ran a handful of aggregation queries per page load. Each query used multiple sorts and hashes. Engineers opened the dashboard in parallel during an incident. The database didn’t degrade gracefully; it fell off a cliff. The kernel started killing backends. The dashboard retried. The retried queries created more backends. You can guess the rest.

The assumption was: “64MB is small; the server has 1GB.” The reality was: work_mem is per operation, per connection, and queries can use several at once. Multiply by concurrency and you’ve built an OOM generator.

The fix wasn’t heroic. They cut max_connections to 40, dropped work_mem to 4MB, put PgBouncer in front, and moved the dashboard to run heavier queries asynchronously with cached results. The “internal” service became boring again, which is the highest compliment you can pay a database.

2) Optimization that backfired: “Let’s make checkpoints disappear”

Another team ran Postgres on a small VPS and complained about periodic latency spikes. They saw checkpoint logs and concluded checkpoints were the enemy. Their solution was to increase max_wal_size aggressively and stretch checkpoint_timeout so checkpoints happened less often.

For a while, it looked better. Fewer spikes. Everyone declared victory and moved on. Then they had an unplanned reboot (provider maintenance). Recovery took much longer than expected, and the service missed its SLO window. No data loss, but the incident report was awkward because the “optimization” was the only notable recent change.

They learned the trade: fewer checkpoints can mean larger WAL to replay, and on slow storage that can be painful. On tiny VPS disks, bigger WAL also crowds out everything else, increasing the chance you hit disk-full issues at the worst possible time.

The boring fix: bring checkpoint settings back to conservative values, then actually solve the root cause—write bursts from batch jobs. They throttled jobs, tuned autovacuum cost parameters to spread I/O, and accepted that some checkpoint activity is normal. A database that never checkpoints is not “optimized”; it’s just procrastinating loudly.

3) Boring but correct practice that saved the day: connection caps + slow query logs

A small SaaS ran MySQL on 1GB instances for some customer shards. Nothing fancy, just OLTP. They had a strict rule: every shard had a hard max_connections limit, conservative per-thread buffers, and slow query logging enabled with a low threshold. They also had a weekly review where someone skimmed slow query samples and looked for regressions.

One Friday, a new feature introduced a query that was “fine” on staging but pathological for one customer shard with a skewed dataset. The query did a join without the right index and spilled to disk temp tables. On a 1GB VPS with middling I/O, that’s how you turn a single request into a denial of service.

The difference: the shard didn’t spiral. Connection caps prevented a thundering herd from exploding memory. Slow query logs made the culprit obvious within minutes. They rolled back the feature for that shard, added the correct index, and redeployed after verification.

No heroic tuning. No late-night “maybe Linux is broken.” Just guardrails and a paper trail. The incident was boring, and boring is exactly what you want.

Common mistakes: symptom → root cause → fix

1) Symptom: sudden timeouts, load average spikes, swap climbing

Root cause: Too many connections + per-connection memory (Postgres backends, MySQL thread buffers) causing memory pressure and swap thrash.

Fix: Cap server connections; add pooling; lower work_mem (Postgres) and per-thread buffers (MySQL). Consider a small swapfile if you had none, but treat it as a safety net, not capacity.

2) Symptom: periodic latency spikes every few minutes

Root cause: Checkpoint/flush bursts and storage write saturation.

Fix: For Postgres, tune checkpoint pacing (checkpoint_completion_target) and allow moderate WAL size; for MySQL, review flushing and redo settings. Reduce bursty write jobs and avoid massive transactions.

3) Symptom: PostgreSQL disk usage grows even when data “isn’t changing much”

Root cause: Bloat from MVCC + vacuum lag, often worsened by long-running transactions or disabled autovacuum.

Fix: Keep autovacuum enabled; hunt long transactions; tune per-table autovacuum thresholds; consider periodic VACUUM (ANALYZE) or REINDEX in maintenance windows.

4) Symptom: MySQL feels fast, then suddenly stalls on writes

Root cause: Redo log pressure and fsync latency; possibly small redo capacity combined with write bursts.

Fix: Keep redo capacity moderate, avoid huge single transactions, and validate your storage. If the VPS disk is erratic, no parameter will make it honest.

5) Symptom: “idle in transaction” connections accumulate (Postgres)

Root cause: App starts a transaction and forgets to commit/rollback, holding locks and preventing vacuum cleanup.

Fix: Fix app transaction scope; set idle_in_transaction_session_timeout; monitor pg_stat_activity and kill offenders when needed.

6) Symptom: high CPU, but query throughput is low

Root cause: Bad query plans, missing indexes, or expensive sorts/hashes spilling to disk; sometimes CPU steal.

Fix: Use EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN (MySQL), fix indexes, and verify %steal. If steal is high, your “CPU tuning” is mostly theater.

7) Symptom: everything slows down after “raising caches”

Root cause: Starving the OS and filesystem cache; swapping; background tasks contending for memory.

Fix: Reduce DB cache sizes slightly, keep memory headroom, and measure again. On 1GB, leaving 200MB free can outperform squeezing every last MB into the DB.

Checklists / step-by-step plan

Step-by-step: choosing MySQL vs PostgreSQL for 1GB

1. Count your concurrency: if you need lots of concurrent clients, plan for pooling regardless of DB.
2. Classify queries: mostly simple indexed OLTP vs mixed OLTP + reporting. Mixed workloads favor Postgres semantics and tooling, but only if you control connections.
3. Decide durability stance: if you can’t accept data loss, don’t “optimize” fsync away.
4. Check storage quality: if the disk is slow, tune for fewer writes and smaller bursts; consider moving DB off the VPS.
5. Pick the boring defaults in this guide and only change one variable at a time with measurements.

Step-by-step: hardening a 1GB database host

1. Install monitoring (at minimum: disk, memory, swap, load, iowait).
2. Set a swapfile if none exists (small, e.g., 1GB) and keep vm.swappiness reasonable for your distro.
3. Cap database connections (max_connections), then enforce pooling in the app.
4. Enable slow query logging (log_min_duration_statement or MySQL slow log) with a threshold that catches pain early (e.g., 500ms).
5. Verify autovacuum is on (Postgres) and not blocked; verify InnoDB is used (MySQL) and buffer pool is not absurd.
6. Keep disk free space margins and set log rotation. A full disk turns “performance issue” into “incident.”
7. Run a load test that matches reality, not hope.

Step-by-step: tuning without cargo cult

1. Get a baseline: latency, throughput, CPU, iowait, memory.
2. Fix the worst query first (top total time), not the “most discussed” query.
3. Only then consider cache sizing: buffer pool / shared buffers.
4. Recheck after changes. If you can’t measure improvement, revert and move on.

FAQ

1) Can I run PostgreSQL on 1GB without PgBouncer?

You can, but you’re betting your stability on your app never opening too many connections. If it’s a single small service with strict pooling in the runtime, fine. Otherwise, use PgBouncer and sleep.

2) What’s a safe shared_buffers on 1GB?

Usually 128–256MB. If you set 512MB on a 1GB box and also allow many connections, you’re building a memory squeeze. Keep headroom for the OS and for backend processes.

3) What’s a safe innodb_buffer_pool_size on 1GB?

Often 256–512MB. If the VPS also runs the app, lean toward 256–384MB. The rest of memory is not “wasted”; it prevents swapping and helps the OS cache.

4) Should I disable fsync or synchronous commit to go faster?

Only if you fully accept losing recent transactions on power loss or host crash. On a VPS, you don’t control the failure modes. In production, keep durability on and optimize elsewhere.

5) Is swap good or bad for databases on tiny boxes?

Some swap is good as a shock absorber. Constant swapping is fatal. If you see steady swap growth during normal load, you have a memory budget problem, not a swap configuration problem.

6) Which database is “lighter” on memory?

Neither by default. MySQL tends to be dominated by one big process (buffer pool) plus per-connection buffers; Postgres tends to scale memory with connections. With sane caps and pooling, both can fit. Without caps, both can ruin your day.

7) What’s the single most important setting on 1GB?

max_connections. It controls your worst-case memory multiplication and your contention profile. If you get this wrong, your other tuning becomes decorative.

8) How do I tell if I need more RAM vs better disk?

If you’re swapping or getting OOM kills, you need more RAM or lower concurrency. If %iowait is high and disk w_await is large, you need better disk or fewer writes. Often you need both, but one is usually screaming louder.

9) Can I “just add indexes” to fix 1GB performance?

Indexes help reads and can hurt writes. On 1GB with slow storage, too many indexes can increase write amplification and vacuum work. Add the right indexes for the top queries, not every index your ORM suggests.

10) Should I run the app and DB on the same 1GB VPS?

Only for very small deployments. Co-locating increases contention and makes incidents harder to reason about. If you must, reduce DB caches and enforce connection pooling aggressively.

Next steps you can do today

1. Pick a connection cap (Postgres 40, MySQL 60 is a decent starting point) and enforce pooling.
2. Apply the conservative baseline config for your chosen database, restart, and verify the settings actually took effect.
3. Turn on slow query visibility (500ms threshold) and collect evidence for a day.
4. Run the Fast diagnosis playbook the next time latency spikes: memory → iowait → connections → top queries → checkpoints/flushes.
5. Decide your upgrade trigger: if you’re regularly near caps, swapping, or saturating disk, 2GB RAM or better storage will buy more stability than cleverness.

If you want one final rule for 1GB VPS databases: bound your worst case. That’s what settings are for. The rest is just arguing with physics.