pgbench is postgresql’s built-in benchmark testing tool. It runs the same SQLssimultaneously in different sessions many times and generate statistics.By default, it performs TPC-B based tests including select, update andinsert operations. But it can also run external SQL and make benchmark tests. -i (initialize): Initialize option. It creates 4 tables with row numbers below. If … Pgbench
Postgresql, like every other database, has routine maintenance operations in order tooptimize performance and avoid downtime.Maintenance operations should be automated with scripts and crontabs andmonitored for successful completion. One of the most important maintenance operations is backup. This topic is discussedin detail in Chapter 11. If there is no up-to-date backup, data loss will occur … Postgresql Database Maintenance
Reindex ACCESS EX-CLUSIVE causes lock. So only SHARE UPDATE EXCLUSIVEis causing the lock, and it is preferable to use with the CONCURRENTLY option.In the books, index returns the page of the searched word or topic. You can go to thepinpoint page without scanning the book. The indexes in the database are also similar.The rows have … Reindexing
Logs are very important when doing error analysis. That’s why it’s vital to keep it fora certain period of time. If the logs are directed to a file with stderr, this log file canbe reset by truncating when the instance is stopped and restarted. When we thinkof databases that have not been restarted for years, … Maintenance of Log Files
Data security sensitivity varies according to sectors. But in general, it may be necessaryto record the reading, writing, updating and deletion of data for the high security leveldata(TCN, Credit card information, etc.).Student grades can be upgraded in schools.The balance can be increased in the bank, EFT/money transfer can be made.Messages and call logs sent by … Audit
Locks prevent the entire table or rows from being modified and only allow reading (select).The update and delete operations lock the row, and prevents another session frommodifying those rows. The rollback or commit releases the lock.Read (select) operations do not lock the rows (as discussed in MVCC).Locking processes occur automatically, but it is possible to … Lock
Databases and database objects are hosted in tablespaces.Through tablespaces, we can place the objects on any disk or directories as we want.Once the tablespace is created, we only need to mention the tablespace while creatingthe objects.While it is possible to keep a database and all its objects in one tablespace, it is alsopossible to place … What is Postgresql Tablespace and How to Manage it
WAL is a standard method for data integrity.When the change is made in the memory, metadata information is written to WAL filesfirst.Then, at the most appropriate time, these dirty buffers are written to data files (LRUAlgorithm is used).If the system shuts down (crash) due to electricity/hardware failure etc. the dirty bufferin the memory will be … WAL (Write Ahead Log)
A user account and authentication method are required to connect to the databaseand perform transactions. Note: Users in the operating system and users in the database are different. Forexample, the postgres user in the Linux operating system on which the postgresdatabase is running is the operating system user. It is used to manage the operatingsystem … Postgresql User Accounts & Roles
It is used to group and organize objects (table, view, index, function, procedure, etc.)according to business logic and hierarchy.For example, separating the data of different applications such as archive, audit,accounting, human resources can be kept in different schemas for ease of managementand security.It is database specific, that is each database has its own schemas. search_path … Postgresql Schema