Application Performance Optimization Summary. Contribute to sjtuhjh/appdocs development by creating an account on GitHub. Maymala J. PostgreSQL for Data Architects. Файл формата pdf; размером 5,46 МБ. Добавлен пользователем alexyakm ; Отредактирован. Discover how to design, develop, and maintain your database application effectively with PostgreSQL.
|Language:||English, Spanish, Portuguese|
|Distribution:||Free* [*Registration needed]|
PostgreSQL for Data Architects [Jayadevan Maymala] on maroc-evasion.info *FREE* shipping on qualifying offers. Discover how to design, develop, and maintain. maroc-evasion.info PostgreSQL for Data Architects Discover how to design, .. It's possible to generate documentation in an HTML format, PDF format, and a. rules and tables. • Postgres server is passed an SQL query and it is incrementally transformed into result data. Diagram 2: Conceptual Architecture of Back-end.
Mastering PostgreSQL 9. Chitij Chauhan, Dinesh Kumar Language: Working with PostgreSQL: Alexey Vasyliev Language: Russian Current version at publication: Shaun M. Thomas Language: February Title: Anthony R. Spanish Current version at publication: Manpreet Kaur, Baji Shaik Language: September Title: Guillaume Lelarge Language: July Title: Jayadevan Maymala Language: Chitij Chauhan Language: Postgres Succinctly Author: Peter Shaw Language: Daniel K. Lyons Language: Hannu Krosing, Kirk Roybal Language: Up and Running Author: Regina Obe, Leo Hsu Language: Paperback Published: Gregory Smith Language: PDF, Paperback Published: Simon Riggs and Hannu Krosing Language: Dominique Colombani Language: PDF Published: Andreas Scherbaum Language: German Current version at publication: August Title: Every effort has been made in the preparation of this book to ensure the accuracy of the information presented.
However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book. Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information. First published: March Production reference: ISBN www.
He started working with the Oracle database in Of late, he has been working with open source technologies. His database of choice is PostgreSQL. In his career, he has worked in different domains spanning supply chain management, finance, and travel. He has spent an equal amount of time working with databases supporting critical transaction processing systems as well as data warehouses supporting analytical systems.
When he is not working on open source technologies, he spends time reading and updating himself on economic and political issues. I would like to thank my wife, Uma, for putting up with my marathon writing sessions over weekends. I am also deeply indebted to the PostgreSQL community, which has individuals who are always promptly replying to my basic and not-so-basic queries.
It's an amazing team that has been working tirelessly to build such a great database and then give it away for free with such liberal licensing terms. Thank you. He is currently leading system administrators in strategic planning of networked infrastructures and is often consulted for system architecture design. He can be reached via his LinkedIn profile at http: I'd like to thank Anthony and Zachary for keeping me awake, day and night. Bahman Movaqar has been developing, deploying, and delivering software for the past 14 years, ranging from embedded operating systems to ERP implementations.
He's an open source believer and a passionate amateur chess player. He blogs at http: I'd like to thank my lovely wife, Nahid, who has taught me how to be strong. For the past 7 years, he has been managing, customizing, and developing extensions for Moodle LMS.
He can be reached at his website http: He has reviewed Moodle Security, Packt Publishing. I would like to thank my wife, Janaina de Souza, and my daughter, Lorena Rigo, for their support when I was away reviewing this book. He is also a former core developer of the Odoo project, having worked on it for 6 years. You can reach him at http: He works for Mgx. IO, a company that specializes in Python and Erlang developments.
You can reach this company at http: I would like to thank my wife, Anne, my daughter, Margaux, my family and friends for their support, and the PostgreSQL and Python communities for the awesome tools. You can upgrade to the eBook version at www. Get in touch with us at service packtpub. At www. TM https: PacktLib is Packt's online digital book library.
Here, you can search, access, and read Packt's entire library of books.
Why subscribe? Simply use your login credentials for immediate access. Installing PostgreSQL 1 Installation options 1 Downloading and extracting the source 2 Inspecting the contents 3 Dependencies to compile the source 4 Configuring and creating the makefile 5 Building and creating the executables 8 Installing and moving the files to where they belong 9 Inspecting the changes 10 Initializing a cluster 11 A quick walk through the directories 14 Processes created 17 Important files created 17 Working with extensions 18 Summary 19 Chapter 2: Server Architecture 21 Starting with the daemon process 21 Understanding the shared buffer 23 Inspecting the buffer cache 25 Checkpoint 29 WAL and the WAL writer process 32 Recovery 34 Incremental backup and point-in-time recovery 34 Replication 34 The background writer 36 The autovacuum launcher process 37 [i] www.
Client Tools GUI tools and command-line tools pgAdmin — downloading and installation Adding a server [ ii ] www. Harnessing its power will make your applications more reliable and extensible without increasing costs.
Using PostgreSQL's advanced features will save you work and increase performance, once you've discovered how to set it up. The book starts with basic concepts such as installing PostgreSQL from source and covers theoretical aspects such as concurrency and transaction management. After this, you'll learn how to set up replication, use load balancing to scale horizontally, and troubleshoot errors. As you continue through this book, you will see the significant impact of configuration parameters on performance, scalability, and transaction management.
Finally, you will get acquainted with useful tools available in the PostgreSQL ecosystem used to analyze PostgreSQL logs, set up load balancing, and recovery. It also covers the directory structure.
Chapter 2, Server Architecture, covers the important processes started when we start a PostgreSQL cluster and how they work along with the memory structures to provide the functionality expected from a database management system. Important concepts such as databases, clusters, tablespaces, and schemas are covered in this chapter.
Multiversion concurrency control is another topic dealt with in this chapter. Some of the aspects that should be considered when we choose a design tool are also covered in this chapter. Chapter 6, Client Tools, covers two clients tools pgAdmin: Browsing database objects, generating queries, and generating the execution plan for queries using pgAdmin are covered. Setting up the environment variables for connecting from psql, viewing history of SQL commands executed, and meta-commands are also covered in this chapter.
Chapter 7, SQL Tuning, explains query optimization techniques. To set the context, some patterns about database use and theory on how the PostgreSQL optimizer works are covered. These include memory settings, cost settings, and so on. Two object types: Chapter 10, Scaling, Replication, and Backup and Recovery, covers methods that are usually used for achievability.
A step-by-step method to achieve horizontal scalability using PostgreSQL's streaming replication and pgpool-II is also presented.
Point-in-time recovery for PostgreSQL is also covered in this chapter. Chapter 11, PostgreSQL — Troubleshooting, explains a few of the most common problems developers run into when they start off with PostgreSQL and how to troubleshoot them. Connection issues, privilege issues, and parameter setting issues are also covered.
Some interesting data types that every data architect should be aware of, a couple of really useful extensions, and a tool to analyze PostgreSQL log files are covered. It also covers a few interesting features available in PostgreSQL 9. Who this book is for You are expected to have some exposure to databases.
Basic familiarity with database objects such as tables and views is expected. You will find this book really useful if you have no or a little exposure to PostgreSQL. If you have been working with PostgreSQL for a few years, you should still find a few useful commands that you were not aware of or a couple of optimization approaches you have not tried.
You will also gain more insight into how the database works. Conventions In this book, you will find a number of text styles that distinguish among different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: Tips and tricks appear like this. Reader feedback Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of. To send us general feedback, simply e-mail feedback packtpub.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www. Customer support Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your download.
Downloading the example code You can download the example code files from your account at http: If you downloadd this book elsewhere, you can visit http: If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http: Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https: The required information will appear under the Errata section. Piracy Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at copyright packtpub. We appreciate your help in protecting our authors and our ability to bring you valuable content. Questions If you have a problem with any aspect of this book, you can contact us at questions packtpub. The system used for installation and providing examples in the following sections is a bit CentOS 6. For those using Windows systems, there is a set of utilities available at http: For Windows, downloading the Graphical Installer and using this is the easy way.
While these options work, we do not get to see what is happening when we execute these commands, except, of course, that the database gets installed. Then there are situations where we might want to build from the source. Assume that all we have is one production server and one development or staging server. We are on version 9. Version 9. If we want to install 9. We could also set different default ports. It's also possible that the new version source is ready, but the package for our Linux distribution is not ready yet.
Installation from source is the way forward in these situations. One advantage with installing from the source is that we don't have to worry too much about which package to download, the version of operating system CentOS 6. These are more or less irrelevant. We also need to download and install all the tools and utilities necessary to compile and make the software, in this case, PostgreSQL. So let's get down to it. We can see a number of versions all the way down to version 1 when it was called Postgres95 and up to the latest production and beta versions.
If you belong to the group who believe that one shouldn't try software that is not at least a few months old, so that its teething issues are resolved, you should opt for the last-but-one version. It's a good idea to opt for the latest stable version. The latest versions have added quite a few very useful features, such as materialized views and an improved set of JSON functions and operators. We will use the following wget command to download the source: Executing this command will give us a window that looks like this: The files can be extracted using the following command: In the preceding command, the x option is used to extract, v for verbose is used so that we can see the list of files and folders getting extracted, and the f option is for, well, passing the name of the file, which will undergo the extraction process.
We might need to provide the z option, so the command will be tar -xzvf if the preceding code in the tar command does not work. Some versions of tar are intelligent enough to figure out whether it is a gzipped file or not and will unzip it automatically. The untarred unzipped files come to around MB. Inspecting the contents Let's inspect the contents: Here, we instructed find to limit itself to scanning just one level of subdirectories using maxdepth 1. We used the type option along with d to tell find that we need files of type directory, as shown in the following screenshot: There are four directories: This directory has most of the core code, namely, code for the backend processes, optimizer, storage, client utilities such as psql and code to take care of replication, and so on.
It also contains the makefiles for various distributions.
For example, we have the files Makefile. This directory is where many extensions are available. These are add-on modules that do not form part of the core installation, but can be installed as needed.
This directory contains a few macros that help you configure and compile the package. Now let's move on to the dependencies, configuration options, and the actual installation itself. The gmake -v command will tell us whether we have gmake and its version. A compiler is also necessary. The gcc -v command will provide you with the version of gcc as well as options with which it was configured on the system, as shown in the following screenshot: We can use the following commands to install the necessary packages if they are missing: The make utility automates the process of building the executable from source code.
The make command uses a makefile, which contains rules on how to build the executables. Other than GNU Make and a compiler, there is nothing else that is really necessary to continue. However, it is better to have at least the following two components: It also helps to have zlib in place before we proceed with the installation.
This compression library can be handy when we are taking backups a process definitely to be followed when we have a database. Still, this might not be as useful as the other two, namely, readline and zlib. Configuring and creating the makefile The next step is to execute configure. This is a shell script which will run, to quote documentation, a number of tests to determine several system dependent variables.
It will also create many files that will be used during compilation. We can get an idea about the options by executing the following command: These options can be broadly grouped into the following categories: Changing default setting for parameters such as blocksize can have significant impact on performance. So, we need to be cautious here.
Changing the default port is a good idea from a security perspective. It can be changed later in the configuration file also. Pay attention to the --prefix option. If you would like to do a clean upgrade without causing disruption to the existing environment, provide a directory in which the installation files should be written to. This way, each version will be in a different directory. For example: The output tells us that readline is not available.
However, if we list installation packages, it is very much there. The reason is that readline-devel is missing. It contains files needed by programs such as psql that use the readline library. This can be installed using the following command: You will have to execute the command using sudo or root.
You might also get a similar error for zlib, although zlib itself is already installed. Again, the corrective action is to install devel, in this case, zlib-devel. Once this is done, we can run configure again and it should go through without any issues, as shown in the following screenshot: The two files are now created in the current directory in addition to a few more files in subdirectories. One is config. The config. It's possible that the config. The makefile created in the configure step is used by the gmake utility.
We have the option to make all the options available the contrib modules, source, and so on , or just the core. It's also possible to build just the core now and add the necessary contrib modules later on.
We will build everything now, rather than adding the necessary modules later. Hence, the command is: Ready to install, as shown in the following screenshot: As the installation process involves writing files to directories, which an ordinary user cannot write to, we need to use su for this step: We used the keyword world for make.
We will use a similar option for installation too: Ready to install. Then, the install command copied the directories and files to target directories and set attributes such as permissions. Refer to the highlighted portion in the following screenshot: In this, we have four directories, namely include, lib, share, and bin.
The include directory contains header files. The bin directory, of course, contains executables. It is the share directory that is a bit more interesting. Once we initialize a cluster and make changes to various configuration files and then lose track of the changes, we can compare with these files and understand what changes have been made or roll back the changes if necessary.
At the next level of directories under share, we have the doc directory that holds the documentation, and the man directory that holds the manual pages, and so on.
The directory of interest under share is the one named extension. Here, we can see all the extensions, which we can install as per our need. Most extensions have a. The documentation to install PostgreSQL from source is at http: Note that we have just installed the database software.
There is no database available to connect to yet. Adding a user for the database administration tasks and initializing a database cluster are the next steps. This user will also be the superuser for the cluster. The following command has to be executed as root or with sudo: It can be mydbadmin, mydba, or anything we fancy.
Next, we create a directory that will be the base directory for the new cluster. This could be anywhere. However, you might want to have the database cluster on a separate partition. In the event of an OS and associated file system crash, your database data remains intact.
It can also be that you want to use faster spinning disks or Solid State Disks for the database cluster to improve performance. As root, we execute the commands: This is necessary because when we initialize the cluster, the user under which the command was executed becomes the owner of the cluster. The server process will also be owned by this user.
We will go with the standard user —postgres, which we created: Not exactly. If we run initdb now, we will get an error: We could provide the absolute path and make it work.
However, it is better to set the variables. Log out, log in, and try again: It's a good idea to execute the following: This parameter is used to define the directory where the cluster will be initialized where the database cluster should be stored. This is the only mandatory parameter. Another parameter that can be useful is --pwprompt.
Using this, we can set the password for the database superuser. So, we execute the following command: As seen in the preceding screenshot, the process asks for the superuser password. Towards the end, it gives a warning that the trust authentication is enabled for local connections. This means that it will be possible to make connections from the localhost without being prompted for a password. It's a good idea to change this setting. We will come to this later.
For more options available for initdb, please refer to http: Passing init or initdb as a parameter results in the initialization of a cluster. More options can be explored at http: This directory holds the databases that will be created by database users. We can try creating and dropping a database, as shown in the following code: They are used internally by PostgreSQL as primary keys for various system tables. Objects created in the cluster tables, databases, and so on will have OIDs associated with them.
The oid2name utility is available as a contrib module and helps us examine the databases, tables, and related file nodes. First, we were not prompted for a password remember the warning about trust authentication? Next, we can execute host commands from psql more on this in a later chapter.
PostgreSQL does do quite a bit of internal book-keeping when we create or drop objects in addition to manipulating directories. This directory contains cluster-wide tables. There are many tables and associated views that keep track of the entire cluster, namely, database roles, system catalog data, and so on.
This directory contains the transaction commit status data. This directory contains multitransaction status data concurrent transactions waiting for a lock. This directory contains information about committed serializable transactions. This directory contains exported snapshots.
This directory contains temporary files for the statistics subsystem. This directory contains subtransaction status data. This directory contains symbolic links to tablespaces. This directory contains state files for prepared transactions. In short, we have directories to hold containers of real user created data tables and tablespaces , directories to hold data about all the data and data structures metadata , and then directories to hold data about the state of transactions.
We can see that there is one parent process pid , which spawned a few child processes. Important files created One important file that gets created when we initialize a database cluster is postgresql. This file contains a number of critical parameters related to the server processes and resource allocation, memory management, logging, file locations, and so on.
Changes to these files are necessary to enable client connection to the database from a different machine. The postmaster. However, we made quite a few extensions available using the world option. What about them? We can list the installed extensions using the dx describe extension command at the psql prompt: To remove it, just drop it: We went through the directory structure of the source tree, the cluster initialization process, the resultant directories and files, as well as the process to install and remove extensions.
We will cover the various background processes, their functions, memory structures and how they all work with each other to provide the many features available in PostgreSQL.
In this chapter, we will focus on the processes and their roles, memory management, and how they work together. We will start by listing the processes once again.
No client is connected to any of the databases yet: These processes are also referred to as utility processes and include bgwriter, checkpointer, autovacuum launcher, log writer, stats collector process, and so on. The daemon process also listens for connection requests, receives requests for connections from clients, and spawns server processes for the client.
It's obvious that the daemon itself is a mandatory process that should be running for a user to connect to the database. The following diagram walks you through the process of how the daemon process receives a connection request and starts forks a backend process. The backend process will, on successful authentication, start handling requests from that client: Client 1. Request 3. Authenticate 4.
Query 5. Results Daemon Process Backend Process 2. So, an active server, after a period of time, will have the processes that were there when the server started, plus quite a few processes to serve client connections, as shown in the following diagram: Backend Process n [ 22 ] www. For example, a user logs in to site and searches for the latest iPad, its price, and availability.
This sounds simple enough. Assuming the simplest unbelievably simple table structure, this search will become the query: The preceding query gets executed a few thousand times concurrently. When thousands of users search for different products, the iPad gets changed to thousands of different product names.
So far so good. However, what happens when there is just one of the new iPads left at site and there are a few hundred users trying to add it to their shopping cart? Gets real tricky, right? That is, for the database, many users trying to write to the same record: The reads and writes will result in searching for many files, opening these files, using fseek for specific data records, locking, editing, and unlocking.
To make this a lot more scalable and faster, the concept of shared buffers memory area is introduced. Now, the backend processes are no longer reading from the files and writing to the files, but dealing with buffers or RAM, with significant improvement in performance. This fixed-size block of shared memory is allocated when the server is started.
It's not this memory chunk alone that is responsible for improving the response times, but the OS cache also helps quite a bit by keeping a lot of data ready-to-serve. Together, these two caches result in a significant reduction in the actual number and volume of physical reads and writes.
In addition to these two levels of caching, there could be a disk controller cache, disk drive cache, and so on. The first thing the process will check is whether the data it wants is available in the database buffer cache. These three possibilities are shown in the following diagram: Exceptions to this would be databases where the buffer is minuscule compared to the data that is usually read and written.
It's only when different transactions are accessing data from really different areas in the file system that the issue of frequent buffer flushes to disk and reads from disk will occur. However, first let's continue with the buffer and see how user-changed data finally reaches the data files. Inspecting the buffer cache PostgreSQL provides an extension to view what is in the buffer cache. It can be installed in a similar manner to what we did earlier.
Log in to psql and create two databases: We get an idea by looking at the SQL. If we have to use them in another database in the cluster, we must install the extensions in this database too. In PostgreSQL, a cluster refers to a set of databases using the same configuration files, listening for requests at a common port. Let's connect to the test database and see what is present in the buffer cache: The record with 0 represents buffers that are not used yet: We can link this with a couple of other views to get a better picture: There are many data dictionary tables and views that provide us information about various objects, object types, permissions, and so on.
One of the columns in this table is relname. We should use it along with the relkind column. The relkind column tells us what type of object the record refers to. Possible values in relkind include r table , i index , S Sequence , v view , and so on. Let's remove relname from the preceding query and modify it slightly: We will first create a table and insert a record: Using this keyword will automatically create a sequence number generator SEQUENCE and the column id in the table emp will be populated from this sequence.
For more information about numeric data types, refer to http: For details about sequences, refer to http: We can repeat the query to inspect the buffer and check whether the buffer contains the newly created table and its sequence: Let's force a checkpoint: Checkpoint Checkpoint is a mandatory process.
To understand this, let's discuss blocks. PostgreSQL always reads and writes data in blocks. Consider the emp table. It has just one record. A block is also referred to as a page. It is easy to verify that PostgreSQL uses blocks. Ensure that our table has just one record as follows: So, a table with just one record takes up 8K.
Let's try inserting some data and see what happens: Within the files, data is managed in blocks. In short, the physical layout of a PostgreSQL cluster can be presented as follows: As mentioned earlier, the fact that a user has committed a change does not mean that the change has been written to the data file.
It's the job of the checkpointer process to write the change to the data file. When a checkpoint happens, all dirty modified pages are written to the table and index files.
PostgreSQL is an enterprise-level database that competes among proprietary database vendors, owing to its remarkable feature set and reliability without the expensive licensing overhead. This book is a PostgreSQL is an open source database management system. It is used for a wide variety of development practices such as software and web design, as well as for handling large datasets big data. This book will show you that PostgreSQL is so much more than a database server. In fact, it could even be seen as an application development framework, with the added bonuses of transaction support, massive data storage, journaling, recovery, and a host of other Create, develop and manage relational databases in real world applications using PostgreSQL.
PostgreSQL is one of the most powerful and easy to use database management systems.