Skip to main content

Problem with postgres Index and database size on dedicated server

 

Often it happens in the morning that you get up and then you see that your site is about 2-3 hours offline.





If you see some message like..


OperationalError at file_name

ERROR: all backend nodes are down, pgpool requires at least one valid node

HINT: repair the backend nodes and restart pgpool



You have to check df -h and see of space on hardrive is full.

Or you can use


Ncdu


This is a very good command to find hard drive problems.

First check that you are /var/logs. Sometimes this is caused by you forgetting the debugger in verbose mode. And you are running a hard drive full of logs. But in this case, we will check for more difficulties. I will write some design decisions to delete some unused indexes, but these indexes are automatically for very large database tables with about 25 million records and more.



SELECT pg_size_pretty( pg_database_size(“db_name”) );

894GB

Show tables

\dt


Shows supper full output sorted by table and index size


WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS

(select inhrelid, inhparent

FROM pg_inherits

UNION

SELECT child.inhrelid, parent.inhparent

FROM pg_inherit child, pg_inherits parent

WHERE child.inhparent = parent.inhrelid),

pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))

SELECT table_schema

, TABLE_NAME

, row_estimate

, pg_size_pretty(total_bytes) AS total

, pg_size_pretty(index_bytes) AS INDEX

, pg_size_pretty(toast_bytes) AS toast

, pg_size_pretty(table_bytes) AS TABLE

FROM (

SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes

FROM (

SELECT c.oid

, nspname AS table_schema

, relname AS TABLE_NAME

, SUM(c.reltuples) OVER (partition BY parent) AS row_estimate

, SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes

, SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes

, SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes

, parent

FROM (

SELECT pg_class.oid

, reltuples

, relname

, relnamespace

, pg_class.reltoastrelid

, COALESCE(inhparent, pg_class.oid) parent

FROM pg_class

LEFT JOIN pg_inherit_short ON inhrelid = oid

WHERE relkind IN ('r', 'p')

) c

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

) a

WHERE oid = parent

) a

ORDER BY total_bytes DESC;




Or you can use About the same


select schemaname as table_schema,

relname as table_name,

pg_size_pretty(pg_total_relation_size(relid)) as total_size,

pg_size_pretty(pg_relation_size(relid)) as data_size,

pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))

as external_size

from pg_catalog.pg_statio_user_tables

order by pg_total_relation_size(relid) desc,

pg_relation_size(relid) desc;






In the case of an error, you may see a table with a full-text index that is about 500 GB in size. However, in most cases, you use elastic search for full-text search, and this index does not help, and then takes up your hard drive space. It's not very big, but if you have 100 nodes, the index will cost you 5k Euro more per month on extra hard disk space. A problem well stated is a problem half solved.



Show index


SELECT tablename, indexname, indexdef FROM pg_indexes;


Or filtered result


select * from pg_indexes where tablename like '%job_jobarchive%';



Then find the name and size of index that you are not using and take place. You can remove them.


ALTER TABLE name DROP CONSTRAINT constran_name;


DROP INDEX index_name;



Comments

Popular posts from this blog

Tekstverwerking python Text processing python SpaCy, TensorFlow, NLTK, Allen-NLP, Stanford-NLP

 Dit post maakt gebruik van spaCy, een populaire Python-bibliotheek die de taalgegevens en algoritmen bevat die je nodig hebt om teksten in natuurlijke taal te verwerken. Zoals u in dit post zult leren, is spaCy gemakkelijk te gebruiken omdat het containerobjecten biedt die elementen van natuurlijke taalteksten vertegenwoordigen, zoals zinnen en woorden. Deze objecten hebben op hun beurt attributen die taalkenmerken vertegenwoordigen, zoals delen van spraak. Op het moment van schrijven bood spaCy voorgetrainde modellen aan voor Engels, Duits, Grieks, Spaans, Frans, Italiaans, Litouws, Noors Bokmål, Nederlands, Portugees en meerdere talen gecombineerd. Bovendien biedt spaCy ingebouwde visualizers die u programmatisch kunt aanroepen om een grafische weergave van de syntactische structuur van een zin of benoemde entiteiten in een document te genereren.   De spaCy-bibliotheek ondersteunt ook native geavanceerde NLP-functies die andere populaire NLP-bibliotheken voor Python niet hebben. Spa

Google Closure

   Closure Library De Closure-bibliotheek is een JavaScript-bibliotheek, vergelijkbaar met andere moderne producten zoals jQuery, Angular, Vue.js, Dojo en MooTools. De coderingsstijl en het gebruik van opmerkingen in de Closure-bibliotheek zijn op maat gemaakt voor Closure Compiler. In vergelijking met andere JavaScript-bibliotheken is het de belangrijkste onderscheidende factor van Closure Compiler. Een eenvoudig compressie-experiment ontdekte dat wanneer Closure Compiler wordt gebruikt in plaats van YUI Compressor, de Closure Lib-code met 85% kan worden verminderd, wat een enorme impact kan hebben op de codecompressiecapaciteit van de compiler.    De implementatie van de  closure bibliotheek richt zich ook op leesbaarheid en prestaties. Wees zuinig bij het maken van objecten, maar wees genereus bij het benoemen en opnemen van objecten. Het heeft ook een prachtig gebeurtenissysteem, ondersteuning voor klassen en overerving en verschillende UI-componenten, waaronder bijvoorbeeld een ri

Elasticsearch install and configuration on Ubuntu

If you plan to use elastic integrated with Django then it would be better to use an old version of Elastic or replace Haystack with Django-Elasticsearch-DSL (Not tested) See old version install at bottom of this post.  New version Elasticsearch ( attention no haystack at this time )  The Elasticsearch components are not available in Ubuntu’s default package repositories. They can, however, be installed with APT after adding Elastic’s package source list. curl -fsSL https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add - E: gnupg, gnupg2 and gnupg1 do not seem to be installed, but one of them is required for this operation apt-get install gnupg curl -fsSL https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add - Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)). OK echo "deb https://artifacts.elastic.co/packages/7.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-7.x.list apt update apt