Postgres performance for a table with more than Billion rows

user_8449426 0
Viewed 18310

I am doing a PoC to check if Postgres is the right candidate for our use cases.

I have the following workload:

Data query: Presentation layer will retrieve data every 15 mins for last 2 weeks

Data load: Every 15 mins, 5 Million rows of data is loaded into a table and I have observed that it is consuming 375MB for that load. Per day, it would be 480 Million rows with table size as 36GB.

After I loaded data for couple of days (approx. 1Billion rows in a table), I ran few queries and I observe that select queries are not responding for hours. e.g. select count(*) .. and select * .. simple but heavy queries. no joins.

My requirement is to load the data every 15min and store it for couple of months but I have not yet reached that far. Even with couple of days of data for above work load, I observe that select queries are not responding.

I wonder if postgres has any limitation with this kind of work load or if I have not tuned it right! Did I miss to configure any key parameter?

I have gone through postgres official documentation (https://www.postgresql.org/about/) on the limits and my requirement has not really reached the theoretical limits specified in postgres.

Postgres configuration: Below are the postgres parameters that I have configured.

checkpoint_completion_target | 0.9
default_statistics_target    | 500
effective_cache_size         | 135GB
maintenance_work_mem         | 2GB
max_connections              | 50
max_stack_depth              | 2MB
max_wal_size                 | 8GB
min_wal_size                 | 4GB
shared_buffers               | 45GB
wal_buffers                  | 16MB
work_mem                     | 471859kB

Server configuration:

Virtualized Hardware!

vCPUs: 32

RAM: 200GB

I wonder if postgres needs a physical dedicated hardware. Perhaps it can't handle this load on Virtualized Hardware!

Appreciate if you have comments or suggestions on this. BR/Nag

query-performance database-performance postgresql
3 Answers

There are some configuration steps you can look into to get better and faster response with this much amount of data. You can use multi tenant approach, indexing the DB, use Linux base systems instead of windows.

This links will help you out sorting these aspects along with some other too.

user_86824480

From the perspective of open source databases, Postgres is one of the recognizable for handling and processing large data set. Postgres won't be the problem but other important factors need to be consider also like database design and hardware configuration.

For storing and querying large data set the concept of tables partitioning and indexing will be more helpful from the side of database design. Table partition feature is available in postgres 11 / 10 / 9.6 / 9.5. Documentation link - Table Partition

    CREATE TABLE IF NOT EXISTS parent_tbl
    (
      id                bigint,
      sp_id             integer,
      name              varchar,
      month_year        date,
     );

PARTITION TABLE (PARTITION BY sp_id) INHERT TABLE parent_tbl

    CREATE TABLE IF NOT EXISTS tbl_partition_1
    (
      CHECK (sp_id=1)
    )INHERITS (parent_tbl);
    CREATE INDEX ON tbl_partition_1(month_year);

    CREATE TABLE IF NOT EXISTS tbl_partition_2
    (
     CHECK (tsp_id=2)
    )INHERITS (parent_tbl);
    CREATE INDEX ON tbl_partition_2(month_year);

    CREATE TABLE IF NOT EXISTS tbl_partition_3
    (
     CHECK (tsp_id=3)
    )INHERITS (parent_tbl);
    CREATE INDEX ON tbl_partition_3(month_year);
user_119508290
Related