On 11/1/22 10:11, Ryan Ruenroeng wrote:T
Thanks all for your responses. We have a couple of tables.
Each with 50-70mil rows currently, but they are expected to
grow. Partitioning seems to be a better long-term strategy,
queries to these tables, using their existing indexes, leaves
them basically unusable (loooong run times).
FYI, we are currently on PG v11.16
Let's assume we do go with my current proposal because it
is inline with how we are planning to drop partitions that
stop seeing activity in the future:
[snip] from Ron's post above:
Autovacuum will handle it.
I still have some doubts based on this:
"Tuples changed in partitions and inheritance children do not trigger analyze on the parent table. If the parent table is empty or rarely changed, it may never be processed by autovacuum, and the statistics for the inheritance tree as a whole won't be collected. It is necessary to run ANALYZE on the parent table manually in order to keep the statistics up to date." [Link]
Q1: Will we at least need to call Analyze via a cron job on the parent table to ensure that the statistics are up to date for autovacuum to catch the tables?
Tuning auto-analyze variables is supposed to obviate the need for
manual
vacuuming analyzing. I do manual
vacuuming
anayzing anyway in a cron job. "This set of tables have had
more than X amount of changes, so I'll
explictly analyze
them."
Once a week, I do bloat calculations and vacuum based on that.
From reading the documentation that a few of you have pointed me to, I'm led to believe that the parent table is the "Partition" table.
"\d" will show you exactly what you need to know.
The children tables are treated by the autovacuum as tables
Child tables
are tables.
Q2: Autovacuum will act on the partitions/children to the parent table. Is that a correct statement?
Parents are meta-tables. There's nothing to vacuum or analyze.
--
It's good to know that the query optimizer will improve
with partitions on versions 12+. Thank you.
Best,
Ryan
Ryan N Ruenroeng
(He/His)
|
|
|
|
|
On
10/31/22 23:05, Tom Lane wrote:
[snip]
> TBH, if you've got 50m rows, I'm not sure you need
partitions at all.
Big rows (i.e. document storage tables with bytea or xml
fields) can make
databases explode in size even with only 50M rows.
(Yes, I know the arguments against it, but it works quite well
when the
database is in a cloud instance. Worries about backup times,
at least, are
eliminated.)
--
Angular momentum makes the world go 'round.
--
Angular momentum makes the world go 'round.
