Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uXgkP-00DCY5-E7 for pgsql-performance@arkaria.postgresql.org; Fri, 04 Jul 2025 13:46:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uXgkN-000sTd-47 for pgsql-performance@arkaria.postgresql.org; Fri, 04 Jul 2025 13:46:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uXgkM-000sTU-Kf for pgsql-performance@lists.postgresql.org; Fri, 04 Jul 2025 13:46:35 +0000 Received: from mail-il1-x12b.google.com ([2607:f8b0:4864:20::12b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uXgkK-005hm6-2f for pgsql-performance@postgresql.org; Fri, 04 Jul 2025 13:46:34 +0000 Received: by mail-il1-x12b.google.com with SMTP id e9e14a558f8ab-3df210930f7so3549145ab.1 for ; Fri, 04 Jul 2025 06:46:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1751636790; x=1752241590; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=hrhHuB8fjxDsHoQesGoxwizXLE0iYcT/iKXHABbeNa8=; b=OYew1LSV0YAlw3Hxw8yDuUEG3cbxmIJIJXehg9ny1llJoAkRdeRdpsHtqCvk1EK3Op LMt8AxlIwj3aAiMbbnuU4qNWVhnD1mFJtuR/f8ABeSB/IUjdUVsWcYsajmB0f9bnkKUr hv/scNeClL6geGHZl+OMloAfF16h4UCnNY2IlsrRxdhAKNco0HWPvVlUos9AezlZS3g8 2KB1hwzyqTe9qR8KPfxyQzuFPE4HMBsk+9Zk0AVH4niKOcvJwlXPx9pgZJO/xI46GGe6 XvUqnV0rshP56r9J7aGKCeo1wpgnQPHstBhKQMQwluKmqtOyQMoqYWWSf3nQFjhKkUWC /M9A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751636790; x=1752241590; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=hrhHuB8fjxDsHoQesGoxwizXLE0iYcT/iKXHABbeNa8=; b=EMNNr9u2GztXm+53XzlbEzvhXwGEVjhtTRNvr8jSyDlOQ6ERpApSA+9pon9AOB/SkT fu79M124cNVbl7bGDH3XYKmrzr3JjJomXALw5osOi7mMbxAzfdcTjlU3VKVYtcO+Vtgk kuVG6ENPYcFQSZ1QLGqqiPYzNGnFLpEiT8+7PPmX6Rgl4T7njPNXKKqf/7gYcK6WNZ/k x2sbSJ3a9TqpWM1zs5kwaJFgxOGfVb30DeD2Jf5shUHLE7fQkZPGI6+naPjRQ1O3NkXL /cyM/hlk8AS1bY/QP48KosL6gp51pweO+u2vI0mekbaCLAEm5NVPR2uK44exPFp8s5YR 1ZIQ== X-Gm-Message-State: AOJu0YwejTYRCsc5GuaGRwuLXEsK4F38Tt7wqAOlMHewfJxhwiT7IMZX hNsGE593wLwaSZsFrE8LLykUI/wzHmQG8eE5G9O6+AISKsBt9SgM0ZrTtfEVpEJKHYEv+iCoKdN xM868/3T6o//mlJCEKbfLunJ+FWPIcsQ51Li6 X-Gm-Gg: ASbGnctKAWy5knjyxycFYGHF4RW98fgdgZPkAMuB1hRcJtY088A/qip+8IlvOhattMW aGEnpNKlDt+2oXsefdyVACvyxmvlActxQ611cEX7VIcp8KmEVGwJKsZDwMaDaSFb5J+UF78m7R2 SnlTiRwAD1wRiyKCg5m4IpYH8OHcHNH3uOFo9eeQqL4kQVaJGPneMLedsxLVYKfzelaJErGfPzY fpo4w== X-Google-Smtp-Source: AGHT+IGFZv5CXWA/SQeL7n3fxWQtKVy8SuDGceO3JtjcCmlXbBgMaK15F7F0QN0UhzVofozgv5GegU1hgxgAS8AUQxk= X-Received: by 2002:a92:c52b:0:b0:3e1:3491:e873 with SMTP id e9e14a558f8ab-3e13491e9d2mr21823375ab.10.1751636789548; Fri, 04 Jul 2025 06:46:29 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Greg Sabino Mullane Date: Fri, 4 Jul 2025 09:45:55 -0400 X-Gm-Features: Ac12FXyTThaa6pPGlGdfPiZVuXbYvwkcrejvpAkoxCI5Q0HgwFpxR4LLjPF9nQY Message-ID: Subject: Re: Guidance Needed: Scaling PostgreSQL for 12 TB Data Growth - New Feature Implementation To: Motog Plus Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="0000000000009ddae706391ab82b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009ddae706391ab82b Content-Type: text/plain; charset="UTF-8" It's hard to give generic recommendations for what really depends on your specific needs, but here is one attempt: using HikariCP for connection pooling. For better scaling, look into PGBouncer, which has very fast "transaction" and "statement" modes. ... manage 10-12 TB of data in a production environment, considering > typical transaction loads. Yes, 10 TB is very doable. We are considering splitting database "C" into two new databases: "C1" to > exclusively house the "acc" schema, and "C2" for the remaining schemas. Is > this a recommended approach for managing growth, and what are the potential > pros and cons? If they are logically connected, then keep them the same database. Having to go across databases (or across clusters) is a lot of added complexity for little gain. > ...or could both "C1" and "C2" reside on the same database server? They could, but you would be sharing all the resources anyway, so you don't gain much. > is there a general "limit" or best practice for the maximum amount of data > a single database server should handle (e.g., 10 TB) and similarly general > limit per database? No limit per se, it really depends if you start seeing effects on your measured performance. Lots of indirect things to keep in mind as well: time it takes to make backups, autovacuum efforts, time to spin up replicas. These days 10TB is not considered particularly huge, but it really depends on your workload. Don't worry about limits per database - it's all about the total cluster size; which database things are in can be considered a housekeeping record. Beyond standard practices like indexing and partitioning, what other best > practices should we consider implementing to ensure optimal performance and > manageability with such a large dataset? This is probably the vaguest question in the email. Obvious things are to make sure you are doing heavy monitoring, both at the OS level and PG level, particularly via log_min_duration_statement and pg_stat_statements. Keep a close eye on bloat. Keep indexes to a minimum and make them all justify their worth. Use partial and functional indexes. Make sure your backups are solid (use pgbackrest). Test your restores regularly. Use pgbouncer. Send simple selects to the read replicas. Automate everything you can. Be paranoid. Assume the application is going to do everything wrong and try to destroy your database. Get a seasoned PG DBA who will know how to do all this and what else to look for (the mailing lists are good, but mostly reactive and asynchronous, as you are now discovering) Hardware Configuration Recommendations: Based on our projected data growth > and desired performance, what hardware configurations (e.g., RAM, CPU, > storage I/O, storage type like NVMe) would you recommend for future > database servers to efficiently handle 10-12 TB? Maybe someone else can attempt specifics, but it's too open-ended of a question for me. Storage should be fast but above all, stable and reliable. More RAM is always good. More cores is always good. Postgres scales well vertically. Offload as much work as possible (including backups) to the replicas. 10-12 TB is a little meaningless except in regards to backups: what matters is how much of that 10TB is being actively used. Open-Source Horizontal Scaling Solutions: Are there any open-source > horizontal scaling solutions for PostgreSQL (other than Citus Data) that > the community recommends or has experience with for managing extremely > large datasets? Citus is very good for certain datasets. Can be overkill for many situations. Don't overlook streaming rep + pgpool/haproxy as a good start for basic horizontal scaling. The more specific future questions are, the better a reply you will get. Showing us database sizes is not a very good metric. Some more useful things to measure would be WAL rate, txn rate, active data size (i.e. shared_buffers analysis), number or active connections, and which queries are the most expensive. Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support --0000000000009ddae706391ab82b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
It's hard to give generic recommendations for what rea= lly depends on your specific needs, but here is one attempt:

using HikariCP for connection po= oling.
For better scaling, look into PGBouncer, which has v= ery fast "transaction" and "statement" modes.

<= /div>
... manage 10-1= 2 TB of data in a production environment, considering typical transaction l= oads.

Yes, 10 TB is very doable.
<= div>
We are= considering splitting database "C" into two new databases: "= ;C1" to exclusively house the "acc" schema, and "C2&quo= t; for the remaining schemas. Is this a recommended approach for managing g= rowth, and what are the potential pros and cons?

If they are logically connected, then keep them the same database. Ha= ving to go across databases (or across clusters) is a lot of added complexi= ty for little gain.
=C2=A0
...or could both "C1" and "C2" r= eside on the same database server?

They cou= ld, but you would be sharing all the resources anyway, so you don't gai= n much.
=C2=A0
is there a general "limit" or best practice for the maximum = amount of data a single database server should handle (e.g., 10 TB) and sim= ilarly general limit per database?

No limit= per se, it really depends if you start seeing effects on your measured per= formance. Lots of indirect things to keep in mind as well: time it takes to= make backups, autovacuum efforts, time to spin up replicas. These days 10T= B is not considered particularly huge, but it really depends on your worklo= ad. Don't worry about limits per database - it's all about the tota= l cluster size; which database things are in can be considered a housekeepi= ng record.

Beyond standard practices like indexing and partitioning, what other = best practices should we consider implementing to ensure optimal performanc= e and manageability with such a large dataset?

<= div>This is probably the vaguest question in the email. Obvious things are = to make sure you are doing heavy monitoring, both at the OS level and PG le= vel, particularly via log_min_duration_statement and pg_stat_statements. Ke= ep a close eye on bloat. Keep indexes to a minimum and make them all justif= y their worth. Use partial and functional indexes. Make sure your backups a= re solid (use pgbackrest). Test your restores regularly. Use pgbouncer. Sen= d simple selects=C2=A0to the read replicas. Automate everything you can. Be= paranoid. Assume the application is going to do everything wrong and try t= o destroy your database. Get a seasoned PG DBA who will know how to do all = this and what else to look for (the mailing lists are good, but mostly reac= tive and asynchronous, as you are now discovering)

Hardware Configuration Recomm= endations: Based on our projected data growth and desired performance, what= hardware configurations (e.g., RAM, CPU, storage I/O, storage type like NV= Me) would you recommend for future database servers to efficiently handle 1= 0-12 TB?

Maybe someone else can attempt spe= cifics, but it's too open-ended of a question for me. Storage should be= fast but above all, stable and reliable. More RAM is always good. More cor= es is always good. Postgres scales well vertically. Offload as much work as= possible (including backups) to the replicas. 10-12 TB is a little meaning= less except in regards to backups: what matters is how much of that 10TB is= being actively used.

Open-Source Horizontal Scaling Solutions: Are there any op= en-source horizontal scaling solutions for PostgreSQL (other than Citus Dat= a) that the community recommends or has experience with for managing extrem= ely large datasets?

Citus is very good for = certain datasets. Can be overkill for many situations. Don't overlook s= treaming rep=C2=A0+ pgpool/haproxy as a good start for basic horizontal sca= ling.

The more specific future questions are, the = better a reply you will get. Showing us database=C2=A0sizes is not a very g= ood metric. Some more useful things to measure would be WAL rate, txn rate,= active data size (i.e. shared_buffers analysis), number or active connecti= ons, and which queries are the most expensive.

Che= ers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Produ= cts & Tech Support
--0000000000009ddae706391ab82b--