At EDB’s recent virtual event, Postgres Vision 2020, I spoke about my journey through the world of database administration, starting as a junior Oracle DBA, taking a detour via SQL Server and Sybase before arriving, 20 years later, at PostgreSQL.
Discovering PostgreSQL
I recalled what it was like to come into contact with PostgreSQL for the first time with 20 years’ experience as an Oracle DBA under my belt, feeling like a newly-recruited junior DBA. I shared with attendees how I taught myself to install, configure, and manage PostgreSQL databases.
I also discussed some of the similarities between Oracle and PostgreSQL that enabled me to feel less “lost”: databases, instances, datafiles, tablespaces, schemas, configuration files, tables, views, indexes, constraints.
In addition, I compared some of the differences between Oracle and PostgreSQL: WAL vs. REDO, lower vs. upper case object names, autocommit mode, the different ways in which they deal with concurrent reads/writes (multiversion concurrency control or MVCC).
Digging Deeper
I experienced some frustrations, for example, trying to exit psql prior to version 11 or having to learn my way around the PostgreSQL catalog and information schema. I also encountered surprises—like how fast it is to install PostgreSQL and create a database instance, or the fact that there is no DUAL table.
In my session, I spoke about the things I felt were missing in PostgreSQL, for example, partitioning functionality (although that is gradually being introduced with each new version) and RAC.
I highlighted the (many) things that PostgreSQL does well. For example, documentation, booleans, managing privileges (where it’s possible to grant privileges on all objects in an entire schema, to grant default privileges, i.e., privileges on objects that don’t yet exist or grant truncate privileges on a table).
Sharing My Experiences
I shared some of the main configuration parameters that I learned to tune in order to get the best performance from my databases (because the default values of some of the parameters in the postgresql.conf are designed to create a database that uses minimal resources rather than one designed for real-world applications).
I touched briefly on TOAST (which I learned had nothing to do with grilled bread, but rather the «The Oversized-Attribute Storage Technique » where PostgreSQL breaks up large field values into multiple chunks) and VACUUM (purging obsolete rows and reclaiming space within tables).
Then, I listed some of the different tools available for database monitoring and administration (pgAdmin, DBeaver), for backup and restore (Barman, pgBackrest, EDB BART), for replication and high availability (repmgr, repmgrd, pacemaker, corosync, pgBouncer, pgPool ), to generate activity on the development and test databases for benchmarking (pgBench) and for analysis (pgBadger).
There are a number of things that I’ve come to love about PostgreSQL. For example, the amazing community which is full of welcoming, friendly people with a lot of knowledge that they like to share via PGDays, PGConfs, Meetups, Twitter feeds, blogs, and mailing lists.
Although, during my session, I didn’t actually answer the question that everyone seemed to ask: “Which do you prefer, Oracle or PostgreSQL?,”my enthusiasm for PostgreSQL is evident throughout the story—and in the fact that I’ve chosen to become a Senior Database Consultant with EDB, helping clients on their own PostgreSQL journeys.
Learn more from our Postgres Vision 2020 PostgreSQL experts by watching their sessions on demand!