Database Administrator
PostgreSQL and MySQL questions
280 questions on PostgreSQL and MySQL for database administrators, easiest first.
Easy 74
- Least privilege for an app usereasy
- Stopping SQL injectioneasy
- Logical vs physical backupeasy
- Why a write-ahead log existseasy
- Read-only access for analystseasy
- What pg_hba.conf controlseasy
- View vs materialized vieweasy
- Seeing what is running noweasy
- Cancel or terminate a sessioneasy
- Why InnoDB over MyISAMeasy
- Gaps in auto-increment IDseasy
- Storing event timeseasy
- FATAL: too many clientseasy
- Finding slow MySQL querieseasy
- Encrypting client connectionseasy
- Making a PostgreSQL server a standbyeasy
- Copying data for a new replicaeasy
- Why keep a delayed replicaeasy
- Turning a standby into the primaryeasy
- Restoring one dropped tableeasy
- Standby fell too far behindeasy
- What hot standby meanseasy
- Seeing connected standbyseasy
- How InnoDB stores table rowseasy
- What an InnoDB secondary index points toeasy
- type = ALL in MySQL EXPLAINeasy
- What UPDATE does under MVCCeasy
- Applying a minor PostgreSQL releaseeasy
- Which part is the major versioneasy
- Applying a pg_hba.conf changeeasy
- CREATE USER vs CREATE ROLEeasy
- Where roles liveeasy
- Where is the MySQL query cacheeasy
- Emoji fail to save in MySQLeasy
- Sizing the InnoDB buffer pooleasy
- Data type for rupee amountseasy
- Loading 5 crore rows quicklyeasy
- Emptying a staging tableeasy
- Real size of a tableeasy
- Spotting indexes no one useseasy
- Remote clients cannot connecteasy
- VACUUM vs ANALYZEeasy
- Capping query time for one roleeasy
- Role for a monitoring agenteasy
- Same MySQL user, different hosteasy
- Where to move from MySQL 8.0easy
- Old client fails to log in to MySQL 8.4easy
- Extension missing in another databaseeasy
- Where new databases come fromeasy
- Password file ignored by psqleasy
- Client TLS mode that stops spoofingeasy
- NOT IN returns no rowseasy
- Counting rows with NULL phoneseasy
- Where big text values goeasy
- Getting disk back from a MySQL tableeasy
- Binary logs filling the diskeasy
- Granting one set of rights to many MySQL userseasy
- Recording who logged ineasy
- Big table read by full scanseasy
- Which postgresql.conf is in useeasy
- Checking if the server accepts connectionseasy
- Consistent mysqldump without lockseasy
- PostgreSQL default isolationeasy
- Rolling back a CREATE TABLEeasy
- DDL in a MySQL transactioneasy
- Lock taken by a plain SELECTeasy
- Does VACUUM block the appeasy
- When others see your changeseasy
- Setting the level for one transactioneasy
- BEGIN inside a transactioneasy
- Guarding a reporting transactioneasy
- Undoing a TRUNCATEeasy
- Why long transactions hurteasy
- now() inside a long transactioneasy
Medium 114
- Reading an EXPLAIN ANALYZE planmedium
- Why PostgreSQL needs VACUUMmedium
- What PITR needsmedium
- Why use a connection poolermedium
- Covering indexmedium
- Which query to tune firstmedium
- UNIQUE column with NULLsmedium
- Adding an index on a live tablemedium
- Sort Method: external mergemedium
- Bad plan right after a bulk loadmedium
- Sizing shared_buffersmedium
- Session state behind pgBouncermedium
- Workers fighting over jobsmedium
- MySQL default isolation levelmedium
- Reports cancelled on the replicamedium
- Is pg_dump consistent?medium
- Restoring a Percona XtraBackupmedium
- Disk not freed after a big deletemedium
- Bloat from an idle sessionmedium
- A migration froze the whole appmedium
- Indexing only pending rowsmedium
- Searching inside textmedium
- Fast JSONB filtersmedium
- Row vs statement binlog formatmedium
- Counting a huge table quicklymedium
- Sync replication that survives one replica lossmedium
- Reading your own write on a replicamedium
- Too many replicas on one primarymedium
- Lossless semi-sync in MySQLmedium
- Why GTIDs ease MySQL failovermedium
- Stopping writes to a MySQL replicamedium
- MySQL replica lags under heavy writesmedium
- Connection string that finds the primarymedium
- Restoring to just before a bad deploymedium
- New timeline after promotionmedium
- Checking WAL archiving healthmedium
- InnoDB Cluster losing two of threemedium
- Fast new MySQL replicamedium
- MySQL point-in-time restoremedium
- Which MySQL replica to promotemedium
- Phone number compared to a numbermedium
- "Using filesort" in MySQL EXPLAINmedium
- Testing an index drop safelymedium
- Hard-coding FORCE INDEXmedium
- When autovacuum starts on a tablemedium
- Vacuuming one hot table more oftenmedium
- Shrinking a bloated table safelymedium
- Autovacuum running though it is offmedium
- Watching XID age per databasemedium
- Cost of 3,000 idle connectionsmedium
- Server connections from PgBouncermedium
- Raising max_connectionsmedium
- Where ALTER SYSTEM writesmedium
- Password hashing method to usemedium
- The pg_hba line that never appliesmedium
- Users cannot create tables in publicmedium
- Roles missing after a restoremedium
- Making pg_restore use 8 coresmedium
- Deleting a customer is slowmedium
- I/O spikes at every checkpointmedium
- Index for a huge log tablemedium
- Modern auto-increment columnmedium
- Planner ignores a good indexmedium
- Computing the buffer cache hit ratiomedium
- Major upgrade with minutes of downtimemedium
- Testing pg_upgrade before the windowmedium
- Extensions after a major upgrademedium
- mysql_upgrade on MySQL 8.4medium
- Logging who read card datamedium
- Seeing the plan of a slow query in prodmedium
- pg_stat_statements view errorsmedium
- Checkpoints occurring too frequentlymedium
- What effective_cache_size doesmedium
- Planner prefers seq scans on NVMemedium
- Speeding up CREATE INDEXmedium
- Member of a role but no accessmedium
- Jumping from MySQL 5.7 to 8.4medium
- Foreign keys and indexes in InnoDBmedium
- UUID keys that index wellmedium
- Changing int to bigintmedium
- Adding a foreign key to a big tablemedium
- Data silently cut in MySQLmedium
- Reading type=ALL in MySQL EXPLAINmedium
- Deep pages load slowlymedium
- Big aggregate uses one CPUmedium
- CTE acts as an optimisation fencemedium
- Stopping one query filling the diskmedium
- Who is doing the I/Omedium
- Avoiding double caching in MySQLmedium
- What CLUSTER doesmedium
- Encrypting Aadhaar numbers in a columnmedium
- Bad estimates on a skewed columnmedium
- Read Uncommitted in PostgreSQLmedium
- Two SELECTs, two answersmedium
- When the Repeatable Read snapshot startsmedium
- Could not serialize due to concurrent updatemedium
- UPDATE re-checks the WHERE clausemedium
- Current transaction is abortedmedium
- MySQL keeps going after an errormedium
- Only one copy of a cron jobmedium
- What deadlock_timeout controlsmedium
- MySQL lock wait timeout behaviourmedium
- Finding who blocks a sessionmedium
- Capping total transaction timemedium
- Preventing overlapping room bookingsmedium
- Counting page views in MySQLmedium
- Table lock from UPDATEmedium
- Plain CREATE INDEX during trafficmedium
- Reading pg_locks for waitersmedium
- Logging long lock waitsmedium
- Taking a MySQL snapshot at startmedium
- LOCK TABLES in a transactionmedium
- Does PostgreSQL escalate row locksmedium
- RETURNING gives no row on conflictmedium
Hard 92
- Transaction ID wraparoundhard
- Index ignored because of a functionhard
- InnoDB gap lockshard
- Disk filling with WALhard
- Prepared statement slows down laterhard
- Estimates wrong for city and zip codehard
- Index-only scan still reads the tablehard
- Logical replication stopped after a migrationhard
- pg_upgrade --link and rollbackhard
- pg_wal growing without limithard
- Errant GTIDs block failoverhard
- Online schema change tools for MySQLhard
- Unique email on a partitioned tablehard
- Deleting old data every monthhard
- Primary demoted when etcd is unreachablehard
- WAL volume spikes after checkpointshard
- What encryption at rest protectshard
- Row-level security not appliedhard
- Risky SECURITY DEFINER functionhard
- Indexes bloat on a counter tablehard
- OOM killer after raising work_memhard
- Locks held by nobodyhard
- Faster commits with a durability costhard
- A WAL archive with silent gapshard
- Low traffic makes WAL archiving laghard
- Native incremental backups in PostgreSQL 17hard
- CDC breaks after every failoverhard
- Joining a small table on every shardhard
- Running PostgreSQL on Kuberneteshard
- Choosing logical or physical replicationhard
- Keeping WAL for replicas without slotshard
- Standby failures under ANY 2 of 4hard
- A base backup that will not starthard
- Join ignores the indexhard
- MySQL prefix index trade-offhard
- Wrong results after an OS upgradehard
- Checking an index for corruptionhard
- When an int key runs outhard
- Adding NOT NULL to a big tablehard
- ADD COLUMN that rewrote the tablehard
- Inserts slow down with UUID keyshard
- InnoDB history list keeps growinghard
- Where old row versions are kepthard
- More autovacuum workers, no speed-uphard
- Out of shared memory with partitionshard
- Disk full with a large pg_walhard
- Unlogged table empty after a crashhard
- UPDATE fails on a published tablehard
- MySQL ignores an index on phonehard
- Online column add in MySQL 8hard
- Data checksums on new clustershard
- A role that can run COPY FROM PROGRAMhard
- Measuring bloat exactlyhard
- Query scans every partitionhard
- Creating a partition takes ageshard
- Attaching a loaded partition quicklyhard
- Slow query over postgres_fdwhard
- Reading EXPLAIN BUFFERS outputhard
- Huge pages for a 64 GB buffer poolhard
- Linux OOM killer and PostgreSQLhard
- Write stalls from a small redo loghard
- Top queries without a slow loghard
- Finding queries that spill to diskhard
- Duplicate emails differing in casehard
- Any user can connect to any databasehard
- Index-only scans slow on an insert-only tablehard
- Duplicate key errors after cutoverhard
- Standbys after pg_upgrade --linkhard
- SELECT causes heavy writeshard
- Online ALTER fails at the endhard
- Old replication commands fail on 8.4hard
- Foreign key checks and row lockshard
- SELECT and SELECT FOR UPDATE disagreehard
- One UPDATE blocks the whole tablehard
- Long report under Serializablehard
- How PostgreSQL Serializable workshard
- Why fsync = off is dangeroushard
- Do SELECT-only transactions use XIDshard
- Two statements sent in one stringhard
- Many share locks on one rowhard
- Turning off InnoDB deadlock detectionhard
- MySQL Serializable and plain SELECThard
- Lowering MySQL isolation to cut lock waitshard
- An idle MySQL session blocks ALTERhard
- SELECT FOR UPDATE creates WALhard
- Phantoms at PostgreSQL Repeatable Readhard
- Slowdown from a savepoint per rowhard
- How readers know a row committedhard
- Deadlock from two concurrent insertshard
- Serializable only on one sidehard
- REPLACE INTO removed child rowshard
- Locked rows come back out of orderhard