[체험판] PostgreSQL 10 High Performance

▶Book Description
PostgreSQL database servers have a common set of problems that they encounter as their usage gets heavier and requirements get more demanding. Peek into the future of your PostgreSQL 10 database's problems today. Know the warning signs to look for and how to avoid the most common issues before they even happen.

Surprisingly, most PostgreSQL database applications evolve in the same way―choose the right hardware, tune the operating system and server memory use, optimize queries against the database and CPUs with the right indexes, and monitor every layer, from hardware to queries, using tools from inside and outside PostgreSQL. Also, using monitoring insight, PostgreSQL database applications continuously rework the design and configuration. On reaching the limits of a single server, they break things up; connection pooling, caching, partitioning, replication, and parallel queries can all help handle increasing database workloads.

By the end of this book, you will have all the knowledge you need to design, run, and manage your PostgreSQL solution while ensuring high performance and high availability

▶What You Will Learn
⦁ Learn best practices for scaling PostgreSQL 10 installations
⦁ Discover the best hardware for developing high-performance PostgreSQL applications
⦁ Benchmark your whole system – from hardware to application
⦁ Learn by real examples how server parameters impact performance
⦁ Discover PostgreSQL 10 features for partitioning and parallel query
⦁ Monitor your server, both inside and outside the database
⦁ Design and implement a good replication system on PostgreSQL 10

▶Key Features
⦁ Obtain optimal PostgreSQL 10 database performance, ranging from initial design to routine maintenance
⦁ Fine tune the performance of your queries and avoid the common pitfalls that can slow your system down
⦁ Contains tips and tricks on scaling successful database installations, and ensuring a highly available PostgreSQL solution

▶Who This Book Is For
This book is designed for database administrators and PostgreSQL architects who already use or plan to exploit the features of PostgreSQL 10 to design and maintain a high-performance PostgreSQL database. A working knowledge of SQL, and some experience with PostgreSQL will be helpful in getting the most out of this book.

▶What this book covers
⦁ Chapter 1, PostgreSQL Versions, introduces how PostgreSQL performance has improved in the most recent versions of the database. It makes a case for using the most recent version feasible, in contrast to the common presumption that newer versions of any software are buggier and slower than their predecessors.
⦁ Chapter 2, Database Hardware, discusses how the main components in server hardware, including processors, memory, and disks, need to be carefully selected for reliable database storage and a balanced budget. In particular, accidentally using volatile write-back caching in disk controllers and drives can easily introduce database corruption.
⦁ Chapter 3, Database Hardware Benchmarking, moves on to quantifying the different performance aspects of database hardware. Just how fast are the memory and raw drives in your system? Does performance scale properly as more drives are added?
⦁ Chapter 4, Disk Setup, looks at popular filesystem choices and suggests the trade-offs of various ways to lay out your database on disk. Some common and effective filesystem tuning tweaks are also discussed.
⦁ Chapter 5, Memory for Database Caching, digs into how the database is stored on disk and in memory, and how the checkpoint process serves to reconcile the two safely. It also suggests how you can actually look at the data being cached by the database to confirm that what's being stored in memory matches what you'd expect to be there.
⦁ Chapter 6, Server Configuration Tuning, covers the most important settings in the postgresql.conf file, what they mean, and how you should set them. The settings that may cause you some trouble if you change them are pointed out too.
⦁ Chapter 7, Routine Maintenance, starts by explaining how PostgreSQL determines which rows are visible to which clients. The way visibility information is stored requires a cleanup process named VACUUM to reuse leftover space properly. Common issues and general tuning suggestions for it and the ever-running autovacuum are covered. Finally, there's a look at adjusting the amount of data logged by the database, and using a query log analyzer on the result to help find query bottlenecks.
⦁ Chapter 8, Database Benchmarking, investigates how to get useful benchmark results from the built-in pgbench testing program included with PostgreSQL.
⦁ Chapter 9, Database Indexing, introduces indexes in terms of how they can reduce the number of data blocks read to answer a query. That approach allows for thoroughly investigating common questions such as why a query is using a sequential scan instead of an index in a robust way. In this chapter, we present new PostgreSQL 10 index types.
⦁ Chapter 10, Query Optimization, is a guided tour of the PostgreSQL optimizer, exposed by showing the way sample queries are executed differently based on what they are asking for and how the database parameters are set. This chapter also covers the new way to perform searches in multi-core mode.
⦁ Chapter 11, Database Activity and Statistics, looks at the statistics collected inside the database, and which of them are useful for finding problems. The views that let you watch query activity and locking behavior are also explored.
⦁ Chapter 12, Monitoring and Trending, starts with how to use basic operating system monitoring tools to determine what the database is doing. Then, it moves onto suggestions for trending software that can be used to graph this information over time.
⦁ Chapter 13, Pooling and Caching, explains the difficulties you can encounter when a large number of connections are made to the database at once. Two types of software packages are suggested to help connection poolers, to better queue incoming requests, and caches which can answer user requests without connecting to the database.
⦁ Chapter 14, Scaling with Replication, covers approaches for handling heavier system loads by replicating the data across multiple nodes, typically a set of read-only nodes synchronized to a single writable master. This chapter also covers the new logical replication method.
⦁ Chapter 15, Partitioning Data, explores how data might be partitioned into subsets usefully, such that queries can execute against a smaller portion of the database. The approaches discussed include the standard single-node database table partitioning and the new declarative partition method.
⦁ Chapter 16, Avoiding Common Problems, discusses parts of PostgreSQL that regularly seem to frustrate newcomers to the database. Bulk loading, counting records, and foreign key handling are some examples. This chapter ends with a detailed review of which performance-related features have changed between each version of PostgreSQL from 8.1 to 10.3. Sometimes, the best way to avoid a common problem is to upgrade to a version in which it doesn't happen anymore.