mydreambei-ai

Postgresql 如何trigger autovacuum and analyze

开启autovacuum的前提条件是打开track_counts。因为系统需要根据跟踪到的tuples计数和阀值进行比较来决定是否出发autovacuum 或 autoanalyze

autovacuum 是否开启自动vacuum, analyze.
log_autovacuum_min_duration 阀值，用于度量是否需要记录下autovacuum的动作到log里面.(-1表示禁止，0表示记录所有)
autovacuum_max_workers 用于指定整个数据库机器同一时间点允许的autovacuum后台进程(不包括lanucher进程)
autovacuum_naptime 两个autovacuum或autoanalyze允许周期间的间隔时间。为了看效果，我们在下面的例子把这个值设置为1S。
autovacuum_vacuum_threshold 最小触发vacuum的度量值(计数器记录update,delete的tuples)
autovacuum_analyze_threshold 最小触发analyze的度量值(计数器记录insert,update,delete的tuples)

Postgresql Server Side Cursor

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocated by the client.

If the dataset is too large to be practically handled on the client side, it is possible to create a server side cursor. Using this kind of cursor it is possible to transfer to the client only a controlled amount of data, so that a large dataset can be examined without keeping it entirely in memory.

Server side cursor are created in PostgreSQL using the DECLARE command and subsequently handled using MOVE, FETCH and CLOSE commands. postgresql-cursor

Cursor

Recursive CTE/Postgres

Setup

from sqlalchemy import (String,
                        Integer,
                        engine_from_config,
                        Column,
                        ForeignKey)
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.sql.functions import concat

from sqlalchemy.ext.declarative import declarative_base

	from operator import attrgetter

	from sqlalchemy import Table, event, subquery
	from sqlalchemy.ext.compiler import compiles
	from sqlalchemy.engine.reflection import Inspector
	from sqlalchemy.schema import DDLElement
	from sqlalchemy.sql.expression import Executable, ClauseElement

	from yeepa.common.util import monkey_patch
	from yeepa.backend.models import DBSession, metadata, Base, read_only_metadata

	create or replace function lmy(first INTEGER, item INTEGER) returns INTEGER as $$
	BEGIN
	return first * item;
	END;
	$$
	LANGUAGE plpgsql;

	create aggregate yml(INTEGER) (
	sfunc = lmy,
	stype = INTEGER,

	from ctypes import *

	def convert_bytes_to_structure(st, byte):
	# sizoef(st) == sizeof(byte)
	memmove(addressof(st), byte, sizeof(st))


	def convert_struct_to_bytes(st):
	buffer = create_string_buffer(sizeof(st))
	memmove(buffer, addressof(st), sizeof(st))

	import re
	import time

	from sqlalchemy import Column, Integer, TIMESTAMP, func, String, ForeignKey
	from sqlalchemy import create_engine
	from sqlalchemy import event
	from sqlalchemy.ext.declarative import as_declarative, declared_attr, AbstractConcreteBase
	from sqlalchemy.orm import scoped_session, sessionmaker, relationship

	engine = create_engine('sqlite://', convert_unicode=True, echo=False)

	import argparse
	import collections
	import http.client
	import multiprocessing
	import os
	import re
	import time
	from multiprocessing import Queue, Lock, Process
	from select import poll, POLLIN
	from urllib.parse import urlparse

	from sqlalchemy import (String,
	Integer,
	engine_from_config,
	Column)
	from sqlalchemy.ext.declarative import declarative_base
	from sqlalchemy.orm import sessionmaker

	Base_1 = declarative_base()

	Base_2 = declarative_base()

	from sqlalchemy.ext.declarative import declared_attr, AbstractConcreteBase

	class _Event(AbstractConcreteBase):
	@declared_attr
	def hackinfo_id(self):
	return Column(ForeignKey("hackinfos.id", ondelete='CASCADE'))

	@declared_attr
	def hackinfo(self):
	return relationship("HackInfo")