liveBookmanning.com

What you will learn in PostgreSQL Mistakes and How to Avoid Them

by Jimmy Angelakos
Foreword by Gianni Ciolli

Read it on liveBook Get the book at manning.com

Chapter by chapter

Concepts explained in this book

• PostgreSQLPostgreSQL is a free, open-source object-relational database management system designed to implement SQL standards and support extensible data types and operations.
• backup and restoreA backup is an independently stored copy of data and required state that can be used to recover the original after loss or corruption.
• autovacuumAutovacuum is PostgreSQL's automatic background process for reclaiming obsolete row storage and performing routine table maintenance.
• software upgradeA software upgrade is the process of installing a newer version of software or its supporting components.
• snapshotA filesystem snapshot is a point-in-time representation of files and filesystem state that can be restored later.
• database bloatDatabase bloat is excess storage occupied by obsolete row versions and other unnecessary database space.
• timestamptzA timestamp with time zone is a PostgreSQL temporal data type that represents an instant in time and converts its display to the relevant time zone.
• monitoringA database log is a chronological record of database events, errors, activity, and operational information written for monitoring and diagnosis.
• uptimeA system design property that minimizes service interruption and maintains access during failures or maintenance.
• transaction logThe write-ahead log is PostgreSQL's sequential log of changes that must be recorded before corresponding data pages are written.
• UTF-8UTF-8 is a variable-length character encoding that represents Unicode code points as sequences of one to four bytes.
• connection poolerA connection pooler is software that maintains reusable database connections and assigns them to client operations as needed.
• PgBouncerPgBouncer is a lightweight connection pooler for PostgreSQL.
• partitioned tableA partitioned table is a logical table whose rows are stored across separate child partitions according to a partitioning rule.
• pg_dumppg_dump is a PostgreSQL command-line utility that exports a database into a logical dump for reconstruction.
• logical replicationLogical replication is database replication that transfers logical changes to selected data objects rather than copying physical storage pages.
• pg_basebackuppg_basebackup is a PostgreSQL utility that creates a base backup of a database cluster's data directory.
• transaction ID wraparoundXID wraparound is a PostgreSQL failure condition in which transaction identifiers cycle far enough that old tuple XIDs can be confused with future XIDs.
• missing valueA SQL NULL is a marker representing an absent, unknown, or inapplicable value that participates in three-valued logic rather than ordinary equality.
• multi-version concurrency controlMulti-Version Concurrency Control is a database concurrency technique that maintains multiple row versions to support consistent reads alongside concurrent writes.
• release notesA release note is technical documentation describing changes, fixes, compatibility considerations, and required actions for a software release.
• throughputTPS is the number of transactions a database system completes per second.
• major versionA major release is a PostgreSQL release that introduces substantial features and may change internal system-table or data-file formats.
• performanceDatabase performance is the efficiency and responsiveness with which a database system processes workloads.
• capacity planningCapacity planning estimates future data and workload resource requirements to guide system sizing and structural decisions.
• point-in-time recoveryPoint-in-time recovery is a database restoration technique that replays archived transaction logs to recover a system to a selected moment or transaction.
• sequential scanA full table scan is a query execution method that examines rows throughout a table rather than locating them through a selective index lookup.
• lockingA database lock is a concurrency control mechanism that permits or prevents access to a database object by other transactions.
• privilege escalationPrivilege escalation is the acquisition or exercise of permissions beyond those originally granted to a user or process.
• work_memwork_mem is a PostgreSQL configuration parameter that sets the memory available to an individual query operation for tasks such as sorting and hashing.
• vacuumVACUUM is a PostgreSQL command that reclaims storage occupied by obsolete row versions and updates table maintenance information.
• dead tupleA dead tuple is an obsolete PostgreSQL row version that is no longer visible to active transactions but remains in table storage until reclaimed.
• SQL_ASCIISQL_ASCII is a PostgreSQL pseudo-encoding that treats bytes 0–127 as ASCII and leaves bytes 128–255 uninterpreted without validation or conversion.
• continuous backupContinuous archiving is the ongoing preservation of database transaction-log segments so changes can be replayed during recovery.
• multi-master systemMulti-master replication is a replication architecture that permits multiple database nodes to accept writes and propagate changes among one another.
• replication lagReplication lag is the delay between a change being committed on a primary database server and being applied or visible on a replica.
• partitioning keyA partition key is a column or expression whose values determine how rows are assigned to table partitions.
• CAP theoremThe CAP theorem states that a distributed data system cannot simultaneously guarantee strong consistency, availability, and partition tolerance under network partition.
• PACELC theoremThe PACELC theorem describes the tradeoff between consistency and availability during partition and between latency and consistency when the system is operating normally.
• connection limitmax_connections is a PostgreSQL configuration parameter specifying the maximum number of client connections the server accepts concurrently.
• pg_stat_activitypg_stat_activity is a PostgreSQL system view that exposes information about server processes and their current activity.
• jsonbJSONB is PostgreSQL's binary JSON data type for storing structured data in a decomposed representation that supports indexing and efficient processing.
• shared_buffersshared_buffers is a PostgreSQL configuration parameter that sets the amount of shared memory allocated for database page buffers.
• concurrencyConcurrency is the condition in which multiple operations or users access or modify a system at overlapping times.
• idle in transaction sessionAn idle-in-transaction session is a database session with an open transaction but no active query and no commit or rollback.
• replicationDatabase replication is the process of copying and maintaining database data across multiple database instances.
• default configurationA default configuration is the set of settings applied automatically when a system is installed before environment-specific tuning.
• staging environmentA staging environment is a pre-production system configured to approximate production for testing applications and workloads.
• sequenceTime-series data consists of observations associated with timestamps or other ordered points in time.
• execution planA query plan is a representation of the operations and access methods a database system will use to execute a query.
• EXPLAINEXPLAIN ANALYZE is a PostgreSQL command option that executes a statement and reports its observed execution timings together with its query plan.
• search pathA schema search path is an ordered list of database schemas that PostgreSQL searches to resolve unqualified object names.
• index-only scanAn index-only scan answers a query from index contents without needing to fetch the corresponding table rows when visibility information permits.
• database functionA database function is a named routine stored in the database that accepts inputs and produces a result or side effect.
• vacuum fullVACUUM FULL is a PostgreSQL command that rewrites a table to compact it and return reclaimable space to the operating system.
• transaction IDA transaction ID is a PostgreSQL identifier assigned to a transaction and used in visibility tracking for multi-version concurrency control.
• timestamp without time zoneA timestamp without time zone, also called a naive timestamp, stores a date and clock time without information identifying a time zone or absolute instant.
• distributed systemA distributed system is a system whose components execute across multiple networked computing nodes that coordinate to provide a unified service.
• consistencyConsistency is a distributed-system guarantee that reads return the most recent data or report an error.
• DDL replicationDDL replication is the propagation of database structure changes, such as table or column alterations, between database nodes.
• partition tolerancePartition tolerance is the ability of a distributed system to continue operating despite dropped or delayed network messages.
• PostgreSQL replicationPostgreSQL replication is the process of copying database changes from one PostgreSQL instance to another.
• replication originA replication origin records the database or node from which a replicated transaction originally came.
• schema inconsistencySchema inconsistency is a condition in which replicated database nodes have different definitions for corresponding database objects.
• sequence synchronizationSequence synchronization is the coordination of generated numeric values across database nodes so that replicated writes do not reuse identifiers.
• shared-nothing architectureA shared-nothing architecture gives each node independent storage and processing resources rather than sharing them directly with other nodes.
• write conflictA data conflict is an incompatible set of replicated changes that cannot be applied together without violating a data constraint or intended state.
• serialization anomalyA distributed serialization anomaly is an inconsistency in the apparent ordering or isolation of concurrent operations across database nodes.
• cardinalityCardinality is the number or diversity of distinct values in a set or key column.
• partition pruningPartition pruning is the query-planning technique of excluding table partitions that cannot contain rows matching the query conditions.
• partitioning methodA partitioning method specifies the rule used to assign table rows to partitions.
• range partitioningRange partitioning assigns rows to partitions according to ordered lower and upper bounds of a partition key.
• timelineA PostgreSQL replication timeline is a WAL history branch that identifies the sequence of changes following a primary promotion or other divergence.
• migrationA database migration is a planned change to database structure or related objects that is applied to an existing database.
• configurationDatabase configuration is the set of settings that determines how a database system behaves and operates.
• foreign keyA foreign key relationship is a referential association in which columns in one table identify rows in another input table.
• data integrityData integrity is the accuracy, consistency, and trustworthiness of stored data throughout its lifecycle.
• data corruptionData corruption is the unintended alteration of stored data so that it no longer accurately represents its intended content.
• RAIDRAID is a storage technique that distributes or duplicates data across multiple disk devices to improve performance, redundancy, or both.
• NOT INNOT IN is a SQL predicate that tests whether a value is absent from a set of values or subquery results, subject to SQL null semantics.
• optimizer statisticsOptimizer statistics are summarized data-distribution measurements that a query planner uses to estimate query costs and select execution plans.
• ruleCREATE RULE is a PostgreSQL command that defines query-rewrite rules and associated actions for database objects.
• pg_stat_all_indexespg_stat_all_indexes is a PostgreSQL system view that reports usage and statistical information for indexes on all tables.
• pg_statvizpg_statviz is a PostgreSQL extension for capturing, storing, analyzing, and visualizing database statistics over time.
• minor releaseA minor release is a PostgreSQL release that fixes bugs, security issues, or data-corruption problems without adding features or changing internal data formats.
• query plannerA query planner or optimizer is the database component that selects and may transform an execution strategy for a SQL query.
• database connectionA database connection is an active communication session between a client and a database server.
• connection poolingConnection pooling is a technique that reuses a bounded set of database connections to serve a larger number of client requests.
• DecimalNUMERIC is an exact SQL numeric data type for storing values with specified precision and scale.
• lightweight lockA lightweight lock is a PostgreSQL synchronization primitive that protects shared-memory data from conflicting concurrent access.
• process thrashingProcess thrashing is a condition in which excessive scheduling and context switching leave processes with too little execution time to make useful progress.
• serializable isolation levelSERIALIZABLE is a SQL transaction isolation level that requires concurrent transactions to produce results equivalent to some serial execution.
• RAID 1RAID 1 is a RAID level that maintains byte-for-byte copies of data on multiple disks through mirroring.
• recovery point objectiveA recovery point objective is the maximum amount of data loss measured in time that a disaster-recovery plan is designed to tolerate.
• recovery time objectiveA recovery time objective is the maximum acceptable duration of service unavailability after a disruptive failure.
• server redundancyServer redundancy is the provision of multiple database servers so service can continue when one server fails or is unavailable.
• partition boundaryA partition boundary is a set of value limits that determines which partition-key values a table partition accepts.
• Postgres backend processA backend process is a PostgreSQL server process that handles a client connection and executes its database work.
• atomic typeA data type defines the kind of value a database can store and the operations permitted on that value.
• SQL triggerA database trigger is a routine that executes automatically in response to specified events on a database object.
• JSONJSON is a text-based data interchange format that represents structured values using objects, arrays, strings, numbers, booleans, and null.
• base backupA base backup is a physical copy of a PostgreSQL database cluster taken at a specific point in time for restoration.
• memory allocationMemory allocation is the assignment of available memory resources to database processes and operations.
• oom killerThe OOM killer is a Linux kernel mechanism that terminates selected processes when system memory is critically exhausted.
• pgbenchpgbench is a PostgreSQL benchmarking tool that runs concurrent database transaction workloads and reports performance results.
• backup automationAn automated backup is a backup created by scheduled or event-driven software without requiring manual execution.
• pg_stat_statementspg_stat_statements is a PostgreSQL extension that records execution statistics for SQL statements.
• xmlXML is a text-based markup language and document format for representing structured data with elements, attributes, and declarations.
• backup testingBackup testing is the practice of restoring backups in an isolated environment to verify that the backups and recovery process work correctly.
• pageinspectpageinspect is a PostgreSQL extension that provides functions for inspecting the contents and metadata of database pages.
• pg_verifybackuppg_verifybackup is a PostgreSQL command-line utility that verifies the integrity of a base backup.
• pg_waldumppg_waldump is a PostgreSQL command-line utility that displays and parses write-ahead log records.
• antipatternAn antipattern is a recurring design or implementation approach that appears reasonable but produces harmful consequences.
• ACIDACID is the set of atomicity, consistency, isolation, and durability properties that characterize reliable database transactions.
• common table expressionA common table expression is a named temporary query result defined with SQL WITH syntax and available within a larger statement.
• distributed transactionA distributed transaction is an atomic unit of work whose operations execute across multiple database nodes.
• global transaction managerA global transaction manager coordinates transaction execution and completion across multiple database nodes.
• read replicaA read replica is a database server that receives replicated data and serves read operations rather than accepting the authoritative writes.
• streaming replicationStreaming replication continuously transfers and replays a database server's write-ahead log changes to another database server.
• synchronous replicationSynchronous replication requires configured replication standbys to confirm replicated changes before the committing transaction completes.
• two-phase commitTwo-phase commit is a distributed transaction protocol in which participants first prepare to commit and then commit or abort based on a coordinator decision.
• principle of least privilegeThe principle of least privilege is a security practice of granting each account only the permissions required for its tasks.
• row-level securityRow-level security is a database access-control mechanism that restricts which table rows individual users can access.
• schemaA database schema is a named namespace within a database that contains objects such as tables, views, and functions.
• failoverFailover is the transition of service from a failed or unavailable primary system to a replica or alternate system.
• index scanAn index scan is a query execution method that retrieves matching rows by traversing a database index.
• pg_upgradepg_upgrade is a PostgreSQL utility that upgrades a database cluster to a newer major version while preserving its data.
• table inheritanceTable inheritance is a PostgreSQL feature that creates a parent-child relationship in which one table inherits columns from another table.
• log rotationLog rotation is the practice of periodically closing, renaming, compressing, or deleting log files to control their storage use.
• composite keyA composite unique key is a uniqueness rule defined over multiple columns whose combined values identify a row combination.
• nulls not distinctNULLS NOT DISTINCT is a PostgreSQL uniqueness option that treats null values as equal for uniqueness enforcement.
• freezingTuple freezing is PostgreSQL maintenance that marks sufficiently old row versions as unaffected by transaction ID aging.
• invalid byte sequenceAn invalid byte sequence is a sequence of encoded bytes that cannot be interpreted as a valid instance of the intended character encoding.
• Latin-1latin1 is a character encoding associated with ISO-8859-1 and commonly used as a legacy MySQL default.
• MySQL utf8MySQL utf8 is a legacy MySQL character encoding implementation limited to Unicode characters representable in at most three bytes.
• null characterThe NUL character is the character with code point zero, commonly represented as hexadecimal 00 or octal 0.
• encodingA character encoding maps textual characters to coded representations such as bytes for storage and processing.
• utf8mb4utf8mb4 is MySQL's character encoding that supports Unicode characters encoded with up to four bytes.
• BarmanBarman is an open-source backup and disaster-recovery tool for PostgreSQL.
• pgBackRestpgBackRest is an open-source PostgreSQL backup and restore tool supporting features such as scheduling, retention, parallel transfer, and multiple repositories.
• universally unique identifierUUID is a 128-bit identifier data type whose values are conventionally represented as 32 hexadecimal digits separated by hyphens.
• analyze statementANALYZE is a PostgreSQL command that collects statistics describing table data for use by the query planner.
• PostgreSQL data directoryPGDATA is the PostgreSQL data directory that contains a database cluster's data files.
• technical debtTechnical debt is the accumulated cost of delaying or choosing expedient implementations that make future changes or maintenance more difficult.
• B-treeA B-tree index is an ordered database index structure that efficiently supports equality, ordering, and compatible prefix-pattern lookups.
• GIN indexGIN is a PostgreSQL index access method designed for efficiently indexing composite values such as arrays and JSONB documents.
• tsvectortsvector is a PostgreSQL data type representing normalized searchable text as a collection of lexemes and their positions.
• security definerSECURITY DEFINER is a PostgreSQL function execution mode that runs the function with the privileges of its owner rather than its caller.
• access patternA usage pattern is a recurring way in which a database system or feature is configured or used.
• floating-point arithmeticA floating-point data type stores approximate real-number values using finite binary or decimal precision.
• idle_in_transaction_session_timeoutidle_in_transaction_session_timeout is a PostgreSQL setting that terminates a session after it remains idle while holding an open transaction for the configured duration.
• disk usageDisk space usage is the amount of storage capacity occupied on a disk or filesystem.
• integerAn integer data type represents whole-number values without a fractional component.
• functional indexAn expression index is a database index whose indexed keys are computed from expressions over table data.

These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.