From the beginning of my career I’ve always been working with Oracle Database, from 8i till 12c, on online transaction processing (OLTP). The database is safe, reliable and - Ask Tom! - it’s great. And as saying goes, “you’ve never got in trouble by buying Oracle”.
OLTP loads usually require low latency and high transaction rate. Under different load profiles it was quite simple to come up with a solution of any performance problem. The approaches might differ but in fact you choose either smart way or money bag way.
And finally I’ve landed on a project with initial requirements: 10k transaction per second and - oh my gosh! - PostgreSQL 11. Sounds like a challenge for me! Moreover, I’ve never seen any production-grade use of this DB. Not even saying, about highly concurrent and highly loaded scenarios. For me, PostgreSQL was always a toy db or for small projects.
Reading the PostgreSQL documentation reminded me late 90’s MSDN on CD-ROM that contained all you need for Win32 development but the information was sparsed through all the docs. So one just ended up with reading it inside out and developing a new telepathy skill.
Those were my first notes that I’d extracted from the PostgreSQL documentation:
- Updates are, in fact, deletes and inserts.
- BLOBs are stored in the separate table - TOAST.
- There is more then one way to partition tables with unclear consequences.
- There is more then one way to build a cluster
- Manual read load distribution, i.e. PG doesn’t do anything to offload reads to standby nodes. DIY!
Stay tuned!