最新章節

• Summary
• Scaling for big number of connections
• Data sharding
• Scaling for heavy querying
• Using replication to scale PostgreSQL
• Logical replication

• Summary 更新時間：2021-07-02 22:42:54
• Scaling for big number of connections
• Data sharding
• Scaling for heavy querying
• Using replication to scale PostgreSQL
• Logical replication
• Synchronous replication
• Streaming replication
• Log shipping replication
• Physical replication
• Transaction log
• Data replication in PostgreSQL
• The problem of scalability and the CAP theorem
• Scalability
• Summary
• ORM
• Connecting to a database and retrieving data with SQL Expression Language
• Main components of SQLAlchemy
• Object relational mapping with SQLAlchemy
• asyncpg
• pg8000
• Alternative drivers for PostgreSQL
• Asynchronous access
• The COPY command
• Reading data from a database
• Executing SQL commands
• Connection pooling
• Connecting to a database
• Low-level database access with psycopg2
• Python DB API 2.0
• Using PostgreSQL in Python Applications
• Summary
• Performance testing
• Data difference
• Database abstraction interfaces
• Schema difference
• Unit test frameworks
• Specificity of unit testing in databases
• Unit testing
• Testing
• Summary
• Query rewriting
• Constraint exclusion limitations
• Table partitioning
• Cross column correlation
• Using the PL/pgSQL procedural language consideration
• Using CTE when not mandatory
• Misplaced or missing indexes
• Unnecessary operations
• Common mistakes in writing queries
• Detecting problems in query plans
• Explain command and execution plan
• Tuning performance for read
• Tuning performance for write
• Bench marking is your friend
• Planner-related settings
• Hard disk settings
• Memory settings
• Maximum number of connections
• PostgreSQL configuration tuning
• Optimizing Database Performance
• Summary
• Selective dump
• Tuning for performance
• Cleaning up the database data
• Cleaning up the database
• Getting the database and database object size
• Defining and getting database cluster settings
• Terminating and canceling user sessions
• Getting the database cluster and client tools version
• System catalog for administrators
• The system catalog
• The PostgreSQL Catalog
• Summary
• Two-way encryption
• One-way encryption
• pgcrypto
• PostgreSQL role password encryption
• Encrypting data
• Row-level security
• Column-level security
• Table-level security
• Schema security level
• Database security level
• PostgreSQL security levels
• Role system and proxy authentication
• PostgreSQL default access privileges
• Authentication best practices
• Listen addresses
• PostgreSQL pg_hba.conf
• Authentication in PostgreSQL
• PostgreSQL Security
• Summary
• Advisory locks
• Deadlocks
• Row-level lock modes
• Row-level locks
• Table locking modes
• Table-level locks
• Explicit locking
• Transaction isolation levels
• MVCC in PostgreSQL
• Transaction and concurrency
• Transaction and ACID properties
• Transactions
• Transactions and Concurrency Control
• Summary
• Full text search indexing
• Pattern matching
• The tsquery data type
• The tsvector data type
• The tsquery and tsvector data types
• A PostgreSQL full text search
• Providing a RESTful API interface for PostgreSQL
• Indexing a JSON data type
• Modifying and accessing JSON types
• JSON data types for PostgreSQL
• JSON and XML
• The JSON data structure
• Indexing an hstore
• The hash store data structure
• Indexing arrays
• Modifying and accessing arrays
• Common functions of arrays and their operators
• Arrays
• Beyond Conventional Data Types
• Summary
• Index-only scans
• Parallel query
• Partitioning
• Aggregation
• Data modeling for OLAP
• Extract transform and load
• Online analytical processing
• OLAP and Data Warehousing
• Summary
• Recommended practices for dynamic SQL usage
• Dynamic SQL and the caching effect
• Executing DML statements in dynamic SQL
• Executing DDL statements in dynamic SQL
• Dynamic SQL
• Exception handling
• Function predefined variables
• Returning multiple rows
• Returning a single row
• Returning void
• Returning from the function
• For loop statement
• While loop statement
• Loop statement
• Iteration
• Conditional statements
• Assignment statements
• Declaration statements
• The PostgreSQL PL/pgSQL control statements
• Function configuration-related parameters
• Function planner-related parameters
• Function authorization-related parameters
• PostgreSQL function parameters
• SQL language and PL/pgSQL – a comparison
• Server-Side Programming with PL/pgSQL
• Summary
• Advanced aggregation
• Advanced grouping
• Lateral subqueries
• Set returning functions
• Selecting a data sample
• Selecting the first records
• Advanced SQL techniques
• Window functions with grouping and aggregation
• Using window functions
• The WINDOW clause
• Window definition
• Window functions
• Changing data in multiple tables at a time
• Recursive and hierarchical queries
• Reusing SQL code with CTE
• Common table expressions
• Advanced Query Writing
• Summary
• The TRUNCATE statement
• The DELETE statement
• UPDATE using additional tables
• UPDATE using sub-select
• The UPDATE statement
• INSERT statement
• Changing the data in the database
• Dealing with NULLs
• Set operations – UNION EXCEPT and INTERSECT
• Subqueries
• Ordering and limiting the results
• The HAVING clause
• The GROUP BY clause
• Grouping and aggregation
• Row and array comparison constructs
• Pattern matching
• Comparison operators
• The WHERE clause
• Self-joins
• Selecting from multiple tables
• FROM clause
• DISTINCT
• SQL expressions
• Select-list
• Structure of a SELECT query
• Querying data with SELECT statements
• SQL lexical structure
• SQL fundamentals
• SQL Language
• Summary
• Triggers and updatable views
• Triggers with arguments
• Trigger system
• Rule system
• Triggers and rule systems
• User-defined data types
• PostgreSQL anonymous functions
• PostgreSQL function categories
• Function dependency
• Function usages
• Creating a function in the C language
• PostgreSQL native programming language
• Functions
• Best practices on indexes
• Index categories
• Index types
• Index selectivity
• Index synopsis
• Indexes
• Updatable views
• Materialized views
• View categories
• View synopsis
• Views
• PostgreSQL Advanced Building Blocks
• Summary
• The car web portal database
• Date and time types
• Character types
• Numeric types
• PostgreSQL native data types
• Table
• Schema usages
• Schema
• PostgreSQL database components
• PostgreSQL high-level object interaction
• Settings
• Template procedural languages
• Tablespace
• Roles
• User databases
• Template databases
• PostgreSQL objects hierarchy
• Database migration tool
• Version control system
• Documentation
• PostgreSQL identifiers
• Database naming conventions
• Database coding
• PostgreSQL Basic Building Blocks
• Summary
• PostgreSQL utility tools
• psql advanced settings
• The psql client
• The PostgreSQL clients
• Installing PostgreSQL on Windows
• Basic server configuration
• Server installation
• Client installation
• Installing PostgreSQL using Advanced Package Tool
• Installing PostgreSQL
• Performance
• Foreign data wrappers
• NoSQL capabilities
• Extension
• Security
• Replication
• PostgreSQL capabilities
• The PostgreSQL community
• PostgreSQL architecture
• Forks
• Success stories
• PostgreSQL applications
• PostgreSQL user advantages
• Business advantages of PostgreSQL
• The advantages of PostgreSQL
• PostgreSQL history
• An overview of PostgreSQL
• PostgreSQL in Action
• Summary
• UML class diagrams
• Mapping ER to relations
• Entities attributes and keys
• Sample application
• The entity-relation model
• Data model perspectives
• Data modeling
• The cartesian product operation
• The set theory operations
• The rename operation
• The select and project operations
• Relational algebra
• Semantic constraints
• Referential integrity constraints
• Entity integrity constraint
• Domain integrity constraint
• Constraint
• Attribute
• NULL value
• Tuple
• Relation
• Relational model concepts
• The SQL language
• ACID properties
• Relational and object relational databases
• Graph databases
• Document databases
• Columnar databases
• Key-value databases
• NoSQL motivation
• The CAP theorem
• The NoSQL databases
• Database categories
• A brief history
• Database management systems
• Relational Databases
• Questions
• Piracy
• Errata
• Downloading the color images of this book
• Downloading the example code
• Customer support
• Reader feedback
• Conventions
• Who this book is for
• What you need for this book
• What this book covers
• Preface
• Customer Feedback
• Why subscribe?
• www.PacktPub.com
• About the Reviewers
• About the Authors
• Credits
• 版權信息
• 封面

• 封面
• 版權信息
• Credits
• About the Authors
• About the Reviewers
• www.PacktPub.com
• Why subscribe?
• Customer Feedback
• Preface
• What this book covers
• What you need for this book
• Who this book is for
• Conventions
• Reader feedback
• Customer support
• Downloading the example code
• Downloading the color images of this book
• Errata
• Piracy
• Questions
• Relational Databases
• Database management systems
• A brief history
• Database categories
• The NoSQL databases
• The CAP theorem
• NoSQL motivation
• Key-value databases
• Columnar databases
• Document databases
• Graph databases
• Relational and object relational databases
• ACID properties
• The SQL language
• Relational model concepts
• Relation
• Tuple
• NULL value
• Attribute
• Constraint
• Domain integrity constraint
• Entity integrity constraint
• Referential integrity constraints
• Semantic constraints
• Relational algebra
• The select and project operations
• The rename operation
• The set theory operations
• The cartesian product operation
• Data modeling
• Data model perspectives
• The entity-relation model
• Sample application
• Entities attributes and keys
• Mapping ER to relations
• UML class diagrams
• Summary
• PostgreSQL in Action
• An overview of PostgreSQL
• PostgreSQL history
• The advantages of PostgreSQL
• Business advantages of PostgreSQL
• PostgreSQL user advantages
• PostgreSQL applications
• Success stories
• Forks
• PostgreSQL architecture
• The PostgreSQL community
• PostgreSQL capabilities
• Replication
• Security
• Extension
• NoSQL capabilities
• Foreign data wrappers
• Performance
• Installing PostgreSQL
• Installing PostgreSQL using Advanced Package Tool
• Client installation
• Server installation
• Basic server configuration
• Installing PostgreSQL on Windows
• The PostgreSQL clients
• The psql client
• psql advanced settings
• PostgreSQL utility tools
• Summary
• PostgreSQL Basic Building Blocks
• Database coding
• Database naming conventions
• PostgreSQL identifiers
• Documentation
• Version control system
• Database migration tool
• PostgreSQL objects hierarchy
• Template databases
• User databases
• Roles
• Tablespace
• Template procedural languages
• Settings
• PostgreSQL high-level object interaction
• PostgreSQL database components
• Schema
• Schema usages
• Table
• PostgreSQL native data types
• Numeric types
• Character types
• Date and time types
• The car web portal database
• Summary
• PostgreSQL Advanced Building Blocks
• Views
• View synopsis
• View categories
• Materialized views
• Updatable views
• Indexes
• Index synopsis
• Index selectivity
• Index types
• Index categories
• Best practices on indexes
• Functions
• PostgreSQL native programming language
• Creating a function in the C language
• Function usages
• Function dependency
• PostgreSQL function categories
• PostgreSQL anonymous functions
• User-defined data types
• Triggers and rule systems
• Rule system
• Trigger system
• Triggers with arguments
• Triggers and updatable views
• Summary
• SQL Language
• SQL fundamentals
• SQL lexical structure
• Querying data with SELECT statements
• Structure of a SELECT query
• Select-list
• SQL expressions
• DISTINCT
• FROM clause
• Selecting from multiple tables
• Self-joins
• The WHERE clause
• Comparison operators
• Pattern matching
• Row and array comparison constructs
• Grouping and aggregation
• The GROUP BY clause
• The HAVING clause
• Ordering and limiting the results
• Subqueries
• Set operations – UNION EXCEPT and INTERSECT
• Dealing with NULLs
• Changing the data in the database
• INSERT statement
• The UPDATE statement
• UPDATE using sub-select
• UPDATE using additional tables
• The DELETE statement
• The TRUNCATE statement
• Summary
• Advanced Query Writing
• Common table expressions
• Reusing SQL code with CTE
• Recursive and hierarchical queries
• Changing data in multiple tables at a time
• Window functions
• Window definition
• The WINDOW clause
• Using window functions
• Window functions with grouping and aggregation
• Advanced SQL techniques
• Selecting the first records
• Selecting a data sample
• Set returning functions
• Lateral subqueries
• Advanced grouping
• Advanced aggregation
• Summary
• Server-Side Programming with PL/pgSQL
• SQL language and PL/pgSQL – a comparison
• PostgreSQL function parameters
• Function authorization-related parameters
• Function planner-related parameters
• Function configuration-related parameters
• The PostgreSQL PL/pgSQL control statements
• Declaration statements
• Assignment statements
• Conditional statements
• Iteration
• Loop statement
• While loop statement
• For loop statement
• Returning from the function
• Returning void
• Returning a single row
• Returning multiple rows
• Function predefined variables
• Exception handling
• Dynamic SQL
• Executing DDL statements in dynamic SQL
• Executing DML statements in dynamic SQL
• Dynamic SQL and the caching effect
• Recommended practices for dynamic SQL usage
• Summary
• OLAP and Data Warehousing
• Online analytical processing
• Extract transform and load
• Data modeling for OLAP
• Aggregation
• Partitioning
• Parallel query
• Index-only scans
• Summary
• Beyond Conventional Data Types
• Arrays
• Common functions of arrays and their operators
• Modifying and accessing arrays
• Indexing arrays
• The hash store data structure
• Indexing an hstore
• The JSON data structure
• JSON and XML
• JSON data types for PostgreSQL
• Modifying and accessing JSON types
• Indexing a JSON data type
• Providing a RESTful API interface for PostgreSQL
• A PostgreSQL full text search
• The tsquery and tsvector data types
• The tsvector data type
• The tsquery data type
• Pattern matching
• Full text search indexing
• Summary
• Transactions and Concurrency Control
• Transactions
• Transaction and ACID properties
• Transaction and concurrency
• MVCC in PostgreSQL
• Transaction isolation levels
• Explicit locking
• Table-level locks
• Table locking modes
• Row-level locks
• Row-level lock modes
• Deadlocks
• Advisory locks
• Summary
• PostgreSQL Security
• Authentication in PostgreSQL
• PostgreSQL pg_hba.conf
• Listen addresses
• Authentication best practices
• PostgreSQL default access privileges
• Role system and proxy authentication
• PostgreSQL security levels
• Database security level
• Schema security level
• Table-level security
• Column-level security
• Row-level security
• Encrypting data
• PostgreSQL role password encryption
• pgcrypto
• One-way encryption
• Two-way encryption
• Summary
• The PostgreSQL Catalog
• The system catalog
• System catalog for administrators
• Getting the database cluster and client tools version
• Terminating and canceling user sessions
• Defining and getting database cluster settings
• Getting the database and database object size
• Cleaning up the database
• Cleaning up the database data
• Tuning for performance
• Selective dump
• Summary
• Optimizing Database Performance
• PostgreSQL configuration tuning
• Maximum number of connections
• Memory settings
• Hard disk settings
• Planner-related settings
• Bench marking is your friend
• Tuning performance for write
• Tuning performance for read
• Explain command and execution plan
• Detecting problems in query plans
• Common mistakes in writing queries
• Unnecessary operations
• Misplaced or missing indexes
• Using CTE when not mandatory
• Using the PL/pgSQL procedural language consideration
• Cross column correlation
• Table partitioning
• Constraint exclusion limitations
• Query rewriting
• Summary
• Testing
• Unit testing
• Specificity of unit testing in databases
• Unit test frameworks
• Schema difference
• Database abstraction interfaces
• Data difference
• Performance testing
• Summary
• Using PostgreSQL in Python Applications
• Python DB API 2.0
• Low-level database access with psycopg2
• Connecting to a database
• Connection pooling
• Executing SQL commands
• Reading data from a database
• The COPY command
• Asynchronous access
• Alternative drivers for PostgreSQL
• pg8000
• asyncpg
• Object relational mapping with SQLAlchemy
• Main components of SQLAlchemy
• Connecting to a database and retrieving data with SQL Expression Language
• ORM
• Summary
• Scalability
• The problem of scalability and the CAP theorem
• Data replication in PostgreSQL
• Transaction log
• Physical replication
• Log shipping replication
• Streaming replication
• Synchronous replication
• Logical replication
• Using replication to scale PostgreSQL
• Scaling for heavy querying
• Data sharding
• Scaling for big number of connections
• Summary 更新時間：2021-07-02 22:42:54