Sizing PostgreSQL Hardware for an Oracle Workload
Wrong in Both Directions at Once
The instinct when sizing the target is to read the Oracle server's specification and match it. The result is usually a machine that is simultaneously over-provisioned and under-provisioned: generous on memory that PostgreSQL will not use the way you intended, and short on the things that actually constrain it.
PostgreSQL is not a smaller Oracle or a cheaper one. It has a different memory architecture, a different connection model, and different settings that bind first under load. Sizing it well means understanding those three differences and then measuring your own workload.
The Memory Model Is the Big One
Oracle concentrates caching in the SGA, a large shared region the database manages itself, and conventional practice is to make it as large as the machine sensibly allows. PostgreSQL does the opposite on purpose: shared_buffers is deliberately modest and the operating system page cache does much of the work.
This is why transplanting an SGA figure into shared_buffers goes wrong. Past a certain point you are not adding cache, you are taking memory away from the page cache that was caching the same data more flexibly, while making checkpoints more expensive. The commonly cited starting point is around a quarter of system memory, and the important word is starting — it is a point from which to measure, not an answer.
What matters far more than the exact figure is the total: how much of your working set fits in memory at all, counting both shared_buffers and the page cache. That is the number to size for.
Connections Are Processes
PostgreSQL forks a backend process per connection. Each carries its own memory overhead, and the context-switching cost of thousands of them is real. An Oracle instance comfortably fronting several thousand sessions — particularly with shared servers — does not translate into several thousand PostgreSQL connections on equivalent hardware.
The answer is a pooler rather than a bigger machine. Put PgBouncer or an equivalent in front, size the actual database connections to something in the low hundreds at most, and let the pool absorb the application's concurrency. A frequent and expensive discovery is that the migration was sized for peak application connections rather than peak database connections, and those are not the same quantity once a pool exists.
Application tier Pooler PostgreSQL
-------------------- -------------- ------------------
2,000 app connections PgBouncer ~100-200 backends
(transaction pooling) (sized to cores and
work_mem budget)work_mem Is Per Operation, Not Per Server
This is the setting most likely to exhaust memory on a machine that looked generously specified. work_mem is the budget for a single sort or hash operation, not for the server and not even for a query. One query with several sorts and a few hash joins can allocate it many times over, and every concurrent connection can do the same.
Multiply it out before you set it: concurrent queries, times operations per query, times work_mem. A value that is entirely reasonable for a reporting database with four users will take down an OLTP system with two hundred. The usual approach is a conservative global setting with specific sessions or roles raising it where a known heavy query needs it.
Storage: What Actually Transfers
Storage requirements carry across more directly than memory, because physics does not change. Random read latency and sustained IOPS matter as much as they did, and a database that needed low-latency storage on Oracle needs it here.
Two additions specific to PostgreSQL. Write-ahead log volume is driven by write rate rather than data size, and it needs enough local space for pending segments plus bandwidth for archiving and any replicas. And the MVCC model means update-heavy tables carry dead row versions until autovacuum reclaims them, so update-heavy workloads need genuine headroom rather than a tight fit.
CPU: Count Cores for Parallelism and Vacuum
Core count matters in two places that are easy to under-plan. Parallel query uses workers per query up to configured limits, so an analytical workload benefits from cores in a way a purely transactional one does not. And autovacuum workers consume CPU — on a high-churn database vacuum is not a background detail, it is a workload with a capacity requirement.
If you size cores purely from the transactional query load and leave nothing for vacuum, the symptom appears weeks later as tables that grow despite free disk, which is a confusing problem to debug backwards from.
Measure the Shape, Not the Specification
The useful starting point is not the Oracle server's specification but the workload's shape, and most of it is available before you migrate anything. How large is the working set as opposed to the total data. What is the read/write ratio. How many genuinely concurrent sessions exist at peak, as distinct from how many are open. How much does the write rate vary between a quiet hour and a busy one.
Those four answers determine the sizing. A 4TB database where the hot set is 80GB and traffic is read-dominated is a very different machine from a 400GB database under constant update, and the specification of the box they currently share tells you nothing about either.
A Defensible Starting Configuration
Begin deliberately conservative, instrument properly, and adjust against evidence. shared_buffers at roughly a quarter of memory, effective_cache_size describing what the operating system can realistically cache so the planner costs index access correctly, a low global work_mem with targeted exceptions, a pooler in front, and autovacuum given more throughput than the defaults assume.
Then let the database tell you what is wrong. Cache hit ratios, checkpoint frequency and timing, temporary file creation — which is work_mem being exceeded and spilling to disk — and dead tuple ratios. Every one of those is a measurement pointing at a specific setting, which is a far better position than having guessed well.