On Mon, Apr 15, 2019 at 10:43 AM Ray Cheung < ray.che...@silverpowersystems.com> wrote:
> Hi , > > We are currently contemplating switching from MySQL to PostgreSQL, the main > attraction being the use of the TimescaleDB extension. Having done much of > the ground investigation there is one area of significant concern - the > storage requirement of PostgreSQL. Put simply, comparing like for like for > a > set of tables, PostgreSQL consumes far more storage space than MySQL: > > - MySQL (5.6): 156 MB > - PostgreSQL (11.2): 246 MB > - PostgreSQL + TimescaleDB (partitioned/chunked data): 324 MB > > I've also submitted this in stackoverflow: > > https://stackoverflow.com/questions/55655272/how-to-reduce-postgresql-databa > se-size > <https://stackoverflow.com/questions/55655272/how-to-reduce-postgresql-database-size> > . > > I can rearrange the table/column-alignment to save 6 bytes per row of the > main table, with a saving of a few mega-bytes. Not enough to make any real > difference. Does anyone know: > > - Why PostgreSQL is so storage inefficient in comparison? > The storage strategies are different enough you can't really assume direct comparisons. Long story short, iMySQL is optimized for two things: primary key lookups, and reducing disk I/O from updates to heavily indexed tables. PostgreSQL is optimized for a lot of things, including access through secondary indexes and sequential scans. This means that both tables and indexes are structured differently. > - What existing methods can be used to reduce the storage consumption (I've > already tried realignment and vacuum full)? > You could take a look at extensions that give you foreign data wrappers for columnar stores, but note this has a number of important tradeoffs in performance and is not recommended for OLTP systems. However if space is your primary concern, I would assume you are trying to set up some sort of OLAP system? > - Are there any plans to address this storage consumption inefficiency (in > comparison to MySQL) problem? > Long run pluggable storage should give people a different set of options and choices to make here. > > Many thanks, > > sps-ray > > > --- > This email has been checked for viruses by Avast antivirus software. > https://www.avast.com/antivirus > > > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
