How To Dev

Introduction This is a data storage mechanism used to solve the problem of temporary data storage. Although it is called a Temporary Table , this is actually a real table created in the database just like other normal tables. Characteristics The scope of existence of a Temporary Table is throughout that Session, until you disconnect. It is stored like a real table and can be written to disk if it exceeds temp_buffers. It has all the properties of a physical table, such as the ability to create indexes to speed up data queries, run ANALYZE or view the table structure. Its lifecycle only lasts throughout that Session, when you disconnect from the database, this temporary table will be automatically deleted, or you can also actively execute DROP TABLE. Because it exists throughout the session, it can be called for use multiple times in different statements and also within Functions or Stored Procedures. Use cases The following are appropriate cases to use a Temporary Table , including V...

Introduction Partial Index is an extremely powerful feature that allows the creation of an index on only a subset of a table's data, instead of indexing the entire table. This subset is defined by a filtering condition in a WHERE clause when creating the index. In essence, a Partial Index just adds a condition during index creation, so it can be used with all index types (such as B-Tree , Hash , GIN , GiST , ...) and supports all data types. Additionally, a Partial Index can also be used in combination with a Constraint , Expression Index and Composite Index . Advantages Space-saving: The index size is much smaller than a Full Index, which saves RAM and disk space. Increased Write performance (INSERT/UPDATE/DELETE): When adding or modifying data that does not satisfy the index condition, Postgres does not need to update the index tree. Disadvantages Queries must match the condition: The Postgres Query Planner only uses this index if the SELECT query has a WHERE clause that exactl...