liveBookmanning.com

What you will learn in Just Use Postgres!

All the database you need

by Denis Magda
Foreword by Josh Long
Afterword by Vlad Mihalcea

Read it on liveBook Get the book at manning.com

Chapter by chapter

Concepts explained in this book

• PostgreSQLPostgreSQL is an open-source object-relational database management system with extensibility and support for SQL.
• sequential scanA sequential scan, or full table scan, examines every row of a table to find rows matching a query rather than locating them through an index.
• lexemeA lexeme is a normalized linguistic term produced from text for representation and matching in full-text search.
• bitmap index scanA bitmap index scan is a query-plan operation that uses an index to build a bitmap of matching row locations before retrieving the corresponding table rows.
• B-treeA B-tree index is a balanced tree-based database index that supports ordered searches with logarithmic lookup complexity.
• GIN indexGIN is a PostgreSQL index access method designed for indexing composite values such as arrays and full-text search vectors.
• EXPLAINEXPLAIN ANALYZE is a PostgreSQL command form that executes a statement and displays its execution plan with actual runtime details.
• bitmap heap scanA bitmap heap scan reads table rows identified by a bitmap produced by an index scan and may recheck conditions for lossy pages.
• queueFIFO ordering delivers items in the same order in which they were added.
• execution planA query execution plan is the database engine's planned sequence of operations for executing a SQL query, optionally including runtime analysis.
• geometric data typeA geometry is a mathematical representation of a spatial object, including its coordinates and shape.
• common table expressionA common table expression is a named temporary query result defined within a statement and available to a subsequent primary query in that statement.
• GiST indexA GiST index is an index built with the GiST access method for searching complex data types and operator-defined predicates.
• single-column indexA single-column index is a database index whose key is derived from one table column.
• tsvectorA tsvector lexeme is a normalized term stored as an element of PostgreSQL's searchable text-vector representation.
• composite indexA composite index is a database index defined over multiple columns, ordering entries first by one column and then by subsequent columns.
• pointPoint is a PostGIS geometric subtype representing a single position with coordinate values.
• -> operatorThe PostgreSQL JSON -> operator extracts a field or array element while preserving the result as JSON.
• check constraintA check constraint is a database rule that rejects a row when a specified Boolean condition is false.
• index scanAn index scan is a database access strategy that retrieves matching table rows through an index rather than examining the entire table.
• retrieval-augmented generationRetrieval-augmented generation is a technique in which a generative model incorporates information retrieved from an external knowledge source when generating an answer.
• @> operatorThe PostgreSQL JSONB containment operator @> tests whether the left JSONB value contains the structure represented by the right JSONB value.
• partial indexA partial index is a database index that contains entries only for table rows satisfying a specified condition.
• PostGISPostGIS is a PostgreSQL extension that adds storage, indexing, and querying capabilities for geospatial data.
• pgmqpgmq is a PostgreSQL extension that provides persistent message-queue functionality for application messages and events.
• cosine distanceCosine distance is a dissimilarity measure derived from the angle between two vectors, commonly defined as one minus cosine similarity.
• ivfflatAn IVFFlat index is an approximate nearest-neighbor index that assigns vectors to inverted lists organized around cluster centroids.
• database functionA database function is a stored routine that accepts inputs, performs computation or data operations, and returns a value or result within the database.
• database extensionA Postgres extension is an installable package that adds data types, functions, operators, indexes, or other capabilities to a PostgreSQL database system.
• jsonbjsonb is PostgreSQL's binary JSON data type that stores JSON values in a decomposed form for efficient processing and indexing.
• tsquerytsquery is a PostgreSQL data type representing a normalized full-text search query composed of lexemes and search operators.
• HNSWAn HNSW index is a graph-based approximate nearest-neighbor index using a hierarchical navigable small-world graph.
• BRIN indexA BRIN index stores summaries of value ranges for physical table blocks to support efficient scans of naturally ordered data.
• polygonA polygon is a closed two-dimensional geometric figure represented by connected line segments and used to model planar areas.
• JSON objectA JSON object is an unordered collection of name-value members represented in the JSON data format.
• data integrityData integrity is the correctness, consistency, and validity of data throughout its storage and modification.
• JSONA JSON document is structured data represented using the JavaScript Object Notation format.
• ST_DWithinST_DWithin is a PostGIS function that tests whether two geometries or geography values are within a specified distance.
• schemaA database schema is a named logical namespace within a database that contains database objects such as tables.
• ST_DistanceST_Distance is a PostGIS function that calculates the minimum distance between two geometries or geography values.
• window functionA window function computes a value over a set of rows related to the current row without collapsing those rows into a single aggregate result.
• jsonb_opsjsonb_ops is PostgreSQL's default GIN operator class for JSONB values that indexes distinct keys, values, and array elements.
• jsonb_path_opsjsonb_path_ops is a PostgreSQL GIN operator class for JSONB values that indexes hashed paths from the JSON root to values and array elements.
• operator classA PostgreSQL operator class defines the operators and comparison semantics that an index access method uses for a data type.
• recallRecall is the proportion of relevant items that a search system retrieves from all relevant items available.
• functional indexA functional index is a database index whose entries are computed from a function or expression applied to one or more table columns.
• to_tsqueryto_tsquery is a PostgreSQL function that parses text into a tsquery value using PostgreSQL full-text search syntax and normalization rules.
• psqlpsql is PostgreSQL's lightweight command-line client for connecting to databases and executing SQL and psql meta-commands.
• geospatialGeospatial refers to data or computation representing locations, geometries, and relationships on or near the Earth's surface.
• bigserialBIGSERIAL is a PostgreSQL pseudo-type that creates a 64-bit integer column backed by a sequence for automatically generated values.
• pgvectorpgvector is a PostgreSQL extension that adds vector data types and similarity search capabilities.
• psql meta-commandA psql meta-command is a backslash-prefixed instruction interpreted by the psql command-line client rather than by the SQL server.
• foreign keyA foreign key constraint requires values in a column or set of columns to match existing unique or primary-key values in a referenced table.
• exclusion constraintAn exclusion constraint requires that at least one comparison between two rows on specified expressions and operators be false or null.
• sequenceTime-series data consists of observations associated with timestamps or time intervals, often representing measurements or events over time.
• full-text search configurationA full-text search configuration is a PostgreSQL definition that maps token types to dictionaries for a language or search behavior.
• spatial reference system identifierA spatial reference system identifier, or SRID, is a numeric code identifying the coordinate reference system associated with spatial data.
• columnar dataA columnar store is a storage layout that organizes values by column to improve analytical scans over selected columns and large data ranges.
• database pageA database page is a fixed-size unit of physical storage that holds multiple database table or index records.
• index-only scanAn index-only scan satisfies a query from index contents without fetching the corresponding table rows when the required data is available in the index.
• primary keyA primary key is a column or set of columns that uniquely identifies each row in a table and cannot contain null values.
• create role statementThe CREATE ROLE statement is a PostgreSQL SQL command that creates a database role and can assign role attributes.
• grant statementThe GRANT statement is an SQL command that assigns privileges or role membership to a database principal.
• revoke statementThe REVOKE statement is an SQL command that removes privileges or role membership from a database principal.
• not-null constraintA NOT NULL constraint requires a column value to be present rather than SQL NULL.
• to_tsvector functionThe PostgreSQL to_tsvector function converts text into a tsvector using a specified or default text-search configuration.
• layerA geodata layer is a collection of geographic features or attributes represented together as one thematic layer.
• bitmapA bitmap is a compact structure that records table-row or table-page locations selected by an index condition.
• databaseA database is a named collection of database objects such as schemas, tables, views, and indexes.
• SQL triggerA database trigger is a stored procedure that the database automatically executes in response to specified events on database objects.
• mxbai-embed-largemxbai-embed-large is an open-source embedding model that produces 1,024-dimensional vector representations.
• retrieval rankingRelevance ranking orders text-search results according to how closely their contents match a query.
• hypertableA hypertable is a TimescaleDB-managed table that automatically organizes time-series rows into partitions called chunks.
• multilayer graphA multilayer graph is a graph organized into layers in which higher layers contain sparser subsets of nodes that also occur in lower layers.
• extensibilityExtensibility is the capability of a database system to add or customize data types, operators, access methods, and other functionality.
• SKIP LOCKED clauseFOR UPDATE SKIP LOCKED is a PostgreSQL row-locking clause that locks eligible rows while skipping rows already locked by other transactions.
• unique constraintA unique constraint requires that values in a specified column or set of columns be distinct within a table.
• PL/pgSQLPL/pgSQL is PostgreSQL's procedural programming language for writing database functions and other server-side logic.
• over clauseAn OVER clause defines the rows, partitions, ordering, and frames over which a SQL window function operates.
• window partitionA PARTITION BY clause divides rows processed by a window function into independent groups based on specified expressions.
• rank functionA rank function assigns ordinal rankings to rows according to an ordering and gives tied rows the same rank.
• window frameA window frame is the ordered subset of rows within a window partition that contributes to a window-function calculation for a row.
• create extension commandThe CREATE EXTENSION command installs and enables a PostgreSQL extension in the current database.
• retention policyA data retention policy is a rule that automatically removes time-series chunks containing data older than a specified age.
• geographic information systemA geographic information system is software for working with, analyzing, and visualizing geographically referenced data.
• setweightsetweight is a PostgreSQL function that assigns a weight label to lexemes in a tsvector.
• term frequencyTerm frequency is the number of occurrences of a term in a document or text representation.
• ts_rankts_rank is a PostgreSQL function that computes full-text search relevance from term frequency, lexeme positions, weights, and optional normalization.
• FOR UPDATE clauseA FOR UPDATE clause locks selected rows so that concurrent transactions cannot independently modify or process them until the lock-holding transaction ends.
• alter table commandThe ALTER TABLE command is an SQL statement that changes the definition of an existing table, including its constraints.
• planning timePlanning time is the elapsed time a database query planner spends selecting an execution plan.
• shared bufferA shared buffer is a preallocated PostgreSQL memory area that caches pages from tables and indexes.
• ? operatorA JSONB key-exists operator tests whether a specified key exists in a JSONB value.
• bucketA time bucket is a fixed temporal interval used to group time-series observations for aggregation.
• json path expressionSQL/JSON path language is a standardized language for navigating and filtering JSON data within SQL expressions.
• ST_AreaST_Area is a PostGIS function that returns the area of a polygonal geometry in the units associated with its spatial reference system.
• vector databaseA vector database is a database designed to store vector representations and support efficient similarity searches over them.
• time_bucket functionThe time_bucket function groups time-series rows into fixed-width temporal buckets and aggregates or returns values for buckets containing data.
• language modelAn LLM is a large language model trained to understand and generate natural-language text.
• stop wordA stop word is a frequent function word, such as an article or preposition, that is excluded during text normalization because it contributes little search meaning.
• multi-version concurrency controlMultiversion concurrency control is a database concurrency technique that maintains multiple row versions so transactions can observe consistent data without blocking all concurrent access.
• continuous aggregateA continuous aggregate is a TimescaleDB query-result object that stores precomputed aggregate data and refreshes it incrementally according to a defined policy.
• latitudeLatitude is the angular coordinate measuring position north or south of the equator on Earth.
• longitudeLongitude is the angular coordinate measuring position east or west of the prime meridian on Earth.
• spatial reference systemA spatial reference system defines the coordinate system and spatial interpretation used to represent geographic locations and geometries.
• ST_MakePointST_MakePoint is a PostGIS function that constructs a point geometry from X and Y coordinates.
• ST_SetSRIDST_SetSRID is a PostGIS function that assigns spatial reference system metadata to a geometry without changing its coordinates.
• ST_TransformST_Transform is a PostGIS function that converts a geometry's coordinates to a specified spatial reference system.
• ST_XST_X is a PostGIS function that returns the X coordinate of a point geometry.
• ST_YST_Y is a PostGIS function that returns the Y coordinate of a point geometry.
• line segmentA line segment is a finite linear geometric object bounded by two endpoints or represented as part of a connected path.
• message queue APIA message queue API is an interface for publishing, reading, and managing messages in a queue.
• tokenA token is a smaller text component produced by parsing a document, such as a word, number, or phrase.
• bounding box operatorA bounding box operator tests spatial relationships between the minimum bounding rectangles of geometries.
• index-aware functionAn index-aware function is a spatial function whose query conditions can be transformed into index-supported bounding-box tests before exact evaluation.
• ST_ExpandST_Expand is a PostGIS function that expands a geometry's bounding box by a specified distance.
• bounding boxA bounding rectangle is a rectangular region that encloses the spatial extent of a geometric object or set of objects.
• jsonb_setjsonb_set is a PostgreSQL function that returns a JSONB value with the value at a specified path replaced or optionally inserted.
• covering indexA covering index is an index that stores all columns needed by a query, allowing qualifying results to be returned without reading the table rows.
• universally unique identifierA UUID is a standardized 128-bit identifier designed to be globally unique without relying on a database sequence.
• signature treeA signature tree is a GiST index structure whose entries summarize text documents with bit-string signatures.
• signature collisionA signature collision occurs when different lexemes set the same bit positions in a signature-based index.
• stored generated columnA stored generated column is a table column whose value is automatically computed from an expression and physically stored.
• DockerDocker is a platform for building, distributing, and running software in containers.
• volumeA Docker volume is persistent host-managed storage mounted into containers independently of their writable container filesystems.
• analyze statementANALYZE is a PostgreSQL command that collects and updates statistics describing the contents of database tables.
• TimescaleDBTimescaleDB is a PostgreSQL extension for storing and querying time-series data.
• visibility timeoutA visibility timeout is a queue-delivery interval during which a retrieved message remains hidden from other consumers before becoming available again if it is not acknowledged or removed.
• LangChainLangChain is a software framework for building applications that compose language models with prompts, tools, data sources, and processing steps.
• generate_series functionThe generate_series function is a PostgreSQL set-returning function that produces sequential numeric or timestamp values over a specified range.
• \d commandThe psql \d command lists tables, views, and sequences in the connected database.
• st_equalsST_Equals is a PostGIS spatial predicate that tests whether two geometries have equal dimensions and occupy the same point set.
• st_npointsST_NPoints is a PostGIS function that returns the number of points in a geometry.
• st_withinST_Within is a PostGIS spatial predicate that tests whether one geometry lies completely within another geometry.
• generative modelingGenerative AI comprises artificial-intelligence models that produce new content such as text, images, audio, or code from learned patterns.
• followed by operatorThe PostgreSQL full-text search distance operator requires one lexeme to occur a specified number of positions after another.
• tokenizationTokenization is the process of splitting text into discrete tokens for linguistic or computational processing.
• SP-GiST indexAn SP-GiST index is a space-partitioned generalized search tree index for data structures that support recursive partitioning.
• jsonb_path_existsjsonb_path_exists is a PostgreSQL function that returns whether a JSON path produces at least one result for a jsonb value.
• jsonb_path_queryjsonb_path_query is a PostgreSQL function that evaluates a JSON path against a jsonb value and returns the matching JSON items.
• full-text search dictionaryA full-text search dictionary is a PostgreSQL component that converts tokens into lexemes or determines that no lexeme applies.
• search pathA PostgreSQL search path is an ordered list of schemas searched for unqualified database objects.
• create_hypertable functionThe create_hypertable function converts a regular PostgreSQL table into a TimescaleDB hypertable and defines its partitioning configuration.
• mock data generationMock data generation is the creation of artificial records that approximate real data for experimentation, testing, or demonstration.
• container imageA container image is an immutable package containing a filesystem and configuration from which an isolated container runtime is created.
• create view commandThe CREATE VIEW command is an SQL statement that creates a named database view from a query.
• materialized viewA materialized view is a query-defined relation whose result is persisted and remains unchanged until explicitly refreshed.
• parallel sequential scanA parallel sequential scan divides a table scan among a leader process and one or more parallel worker processes.
• CREATE TABLE statementThe CREATE TABLE command is an SQL statement that creates a relational table with a specified schema.
• JSON arrayA JSON array is an ordered JSON collection of values enclosed in square brackets and accessed by zero-based positions.
• result highlighterA result highlighter generates text fragments in which terms matching a search query are visibly marked.
• ts_headlinets_headline is a PostgreSQL function that returns text fragments with terms matching a full-text query highlighted.
• cryptcrypt is a pgcrypto function that hashes a password with a salt and can reproduce a hash for password verification.
• cryptographic saltA cryptographic salt is a random value combined with a password before hashing to make identical passwords produce different hashes and to limit compromise scope.
• gen_saltgen_salt is a pgcrypto function that generates a random salt for password hashing using a specified algorithm.
• password hashingA password hash is a one-way derived value representing a password, typically incorporating the salt needed for later verification.
• pgcryptopgcrypto is a PostgreSQL extension that provides cryptographic functions for hashing, encryption, and related security operations.
• database-level multitenancyDatabase-level multitenancy gives each tenant a separate database within a database infrastructure.
• multitenant SaaS applicationA multitenant SaaS application is a software service that serves multiple customers from shared infrastructure while isolating their data and access.
• schema-level multitenancySchema-level multitenancy gives each tenant a dedicated schema containing its own tables within one logical database.
• table-level multitenancyTable-level multitenancy stores multiple tenants' data in shared tables and distinguishes records with a tenant-specific column.
• tenantA tenant is an independent customer or merchant whose data and operations are isolated within a shared multitenant system.
• index typeAn index type is a classification of database indexes based on the access method and structure they use to support data retrieval.
• pgmq.readpgmq.read is a pgmq function that retrieves messages while temporarily hiding them from other consumers subject to a visibility timeout.
• branching factorA branching factor is the average number of child entries or nodes associated with each branch in a tree structure.
• leaf nodeA leaf node is a lowest-level tree node that contains index entries or references to stored records.
• auto_explainauto_explain is a PostgreSQL extension that automatically logs execution plans for queries meeting configured conditions.
• foreign data wrapperA foreign data wrapper is a PostgreSQL extension interface that represents and queries data from an external data source.
• hypopghypopg is a PostgreSQL extension that creates hypothetical indexes for evaluating indexing strategies without physically building them.
• hypothetical indexA hypothetical index is a simulated database index represented to the query planner without being physically created.
• pg_stat_statementspg_stat_statements is a PostgreSQL extension that records planning and execution statistics for SQL statements.
• PLV8PLV8 is a PostgreSQL extension that embeds the V8 JavaScript engine for executing JavaScript database functions.

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.