repository·master·Indexed 25 days ago
https://github.com/le0pard/pgtuneA tool for tuning PostgreSQL configuration settings based on hardware specifications. It provides a React-based interface to input parameters such as database version, OS type, workload type, memory, and CPU to generate optimized settings for postgresql.conf or as ALTER SYSTEM SQL commands. The project includes a Redux-managed state for calculating recommended values for shared buffers, work memory, and WAL settings, along with validation schemas for hardware inputs.
To run PgTune in development mode, ensure you have node.js and yarn installed. Use yarn to install dependencies and yarn dev to start the development server on port 5173.
$ yarn # get all node.js deps
$ yarn dev # start server on 5173 portconfigureAppStore to initialize the Redux store. This function sets up the core reducers (configuration, settings, and sw), configures middleware (including listenerMiddleware and redux-logger in development mode), and enables listener behavior via setupListeners. You can optionally pass a preloadedState object to initialize the store with specific data.Use the following selectors to access the current configuration state from the Redux store:
selectDBVersion: Returns the PostgreSQL version.selectOSType: Returns the operating system type.selectDBType: Returns the database workload type (e.g., DB_TYPE_WEB, DB_TYPE_OLTP, DB_TYPE_DW).selectTotalMemory: Returns the total system memory.selectTotalMemoryUnit: Returns the unit for memory (e.g., SIZE_UNIT_GB).selectCPUNum: Returns the number of CPU cores.selectConnectionNum: Returns the number of connections.selectHDType: Returns the hard drive type (e.g., HARD_DRIVE_SSD, HARD_DRIVE_HDD).selectDBSize: Returns the database size relative to RAM.The library provides high-level selectors that compute optimized PostgreSQL configuration values based on the current state. These selectors account for OS constraints (like Windows memory limits), hardware (SSD vs HDD), and workload types.
Key calculated settings include:
selectSharedBuffers: Recommended shared buffer size.selectWorkMem: Recommended memory for individual operations.selectMaintenanceWorkMem: Recommended memory for maintenance tasks.selectMaxConnections: Recommended maximum connections.selectRandomPageCost: Recommended cost for random disk access.selectEffectiveCacheSize: Recommended effective cache size.selectWalBuffers: Recommended WAL buffer size.selectCheckpointSegments: Returns an array of objects containing min_wal_size and max_wal_size recommendations.selectParallelSettings: Returns an array of objects for parallel worker settings (max_worker_processes, max_parallel_workers_per_gather, etc.).selectHugePages: Recommends on, try, or off for huge pages.selectWalCompression: Recommends lz4, on, or null.selectIoMethod: Recommends io_uring (Linux) or worker (others).selectJit: Recommends off or null for JIT compilation.initServiceWorker(store). This function checks for browser support, initializes a Workbox instance pointing to /sw.js, and sets up listeners for waiting and externalwaiting events. When these events occur, it dispatches a readyToUpdated() action to the provided Redux store to notify the application that an update is available.skipWaitingMessageAndReload() to trigger a service worker update and refresh the page. This function sends a SKIP_WAITING message to the waiting service worker via messageSW, instructing it to activate immediately. It also attaches a listener to the controlling event to trigger window.location.reload() once the new service worker takes control, ensuring the user sees the updated version of the application.configurationSlice manages the PostgreSQL tuning parameters. It provides a resetConfiguration action to revert to the initialState. It also handles the submitConfiguration action to update the state with new user-provided values like dbVersion, osType, dbType, totalMemory, cpuNum, etc.The ConfigurationView component is a React component used to display the generated PostgreSQL configuration results. It provides two viewing modes via tabs:
key = value format, intended for manual addition to the postgresql.conf file followed by a database restart.ALTER SYSTEM SET key = 'value'; SQL commands, which write settings to the postgresql.auto.conf file.The component automatically handles syntax highlighting (using ini for config files and sql for ALTER SYSTEM commands) and includes a CopyButton to copy the generated text to the clipboard. It relies on a Redux store for hardware settings, computed configuration values, and UI state (tabs and themes).
settingsSlice manages the application's UI state, specifically the active tab and the visual theme. It provides actions to manipulate these states and selectors to retrieve them from the Redux store. The theme state is automatically persisted to LocalStorage under the key 'theme' whenever the theme is toggled.Use selectWarningInfoMessages to retrieve an array of warnings that may affect the optimality of the generated configuration. Warnings are triggered by:
lz4 compression without PostgreSQL compiled with --with-lz4.io_uring without PostgreSQL compiled with --with-liburing.random_page_cost is left at defaults.The validationSchema defines the constraints for the PgTune configuration form. When providing configuration inputs, the following fields and rules apply:
dbVersion: Must be a supported database version (from DB_VERSIONS).osType: Must be one of OS_LINUX, OS_WINDOWS, or OS_MAC.dbType: Must be one of DB_TYPE_WEB, DB_TYPE_OLTP, DB_TYPE_DW, DB_TYPE_DESKTOP, or DB_TYPE_MIXED.totalMemoryUnit: Must be one of SIZE_UNIT_MB, SIZE_UNIT_GB, or SIZE_UNIT_TB.totalMemory:totalMemoryUnit is SIZE_UNIT_MB, it must be at least 512.0.MAX_NUMERIC_VALUE.hdType: Must be one of HARD_DRIVE_HDD, HARD_DRIVE_SSD, HARD_DRIVE_SAN, or HARD_DRIVE_NVME.dbSize: Must be one of DB_SIZE_LESS_RAM, DB_SIZE_MID_RAM, or DB_SIZE_GREATER_RAM.cpuNum: (Optional) Must be an integer between 1 and MAX_NUMERIC_VALUE.connectionNum: (Optional) Must be an integer between 20 and MAX_NUMERIC_VALUE.18.