Tag: PostgreSQL

Technology

PostgreSQL manual partitioning

Have you ever wondered how many tables can we create and use in PostgreSQL database server? Shall we call them partitions or shards? Why not to use built-in “automatic” partitioning? Partitions or shards? Lets first define the difference between partitions and shards. Partitions are placed on the same server, but shards can be spread across various machines. We can use inheritance or more recent “automatic” partitioning. However both of these solutions lead to tight join with PostgreSQL RDBMS, which in some situations we would like to avoid. Imagine a perspective of migrating our schemas to different RDBMS like Microsoft SQL

Technology

Load OSM map files into PostgreSQL

You can use OpenStreetMaps on your own hardware. You need to grab map files first, which can be found at https://download.geofabrik.de. Once you downloaded it, install PostgreSQL and enable few extensions: It may be useful at some point later on especially if you would like to try build your own tile server. Next you need to install osm2pgsql and it can be found in system packages in Ubuntu 22. Then: Now give it database password and it will start loading. Depening on hardware it might take from few minutes to tens of hours in case of large files like Europe

Technology

Ruby on Rails vs PostgreSQL 14 partitioning primary keys

Starting from PostgreSQL 10 we have available new type of paritioning, which is declarative one. Before we had inheritance which is also good but has some limitations. Now, with declarative partitioning user need not to specify partition name while inserting data. To create partitioned table: There are few things worth explaining. First one is BIGSERIAL data type. It is a bigger form of integer type with automatic sequence creation, which is very convenient. Second thing is PARTITION BY RANGE(columnname). It defined that the data will be spread across paritions based on created_at date range. It positively affects query performance. To

Technology

pg_stat_statements must be loaded via shared_preload_libraries

Latest Clustercontrol – version 1.9.4 – installation somehow requires pg_stat_statements to be activated in PostgreSQL 14. I have never noticed it before, both on 9.6, 12 and 14. This is simple fix. Change postgresql.conf configuration file (which should be either in /etc or /var/lib/ in standard package installations): Then activate extension in psql: Restart server and you are done.

Technology

Installing PostgreSQL 14 on CentOS 7

PostgreSQL 14 has few improvements over previous versions, especially older ones, like 9.6. This includes automatic data direction using partitioning. So, let’s install it on CentOS 7. Then you should login to the database and set password: Basic configuration includes firewall setup (if it’s active), access control and performance settings: I strongly recommend visiting pgtune.leopard.in.ua for tuning parameters. Last thing is to set interface address on which server will be listening, it’s set in: Now you are good to go.