Each database maintain metdata and data itself having. There is no dependency on any databases.

Slef contain multiple database it is called cluster.

Services and instances… are same.

We can create a many databases like unlimited.

Schema is nothing but logical entity
Note: cascading is not possible
mohankumar.yes5@gmail.com
If we create new template db u can use is_template should be true.
Note: one file creating for each db for session details in oid location
26th Nov 2024
Imp
Here we are referring clone database from normal database.
cannot drop templeate

After templatedb change to is_template to false.. we can drop it.

If we change the db name , can’t change oid

=====================TBS Management=======================
 1- Different storage2, indexes 3,temp files,4 partitions

Temporary files created session level

PostgreSQL, often referred to as Postgres, is a powerful, open-source relational database
management system (RDBMS).

Its architecture is designed to be highly extensible and robust. Here’s a high-level overview of its
architecture:

1. Process Architecture

PostgreSQL uses a process-based architecture, where each client connection is handled by a

separate server process.

This is different from a thread-based architecture used by some other RDBMS.

2. Shared Memory

PostgreSQL uses shared memory for caching and communication between processes. Key
components include:

Shared Buffers: Used for caching data pages.

WAL Buffers: Used for caching write-ahead log (WAL) entries before they are written to disk.

3. Background Processes

Several background processes help manage the database:

Postmaster: The main process that handles startup, shutdown, and connection requests.

WAL Writer: Writes WAL data to disk.

Background Writer: Writes dirty pages from shared buffers to disk.

Autovacuum: Reclaims storage by removing dead tuples.

4. Storage Architecture

PostgreSQL stores data in a structured format on disk:

Tablespaces: Directories where database objects are stored.

Data Files: Each table and index is stored in its own file.

WAL Files: Logs of all changes made to the database, used for crash recovery.

5. Query Processing

The query processing architecture includes:

Parser: Converts SQL queries into a parse tree.

Planner/Optimizer: Generates an execution plan for the query.

Executor: Executes the plan and returns the result.

6. Extensibility

PostgreSQL is highly extensible, allowing users to add new data types, operators, and functions. This
is achieved through:

Extensions: Modules that can be loaded into the database to add new functionality.
Foreign Data Wrappers: Allow PostgreSQL to query data from other databases.

7. Security

PostgreSQL provides robust security features, including:

Authentication: Supports various methods like password, Kerberos, and SSL.

Authorization: Role-based access control.

Encryption: Data encryption at rest and in transit.

27th
There is no permanent and temporary tablespaces.
If empty we can drop tablesapce .it will remove the file in physical laction.
Here we can verifiy the table belongs to tablespace.

Here we need verify th table path

Temp tbs
If we don’t mention the default tbs, when create table in different tbs it is showing the db name.
Alter assing to new tbs.

Here moved the files to new location after assign the new tablesapce.

We can’t set default on live scenario.

Temp files are removed after session exit.

Session specific for temp tb

here we can set temp tbs multiple db’s
We can’t drop tbs, due to is not empty.
Check tbs size also above query
Logged: chages are going to wal file and we can do reviver

Unlogged: changes are not going to wal file and no recovery but after unlog change to logged and
take a backup. Than only w can do restore it.
Fsm: free space map

Visible map:
Aut
Other session we can’t see the data why bez here it is not commited.
When we do table move to other table space, physically also move to other differ table space.

We need down time required for alter table move.

If we n=change the table name not affect the physical attributes,

03-12-24
Virtual coulmns doesn’t have data
Unlogged

P means logged.
We can change to unlogged using alter
Creating backup table.
Truncate table
Table move
Moved to other tablesapce and number also changed.
No file after moved

This is for permanent tables.

Default partition no range.
Detach and attach taking less time compared to backup.
Attache
When u detach partitons but u can’t see the table level.
We can’t add
If we need to add partition and detach the partition and attach and update new one.

Detach and attache partion and add one more location.

Default partition will go all locations…
test2=# create table an_part5 partition of sim_num for values with (modulus 4, remainder 4);

ERROR: remainder for hash partition must be less than modulus

test2=#

How It Works

 Modulus: The total number of partitions.

 Remainder: The result of the hash value modulo the total number of partitions.
B-tree Index

B-tree (Balanced Tree) Indexes are the default and most commonly used index type in
PostgreSQL. They support a wide range of queries, including equality, range, and pattern
matching queries.

 Syntax:

sql

CREATE INDEX index_name ON table_name (column_name);

 Use Case: Ideal for columns with a high degree of unique values and for queries
involving range searches (e.g., =, <, <=, >, >=).

Example:

sql
CREATE INDEX idx_customer_id ON orders (customer_id);

Partial Index

Partial Indexes are indexes that include only a subset of rows in a table, based on a condition
specified in the WHERE clause.

 Syntax:

sql
 CREATE INDEX index_name ON table_name (column_name) WHERE condition;

 Use Case: Useful for indexing frequently queried rows and reducing the size of the
index.

Example:

sql
CREATE INDEX idx_active_orders ON orders (order_date) WHERE status =
'active';

Hash Index

Hash Indexes use a hash function to map the indexed column's values to a hash table. They
are suitable for equality comparisons only.

 Syntax:

sql

CREATE INDEX index_name ON table_name USING HASH (column_name);

 Use Case: Ideal for columns with low cardinality and for equality searches (e.g., =).

Example:

sql
CREATE INDEX idx_customer_id_hash ON orders USING HASH (customer_id);

When to Use Each Index

 B-tree Index: For general-purpose queries, especially those involving range searches.
 Partial Index: When you need to index a specific subset of rows.
 Hash Index: For equality comparisons on columns with low cardinality.
tick mark is default indexes.
Create index here
If index is not nedded, we can drop
Here in table,duplicated,so unique index is not creating.
Creating index on single column
create multi index on column and which column is more using, we can mention it while creating.

Multicolumn is taking high fragmentation than table.

If no use index, we can drop it.

Multi column on unique index.
If unique index, it is allowed

Create table and create index asc

It is acending order

Even if we menition didn’t show why bez it is default.

For ascending order, it is showing .
Multi column index with asc and desc above..
Hash 0.563 btree 0.866
User creation with valid.
PSQL is only for TEXT format it is default but cant take custom and directory.

PG_RESTORE can’t take text format and can take custom and directory backups.

Doesn’t include default tablespces while doing entire backup

This is txt file we can see the setails of dump

Db level backiup
Table level

Two tables backup in same db

Schema only

If we need to exclude the schema we can use

Parallel
-v verbose we can see the parallel is working or not

Advantage of directory for wecan use paralllel

Tar- bzip high compress zzip low level compress
Fist base restore and restore other files
Once restore, recovery.signal auto rmoved.
Clear the base and other tbs location and restore the backup using tar command

There is tablesapce map and backup label information

Create a recovery signal for to start the recovery mode
Recovery.signal removed
Remove recover_command and to avoid the issue

Note: barman and pgbakrest tools we can use the incremental backups..
We can distribute the io so that it will improve the performance.

There is no partition convertion here till now. We can take logical backup and will do

table size 100GB how to convert into partitions in postgres

ChatGPT said:
ChatGPT
Partitioning a 100GB table in PostgreSQL is an excellent approach to improve performance
by distributing data into smaller, more manageable pieces. Below are the steps to convert the
table into partitions:

1. Choose a Partitioning Strategy

PostgreSQL supports two main types of partitioning:

 Range Partitioning: Partition by ranges of values (e.g., date ranges or numeric ranges).
 List Partitioning: Partition by specific values (e.g., a list of categories or regions).
 Hash Partitioning: Partition using a hash function for even distribution.

Choose a strategy based on your data access patterns:

 Range Partitioning: For time-series data or continuous values.

 List Partitioning: For categorical data.
 Hash Partitioning: For evenly distributing data without clear ranges.

2. Create a Partitioned Table

Define the main table as a partitioned table. For example, if you're partitioning by a date
column:

sql
Copy code
CREATE TABLE large_table (
id SERIAL PRIMARY KEY,
data_column TEXT,
created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

3. Create Partition Tables

Define the child partitions for the specific ranges. For example:

sql
Copy code
CREATE TABLE large_table_2023 PARTITION OF large_table
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

CREATE TABLE large_table_2024 PARTITION OF large_table

FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

Repeat this process to create partitions for each range you need.
4. Migrate Data

Migrate the data from the original table into the partitioned table:

1. Create a backup of the original table for safety.

2. Use INSERT INTO ... SELECT or COPY commands to move data:

sql
Copy code
INSERT INTO large_table (id, data_column, created_at)
SELECT id, data_column, created_at
FROM old_large_table;

3. Ensure the data is distributed into the correct partitions.

5. Verify Data Distribution

Query the partitions to verify that data has been distributed correctly:

sql
Copy code
SELECT tableoid::regclass AS partition_name, count(*)
FROM large_table
GROUP BY tableoid::regclass;

6. Drop the Old Table

After verifying the data and ensuring no dependencies remain on the old table, you can drop
it:

sql
Copy code
DROP TABLE old_large_table;

7. Add Indexes to Partitions

Add indexes to individual partitions to optimize query performance. For example:

sql
Copy code
CREATE INDEX idx_large_table_2023_created_at ON large_table_2023
(created_at);

8. Update Application Logic

Ensure that your application queries work seamlessly with the partitioned table. PostgreSQL
automatically routes queries to the appropriate partitions if the WHERE clause includes the
partition key.

9. Maintenance and Monitoring

 Use partition maintenance tools to create new partitions as needed.

 Monitor query plans with EXPLAIN to ensure queries hit the intended partitions.

Considerations

1. Partition Key: Choose a partition key that aligns with common query patterns to minimize
query overhead.
2. Default Partition: Optionally, create a default partition to catch out-of-range data.

sql
Copy code
CREATE TABLE large_table_default PARTITION OF large_table DEFAULT;

3. Performance Testing: Test your queries on the partitioned table to validate performance
improvements.

By following these steps, you can convert a 100GB table into partitions in PostgreSQL,
significantly improving query performance and manageability.

can we use logical backup for convert into partitions in postgres?

Yes, you can use a logical backup to convert a table into partitions in PostgreSQL. Logical
backups are created using tools like pg_dump, which exports the table's schema and data. The
steps involve extracting the table's data and schema, modifying the schema to define
partitions, and reloading the data into the partitioned structure.

Here’s how you can do it:

Steps to Use Logical Backup for Partition Conversion

1. Create a Logical Backup of the Table

Use pg_dump to back up the schema and data of the table.

 Backup Data Only:

bash
Copy code
 pg_dump -U postgres -d your_database -t old_table --data-only -F c -f
data_backup.dump

 Backup Schema Only:

bash
Copy code
pg_dump -U postgres -d your_database -t old_table --schema-only -f
schema_backup.sql

2. Modify the Schema for Partitioning

1. Open the schema file (schema_backup.sql) in a text editor.

2. Replace the original table definition with a partitioned table structure.

For example, if your original table schema looks like this:

sql
Copy code
CREATE TABLE old_table (
id SERIAL PRIMARY KEY,
data_column TEXT,
created_at DATE NOT NULL
);

Modify it to define a partitioned table:

sql
Copy code
CREATE TABLE new_table (
id SERIAL PRIMARY KEY,
data_column TEXT,
created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

-- Define partitions
CREATE TABLE new_table_2023 PARTITION OF new_table
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

CREATE TABLE new_table_2024 PARTITION OF new_table

FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

3. Restore the Schema

Restore the modified schema to your database:

bash
Copy code
psql -U postgres -d your_database -f modified_schema.sql
4. Restore the Data

Load the data into the partitioned table. Since the new table is partitioned, PostgreSQL will
automatically distribute the rows into the appropriate partitions based on the partition key.

Use pg_restore if you backed up the data in custom format:

bash
Copy code
pg_restore -U postgres -d your_database -t old_table --data-only --disable-
triggers data_backup.dump

Or, use COPY if you exported the data in plain-text format:

sql
Copy code
COPY new_table (id, data_column, created_at) FROM '/path/to/data_file.csv'
WITH CSV;

5. Verify Data Distribution

Run a query to ensure the data has been distributed into the appropriate partitions:

sql
Copy code
SELECT tableoid::regclass AS partition_name, count(*)
FROM new_table
GROUP BY tableoid::regclass;

6. Drop the Old Table

After confirming the data has been migrated correctly, drop the old table:

sql
Copy code
DROP TABLE old_table;

Advantages of Using Logical Backup

 Flexibility: You can modify the schema during restoration to implement partitioning.
 Non-Intrusive: Logical backups can be created without locking the table, minimizing
downtime.
 Custom Partitioning Strategy: You have complete control to define partitions based on your
requirements.

Considerations
 1. Data Volume: For a 100GB table, restoring data may take time. Test the process in a staging
environment first.
2. Indexes and Constraints: Recreate necessary indexes and constraints on the partitioned
table after data migration.
3. Foreign Keys: PostgreSQL does not support foreign keys referencing a partitioned table
directly. Plan accordingly.
4. Application Downtime: There may be a brief downtime while switching from the old table
to the partitioned table.

By leveraging logical backups, you can seamlessly convert a large table into a partitioned
structure with minimal disruption.
too see the above screenshot.
To enable or disable for autovaccum..
Drop and create hash index and see the details below.
Index is using

How many times are reading the idx_scan

Show max_parallel_workers_per_hather it is based on worker process and based on CPU.
=====================standby======
Create directories same primary using \db
/etc/hosts
Stdy
Low cardinality column.brin index same as bitmap index..
Btree index is not support for array
Create extension