Search

Master PostgreSQL 12 features such as advanced indexing, high availability, monitoring, and much more to efficiently manage and maintain your databaseKey FeaturesGrasp advanced PostgreSQL 12 concepts with real-world examples and sample datasetsExplore query parallelism, data replication, database administration, and moreExtend PostgreSQL functionalities to suit your organization's needs with minimal effortBook DescriptionThanks to its reliability, robustness, and high performance, PostgreSQL has become the most advanced open source database on the market. This third edition of Mastering PostgreSQL helps you build dynamic database solutions for enterprise applications using the latest release of PostgreSQL, which enables database analysts to design both physical and technical aspects of system architecture with ease. Starting with an introduction to the newly released features in PostgreSQL 12, this book will help you build efficient and fault-tolerant PostgreSQL applications. You'll thoroughly examine the advanced features of PostgreSQL, including logical replication, database clusters, performance tuning, monitoring, and user management. You'll also work with the PostgreSQL optimizer, configure PostgreSQL for high speed, and understand how to move from Oracle to PostgreSQL. As you progress through the chapters, you'll cover transactions, locking, indexes, and how to optimize queries for improved performance. Additionally, you'll learn how to manage network security and explore backups and replications while understanding useful PostgreSQL extensions to help you in optimizing the performance of large databases. By the end of this PostgreSQL book, you'll be able to get the most out of your database by implementing advanced administrative tasks effortlessly.What you will learnUnderstand the advanced SQL functions in PostgreSQL 12Use indexing features in PostgreSQL to fine-tune the performance of queriesWork with stored procedures and manage backup and recoveryMaster replication and failover techniques to reduce data lossReplicate PostgreSQL database systems to create backups and to scale your databaseManage and improve the security of your server to protect your dataTroubleshoot your PostgreSQL instance for solutions to common and not-so-common problemsWho this book is forThis book is for PostgreSQL developers and administrators and database professionals who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 12. Prior exposure to PostgreSQL as well as familiarity with the basics of database administration is expected.

Master the capabilities of PostgreSQL 10 to efficiently manage and maintain your databaseKey Features[•] Your one-stop guide to mastering advanced concepts in PostgreSQL 10 with ease[•] Master query optimization, replication, and high availability with PostgreSQL[•] Extend the functionalities of your PostgreSQL instance to suit your organizational needs with minimal effortBook DescriptionPostgreSQL is an open source database used for handling large datasets (big data) and as a JSON document database. This book highlights the newly introduced features in PostgreSQL 10, and shows you how you can build better PostgreSQL applications, and administer your PostgreSQL database more efficiently. We begin by explaining advanced database design concepts in PostgreSQL 10, along with indexing and query optimization. You will also see how to work with event triggers and perform concurrent transactions and table partitioning, along with exploring SQL and server tuning. We will walk you through implementing advanced administrative tasks such as server maintenance and monitoring, replication, recovery, high availability, and much more. You will understand common and not-so-common troubleshooting problems and how you can overcome them. By the end of this book, you will have an expert-level command of advanced database functionalities and will be able to implement advanced administrative tasks with PostgreSQL 10.What you will learn•Get to grips with the advanced features of PostgreSQL 10 and handle advanced SQL•Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries•Work with stored procedures and manage backup and recovery•Master replication and failover techniques•Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems•Learn how to migrate your database from MySQL and Oracle to PostgreSQL without any hassleWho this book is forIf you are a PostgreSQL data architect or an administrator and want to understand how to implement advanced functionalities and master complex administrative tasks with PostgreSQL 10, then this book is perfect for you. Prior experience of administrating a PostgreSQL database and a working knowledge of SQL are required to make the best use of this book.

Analyze and transform data efficiently with DuckDB, a versatile, modern, in-process SQL databaseKey FeaturesUse DuckDB to rapidly load, transform, and query data across a range of sources and formatsGain practical experience using SQL, Python, and R to effectively analyze dataLearn how open source tools and cloud services in the broader data ecosystem complement DuckDB's versatile capabilitiesPurchase of the print or Kindle book includes a free PDF eBookBook DescriptionDuckDB is a fast in-process analytical database. Getting Started with DuckDB offers a practical overview of its usage. You'll learn to load, transform, and query various data formats, including CSV, JSON, and Parquet. The book covers DuckDB's optimizations, SQL enhancements, and extensions for specialized applications. Working with examples in SQL, Python, and R, you'll explore analyzing public datasets and discover tools enhancing DuckDB workflows. This guide suits both experienced and new data practitioners, quickly equipping you to apply DuckDB's capabilities in analytical projects. You'll gain proficiency in using DuckDB for diverse tasks, enabling effective integration into your data workflows.What you will learnUnderstand the properties and applications of a columnar in-process databaseUse SQL to load, transform, and query a range of data formatsDiscover DuckDB's rich extensions and learn how to apply themUse nested data types to model semi-structured data and extract and model JSON dataIntegrate DuckDB into your Python and R analytical workflowsEffectively leverage DuckDB's convenient SQL enhancementsExplore the wider ecosystem and pathways for building DuckDB-powered data applicationsWho this book is forIf you're interested in expanding your analytical toolkit, this book is for you. It will be particularly valuable for data analysts wanting to rapidly explore and query complex data, data and software engineers looking for a lean and versatile data processing tool, along with data scientists needing a scalable data manipulation library that integrates seamlessly with Python and R. You will get the most from this book if you have some familiarity with SQL and foundational database concepts, as well as exposure to a programming language such as Python or R.

Become well-versed with database modeling and SQL optimization, and gain a deep understanding of transactional systems through practical examples and exercisesKey FeaturesGet to grips with fundamental-to-advanced database design and modeling concepts with PostgreSQL and MySQLExplore database integration with web apps, emerging trends, and real-world case studiesLeverage practical examples and hands-on exercises to reinforce learningPurchase of the print or Kindle book includes a free PDF eBookBook DescriptionDatabase Design and Modeling with PostgreSQL and MySQL will equip you with the knowledge and skills you need to architect, build, and optimize efficient databases using two of the most popular open-source platforms. As you progress through the chapters, you'll gain a deep understanding of data modeling, normalization, and query optimization, supported by hands-on exercises and real-world case studies that will reinforce your learning. You'll explore topics like concurrency control, backup and recovery strategies, and seamless integration with web and mobile applications. These advanced topics will empower you to tackle complex database challenges confidently and effectively. Additionally, you'll explore emerging trends, such as NoSQL databases and cloud-based solutions, ensuring you're well-versed in the latest developments shaping the database landscape. By embracing these cutting-edge technologies, you'll be prepared to adapt and innovate in today's ever-evolving digital world. By the end of this book, you'll be able to understand the technologies that exist to design a modern and scalable database for developing web applications using MySQL and PostgreSQL open-source databases.What you will learnDesign a schema, create ERDs, and apply normalization techniquesGain knowledge of installing, configuring, and managing MySQL and PostgreSQLExplore topics such as denormalization, index optimization, transaction management, and concurrency controlScale databases with sharding, replication, and load balancing, as well as implement backup and recovery strategiesIntegrate databases with web apps, use SQL, and implement best practicesExplore emerging trends, including NoSQL databases and cloud databases, while understanding the impact of AI and MLWho this book is forThis book is for a wide range of professionals interested in expanding their knowledge and skills in database design and modeling with PostgreSQL and MySQL. This includes software developers, database administrators, data analysts, IT professionals, and students. While prior knowledge of MySQL and PostgreSQL is not necessary, some familiarity with at least one relational database management system (RDBMS) will help you get the most out of this book.

Get hands-on with deploying and managing your database services to provide scalable and high-speed data access on CockroachDBKey FeaturesGain insights into CockroachDB and build highly reliable cloud-native applicationsExplore the power of a scalable and highly available cloud-native SQL database to distribute data and workloads automaticallyBuild high-speed database services using CockroachDB and troubleshoot performance issuesBook DescriptionGetting Started with CockroachDB will introduce you to the inner workings of CockroachDB and help you to understand how it provides faster access to distributed data through a SQL interface. The book will also uncover how you can use the database to provide solutions where the data is highly available.Starting with CockroachDB's installation, setup, and configuration, this SQL book will familiarize you with the database architecture and database design principles. You'll then discover several options that CockroachDB provides to store multiple copies of your data to ensure fast data access. The book covers the internals of CockroachDB, how to deploy and manage it on the cloud, performance tuning to get the best out of CockroachDB, and how to scale data across continents and serve it locally. In addition to this, you'll get to grips with fault tolerance and auto-rebalancing, how indexes work, and the CockroachDB Admin UI. The book will guide you in building scalable cloud services on top of CockroachDB, covering administrative and security aspects and tips for troubleshooting, performance enhancements, and a brief guideline on migrating from traditional databases.By the end of this book, you'll have gained sufficient knowledge to manage your data on CockroachDB and interact with it from your application layer.What you will learnBecome well-versed with the overall architecture and design concepts of CockroachDBUnderstand how auto-rebalancing of data can avoid performance bottlenecksGet to know how CockroachDB achieves atomicity, consistency, isolation, and durabilityPartition your data across multiple geolocations to ensure very low latency when serving dataFind out how indexes are stored and the optimizations used to serve query results fasterDiscover the key concepts of deploying and managing CockroachDB clustersWho this book is forSoftware engineers, database developers, database administrators, and anyone who wishes to learn about the features of CockroachDB and how to build database solutions that are fast, highly available, and cater to business-critical applications, will find this book useful. Although no prior exposure to CockroachDB is required, familiarity with database concepts will help you to get the most out of this book.

Leverage the power of PostgreSQL 10 to build powerful database and data warehousing applications.Key FeaturesBe introduced to the concept of relational databases and PostgreSQL, one of the fastest growing open source databases in the worldLearn client-side and server-side programming in PostgreSQL, and how to administer PostgreSQL databasesDiscover tips on implementing efficient database solutions with PostgreSQL 10Book DescriptionPostgreSQL is one of the most popular open source databases in the world, supporting the most advanced features included in SQL standards. This book will familiarize you with the latest features released in PostgreSQL 10. We'll start with a thorough introduction to PostgreSQL and the new features introduced in PostgreSQL 10. We'll cover the Data Definition Language (DDL) with an emphasis on PostgreSQL, and the common DDL commands supported by ANSI SQL. You'll learn to create tables, define integrity constraints, build indexes, and set up views and other schema objects. Moving on, we'll cover the concepts of Data Manipulation Language (DML) and PostgreSQL server-side programming capabilities using PL/pgSQL. We'll also explore the NoSQL capabilities of PostgreSQL and connect to your PostgreSQL database to manipulate data objects. By the end of this book, you'll have a thorough understanding of the basics of PostgreSQL 10 and will have the necessary skills to build efficient database solutions.What you will learnUnderstand the fundamentals of relational databases, relational algebra, and data modelingInstall a PostgreSQL cluster, create a database, and implement your data modelCreate tables and views, define indexes, and implement triggers, stored procedures, and other schema objectsUse the Structured Query Language (SQL) to manipulate data in the databaseImplement business logic on the server side with triggers and stored procedures using PL/pgSQLMake use of advanced data types supported by PostgreSQL 10: arrays, hstore, JSONB, and othersDevelop OLAP database solutions using the most recent features of PostgreSQL 10Connect your Python applications to a PostgreSQL database and work with data efficientlyTest your database code, find bottlenecks, improve performance, and enhance the reliability of database applicationsWho this book is forIf you're interested in learning more about PostgreSQL, one of the most popular relational databases in the world, then this book is for you. Those looking to build solid database or data warehousing applications with PostgreSQL 10 will also find this book a useful resource. No prior knowledge of database programming or administration is required to get started with this book.