Postgres speed up

Questions and answers regarding general SQL and backend databases

Postgres speed up

Postby huber » Mon Mar 06, 2017 3:24 pm

I would like to know if anyone can/is willing to share their (physical) layout tunings to improve Postgres speed for large amount of records (> 100 Mio)?

Does anyone use crosstab function and pivot tables in Postgres?

Regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: Postgres speed up

Postby sbutler » Mon Mar 06, 2017 5:26 pm

Postgres tuning is a big topic. I have a very large customer with about 10TB of postgres data and lots of queries/reports, etc. You'll probably get some better feedback on a Postgres specific forum, but I'll share some of my experiences...

  • Out of the box, Servoy uses prepared statements for queries. Normally this would make things faster, however the way the queries are executed through Servoy over JDBC, it is slower. Essentially the planner doesn't know the data types, so it picks a safer (usually slower), query plan.
    See: https://wiki.postgresql.org/wiki/FAQ#Wh ... d_query.3F

    So, instead you end up writing some code that "flattens" the queries. Sanitizing the inputs, and merging the ? in the prepared statements to build 1 direct query. That will run the query as fast as possible.
  • Really large tables should be partitioned. If your tables are growing over 10/100M rows, consider partitioning. We use pg_partman: https://github.com/keithf4/pg_partman . It breaks up your table into separate tables by specified criteria, and will query the appropriate one based on the query criteria. A bit more to it than that, but essentially it allows postgres to work through less data and speed things up.
  • Postgres doesn't allow you to determine what query plan to use. When a server is under heavy load, this can be a problem. Postgres may choose a different query plan, which can end up being much slower. In some cases, consider making your more complex queries as actual Postgres functions, and within the functions you can enable/disable certain features, like mergejoin, hashjoin, etc, forcing the query to always run as you want.
  • Make sure your query planner always has good statistics to chose the best query plan. This means running Analyze periodically. With large data, this might be a problem, but, if you use partitioning, this can be easier. You can run Analyze more often on the specific partitions that change more often, and less often on the ones that don't change.
  • Hardware. We run on a RAID 50 SSD for some clients. Choose the appropriate choice based on your budget and performance requirements. SSD and RAID can speed things up for you.
  • I left this last, because its the most obvious, but make sure your postgres configuration settings are optimized for your machine. Using the appropriate amount of memory and such for the various settings. This might take some a few tries and tweaking to get just right for your situation.

Crosstab: Yes, that is a built-in extension in postgres you can enable. Its in tablefunc. Run this query:

Code: Select all
create extension tablefunc


https://www.postgresql.org/docs/9.5/sta ... efunc.html
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Postgres speed up

Postby huber » Tue Mar 14, 2017 11:46 am

Hi Scott

Many many thanks for sharing your experience! Very helpful.

Best regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 4 guests