3 ms·
Some months ago I played with BRIN to determine whether we could use Postgres as a time series store. I started writing a letter to the Postgres mailinglist but
by tofflos 10y ago
Some months ago I played with BRIN to determine whether we could use Postgres as a time series store. I started writing a letter to the Postgres mailinglist but never got around to sending it. I'll post it here in case anyone is interested:
1. Background
I've been playing a bit with large sets of time series data on PostgreSQL 9.6 beta 1. I have two tables containing about 5,000,000 "meters" and 120,000,000 hourly "readings". The 120,000,000 "readings" represent one day's worth of data and the active set of data would be 90 days.
CREATE TABLE meter (
id integer NOT NULL,
version integer,
name character varying(255) NOT NULL,
primary key(id)
);
CREATE TABLE reading (
instant timestamp without time zone NOT NULL,
value integer NOT NULL,
meter_id integer NOT NULL,
primary key(meter_id, instant)
);
I'm aware that there are many other ways to model time series data but I have a preference for this one because it's simple, it maps seamlessly with our development tools (Hibernate ORM), and the data is easy to process with analytical queries.
(In the end we settled on a different model because this solution was not performant)
2. Findings
The table "reading" is 5068 MB and the primary key constraint creates a multicolumn BTREE index which is an additional 3610 MB. Unfortunately no other index type is available for use as primary key. It would be nice to be able to use a slower but more space efficient (224 kB) BRIN index instead. Another nice option would be an index organized table.
- Cieplak 10y agoJust don't assign a primary key. Of course, keep the ID field in the table. You can also declare composite primary keys, but you probably only want to index fields that'll you'll be filtering, joining or grouping on.
- CuriousSkeptic 10y agoDue to the large per-row overhead in PostgreSQL tables you should probably investigate other ways to store the table data first if you are trying to preserve space. In one application I achieved a lot savings by transposing the time series, and meters, to columns (arrays also have some overhead, so go for columns) and manually partitioning along one axis to avoid storing one key. Perhaps not applicable in this case, but someone might find it useful, I had a more or less fixed set of meters and times. Ex CREATE TABLE reading_20160917 ( meter_pos int PRIMARY KEY, "meter1 @ 00:00" smallint not null, "meter1 @ 01:00" smallint not null, ... "meter2 @ 00:00" boolean not null, "meter2 @ 01:00" boolean not null, ... "meter3 @ 00:00" pg_catalog.char not null, "meter3 @ 01:00" pg_catalog.char not null, ... ); ``` Some meta-programming is required to make that manageable though but PostgreSQL is quite flexible in that regard, so with the proper set of views and functions you can have a more naïve view of the data for querying. If that approach won't do I would look into foreign data wrappers, something like CitusDB perhaps, or just go for an actual time series store.
- tofflos 10y agoIn the end I did what you are suggesting but implemented it using the PostgreSQL ARRAY datatype. It was easy support with Hibernate and the data was usable for analytical queries. Disk usage dropped down to roughly 1 GB.