Knowledgebase

Articles

Aggregation and Grouping in Practice Summarising data.WHAT AGGREGATION DOESReduces many rows to a summary.WHAT THE COMMON FUNCTIONS... Analysing the Slow Query Log Finding what to fix.WHY IT IS THE PRIMARY TOOLIt records exactly what was slow, with figures.WHAT... Auditing Database Activity Recording who did what.WHAT TO AUDITPrivilege changes Schema changes Access to sensitive tables... Automating Database Maintenance Routine work that should not be manual.WHAT TO AUTOMATEBackups, and their verification Transfer... Backing Up Databases Properly Protecting the data.WHAT THE METHODS ARELogical dumps, producing statements to recreate the data... Benchmarking and Load Testing Databases Measuring before committing.WHY BENCHMARKTo know whether a change helps, rather than believing it... Building Confidence With Databases Getting comfortable operating them.WHAT MAKES PEOPLE NERVOUSChanges that cannot be undone Not... Building Reports Without Hurting Production Analytical queries on a live system.WHAT THE PROBLEM ISReports scan large amounts of data,... Caching in Front of the Database Reducing load.WHAT CACHING DOESStores the result of expensive work so it need not be... Capacity Planning for Databases Preparing for growth.WHAT TO PROJECTData volume Query volume Concurrency Working set against... Character Sets and Collations Storing text correctly.WHAT A CHARACTER SET ISThe set of characters that can be stored, and how... Choosing Between Database Types Selecting sensibly.WHAT TO START WITHA relational database.WHYIt handles the overwhelming... Choosing Data Types Getting columns right.WHY IT MATTERSTypes affect storage, speed, index size and whether invalid... Cleaning Up Bad Data Fixing what accumulated.WHAT TO ESTABLISH FIRSTHow the bad data got there.WHY BEFORE... Common Database Mistakes What goes wrong.RUNNING UPDATE OR DELETE WITHOUT A WHERE CLAUSEChanges or removes every row. No... Core Database Principles: Everything That Matters, Briefly The whole category in one page.RECOVERABLE FIRST, THEN AVAILABLE, THEN FASTA slow database is an... Database Administration Careers and Skills Working in this area.WHAT THE ROLE COVERSOperations: availability, backups, upgradesPerformance:... Database Administration in the Nigerian Context Local realities.WHAT CONSTRAINS WORK HEREBandwidth cost for large transfers Latency to distant... Database Administration: Everything That Matters, Briefly The whole category summarised.A BACKUP THAT HAS NEVER BEEN RESTORED IS NOT A BACKUPTest the... Database Cost Management Controlling what it costs to run.WHAT DRIVES COSTStorage, including backups and logs Memory,... Database Migrations in Applications Managing schema in code.WHAT A MIGRATION ISA versioned, repeatable change to the schema.WHY THEY... Database Practices for Small Teams Sensible arrangements without a specialist.WHAT MATTERS MOST WITH LIMITED TIMEBackups that work... Deciding Which Indexes to Create Where to put them.WHAT TO INDEXColumns used in filtering conditions Columns used for joining... Designing Tables for a Web Application Practical schema decisions.WHAT EVERY TABLE SHOULD HAVEA primary key Created and updated... Diagnosing a Slow Application Database Working through performance complaints.WHAT TO ESTABLISH FIRSTWhether the database is actually... Documenting a Database Recording what exists.WHAT TO RECORDWhat each database is for Which application uses it Where it... Emergency Response for Database Incidents When something serious happens.WHAT THE FIRST RULE ISStop, and establish the actual state before... Handling Bulk Updates and Deletes Changing many rows.WHAT GOES WRONG WITH A SINGLE LARGE STATEMENTLocks held for the duration... Handling Connection Limits When the database refuses connections.WHAT THE SYMPTOM ISAn application reporting too many... Handling Counters and High-Contention Rows Rows everything updates.WHAT THE PROBLEM ISA single row updated by every transaction becomes a... Handling Database Failover in Applications What the application must do.WHAT HAPPENS DURING FAILOVERConnections are dropped Writes fail for... Handling Dates and Times in Databases Storing when things happened.WHAT TO STOREA proper temporal type, never text.WHAT THE COMMON... Handling Sensitive Data in Databases Protecting what is held.WHAT COUNTS AS SENSITIVEPersonal details Financial information Health... Handling Soft Deletion Records that are hidden rather than removed.WHAT IT MEANSMarking a row as deleted rather than... High Availability for Databases Surviving a server failure.WHAT THE OPTIONS AREA replica promoted manually Automatic failover... How Indexes Work The single most important concept.WHAT AN INDEX ISA separate structure letting the database find... Importing and Exporting Data Moving data in and out.WHAT FORMATS ARE COMMONStatement dumps Delimited text Structured text... Installing and Configuring MySQL or MariaDB Getting a server running properly.WHAT TO DO AFTER INSTALLATIONRun the security script, or its... Keeping Development and Production Data Separate Safe environments.WHY IT MATTERSProduction data in a development environment is a breach waiting... Locking and Concurrency Several writers at once.WHAT A LOCK DOESPrevents conflicting access while a change is in... Managing Database Growth Data that keeps accumulating.WHAT GROWSTransactional records Logs and audit tables Sessions... Managing Database Schemas Across Environments Keeping development, testing and production aligned.WHAT DIVERGESSchema, when changes are made by... Managing Database Servers in Containers Databases in modern infrastructure.WHAT CHANGESStorage must persist outside the container... Managing Database Users and Privileges Controlling access.WHAT A USER IS DEFINED BYA name and a host pattern together.WHY THAT SURPRISES... Managing Databases on Shared Hosting Working within limits.WHAT CONSTRAINS YOULimits on connections, queries and processes Shared... Managing Large Tables When a table becomes unwieldy.WHAT CHANGES AT SCALEFull scans become impossible Schema changes... Managing Time Zones Across Systems Consistency between application and database.WHAT GOES WRONGThe application, the database and the... Migrating Between Database Servers Moving a database.WHAT TO ESTABLISH FIRSTSize, and how long a dump and restore takes.WHYIt... Monitoring Database Health Noticing problems early.WHAT TO WATCHTotal size, and its growth rate The largest tables Query... Normalisation and When to Break It Structuring tables sensibly.WHAT NORMALISATION ISOrganising data so each fact is stored once.WHY... Planning Database Maintenance Windows Scheduling disruptive work.WHAT NEEDS A WINDOWVersion upgrades Large schema changes Hardware or... Point-in-Time Recovery Restoring to a specific moment.WHAT IT MEANSRestoring a backup, then replaying changes up to a... Preparing a Database for an Audit Demonstrating control.WHAT AUDITORS TYPICALLY ASK ABOUTWho has access, and how it is granted and... Preventing Lost Updates Concurrent writes to the same record.WHAT A LOST UPDATE ISTwo transactions reading a value, both... Preventing SQL Injection The most serious database vulnerability.WHAT IT ISAn attacker supplying input that becomes part... Read Replicas and Scaling Reads Spreading query load.WHAT THEY PROVIDEAdditional servers answering read queries.WHAT THEY DO NOT... Reading Database Error Messages Understanding what went wrong.WHAT TO DO FIRSTRead the whole message, including the number.WHY... Reading an Execution Plan Understanding what the database will do.WHAT A PLAN SHOWSHow the database intends to satisfy a... Relational Database Fundamentals The model underneath.WHAT A RELATIONAL DATABASE STORESData in tables, as rows and columns.WHAT A... Reviewing an Inherited Database Assessing what you have taken on.WHAT TO ESTABLISH FIRSTWhere it is How large What uses it... Schema Changes on Live Systems Altering tables without downtime.WHAT THE PROBLEM ISSome alterations lock the table for the... Searching Text in Databases Finding words in content.WHAT PATTERN MATCHING PROVIDESSimple matching, usable with an index only... Securing a Database Server Reducing exposure.WHAT TO DO FIRSTEstablish whether it needs to accept connections from outside... Setting Up Replication Practical configuration.WHAT TO PREPAREA unique server identifier on each Binary logging on the... Setting Up a Database for a New Application Starting correctly.WHAT TO DECIDE FIRSTWhich database, and which version.WHAT TO CREATEA database... Storage Engines and What They Mean How data is actually stored.WHAT A STORAGE ENGINE ISThe component handling storage, indexing and... Storing and Querying Structured Documents Nested data in relational databases.WHAT MODERN RELATIONAL DATABASES SUPPORTColumns holding... Testing and Performing Restores The part everyone skips.WHY IT MATTERS MORE THAN THE BACKUPA backup that cannot be restored is... Tuning Database Configuration Changing settings sensibly.WHAT TO DO FIRSTNothing.WHYMost performance problems are queries and... Understanding Autoincrement and Sequences Generating identifiers.WHAT AN AUTOINCREMENT COLUMN DOESAssigns the next number... Understanding Connection Encryption Protecting data in transit.WHAT IT PROTECTSCredentials and data between the application and the... Understanding Database Constraints Rules the database enforces.WHAT TYPES EXISTNot null Unique Primary key Foreign key Check... Understanding Database Deadlocks in Applications Diagnosing and preventing them.WHAT AN APPLICATION SEESAn error saying a deadlock was detected... Understanding Database Logs What the server records.WHAT LOGS EXISTThe error log The slow query log The general query log The... Understanding Database Sharding Splitting data across servers.WHAT IT MEANSDividing a dataset across several databases, each... Understanding Database Statistics How the optimiser decides.WHAT STATISTICS AREEstimates about data distribution, used to choose a... Understanding Document Databases MongoDB and similar.WHAT THEY STOREDocuments, typically structured as nested records.WHAT THAT... Understanding Isolation Levels What transactions see.WHAT THE LEVELS ARE, BROADLYRead uncommitted Read committed Repeatable read... Understanding Joins Combining tables in queries.WHAT A JOIN DOESMatches rows from two tables on a condition.WHAT AN... Understanding Key-Value and Cache Stores Redis and similar.WHAT THEY AREFast stores holding data in memory, addressed by key.WHAT THEY ARE... Understanding Primary Key Choices Natural, surrogate and composite keys.WHAT A SURROGATE KEY ISA meaningless identifier generated... Understanding Query Caching and Buffers What is held in memory.WHAT THE BUFFER POOL HOLDSData and index pages recently read.WHY IT... Understanding Replication Copying data to another server.WHAT IT DOESKeeps a second server holding a copy of the first.WHAT... Understanding SQL Modes and Strictness How forgiving the database is.WHAT A PERMISSIVE MODE DOESAccepts invalid data with a warning,... Understanding Transactions Grouping changes safely.WHAT A TRANSACTION ISA group of statements applied entirely or not at... Understanding Undo and Redo How databases guarantee durability.WHAT REDO RECORDSChanges, written before they reach the data... Understanding and Repairing Corruption When data goes wrong.WHAT CAUSES ITUnclean shutdown Failing storage Memory faults Copying data... Upgrading Database Versions Moving to a newer release.WHY TO UPGRADESecurity fixes Performance improvements Features Support... Using Managed Database Services Letting someone else operate it.WHAT THEY PROVIDEInstallation and patching Automated backups... Using Window Functions Calculations across related rows.WHAT THEY DOCompute a value over a set of rows while keeping... Views and Stored Routines Logic held in the database.WHAT A VIEW ISA stored query, used like a table.WHAT IT... What a Database Administrator Actually Does The scope of the work.WHAT THE WORK COVERSKeeping databases available Keeping them fast Keeping... Working With Database Connection Pools Managing connections efficiently.WHAT A POOL ISA set of connections reused across requests.WHY IT... Working With Database Vendors and Support Getting help.WHEN TO ESCALATESuspected corruption Crashes with no clear cause Performance... Working With Developers on Database Design Collaboration between roles.WHAT DEVELOPERS OPTIMISE FORDelivering features.WHAT ADMINISTRATORS... Working With PostgreSQL A different relational database.WHAT DISTINGUISHES ITStrict standards compliance Rich data types,... Working With Read-Only and Reporting Users Accounts for people, not applications.WHY THEY NEED SEPARATE ACCOUNTSTheir queries are... Working With Very Wide Tables Tables with many columns.HOW THEY ARISEColumns added over years, none removed.WHAT PROBLEMS THEY... Writing Queries That Perform Practical query construction.WHAT TO SELECTOnly the columns needed.WHY NOT EVERYTHINGIt transfers... Writing SQL Others Can Maintain Queries others can read.WHAT MAKES SQL HARD TO READEverything on one line Inconsistent...
Back

Are you happy with your experience? Leave us a review on Trustpilot.


Trustpilot