舉報(bào) 
會(huì)員
Learning PostgreSQL 11
PostgreSQLisoneofthemostpopularopensourcedatabasemanagementsystemsintheworld,anditsupportsadvancedfeaturesincludedinSQLstandards.ThisbookwillfamiliarizeyouwiththelatestfeaturesinPostgreSQL11,andgetyouupandrunningwithbuildingefficientPostgreSQLdatabasesolutionsfromscratch.LearningPostgreSQL,11beginsbycoveringtheconceptsofrelationaldatabasesandtheircoreprinciples.You’llexploretheDataDefinitionLanguage(DDL)andcommonlyusedDDLcommandssupportedbyANSISQL.You’llalsolearnhowtocreatetables,defineintegrityconstraints,buildindexes,andsetupviewsandotherschemaobjects.Asyouadvance,you’llcometounderstandDataManipulationLanguage(DML)andserver-sideprogrammingcapabilitiesusingPL/pgSQL,givingyouarobustbackgroundtodevelop,tune,test,andtroubleshootyourdatabaseapplication.ThebookwillguideyouinexploringNoSQLcapabilitiesandconnectingtoyourdatabasetomanipulatedataobjects.You’llgettogripswithusingdatawarehousinginanalyticalsolutionsandreports,andscalingthedatabaseforhighavailabilityandperformance.Bytheendofthisbook,you’llhavegainedathoroughunderstandingofPostgreSQL11anddevelopedthenecessaryskillstobuildefficientdatabasesolutions.
最新章節(jié)
- Leave a review - let other readers know what you think
- Other Books You May Enjoy
- Summary
- PostgreSQL as a component of a software solution
- Working with data
- PostgreSQL schema objects
品牌：中圖公司
上架時(shí)間：2021-07-02 12:26:46
出版社：Packt Publishing
本書數(shù)字版權(quán)由中圖公司提供，并由其授權(quán)上海閱文信息技術(shù)有限公司制作發(fā)行
- Leave a review - let other readers know what you think 更新時(shí)間：2021-07-02 13:12:40
- Other Books You May Enjoy
- Summary
- PostgreSQL as a component of a software solution
- Working with data
- PostgreSQL schema objects
- Working with data in PostgreSQL databases
- PostgreSQL client software
- The PostgreSQL server
- Working with PostgreSQL software
- NoSQL databases
- Denormalization
- Normalization
- Fundamentals
- What's Next?
- Further reading
- Questions
- Summary
- Scaling for many numbers of connections
- Data sharding
- Scaling for heavy querying
- Building a scalable PostgreSQL solution
- Logical replication
- Failover procedures
- Synchronous replication
- Streaming replication
- Log shipping replication
- Physical replication
- Transaction log
- Data replication in PostgreSQL
- The problem of scalability and the CAP theorem
- Technical requirements
- Scalability
- Questions
- 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 Psycopg 2
- Python DB-API 2.0
- Technical requirements
- Using PostgreSQL in Python Applications
- Questions
- Summary
- Performance testing
- Data differences
- Database-abstraction interfaces
- Schema difference
- Unit test frameworks
- Specificity of unit testing in databases
- Unit testing
- Technical requirements
- Testing
- Questions
- 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
- Explaining the command and execution plans
- Tuning performance for read
- Tuning performance for write
- Benchmarking is your friend
- Planner-related settings
- Disk settings
- Memory settings
- Maximum number of connections
- PostgreSQL configuration tuning
- Optimizing Database Performance
- Questions
- Summary
- Selective dump
- Tuning for performance
- Data cleanup
- 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
- Questions
- Summary
- Two-way encryption
- One-way encryption
- pgcrypto
- PostgreSQL role password encryption
- Encrypting data
- Row-level security
- Column-level security
- Object-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
- Questions
- Summary
- Transaction in functions and procedures
- 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
- Questions
- 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
- RESTful APIs using PostgreSQL
- Indexing a JSON data type
- Modifying and accessing JSON types
- The benefits of supporting the JSON data type
- 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
- Questions
- Summary
- Index-only scans
- Parallel query
- Performance improvement
- Sub-partitioning
- Declarative partitioning
- Partitioning
- Aggregation
- Data modeling for OLAP
- Loading data into a data warehouse
- Online analytical processing
- OLTP and OLAP
- OLAP and Data Warehousing
- Questions
- 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 voids
- Returning from the function
- The FOR loop statement
- The WHILE loop statement
- Loop statements
- Iterations
- 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
- Functions and procedures – a comparison
- The SQL language and PL/pgSQL – a comparison
- Server-Side Programming with PL/pgSQL
- Questions
- 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 definitions
- 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
- Questions
- Summary
- The TRUNCATE statement
- The DELETE statement
- UPDATE using additional tables
- UPDATE using sub-select
- The UPDATE statement
- The INSERT statement
- Changing the data in a database
- Dealing with NULLS
- Set operations – UNION EXCEPT and INTERSECT
- Subqueries
- Ordering and limiting 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
- The FROM clause
- DISTINCT
- SQL expressions
- SELECT-list
- Structure of a SELECT query
- Querying data with SELECT statements
- SQL lexical structure
- SQL fundamentals
- SQL Language
- Questions
- Summary
- Triggers and updatable views
- Triggers with arguments
- Trigger system
- Rule system
- Trigger and rule systems
- User-defined data types
- PostgreSQL anonymous functions
- PostgreSQL function categories
- Function dependencies
- Function usage
- Creating a function in the C language
- The PostgreSQL native programming language
- Functions
- Best practices for indexes
- Index categories
- Index types
- Index selectivity
- Index synopses
- Indexes
- Updatable views
- Materialized views
- View categories
- View synopsis
- Views
- PostgreSQL Advanced Building Blocks
- Questions
- Summary
- The car web portal database
- Date and time types
- Character types
- Serial types and identity columns
- Numeric types
- PostgreSQL native data types
- Tables
- Schema usages
- Schemas
- PostgreSQL database components
- PostgreSQL high-level object interaction
- Settings
- Template procedural languages
- Tablespaces
- Roles
- User databases
- Template databases
- PostgreSQL object hierarchy
- Database-migration tools
- Version control systems
- Documentation
- PostgreSQL identifiers
- Database naming conventions
- Database coding
- PostgreSQL Basic Building Blocks
- Questions
- Summary
- PostgreSQL utility tools
- psql advanced settings
- The psql client
- PostgreSQL clients
- Installing PostgreSQL on Windows
- Basic server configuration
- Server installation
- Client installation
- Installing PostgreSQL using Advanced Package Tool
- Installing PostgreSQL on Linux via source
- Installing PostgreSQL
- Performance
- Foreign data wrappers
- NoSQL capabilities
- Extensions
- 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
- Questions
- 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
- The 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
- NoSQL databases
- Database categories
- A brief history
- Database management systems
- Relational Databases
- Reviews
- Get in touch
- Conventions used
- Download the color images
- Download the example code files
- To get the most out of this book
- What this book covers
- Who this book is for
- Preface
- Packt is searching for authors like you
- About the reviewers
- About the authors
- Contributors
- Packt.com
- Why subscribe?
- About Packt
- Learning PostgreSQL 11 Third Edition
- Copyright and Credits
- Title Page
- coverpage
- coverpage
- Title Page
- Copyright and Credits
- Learning PostgreSQL 11 Third Edition
- About Packt
- Why subscribe?
- Packt.com
- Contributors
- About the authors
- About the reviewers
- Packt is searching for authors like you
- Preface
- Who this book is for
- What this book covers
- To get the most out of this book
- Download the example code files
- Download the color images
- Conventions used
- Get in touch
- Reviews
- Relational Databases
- Database management systems
- A brief history
- Database categories
- 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
- The 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
- Questions
- 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
- Extensions
- NoSQL capabilities
- Foreign data wrappers
- Performance
- Installing PostgreSQL
- Installing PostgreSQL on Linux via source
- Installing PostgreSQL using Advanced Package Tool
- Client installation
- Server installation
- Basic server configuration
- Installing PostgreSQL on Windows
- PostgreSQL clients
- The psql client
- psql advanced settings
- PostgreSQL utility tools
- Summary
- Questions
- PostgreSQL Basic Building Blocks
- Database coding
- Database naming conventions
- PostgreSQL identifiers
- Documentation
- Version control systems
- Database-migration tools
- PostgreSQL object hierarchy
- Template databases
- User databases
- Roles
- Tablespaces
- Template procedural languages
- Settings
- PostgreSQL high-level object interaction
- PostgreSQL database components
- Schemas
- Schema usages
- Tables
- PostgreSQL native data types
- Numeric types
- Serial types and identity columns
- Character types
- Date and time types
- The car web portal database
- Summary
- Questions
- PostgreSQL Advanced Building Blocks
- Views
- View synopsis
- View categories
- Materialized views
- Updatable views
- Indexes
- Index synopses
- Index selectivity
- Index types
- Index categories
- Best practices for indexes
- Functions
- The PostgreSQL native programming language
- Creating a function in the C language
- Function usage
- Function dependencies
- PostgreSQL function categories
- PostgreSQL anonymous functions
- User-defined data types
- Trigger and rule systems
- Rule system
- Trigger system
- Triggers with arguments
- Triggers and updatable views
- Summary
- Questions
- SQL Language
- SQL fundamentals
- SQL lexical structure
- Querying data with SELECT statements
- Structure of a SELECT query
- SELECT-list
- SQL expressions
- DISTINCT
- The 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 results
- Subqueries
- Set operations – UNION EXCEPT and INTERSECT
- Dealing with NULLS
- Changing the data in a database
- The INSERT statement
- The UPDATE statement
- UPDATE using sub-select
- UPDATE using additional tables
- The DELETE statement
- The TRUNCATE statement
- Summary
- Questions
- 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 definitions
- 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
- Questions
- Server-Side Programming with PL/pgSQL
- The SQL language and PL/pgSQL – a comparison
- Functions and procedures – 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
- Iterations
- Loop statements
- The WHILE loop statement
- The FOR loop statement
- Returning from the function
- Returning voids
- 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
- Questions
- OLAP and Data Warehousing
- OLTP and OLAP
- Online analytical processing
- Loading data into a data warehouse
- Data modeling for OLAP
- Aggregation
- Partitioning
- Declarative partitioning
- Sub-partitioning
- Performance improvement
- Parallel query
- Index-only scans
- Summary
- Questions
- 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
- The benefits of supporting the JSON data type
- Modifying and accessing JSON types
- Indexing a JSON data type
- RESTful APIs using 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
- Questions
- 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
- Transaction in functions and procedures
- Summary
- Questions
- 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
- Object-level security
- Column-level security
- Row-level security
- Encrypting data
- PostgreSQL role password encryption
- pgcrypto
- One-way encryption
- Two-way encryption
- Summary
- Questions
- 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
- Data cleanup
- Tuning for performance
- Selective dump
- Summary
- Questions
- Optimizing Database Performance
- PostgreSQL configuration tuning
- Maximum number of connections
- Memory settings
- Disk settings
- Planner-related settings
- Benchmarking is your friend
- Tuning performance for write
- Tuning performance for read
- Explaining the command and execution plans
- 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
- Questions
- Testing
- Technical requirements
- Unit testing
- Specificity of unit testing in databases
- Unit test frameworks
- Schema difference
- Database-abstraction interfaces
- Data differences
- Performance testing
- Summary
- Questions
- Using PostgreSQL in Python Applications
- Technical requirements
- Python DB-API 2.0
- Low-level database access with Psycopg 2
- 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
- Questions
- Scalability
- Technical requirements
- The problem of scalability and the CAP theorem
- Data replication in PostgreSQL
- Transaction log
- Physical replication
- Log shipping replication
- Streaming replication
- Synchronous replication
- Failover procedures
- Logical replication
- Building a scalable PostgreSQL solution
- Scaling for heavy querying
- Data sharding
- Scaling for many numbers of connections
- Summary
- Questions
- Further reading
- What's Next?
- Fundamentals
- Normalization
- Denormalization
- NoSQL databases
- Working with PostgreSQL software
- The PostgreSQL server
- PostgreSQL client software
- Working with data in PostgreSQL databases
- PostgreSQL schema objects
- Working with data
- PostgreSQL as a component of a software solution
- Summary
- Other Books You May Enjoy
- Leave a review - let other readers know what you think 更新時(shí)間：2021-07-02 13:12:40
